注册 登录
  • 欢迎访问Sharezer Blog

【小工具】Excel导出工具

C# sharezer 520次浏览 已收录 0个评论

功能

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

界面

【小工具】Excel导出工具

过程

新建工程

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,然后添加下以两个事件:

【小工具】Excel导出工具

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());
        }
    }

}


Sharezer , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明【小工具】Excel导出工具
喜欢 (0)
[liangshaoze@sina.cn]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址