将datatable相关内容写入到excel中

最新推荐文章于 2024-08-14 11:34:36 发布

wsqplsh

最新推荐文章于 2024-08-14 11:34:36 发布

阅读量1.8k

点赞数

分类专栏： C#

本文链接：https://blog.csdn.net/wsqplsh/article/details/50524468

版权

这篇博客介绍了如何使用NPOI库和Excel Interop接口将DataTable的内容导出到Excel文件中。文章详细展示了两种方法，包括NPOI的HSSFWorkbook实现和Excel Interop的应用，涉及单元格样式、列宽调整和数据类型的处理。

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

1、整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。

2、NPOI是POI的C#版本，NPOI的行和列的index都是从0开始

3、POI读取Excel有两种格式一个是HSSF，另一个是XSSF。 HSSF和XSSF的区别如下：
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
即：HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。

使用npoi

public static void ExportExcel(System.Data.DataTable dtSource, string strHeaderText, string strFileName)
{
#region
MemoryStream ms = Export(dtSource, strHeaderText);
FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write, FileShare.Read);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
#endregion
}

public static MemoryStream Export(System.Data.DataTable dtSource, string strHeaderText)
{
#region
HSSFWorkbook workbook = new HSSFWorkbook();
#region 右击文件属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.southgis.com";
workbook.DocumentSummaryInformation = dsi;

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "southgis"; //填加xls文件作者信息
si.ApplicationName = "AppDMS"; //填加xls文件创建程序信息
si.LastAuthor = "s"; //填加xls文件最后保存者信息
si.Comments = "s"; //填加xls文件作者信息
si.Title = "s"; //填加xls文件标题信息
si.Subject = "s";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion

HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}

int rowIndex = 0;
//列风格
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection;//.CENTER_SELECTION;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//.CellBorderType.THIN;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//.CellBorderType.THIN; ;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//.CellBorderType.THIN;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//.CellBorderType.THIN; ;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 600;
headStyle.SetFont(font);
//行风格
HSSFCellStyle rowStyle = workbook.CreateCellStyle() as HSSFCellStyle;
rowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//.CENTER;
rowStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//.CellBorderType.THIN;
rowStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//.CellBorderType.THIN;
rowStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//.CellBorderType.THIN;
rowStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//.CellBorderType.THIN;
font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 300;
rowStyle.SetFont(font);

//创建sheet
HSSFSheet sheet = null;
do
{
#region 新建表，填充表头，填充列头，样式
sheet = workbook.CreateSheet() as HSSFSheet;
sheet.DisplayGridlines = true;

#region 表头及样式
{
if (!string.IsNullOrEmpty(strHeaderText))
{
rowIndex++;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.CreateCell(0).SetCellValue(strHeaderText);

for (int i = 0; i < dtSource.Columns.Count; i++)
{
headerRow.CreateCell(i + 1);
headerRow.GetCell(i).CellStyle = headStyle;
}

sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, dtSource.Columns.Count - 1));
headerRow = null;
}
}
#endregion

#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(rowIndex) as HSSFRow;

rowIndex++;

foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

}
headerRow = null;
}
#endregion
#endregion
int dtRowIndex = 0;
while (dtRowIndex < dtSource.Rows.Count)
{
#region 填充内容
DataRow row = dtSource.Rows[dtRowIndex];
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;

string drValue = row[column].ToString();
dataRow.GetCell(column.Ordinal).CellStyle = rowStyle;

switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValu