首页 > 代码库 > 导出Excel文件
导出Excel文件
引用：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HPSF;
using System.IO;
using DevExpress.XtraEditors;
using System.Windows.Forms;
using NPOI.SS.Util;
方法：
#region 全局变量
private static DataTable _dt;
private static HSSFWorkbook _hssfWorkbook;
#endregion
#region 初始化
/// <summary>
/// 构造函数
/// </summary>
public DataImportAndExport()
{
}
/// <summary>
/// 导出时Excel文件属性信息
/// </summary>
private static void InitializeWorkbook(HSSFWorkbook hssfWorkbook)
{
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "包钢无缝钢管厂";
hssfWorkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = String.Empty;
si.CreateDateTime = DateTime.Now;
hssfWorkbook.SummaryInformation = si;
}
private static void InitializeWorkbook(string path)
{
//read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
_hssfWorkbook = new HSSFWorkbook(file);
}
}
/// <summary>
/// 将Excel文件转换为DataTable
/// </summary>
private static void ConvertToDataTable()
{
ISheet sheet = _hssfWorkbook.GetSheetAt(0);
if (sheet == null)
{
throw new Exception("所导入Excel文件不存在！");
}
if (sheet.LastRowNum < 1)
{
throw new Exception("导入Excel内容为空！");
}
_dt = new DataTable();
IRow firstRow = sheet.GetRow(0);
for (int i = 0; i < firstRow.LastCellNum; i++)
{
ICell cell = firstRow.GetCell(i);
if (cell == null)
{
throw new Exception("导入Excel格式非法！");
}
else
{
_dt.Columns.Add(Convert.ToString(cell), typeof(String));
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dr = _dt.NewRow();
for (int j = 0; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
dr[j] = null;
}
else
{
switch (cell.CellType)
{
case CellType.BLANK:
dr[j] = null;
break;
case CellType.BOOLEAN:
dr[j] = cell.BooleanCellValue;
break;
case CellType.ERROR:
dr[j] = cell.ErrorCellValue;
break;
case CellType.NUMERIC:
dr[j] = cell.NumericCellValue;
break;
case CellType.STRING:
dr[j] = cell.StringCellValue;
break;
case CellType.FORMULA:
default:
dr[j] = "=" + cell.CellFormula;
break;
}
}
}
_dt.Rows.Add(dr);
}
}
#endregion
#region Excel导入到DataTable
/// <summary>
/// Excel文件导入到DataTable
/// </summary>
/// <param name="path">Excel文件路径</param>
/// <param name="dataTable">目标DataTable</param>
public void ImportFromXls(string path, DataTable dataTable)
{
try
{
if (dataTable == null)
{
throw new Exception("要导入的DataTable为null！");
}
InitializeWorkbook(path);
ConvertToDataTable();
if (_dt.Columns.Count != dataTable.Columns.Count)
{
throw new Exception("Excel与DataTable结构不一致！");
}
for (int i = 0; i < _dt.Columns.Count; i++)
{
if (!_dt.Columns[i].ColumnName.Equals(dataTable.Columns[i].ColumnName))
{
throw new Exception("Excel与DataTable结构不一致！");
}
}
dataTable.Rows.Clear();
foreach (DataRow dr in _dt.Rows)
{
DataRow row = dataTable.NewRow();
foreach (DataColumn col in dataTable.Columns)
{
string colName = col.ColumnName;
string drValue = http://www.mamicode.com/dr[colName].ToString();
switch (col.DataType.ToString())
{
case "System.String":
//字符串
row[colName] = drValue;
break;
case "System.DateTime":
//日期型
if (drValue.Length > 0)
{
double value = http://www.mamicode.com/0;
double.TryParse(drValue, out value);
DateTime dateValue = http://www.mamicode.com/DateTime.FromOADate(value);
row[colName] = dateValue;
}
else
{
row[colName] = DBNull.Value;
}
break;
case "System.Boolean":
//布尔型
bool boolValue = http://www.mamicode.com/false;
bool.TryParse(drValue, out boolValue);
row[colName] = boolValue;
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = http://www.mamicode.com/0;
int.TryParse(drValue, out intValue);
row[colName] = intValue;
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubleValue = http://www.mamicode.com/0;
double.TryParse(drValue, out doubleValue);
row[colName] = doubleValue;
break;
case "System.DBNull":
//空值处理
row[colName] = String.Empty;
break;
default:
row[colName] = String.Empty;
break;
}
}
dataTable.Rows.Add(row);
}
}
catch (Exception e)
{
throw e;
}
}
#endregion
#region 将DataTable导出为Excel-单个DataTable
/// <summary>
/// DataTable导出为Excel文件，忽略名以#开头的列
/// 包含“时间”的日期型字段显示为:yyyy-MM-dd HH:mm:ss
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strFileName">Excel文件保存位置</param>
public static void ExportToXls(DataTable dtSource, string strFileName)
{
try
{
List<int> indexHideCol = new List<int>();
foreach (DataColumn item in dtSource.Columns)
{
if (item.Caption.IndexOf("#") == 0)
{
indexHideCol.Add(item.Ordinal);
}
}
for (int i = indexHideCol.Count - 1; i >= 0; i--)
{
dtSource.Columns.RemoveAt(indexHideCol[i]);
}
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
InitializeWorkbook(hssfWorkbook);
ISheet sheet = hssfWorkbook.CreateSheet("Sheet1");
ICellStyle dateStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format = hssfWorkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
ICellStyle timeStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format2 = hssfWorkbook.CreateDataFormat();
timeStyle.DataFormat = format2.GetFormat("yyyy-MM-dd HH:mm:ss");
ICellStyle numberStyle = hssfWorkbook.CreateCellStyle();
numberStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");
//获得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).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;
int sheetIndex = 2;
#region 当dtSource只有列头时，填充列头及样式
if (dtSource.Rows.Count == 0)
{
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtSource.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
foreach (DataRow dr in dtSource.Rows)
{
#region 新建表，填充列头
if (rowIndex == 65536 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = hssfWorkbook.CreateSheet("Sheet" + sheetIndex);
sheetIndex++;
}
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtSource.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow row = sheet.CreateRow(rowIndex);
foreach (DataColumn col in dtSource.Columns)
{
ICell cell = row.CreateCell(col.Ordinal);
if (dr[col] == DBNull.Value)
{
cell.SetCellType(CellType.BLANK);
cell.SetCellValue(string.Empty);
}
else
{
string drValue = http://www.mamicode.com/dr[col].ToString();
switch (col.DataType.ToString())
{
case "System.String":
//字符串
cell.SetCellValue(drValue);
break;
case "System.DateTime":
//日期型
DateTime dateValue;
DateTime.TryParse(drValue, out dateValue);
cell.SetCellValue(dateValue);
if (col.ColumnName.Contains("时间"))
{
cell.CellStyle = timeStyle;
}
else
{
cell.CellStyle = dateStyle;
}
break;
case "System.Boolean":
//布尔型
bool boolValue = http://www.mamicode.com/false;
bool.TryParse(drValue, out boolValue);
cell.SetCellValue(boolValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = http://www.mamicode.com/0;
int.TryParse(drValue, out intValue);
cell.SetCellValue(intValue);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubleValue = http://www.mamicode.com/0;
double.TryParse(drValue, out doubleValue);
cell.SetCellValue(doubleValue);
cell.CellStyle = numberStyle;
break;
case "System.DBNull":
//空值处理
cell.SetCellValue(string.Empty);
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
}
#endregion
rowIndex++;
}
FileStream file = new FileStream(strFileName, FileMode.Create);
hssfWorkbook.Write(file);
file.Close();
//sheet.Dispose();
//hssfWorkbook.Dispose();
}
catch (Exception e)
{
XtraMessageBox.Show(e.Message, "异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// DataTable导出为Excel文件
/// 包含“时间”的日期型字段显示为:yyyy-MM-dd HH:mm:ss
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strFileName">Excel文件保存位置</param>
/// <param name="sheetName">Excel文件的sheetName</param>
public static void ExportToXls(DataTable dtSource, string strFileName,string sheetName)
{
try
{
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
InitializeWorkbook(hssfWorkbook);
ISheet sheet = hssfWorkbook.CreateSheet(sheetName);
ICellStyle dateStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format = hssfWorkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
ICellStyle timeStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format2 = hssfWorkbook.CreateDataFormat();
timeStyle.DataFormat = format2.GetFormat("yyyy-MM-dd HH:mm:ss");
ICellStyle numberStyle = hssfWorkbook.CreateCellStyle();
numberStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");
//获得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).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;
int sheetIndex = 1;
#region 当dtSource只有列头时，填充列头及样式
if (dtSource.Rows.Count == 0)
{
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtSource.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
foreach (DataRow dr in dtSource.Rows)
{
#region 新建表，填充列头
if (rowIndex == 65536 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = hssfWorkbook.CreateSheet(String.Format("{0}-{1}", sheetName, sheetIndex));
sheetIndex++;
}
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtSource.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow row = sheet.CreateRow(rowIndex);
foreach (DataColumn col in dtSource.Columns)
{
ICell cell = row.CreateCell(col.Ordinal);
if (dr[col] == DBNull.Value)
{
cell.SetCellType(CellType.BLANK);
cell.SetCellValue(string.Empty);
}
else
{
string drValue = http://www.mamicode.com/dr[col].ToString();
switch (col.DataType.ToString())
{
case "System.String":
//字符串
cell.SetCellValue(drValue);
break;
case "System.DateTime":
//日期型
DateTime dateValue;
DateTime.TryParse(drValue, out dateValue);
cell.SetCellValue(dateValue);
if (col.ColumnName.Contains("时间"))
{
cell.CellStyle = timeStyle;
}
else
{
cell.CellStyle = dateStyle;
}
break;
case "System.Boolean":
//布尔型
bool boolValue = http://www.mamicode.com/false;
bool.TryParse(drValue, out boolValue);
cell.SetCellValue(boolValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = http://www.mamicode.com/0;
int.TryParse(drValue, out intValue);
cell.SetCellValue(intValue);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubleValue = http://www.mamicode.com/0;
double.TryParse(drValue, out doubleValue);
cell.SetCellValue(doubleValue);
cell.CellStyle = numberStyle;
break;
case "System.DBNull":
//空值处理
cell.SetCellValue(string.Empty);
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
}
#endregion
rowIndex++;
}
FileStream file = new FileStream(strFileName, FileMode.Create);
hssfWorkbook.Write(file);
file.Close();
//sheet.Dispose();
//hssfWorkbook.Dispose();
}
catch (Exception e)
{
XtraMessageBox.Show(e.Message, "异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
#region 将DataTable导出为Excel-两个DataTable
/// <summary>
/// DataTable导出为Excel文件，忽略名以#开头的列
/// 包含“时间”的日期型字段显示为:yyyy-MM-dd HH:mm:ss
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strFileName">Excel文件保存位置</param>
public static void ExportToXls(DataTable dtSource1, DataTable dtSource2, string sheet1Name, string sheet2Name, string strFileName)
{
try
{
List<int> indexHideCol1 = new List<int>();
foreach (DataColumn item in dtSource1.Columns)
{
if (item.Caption.IndexOf("#") == 0)
{
indexHideCol1.Add(item.Ordinal);
}
}
for (int i = indexHideCol1.Count - 1; i >= 0; i--)
{
dtSource1.Columns.RemoveAt(indexHideCol1[i]);
}
List<int> indexHideCol2 = new List<int>();
foreach (DataColumn item in dtSource2.Columns)
{
if (item.Caption.IndexOf("#") == 0)
{
indexHideCol2.Add(item.Ordinal);
}
}
for (int i = indexHideCol2.Count - 1; i >= 0; i--)
{
dtSource2.Columns.RemoveAt(indexHideCol2[i]);
}
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
InitializeWorkbook(hssfWorkbook);
ICellStyle dateStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format = hssfWorkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
ICellStyle timeStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format2 = hssfWorkbook.CreateDataFormat();
timeStyle.DataFormat = format2.GetFormat("yyyy-MM-dd HH:mm:ss");
ICellStyle numberStyle = hssfWorkbook.CreateCellStyle();
numberStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");
#region sheet1
ISheet sheet1 = hssfWorkbook.CreateSheet(sheet1Name);
//获得列宽
int[] arrColWidth = new int[dtSource1.Columns.Count];
foreach (DataColumn item in dtSource1.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (int i = 0; i < dtSource1.Rows.Count; i++)
{
for (int j = 0; j < dtSource1.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource1.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int sheetIndex = 2;
#region 当dtSource1只有列头时，填充列头及样式
if (dtSource1.Rows.Count == 0)
{
IRow headerRow = sheet1.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtSource1.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet1.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
#region 当dtSource1含有数据时，填充表头及内容
foreach (DataRow dr in dtSource1.Rows)
{
#region 新建表，填充列头
if (rowIndex == 65536 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet1 = hssfWorkbook.CreateSheet("Sheet" + sheetIndex);
sheetIndex++;
}
#region 列头及样式
{
IRow headerRow = sheet1.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtSource1.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet1.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow row = sheet1.CreateRow(rowIndex);
foreach (DataColumn col in dtSource1.Columns)
{
ICell cell = row.CreateCell(col.Ordinal);
if (dr[col] == DBNull.Value)
{
cell.SetCellType(CellType.BLANK);
cell.SetCellValue(string.Empty);
}
else
{
string drValue = http://www.mamicode.com/dr[col].ToString();
switch (col.DataType.ToString())
{
case "System.String":
//字符串
cell.SetCellValue(drValue);
break;
case "System.DateTime":
//日期型
DateTime dateValue;
DateTime.TryParse(drValue, out dateValue);
cell.SetCellValue(dateValue);
if (col.ColumnName.Contains("时间"))
{
cell.CellStyle = timeStyle;
}
else
{
cell.CellStyle = dateStyle;
}
break;
case "System.Boolean":
//布尔型
bool boolValue = http://www.mamicode.com/false;
bool.TryParse(drValue, out boolValue);
cell.SetCellValue(boolValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = http://www.mamicode.com/0;
int.TryParse(drValue, out intValue);
cell.SetCellValue(intValue);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubleValue = http://www.mamicode.com/0;
double.TryParse(drValue, out doubleValue);
cell.SetCellValue(doubleValue);
cell.CellStyle = numberStyle;
break;
case "System.DBNull":
//空值处理
cell.SetCellValue(string.Empty);
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
}
#endregion
rowIndex++;
}
#endregion
#endregion
#region sheet2
ISheet sheet2 = hssfWorkbook.CreateSheet(sheet2Name);
//获得列宽
arrColWidth = new int[dtSource2.Columns.Count];
foreach (DataColumn item in dtSource2.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (int i = 0; i < dtSource2.Rows.Count; i++)
{
for (int j = 0; j < dtSource2.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource2.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
rowIndex = 0;
sheetIndex = 2;
#region 当dtSource2只有列头时，填充列头及样式
if (dtSource2.Rows.Count == 0)
{
IRow headerRow = sheet2.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtSource2.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet2.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
foreach (DataRow dr in dtSource2.Rows)
{
#region 新建表，填充列头
if (rowIndex == 65536 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet2 = hssfWorkbook.CreateSheet("Sheet" + sheetIndex);
sheetIndex++;
}
#region 列头及样式
{
IRow headerRow = sheet2.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtSource2.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet2.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow row = sheet2.CreateRow(rowIndex);
foreach (DataColumn col in dtSource2.Columns)
{
ICell cell = row.CreateCell(col.Ordinal);
if (dr[col] == DBNull.Value)
{
cell.SetCellType(CellType.BLANK);
cell.SetCellValue(string.Empty);
}
else
{
string drValue = http://www.mamicode.com/dr[col].ToString();
switch (col.DataType.ToString())
{
case "System.String":
//字符串
cell.SetCellValue(drValue);
break;
case "System.DateTime":
//日期型
DateTime dateValue;
DateTime.TryParse(drValue, out dateValue);
cell.SetCellValue(dateValue);
if (col.ColumnName.Contains("时间"))
{
cell.CellStyle = timeStyle;
}
else
{
cell.CellStyle = dateStyle;
}
break;
case "System.Boolean":
//布尔型
bool boolValue = http://www.mamicode.com/false;
bool.TryParse(drValue, out boolValue);
cell.SetCellValue(boolValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = http://www.mamicode.com/0;
int.TryParse(drValue, out intValue);
cell.SetCellValue(intValue);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubleValue = http://www.mamicode.com/0;
double.TryParse(drValue, out doubleValue);
cell.SetCellValue(doubleValue);
cell.CellStyle = numberStyle;
break;
case "System.DBNull":
//空值处理
cell.SetCellValue(string.Empty);
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
}
#endregion
rowIndex++;
}
#endregion
FileStream file = new FileStream(strFileName, FileMode.Create);
hssfWorkbook.Write(file);
file.Close();
//sheet1.Dispose();
//sheet2.Dispose();
//hssfWorkbook.Dispose();
}
catch (Exception e)
{
XtraMessageBox.Show(e.Message, "数据导出异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
#region 将DataTable导出为Excel-多个DataTable
/// <summary>
/// 将多个DataTable导出为Excel文件中的多个Sheet，忽略以#开头的列
/// 包含“时间”的日期型字段显示为:yyyy-MM-dd HH:mm:ss
/// </summary>
/// <param name="dtList">导出的DataTable集合</param>
/// <param name="sheetNameList">Excel文件中sheet名称集合</param>
/// <param name="strFileName">Excel路径名</param>
public static void ExportToXls(List<DataTable> dtList, List<string> sheetNameList, string localFilePath)
{
try
{
if (dtList.Count != sheetNameList.Count)
{
throw new Exception("DataTable个数与sheet个数不一致！");
}
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
InitializeWorkbook(hssfWorkbook);
ICellStyle dateStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format = hssfWorkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
ICellStyle timeStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format2 = hssfWorkbook.CreateDataFormat();
timeStyle.DataFormat = format2.GetFormat("yyyy-MM-dd HH:mm:ss");
ICellStyle numberStyle = hssfWorkbook.CreateCellStyle();
numberStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000");
for (int i = 0; i < dtList.Count; i++)
{
DataTable dt = dtList[i];
List<int> indexHideCol = new List<int>();
foreach (DataColumn col in dt.Columns)
{
if (col.Caption.IndexOf("#") == 0)
{
indexHideCol.Add(col.Ordinal);
}
}
for (int j = indexHideCol.Count - 1; j >= 0; j--)
{
dt.Columns.RemoveAt(indexHideCol[j]);
}
string sheetName = string.Empty;
if (sheetNameList[i].Length > 0)
{
sheetName = sheetNameList[i];
}
else
{
sheetName = "Sheet" + (i + 1);
}
ISheet sheet = hssfWorkbook.CreateSheet(sheetName);
//获得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int k = 0; k < dt.Columns.Count; k++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[j][k].ToString()).Length;
if (intTemp > arrColWidth[k])
{
arrColWidth[k] = intTemp;
}
}
}
int rowIndex = 0;
int sheetIndex = 1;
#region 当dt只有列头时，只填充列头及样式
if (dt.Rows.Count == 0)
{
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dt.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
continue;
}
#endregion
#region 当dt含有数据时，填充表头及内容
foreach (DataRow dr in dt.Rows)
{
#region 新建表，填充列头
if (rowIndex == 65536 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = hssfWorkbook.CreateSheet(String.Format("{0}-{1}", sheetName, sheetIndex));
sheetIndex++;
}
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dt.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow row = sheet.CreateRow(rowIndex);
foreach (DataColumn col in dt.Columns)
{
ICell cell = row.CreateCell(col.Ordinal);
if (dr[col] == DBNull.Value)
{
cell.SetCellType(CellType.BLANK);
cell.SetCellValue(string.Empty);
}
else
{
string drValue = http://www.mamicode.com/dr[col].ToString();
switch (col.DataType.ToString())
{
case "System.String":
//字符串
cell.SetCellValue(drValue);
break;
case "System.DateTime":
//日期型
DateTime dateValue;
DateTime.TryParse(drValue, out dateValue);
cell.SetCellValue(dateValue);
if (col.ColumnName.Contains("时间"))
{
cell.CellStyle = timeStyle;
}
else
{
cell.CellStyle = dateStyle;
}
break;
case "System.Boolean":
//布尔型
bool boolValue = http://www.mamicode.com/false;
bool.TryParse(drValue, out boolValue);
cell.SetCellValue(boolValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = http://www.mamicode.com/0;
int.TryParse(drValue, out intValue);
cell.SetCellValue(intValue);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubleValue = http://www.mamicode.com/0;
double.TryParse(drValue, out doubleValue);
cell.SetCellValue(doubleValue);
cell.CellStyle = numberStyle;
break;
case "System.DBNull":
//空值处理
cell.SetCellValue(string.Empty);
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
}
#endregion
rowIndex++;
}
#endregion
}
FileStream file = new FileStream(localFilePath, FileMode.Create);
hssfWorkbook.Write(file);
file.Close();
}
catch (Exception e)
{
XtraMessageBox.Show(e.Message, "数据导出异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
#region 资源平衡项目专用
/// <summary>
/// 将资源平衡计划导出为Excel
/// </summary>
/// <param name="dtGeneralInfo">基本信息数据源</param>
/// <param name="dtDetailInfo">细节信息数据源</param>
/// <param name="sheetGeneralName">工作表-基本信息</param>
/// <param name="sheetDetailName">工作表-细节信息</param>
/// <param name="strFileName"></param>
public static void ExportRbpToXls(DataTable dtGeneralInfo, DataTable dtDetailInfo, string sheetGeneralName, string sheetDetailName, string strFileName)
{
try
{
List<int> indexHideCol1 = new List<int>();
foreach (DataColumn item in dtGeneralInfo.Columns)
{
if (item.Caption.IndexOf("#") == 0)
{
indexHideCol1.Add(item.Ordinal);
}
}
for (int i = indexHideCol1.Count - 1; i >= 0; i--)
{
dtGeneralInfo.Columns.RemoveAt(indexHideCol1[i]);
}
List<int> indexHideCol2 = new List<int>();
foreach (DataColumn item in dtDetailInfo.Columns)
{
if (item.Caption.IndexOf("#") == 0)
{
indexHideCol2.Add(item.Ordinal);
}
}
for (int i = indexHideCol2.Count - 1; i >= 0; i--)
{
dtDetailInfo.Columns.RemoveAt(indexHideCol2[i]);
}
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
InitializeWorkbook(hssfWorkbook);
ICellStyle dateStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format = hssfWorkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
ICellStyle timeStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format2 = hssfWorkbook.CreateDataFormat();
timeStyle.DataFormat = format2.GetFormat("yyyy-MM-dd HH:mm:ss");
ICellStyle numberStyle = hssfWorkbook.CreateCellStyle();
numberStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");
#region sheetGeneral
ISheet sheetGeneral = hssfWorkbook.CreateSheet(sheetGeneralName);
for (int i = 0; i <= 15; i++)
{
IRow row = sheetGeneral.CreateRow(i);
}
#region 单元格格式
//名称列样式
ICellStyle nameStyle = hssfWorkbook.CreateCellStyle();
nameStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
nameStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont nameFont = hssfWorkbook.CreateFont();
nameFont.FontHeightInPoints = 12;
nameFont.Boldweight = 700;
nameStyle.SetFont(nameFont);
//值列格式
ICellStyle valueStyle = hssfWorkbook.CreateCellStyle();
valueStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
valueStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont valueFont = hssfWorkbook.CreateFont();
valueFont.FontHeightInPoints = 12;
valueStyle.SetFont(valueFont);
#endregion
#region 特殊单元格
string balancePlanNo = Convert.ToString(dtGeneralInfo.Rows[0]["balance_plan_no"]);
string planPeriod = Convert.ToString(dtGeneralInfo.Rows[0]["plan_period"]);
string description = Convert.ToString(dtGeneralInfo.Rows[0]["description"]);
if (!string.IsNullOrEmpty(planPeriod))
{
IRow headerRow = sheetGeneral.GetRow(0);
//标题列样式
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 14;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
string value = http://www.mamicode.com/String.Format("{0}年{1}月资源分配方案", planPeriod.Substring(0, 4), planPeriod.Substring(4, 2));
headerRow.CreateCell(0).SetCellValue(value);
headerRow.GetCell(0).CellStyle = headStyle;
IRow row = sheetGeneral.GetRow(3);
row.CreateCell(0).SetCellValue("计划期间：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(planPeriod);
row.GetCell(1).CellStyle = valueStyle;
}
if (!string.IsNullOrEmpty(balancePlanNo))
{
IRow row = sheetGeneral.GetRow(2);
row.CreateCell(0).SetCellValue("方案编号：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(balancePlanNo);
row.GetCell(1).CellStyle = valueStyle;
}
if (!string.IsNullOrEmpty(description))
{
IRow row = sheetGeneral.GetRow(11);
row.CreateCell(0).SetCellValue("说明：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(description);
row.GetCell(1).CellStyle = valueStyle;
}
#endregion
#region 填充单元格
foreach (DataRow dr in dtGeneralInfo.Rows)
{
string itemCode = Convert.ToString(dr["item_code"]);
string itemName = Convert.ToString(dr["item_name"]);
string itemValue = http://www.mamicode.com/Convert.ToString(dr["item_value"]);
if (itemCode.Equals("Policy"))
{
IRow row = sheetGeneral.GetRow(4);
row.CreateCell(0).SetCellValue("经营策略：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(itemValue);
row.GetCell(1).CellStyle = valueStyle;
}
if (itemCode.Equals("Method"))
{
IRow row = sheetGeneral.GetRow(5);
row.CreateCell(0).SetCellValue("求解算法：");
nameStyle.SetFont(nameFont);
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(itemValue);
row.GetCell(1).CellStyle = valueStyle;
}
if (itemCode.Equals("Constraint"))
{
IRow row = sheetGeneral.GetRow(6);
row.CreateCell(0).SetCellValue("约束条件：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(itemValue);
row.GetCell(1).CellStyle = valueStyle;
}
if (itemCode.Equals("PlanPrice"))
{
IRow row = sheetGeneral.GetRow(7);
row.CreateCell(0).SetCellValue("价格：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(itemValue);
row.GetCell(1).CellStyle = valueStyle;
}
if (itemCode.Equals("PlanCost"))
{
IRow row = sheetGeneral.GetRow(8);
row.CreateCell(0).SetCellValue("成本：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(itemValue);
row.GetCell(1).CellStyle = valueStyle;
}
if (itemCode.Equals("AverageProfit"))
{
IRow row = sheetGeneral.GetRow(9);
row.CreateCell(0).SetCellValue("平均毛利：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(itemValue);
row.GetCell(1).CellStyle = valueStyle;
}
if (itemCode.Equals("AverageCost"))
{
IRow row = sheetGeneral.GetRow(10);
row.CreateCell(0).SetCellValue("平均成本：");
row.GetCell(0).CellStyle = nameStyle;
row.CreateCell(1).SetCellValue(itemValue);
row.GetCell(1).CellStyle = valueStyle;
}
if (itemCode.Equals("SteelProduction"))
{
IRow row = sheetGeneral.GetRow(2);
row.CreateCell(3).SetCellValue("钢产量：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
if (itemCode.Equals("TotalSale"))
{
IRow row = sheetGeneral.GetRow(3);
row.CreateCell(3).SetCellValue("总销量：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
if (itemCode.Equals("TotalProfit"))
{
IRow row = sheetGeneral.GetRow(4);
row.CreateCell(3).SetCellValue("总利润：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
if (itemCode.Equals("TotalCost"))
{
IRow row = sheetGeneral.GetRow(5);
row.CreateCell(3).SetCellValue("总成本：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
if (itemCode.Equals("OutputRate"))
{
IRow row = sheetGeneral.GetRow(6);
row.CreateCell(3).SetCellValue("综合成材率：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
if (itemCode.Equals("ProfitRate"))
{
IRow row = sheetGeneral.GetRow(7);
row.CreateCell(3).SetCellValue("综合利润率：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
if (itemCode.Equals("SaleFillRate"))
{
IRow row = sheetGeneral.GetRow(8);
row.CreateCell(3).SetCellValue("合同需求排产率：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
if (itemCode.Equals("DeviceUtilizeRate"))
{
IRow row = sheetGeneral.GetRow(9);
row.CreateCell(3).SetCellValue("产能利用率：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
if (itemCode.Equals("MarketFillRate"))
{
IRow row = sheetGeneral.GetRow(10);
row.CreateCell(3).SetCellValue("市场饱和度：");
row.GetCell(3).CellStyle = nameStyle;
row.CreateCell(4).SetCellValue(itemValue);
row.GetCell(4).CellStyle = valueStyle;
}
}
#endregion
sheetGeneral.SetColumnWidth(0, 20 * 256);
sheetGeneral.SetColumnWidth(1, 40 * 256);
sheetGeneral.SetColumnWidth(2, 10 * 256);
sheetGeneral.SetColumnWidth(3, 20 * 256);
sheetGeneral.SetColumnWidth(4, 25 * 256);
//合并单元格
sheetGeneral.AddMergedRegion(new CellRangeAddress(0, 1, 0, 4));
sheetGeneral.AddMergedRegion(new CellRangeAddress(11, 11, 1, 4));
sheetGeneral.AddMergedRegion(new CellRangeAddress(2, 10, 2, 2));
#endregion
#region sheetDetail
ISheet sheetDetail = hssfWorkbook.CreateSheet(sheetDetailName);
//获得列宽
int[] arrColWidth = new int[dtDetailInfo.Columns.Count];
foreach (DataColumn item in dtDetailInfo.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (int i = 0; i < dtDetailInfo.Rows.Count; i++)
{
for (int j = 0; j < dtDetailInfo.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtDetailInfo.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int sheetIndex = 2;
#region 当dtDetailInfo只有列头时，填充列头及样式
if (dtDetailInfo.Rows.Count == 0)
{
IRow headerRow = sheetDetail.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtDetailInfo.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheetDetail.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
foreach (DataRow dr in dtDetailInfo.Rows)
{
#region 新建表，填充列头
if (rowIndex == 65536 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheetDetail = hssfWorkbook.CreateSheet(sheetDetailName + sheetIndex);
sheetIndex++;
}
#region 列头及样式
{
IRow headerRow = sheetDetail.CreateRow(0);
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
foreach (DataColumn col in dtDetailInfo.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheetDetail.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow row = sheetDetail.CreateRow(rowIndex);
foreach (DataColumn col in dtDetailInfo.Columns)
{
ICell cell = row.CreateCell(col.Ordinal);
if (dr[col] == DBNull.Value)
{
cell.SetCellType(CellType.BLANK);
cell.SetCellValue(string.Empty);
}
else
{
string drValue = http://www.mamicode.com/dr[col].ToString();
switch (col.DataType.ToString())
{
case "System.String":
//字符串
cell.SetCellValue(drValue);
break;
case "System.DateTime":
//日期型
DateTime dateValue;
DateTime.TryParse(drValue, out dateValue);
cell.SetCellValue(dateValue);
if (col.ColumnName.Contains("时间"))
{
cell.CellStyle = timeStyle;
}
else
{
cell.CellStyle = dateStyle;
}
break;
case "System.Boolean":
//布尔型
bool boolValue = http://www.mamicode.com/false;
bool.TryParse(drValue, out boolValue);
cell.SetCellValue(boolValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = http://www.mamicode.com/0;
int.TryParse(drValue, out intValue);
cell.SetCellValue(intValue);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubleValue = http://www.mamicode.com/0;
double.TryParse(drValue, out doubleValue);
cell.SetCellValue(doubleValue);
cell.CellStyle = numberStyle;
break;
case "System.DBNull":
//空值处理
cell.SetCellValue(string.Empty);
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
}
#endregion
rowIndex++;
}
#endregion
hssfWorkbook.SetPrintArea(0, "A1:E15");
FileStream file = new FileStream(strFileName, FileMode.Create);
hssfWorkbook.Write(file);
file.Close();
//sheetGeneral.Dispose();
//sheetDetail.Dispose();
//hssfWorkbook.Dispose();
}
catch (Exception e)
{
XtraMessageBox.Show(e.Message, "数据导出异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
#region
/// <summary>
/// Excel文件
/// </summary>
/// <param name="dtSource">数据源DataTable</param>
/// <param name="titleName">标题名称</param>
/// <param name="recordTime">统计时间</param>
/// <param name="strFileName">Excel文件路径名</param>
public static void ExportToXlsStock(DataTable dtSource, string titleName, string recordTime, string strFileName)
{
try
{
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
InitializeWorkbook(hssfWorkbook);
ISheet sheet = hssfWorkbook.CreateSheet("Sheet1");
//日期样式
ICellStyle dateStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format = hssfWorkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
//时间样式
ICellStyle timeStyle = hssfWorkbook.CreateCellStyle();
IDataFormat format2 = hssfWorkbook.CreateDataFormat();
timeStyle.DataFormat = format2.GetFormat("yyyy-MM-dd HH:mm:ss");
//数值样式
ICellStyle numberStyle = hssfWorkbook.CreateCellStyle();
numberStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");
//标题样式
ICellStyle titleStyle = hssfWorkbook.CreateCellStyle();
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
titleStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont titleFont = hssfWorkbook.CreateFont();
titleFont.FontHeightInPoints = 14;
titleFont.Boldweight = 700;
titleStyle.SetFont(titleFont);
//第二行样式
ICellStyle nameStyle = hssfWorkbook.CreateCellStyle();
nameStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
nameStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont nameFont = hssfWorkbook.CreateFont();
nameFont.FontHeightInPoints = 11;
nameFont.Boldweight = 700;
nameStyle.SetFont(nameFont);
//表头样式
ICellStyle headStyle = hssfWorkbook.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.FillPattern = FillPatternType.NO_FILL;
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont headerFont = hssfWorkbook.CreateFont();
headerFont.FontHeightInPoints = 10;
headerFont.Boldweight = 700;
headStyle.SetFont(headerFont);
//获得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).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 = 2;
int sheetIndex = 2;
#region 当dtSource只有列头时，填充列头及样式
if (dtSource.Rows.Count == 0)
{
//标题行
IRow titleRow = sheet.CreateRow(0);
titleRow.CreateCell(0).SetCellValue(titleName);
titleRow.GetCell(0).CellStyle = titleStyle;
//第二行
IRow secondRow = sheet.CreateRow(1);
secondRow.CreateCell(0).SetCellValue("统计时间：");
secondRow.GetCell(0).CellStyle = nameStyle;
secondRow.CreateCell(1).SetCellValue(recordTime);
secondRow.GetCell(1).CellStyle = nameStyle;
IRow headerRow = sheet.CreateRow(2);
foreach (DataColumn col in dtSource.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//更改列宽
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(1, 20 * 256);
}
#endregion
#region 当dtSource有数据时，填充表头及内容
foreach (DataRow dr in dtSource.Rows)
{
#region 新建表，填充列头
if (rowIndex == 65536 || rowIndex == 2)
{
if (rowIndex != 2)
{
sheet = hssfWorkbook.CreateSheet("Sheet" + sheetIndex);
sheetIndex++;
}
#region 列头及样式
{
//标题行
IRow titleRow = sheet.CreateRow(0);
titleRow.CreateCell(0).SetCellValue(titleName);
titleRow.GetCell(0).CellStyle = titleStyle;
//第二行
IRow secondRow = sheet.CreateRow(1);
secondRow.CreateCell(0).SetCellValue("统计时间：");
secondRow.GetCell(0).CellStyle = nameStyle;
secondRow.CreateCell(1).SetCellValue(recordTime);
secondRow.GetCell(1).CellStyle = nameStyle;
IRow headerRow = sheet.CreateRow(2);
foreach (DataColumn col in dtSource.Columns)
{
headerRow.CreateCell(col.Ordinal).SetCellValue(col.ColumnName);
headerRow.GetCell(col.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(col.Ordinal, (arrColWidth[col.Ordinal] + 1) * 256);
}
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//更改列宽
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(1, 20 * 256);
}
#endregion
rowIndex = 3;
}
#endregion
#region 填充内容
IRow row = sheet.CreateRow(rowIndex);
foreach (DataColumn col in dtSource.Columns)
{
ICell cell = row.CreateCell(col.Ordinal);
if (dr[col] == DBNull.Value)
{
cell.SetCellType(CellType.BLANK);
cell.SetCellValue(string.Empty);
}
else
{
string drValue = http://www.mamicode.com/dr[col].ToString();
switch (col.DataType.ToString())
{
case "System.String":
//字符串
cell.SetCellValue(drValue);
break;
case "System.DateTime":
//日期型
DateTime dateValue;
DateTime.TryParse(drValue, out dateValue);
cell.SetCellValue(dateValue);
if (col.ColumnName.Contains("时间"))
{
cell.CellStyle = timeStyle;
}
else
{
cell.CellStyle = dateStyle;
}
break;
case "System.Boolean":
//布尔型
bool boolValue = http://www.mamicode.com/false;
bool.TryParse(drValue, out boolValue);
cell.SetCellValue(boolValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue = http://www.mamicode.com/0;
int.TryParse(drValue, out intValue);
cell.SetCellValue(intValue);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubleValue = http://www.mamicode.com/0;
double.TryParse(drValue, out doubleValue);
cell.SetCellValue(doubleValue);
cell.CellStyle = numberStyle;
break;
case "System.DBNull":
//空值处理
cell.SetCellValue(string.Empty);
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
}
#endregion
rowIndex++;
}
#endregion
FileStream file = new FileStream(strFileName, FileMode.Create);
hssfWorkbook.Write(file);
file.Close();
}
catch (Exception e)
{
XtraMessageBox.Show(e.Message, "数据导出异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion