【小工具】Excel导出工具

功能

• 读取Excel文档（2003或2007）
• 在表格中列出数据
• 编辑、输入、删除数据
• 导出成json、xml、csv格式

界面

过程

新建工程

Visual Studio中新建一个Windows窗体，基于C#。

选择文件

一种方法是点击按钮触发事件

private void BtnSelect_Click(object sender, EventArgs e)
{
    OpenFileDialog fileDialog = new OpenFileDialog();
    fileDialog.Multiselect = true;
    fileDialog.Title = "请选择文件";
    fileDialog.Filter = "Excel|*.xls; *.xlsx";
    fileDialog.ValidateNames = true; //文件有效性验证ValidateNames 验证用户输入是否是一个有效的Windows文件名
    fileDialog.CheckFileExists = true; //验证路径有效性
    fileDialog.CheckPathExists = true; //验证文件有效性

    if (fileDialog.ShowDialog() == DialogResult.OK)
    {
        ReadExcelFile(fileDialog.FileName);
    }
}

或另一种操作是直接拖拽文件到窗口上

首先把form的AllowDrop设置为true，然后添加下以两个事件：

private void form1_DragEnter(object sender, DragEventArgs e)
{
    if (e.Data.GetDataPresent(DataFormats.FileDrop))
    {
        e.Effect = DragDropEffects.Link;
    }
    else
    {
        e.Effect = DragDropEffects.None;
    }
}

private void form1_DragDrop(object sender, DragEventArgs e)
{
    string path = ((Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString();
    ReadExcelFile(path);
}

读取文件

引用NPOI库

最新库可以读03和07的文档

/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file)
{
    DataTable dt = new DataTable();
    try
    {
        using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            //表头
            IRow header = sheet.GetRow(sheet.FirstRowNum);
            List<int> columns = new List<int>();
            for (int i = 0; i < header.LastCellNum; i++)
            {
                object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                if (obj == null || obj.ToString() == string.Empty)
                {
                    dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    //continue;
                }
                else
                    dt.Columns.Add(new DataColumn(obj.ToString()));
                columns.Add(i);
            }

            //数据
            for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
            {
                DataRow dr = dt.NewRow();
                bool hasValue = false;
                foreach (int j in columns)
                {
                    dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                    if (dr[j] != null && dr[j].ToString() != string.Empty)
                    {
                        hasValue = true;
                    }
                }

                if (hasValue)
                {
                    dt.Rows.Add(dr);
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error");
    }
    return dt;
}

/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(string file)
{
    DataTable dt = new DataTable();
    try
    {
        using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
            ISheet sheet = xssfworkbook.GetSheetAt(0);

            //表头
            IRow header = sheet.GetRow(sheet.FirstRowNum);
            List<int> columns = new List<int>();
            for (int i = 0; i < header.LastCellNum; i++)
            {
                object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                if (obj == null || obj.ToString() == string.Empty)
                {
                    dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    //continue;
                }
                else
                {
                    if (dt.Columns.IndexOf(obj.ToString()) >= 0)
                    {
                        throw new Exception("列表名重复: " + obj.ToString());
                    }
                    dt.Columns.Add(new DataColumn(obj.ToString()));
                }
                columns.Add(i);
            }

            //数据
            for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
            {
                DataRow dr = dt.NewRow();
                bool hasValue = false;
                foreach (int j in columns)
                {
                    dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                    if (dr[j] != null && dr[j].ToString() != string.Empty)
                    {
                        hasValue = true;
                    }
                }

                if (hasValue)
                {
                    dt.Rows.Add(dr);
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }


    return dt;
}

显示数据

把读取出来的Excel数据保存到DataTable中，在form中增加一个DataGridView，绑定DataTable到DataGridView的DataSource

导出数据

void ConvertToJson(string path, DataTable data, Encoding encoding)
{
    try
    {
        if (File.Exists(path))
        {
            File.Delete(path);
        }

        StringBuilder jsonBuilder = new StringBuilder();
        jsonBuilder.Append("{\"");
        jsonBuilder.Append(data.TableName);
        jsonBuilder.Append("\":[");
        //jsonBuilder.Append("[");
        for (int i = 0; i < data.Rows.Count; i++)
        {
            jsonBuilder.Append("{");
            for (int j = 0; j < data.Columns.Count; j++)
            {
                jsonBuilder.Append("\"");
                jsonBuilder.Append(data.Columns[j].ColumnName);
                jsonBuilder.Append("\":\"");
                jsonBuilder.Append(data.Rows[i][j].ToString());
                jsonBuilder.Append("\",");
            }
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("},");
        }
        jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
        jsonBuilder.Append("]");
        jsonBuilder.Append("}");

        //生成Json字符串
        string json = jsonBuilder.ToString();
        //写入文件
        using (FileStream fileStream = new FileStream(path, FileMode.Create, FileAccess.Write))
        {
            using (TextWriter textWriter = new StreamWriter(fileStream, encoding))
            {
                textWriter.Write(json);
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error");
    }
}

void ConvertToXml(string path, DataTable data, Encoding encoding)
{
    try
    {
        //如果文件DataTable.xml存在则直接删除
        if (File.Exists(path))
        {
            File.Delete(path);
        }
        XmlTextWriter writer = new XmlTextWriter(path, encoding);
        writer.Formatting = System.Xml.Formatting.Indented;
        //XML文档创建开始
        writer.WriteStartDocument();
        writer.WriteStartElement(data.TableName); //DataTable开始
        //按行各行
        for (int j = 0; j < data.Rows.Count; j++)
        {
            writer.WriteStartElement("item");
            //打印各列
            for (int k = 0; k < data.Columns.Count; k++)
            {
                writer.WriteAttributeString(
                    data.Columns[k].ToString(), data.Rows[j][k].ToString());
            }
            writer.WriteEndElement();
        }
        writer.WriteEndElement(); //DataTable结束
        writer.WriteEndDocument();
        writer.Close();
        //XML文档创建结束
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error");
    }
}

void ConvertToCsv(string path, DataTable data, Encoding encoding)
{
    if (File.Exists(path))
    {
        File.Delete(path);
    }

    //读取数据表行数和列数
    int rowCount = data.Rows.Count;
    int colCount = data.Columns.Count;

    //创建一个StringBuilder存储数据
    StringBuilder stringBuilder = new StringBuilder();

    //读取数据
    for (int i = -1; i < rowCount; i++)
    {
        for (int j = 0; j < colCount; j++)
        {
            if (i == -1)//-1为列名
                stringBuilder.Append(data.Columns[j] + ",");
            else
                stringBuilder.Append(data.Rows[i][j] + ",");//使用","分割每一个数值
        }
        //使用换行符分割每一行
        stringBuilder.Append("\r\n");
    }

    //写入文件
    using (FileStream fileStream = new FileStream(path, FileMode.Create, FileAccess.Write))
    {
        using (TextWriter textWriter = new StreamWriter(fileStream, encoding))
        {
            textWriter.Write(stringBuilder.ToString());
        }
    }

}