DataTable数据导出到Excel（C# 报表数据导出、导入）

最新推荐文章于 2024-08-11 22:03:00 发布

Honey66

最新推荐文章于 2024-08-11 22:03:00 发布

阅读量1.2k

点赞数

文章标签：报表批量导入报表批量导出 excel文件数据导入导出数据到excel C#

本文链接：https://blog.csdn.net/kexianjun66/article/details/98198619

版权

这个C#代码示例展示了如何使用NPOI库从DataTable导出数据到Excel文件，包括设置列宽、样式以及处理不同数据类型的单元格。它支持2003和2007以上版本的Excel格式。

摘要由CSDN通过智能技术生成

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Web;

namespace SZYH.Common
{
public class NpoiExcelHelper
{
public enum ExcelStyle
{
Office2003 = 0,
Office2007AndAbove = 1
}

#region 从datatable中将数据导出到excel
/// <summary>
/// 导出DataTable到流，此方法包含所有格式。
/// 2003 最大行数为65535，2007及以上为1048576
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strHeaderText"></param>
/// <param name="excellStyle"></param>
/// <returns></returns>
public static NpoiMemoryStream ExportDt(DataTable dtSource, string strHeaderText, ExcelStyle excellStyle = ExcelStyle.Office2007AndAbove)
{
IWorkbook workbook = new XSSFWorkbook();
if (excellStyle == ExcelStyle.Office2003)
workbook = new HSSFWorkbook();
//
var sheet = workbook.CreateSheet();
//
//
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
var arrColWidths = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (var i = 0; i < dtSource.Rows.Count; i++)
{
for (var j = 0; j < dtSource.Columns.Count; j++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidths[j])
{
arrColWidths[j] = intTemp;
}
}
}
var rowIndex = 0;
var maxRows = excellStyle == ExcelStyle.Office2003 ? 65535 : 1048576;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == maxRows || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
var headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
var headerRow = sheet.CreateRow(1);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidths[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);