/// <summary>
/// 导出到EXCEL
/// </summary>
/// <param name="dtSource"></param>
/// <returns></returns>
public static byte[] ExportToExcel(DataTable dtSource)
{
HSSFWorkbook workbook = NPOIHelper.InitializeBlankWorkbook("www.danfoss.com", "danfoss", "danfoss export file");
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
//设置单元格显示格式
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
dateStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
HSSFCellStyle intStyle = (HSSFCellStyle)workbook.CreateCellStyle();
intStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
HSSFCellStyle doubleStyle = (HSSFCellStyle)workbook.CreateCellStyle();
doubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
HSSFCellStyle strStyle = (HSSFCellStyle)workbook.CreateCellStyle();
strStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
HSSFCellStyle genStyle = (HSSFCellStyle)workbook.CreateCellStyle();
genStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("General");
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//设置列宽 同列标题宽度
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;
// }
// }
//}
//设置列格式
HSSFCellStyle[] arrColStyle = new HSSFCellStyle[dtSource.Columns.Count];
foreach (DataColumn column in dtSource.Columns)
{
switch (column.DataType.ToString())
{
case "System.DateTime": //日期类型
arrColStyle[column.Ordinal] = dateStyle; //日期格式化显示 m/d/yy
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
arrColStyle[column.Ordinal] = intStyle;
break;
case "System.Decimal": //浮点数
case "System.Double":
arrColStyle[column.Ordinal] = doubleStyle;
break;
case "System.String": //字符串类型
arrColStyle[column.Ordinal] = strStyle;
break;
case "System.Byte":
case "System.Boolean": //布尔型
case "System.DBNull": //空值处理
default:
arrColStyle[column.Ordinal] = genStyle;
break;
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 超出行数则新建表，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
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] + 4) * 256);
sheet.SetDefaultColumnStyle(column.Ordinal, arrColStyle[column.Ordinal]);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
if (row[column].GetType() == typeof(DBNull))
{
continue;
}
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue((string)row[column]);
//newCell.CellStyle = strStyle;
break;
case "System.DateTime": //日期类型
newCell.SetCellValue((DateTime)row[column]);
//newCell.CellStyle = dateStyle;//日期格式化显示 m/d/yy
break;
case "System.Boolean": //布尔型
newCell.SetCellValue((bool)row[column]);
break;
case "System.Int16": //整型
newCell.SetCellValue((Int16)row[column]);
//newCell.CellStyle = intStyle;
break;
case "System.Int32":
newCell.SetCellValue((Int32)row[column]);
//newCell.CellStyle = intStyle;
break;
case "System.Int64":
newCell.SetCellValue((Int64)row[column]);
//newCell.CellStyle = intStyle;
break;
case "System.Byte":
newCell.SetCellValue((Byte)row[column]);
//newCell.CellStyle = intStyle;
break;
case "System.Decimal": //浮点数
//double doubV = 0;
//double.TryParse(drValue, out doubV);
newCell.SetCellValue((double)(System.Decimal)row[column]);
//newCell.CellStyle = doubleStyle;
break;
case "System.Double":
newCell.SetCellValue((double)row[column]);
//newCell.CellStyle = doubleStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.GetBuffer());
//return workbook.GetBytes();
}
/// <summary>
/// 读取Excel文件的内容，并转换为DataTable
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
public static DataTable ReadAsDatatable(Stream s)
{
HSSFWorkbook wb = new HSSFWorkbook(s);
return(NPOIHelper.ConvertToDataTable(wb));
}
