/// <summary>
/// 插入数据行
/// </summary>
protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
{
int rowCount = 0;
int sheetCount = 1;
NPOI.SS.UserModel.ISheet newsheet = null;
//循环数据源导出数据集
newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet, excelWorkbook);
#region 样式声明
//格式化显示
NPOI.SS.UserModel.ICellStyle cellStyle_DateTime = excelWorkbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = excelWorkbook.CreateDataFormat();
cellStyle_DateTime.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
#endregion
foreach (DataRow dr in dtSource.Rows)
{
rowCount++;
//超出10000条数据 创建新的工作簿
if (rowCount == 5000)
{
rowCount = 1;
sheetCount++;
newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet, excelWorkbook);
}
NPOI.SS.UserModel.IRow newRow = newsheet.CreateRow(rowCount);
InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, cellStyle_DateTime);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream xlsx
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
public static MemoryStream ExportXlsx(IEnumerable <DataTable> dataTables, string strHeaderText, string passaord = null)
{
XSSFWorkbook workbook = new XSSFWorkbook();
int i = 0;
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 右击文件 属性信息
{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "NPOI";
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "文件作者信息"; //填加xlsx文件作者信息
// si.ApplicationName = "创建程序信息"; //填加xlsx文件创建程序信息
// si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
// si.Comments = "作者信息"; //填加xls文件作者信息
// si.Title = "标题信息"; //填加xls文件标题信息
// si.Subject = "主题信息";//填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.GetProperties().CustomProperties.AddProperty("Company", "NPOI");
// if (!workbook.GetProperties().CustomProperties.Contains("Company"))
// workbook.GetProperties().CustomProperties.AddProperty("Company", dsi.Company);
}
#endregion
foreach (DataTable dt in dataTables)
{
string sheetName = string.IsNullOrEmpty(dt.TableName)
? "Sheet " + (++i).ToString()
: dt.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
int rowIndex = 0;
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
//取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int k = 0; k < dt.Rows.Count; k++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[k][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
rowIndex += 1;
}
#endregion
//#region 表头
//for (int j = 0; j < dt.Columns.Count; j++)
//{
// string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)
// ? "Column " + j.ToString()
// : dt.Columns[j].ColumnName;
// headerRow.CreateCell(j).SetCellValue(columnName);
//}
//#endregion
#region 内容
for (int a = 0; a < dt.Rows.Count; a++)
{
DataRow dr = dt.Rows[a];
IRow row = sheet.CreateRow(a + rowIndex);
for (int b = 0; b < dt.Columns.Count; b++)
{
row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty);
DataColumn dc = dt.Columns[b];
NPOI.SS.UserModel.ICell newCell = row.CreateCell(dc.Ordinal);
string drValue = dr[b].ToString();
switch (dc.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.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
}
#endregion
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
// ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string passaord = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat();
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;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
}
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont 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, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
rowIndex += 1;
}
#endregion
}
#endregion
#region 填充内容
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
NPOI.SS.UserModel.ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
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.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public bool Export(DataTable dtSource, string[] headersTitle, string strHeaderText)
{
FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
HSSFWorkbook workbook = new HSSFWorkbook();
this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName;
ISheet sheet = workbook.CreateSheet(this._sheetName);
#region 右击文件属性相关信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "公司名称";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "zhaolf";
// si.ApplicationName = "创建程序信息";
// si.LastAuthor = "zhaolf";
// si.Comments = "软件部";
// si.Title = "报表导出";
// si.Subject = "报表导出";
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
dateStyle.Alignment = HorizontalAlignment.Center; // HorizontalAlign.Center;// CellHorizontalAlignment.CENTER;
dateStyle.BorderBottom = BorderStyle.Thin; // CellBorderType.THIN;
dateStyle.BorderTop = BorderStyle.Thin; //CellBorderType.THIN;
dateStyle.BorderLeft = BorderStyle.Thin; //CellBorderType.THIN;
dateStyle.BorderRight = BorderStyle.Thin; //CellBorderType.THIN;
//取得列宽
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 index = 0; index < dtSource.Rows.Count; index++)
{
for (int icount = 0; icount < dtSource.Columns.Count; icount++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[index][icount].ToString()).Length;
if (intTemp > arrColWidth[icount])
{
arrColWidth[icount] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; // CellHorizontalAlignment.CENTER;
IFont 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));
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; // CellHorizontalAlignment.CENTER;
// 填充列的背景色
headStyle.FillForegroundColor = HSSFColor.Grey50Percent.Index; // HSSFColor.GREY_50_PERCENT.index;
headStyle.FillPattern = FillPattern.SolidForeground; // CellFillPattern.SOLID_FOREGROUND;
headStyle.BorderBottom = BorderStyle.Thin; // CellBorderType.THIN;
headStyle.BorderTop = BorderStyle.Thin; //CellBorderType.THIN;
headStyle.BorderLeft = BorderStyle.Thin; //CellBorderType.THIN;
headStyle.BorderRight = BorderStyle.Thin; //CellBorderType.THIN;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int index = 0; index < headersTitle.Count(); index++)
{
headerRow.CreateCell(index).SetCellValue(headersTitle[index]);
headerRow.GetCell(index).CellStyle = headStyle;
sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256);
}
//for (int i = 0; i < dtSource.Columns.Count; i++)
//{
// headerRow.CreateCell(i).SetCellValue(dtSource.Columns[i].ColumnName.ToString());
// headerRow.GetCell(i).CellStyle = headStyle;
// sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
//}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
/****-----------------此段是设置Excel表格样式----------------------****/
/*
* 2014-06-09
* 注释原因：当数据超过334行时，当到达第335行的第10列时程序会发生异常，暂时不知道问题所在,所以先注释
* 没有样式，不影响数据的导出
*/
//ICellStyle headStyle = workbook.CreateCellStyle();
//headStyle.Alignment = HorizontalAlignment.Center; // CellHorizontalAlignment.CENTER;
//// 填充列的背景色
//headStyle.Alignment = HorizontalAlignment.Center; // CellHorizontalAlignment.CENTER;
//headStyle.BorderBottom = BorderStyle.Thin; // CellBorderType.THIN;
//headStyle.BorderTop = BorderStyle.Thin; // CellBorderType.THIN;
//headStyle.BorderLeft = BorderStyle.Thin; // CellBorderType.THIN;
//headStyle.BorderRight = BorderStyle.Thin; // CellBorderType.THIN;
// dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
//dataRow.GetCell(column.Ordinal).CellStyle = headStyle;
/****-----------------此段是设置Excel表格样式----------------------****/
string drValue = row[column].ToString();
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.SetCellValue(boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull":
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
//using (MemoryStream ms = new MemoryStream())
//{
// workbook.Write(ms);
// ms.Flush();
// ms.Position = 0;
// //sheet.Dispose();
// //workbook.Dispose();
// return true;
//}
//写入数据流
workbook.Write(fs);
fs.Flush();
fs.Close();
return(true);
}