C# 使用NPOI进行Excel的导入导出

C# 使用NPOI进行Excel的导入导出

将Excel的数据转换到DataTable

/// <summary>
/// Excel换为DataTable
/// </summary>
/// <param name="FileName">文件名称</param>
/// <param name="sheetName">表名，默认取第一张</param>
/// <returns>DataTable</returns>
private static DataTable ExcelToTable(string FileName, string sheetName)
{
    DataTable? dt = new DataTable();
    Stream stream = File.Open(FileName, FileMode.Open);
    IWorkbook? workbook = null;
    try
    {
        //表格版本判断
        if (Path.GetExtension(FileName).Equals(".xls"))
        {
            workbook = new HSSFWorkbook(stream);
        }
        else if (Path.GetExtension(FileName).Equals(".xlsx"))
        {
            workbook = new XSSFWorkbook(stream);
        }
        ISheet sheet = null;
        //获取工作表 默认取第一张
        if (string.IsNullOrWhiteSpace(sheetName))
            sheet = workbook.GetSheetAt(0);
        else
            sheet = workbook.GetSheet(sheetName);

        if (sheet == null)
            return null;

        #region 获取表头
            IRow headerRow = sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum;
        for (int j = 0; j < cellCount; j++)
        {
            ICell cell = headerRow.GetCell(j);
            if (cell != null)
            {
                dt.Columns.Add(cell.ToString());
            }
            else
            {
                dt.Columns.Add("");
            }
        }
        #endregion

            #region 获取内容
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        //判断单元格是否为日期格式
                        if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
                        {
                            if (row.GetCell(j).DateCellValue.Year >= 1970)
                            {
                                dataRow[j] = row.GetCell(j).DateCellValue.ToString();
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();

                            }
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                }
                dt.Rows.Add(dataRow);
            }
        #endregion

    }
    catch (Exception)
    {
        dt = null;
    }
    return dt;
}

将DataTable转换成Excel(xls)数据并保存

/// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="FileName">路径</param>
/// <param name="tablename">表名</param>
public void TableToExcelForXLS(DataTable dt, string FileName, string? tablename = null)
{
    try
    {
        HSSFWorkbook hssfworkbook = new HSSFWorkbook();
        if (tablename == null)
            tablename = "Sheet1";
        ISheet sheet = hssfworkbook.CreateSheet(tablename);
        int n = 0;  //控制表名,在第一行(列名前面一行)显示

        if (dt.TableName != null && dt.TableName != "")
        {
            //表名
            //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
            //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count));
            IRow rowtitle = sheet.CreateRow(0);

            ICell celltitle = rowtitle.CreateCell(0);
            celltitle.SetCellValue(dt.TableName);
            //设置单元格样式时需要注意，务必创建一个新的样式对象进行设置，否则会将工作表所有单元格的样式一同设置，它们应该共享的是一个样式对象
            ICellStyle style = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            //新建一个字体样式对象
            IFont font = hssfworkbook.CreateFont();
            //设置字体加粗样式
            font.FontHeight = short.MaxValue;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);
            //将新的样式赋给单元格
            celltitle.CellStyle = style;
            //添加表名之后置为1
            n = 1;
        }
        //列宽数组(自适应列宽)
        int[] ColumnWidthArray = new int[dt.Columns.Count];
        //表头
        IRow row = sheet.CreateRow(n);
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            ICell cell = row.CreateCell(i);
            cell.SetCellValue(dt.Columns[i].ColumnName);
            //可以获取中文长度，中文占2个字符
            ColumnWidthArray[i] = System.Text.Encoding.Default.GetBytes(dt.Columns[i].ColumnName).Length;
        }

        //数据
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            IRow row1 = sheet.CreateRow(i + n + 1);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ICell cell = row1.CreateCell(j);
                String cellValue = dt.Rows[i][j].ToString();
                cell.SetCellValue(cellValue);
                if (System.Text.Encoding.Default.GetBytes(cellValue).Length > ColumnWidthArray[j])
                {
                    ColumnWidthArray[j] = System.Text.Encoding.Default.GetBytes(cellValue).Length;
                }
            }
        }
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sheet.SetColumnWidth(i, (ColumnWidthArray[i] + 2) * 256);
        }
        //转为字节数组
        MemoryStream stream = new MemoryStream();
        hssfworkbook.Write(stream);
        var buf = stream.ToArray();

        //保存为Excel文件
        FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
        fs.Write(buf, 0, buf.Length);
        fs.Flush();
    }
    catch (Exception)
    {
        return;
    }
}

方法使用示例

DataTable dataTable = new DataTable();
private void excel导入_Click(object sender, EventArgs e)
{
    OpenFileDialog openFileDialog = new OpenFileDialog();
    if (openFileDialog.ShowDialog() == DialogResult.OK)
    {
        dataTable = ExcelToTable(openFileDialog.FileName, "Sheet1");
        DG.DataSource = dataTable;
    }
}

private void Excel导出_Click(object sender, EventArgs e)
{
    SaveFileDialog saveFileDialog = new SaveFileDialog();
    saveFileDialog.Filter = "xlxs files(*.xlxs)|*.xlxs|xls files(*.xls)|*.xls|All files(*.*)|*.*";
    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        TableToExcelForXLS(dataTable, saveFileDialog.FileName, "S1");
    }
}

总结

挺好用的 没找到的 xlxs的保存方法,能保存xls也行了

2022/12/2