public void ExportExcelDouble(DataTable dt)
{
try
{
//创建一个工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建一个 sheet 表
ISheet sheet = workbook.CreateSheet(dt.TableName);
//创建第一行
IRow rowFirst = sheet.CreateRow(0);
//创建第二行
IRow rowSecond = sheet.CreateRow(1);
//创建一个单元格
ICell cell = null;
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Justify; //垂直对齐(默认应该为center，如果center无效则用justify)
cellStyle.Alignment = HorizontalAlignment.Center; //水平对齐
//创建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
cellStyle.DataFormat = dataFormat.GetFormat("@");
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 2));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 3, 5));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 6, 8));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 10, 12));
rowFirst.CreateCell(0).SetCellValue("考试内容");
rowFirst.Cells[0].CellStyle = cellStyle;
rowFirst.CreateCell(3).SetCellValue("考试要求");
rowFirst.Cells[1].CellStyle = cellStyle;
rowFirst.CreateCell(6).SetCellValue("难度值");
rowFirst.Cells[2].CellStyle = cellStyle;
rowFirst.CreateCell(9).SetCellValue("题型");
rowFirst.Cells[3].CellStyle = cellStyle;
rowFirst.CreateCell(10).SetCellValue("题目来源");
rowFirst.Cells[4].CellStyle = cellStyle;
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowSecond.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowSecond.Cells[col.Ordinal].CellStyle = cellStyle;
}
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
//设置导出文件路径
string path = HttpContext.Current.Server.MapPath("/ImportExcel/");
//设置新建文件路径及名称
string savePath = path + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//创建文件
FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//还可以调用下面的方法，把流输出到浏览器下载
OutputClient(bytes);
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
workbook.Close();
sheet = null;
workbook = null;
}
catch (Exception ex)
{
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="columnNames">列名</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string[] columnNames)
{
if (columnNames != null && columnNames.Length != dtSource.Columns.Count)
{
throw new ArgumentException("参数不正确:columnNames,数组元素的个数需要和数据源列的数量相同!");
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "上海驰亚防伪科技有限公司";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "技术部";
si.Title = strHeaderText;
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
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();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.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));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
if (columnNames == null)
{
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (((arrColWidth[column.Ordinal] + 1) * 256) >= 40000)
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 40000);
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
else
{
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(columnNames[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (((arrColWidth[column.Ordinal] + 1) * 256) >= 40000)
{
sheet.SetColumnWidth(column.Ordinal, 40000);
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
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);
}
}
private void generate_my_data()
{
ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");
DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
IRow row0 = sheet0.CreateRow(0);
table.TableName = "Załatwienia";
table.Columns.Remove("id_");
row0.CreateCell(0).SetCellValue("Opis");
row0.CreateCell(1).SetCellValue("Sprawy według repetoriów i wykazów");
var crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet0.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 8);
sheet0.AddMergedRegion(crs);
row0 = sheet0.CreateRow(1);
row0.CreateCell(1).SetCellValue("C");
row0.CreateCell(2).SetCellValue("CG-G");
row0.CreateCell(3).SetCellValue("Ns");
row0.CreateCell(4).SetCellValue("Nc");
row0.CreateCell(5).SetCellValue("Co");
row0.CreateCell(6).SetCellValue("Cps");
row0.CreateCell(7).SetCellValue("WSC");
row0.CreateCell(8).SetCellValue("Łącznie");
int rol = 2;
foreach (DataRow rowik in table.Rows)
{
row0 = sheet0.CreateRow(rol);
for (int i = 0; i < 9; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row0.CreateCell(i).SetCellValue(ji);
row0.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
// druga tabela
view = (DataView)statystyki.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table = view.ToTable();
table.TableName = "Załatwienia";
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
//table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
//
//robienie
int ro = 2;
//-----------------
IDataFormat format = hssfworkbook.CreateDataFormat();
//-----------------
ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
IRow row2 = sheet1.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet1.AddMergedRegion(crs);
row2.CreateCell(1).SetCellValue("Nazwisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet1.AddMergedRegion(crs);
row2.CreateCell(2).SetCellValue("Imię");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet1.AddMergedRegion(crs);
row2.CreateCell(3).SetCellValue("Funkcja");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet1.AddMergedRegion(crs);
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet1.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Liczba sesji");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 6);
sheet1.AddMergedRegion(crs);
row2.CreateCell(7).SetCellValue("Załatwienia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 7, 14);
sheet1.AddMergedRegion(crs);
row2.CreateCell(15).SetCellValue("Il. sporządzonych uzasadnień");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 15, 15);
sheet1.AddMergedRegion(crs);
row2.CreateCell(16).SetCellValue("Nieobecności");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 16, 17);
sheet1.AddMergedRegion(crs);
row2 = sheet1.CreateRow(1);
row2.CreateCell(5).SetCellValue("rozprawy");
row2.CreateCell(6).SetCellValue("posiedzenia");
row2.CreateCell(7).SetCellValue("C");
row2.CreateCell(8).SetCellValue("C-GC");
row2.CreateCell(9).SetCellValue("Ns");
row2.CreateCell(10).SetCellValue("Nc");
row2.CreateCell(11).SetCellValue("Co");
row2.CreateCell(12).SetCellValue("Cps");
row2.CreateCell(13).SetCellValue("WSC");
row2.CreateCell(14).SetCellValue("Razem");
row2.CreateCell(16).SetCellValue("Urlopy");
row2.CreateCell(17).SetCellValue("Zwolnienia");
foreach (DataRow rowik in table.Rows)
{
row2 = sheet1.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// trzeci sheet
view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_06");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
sheet1.AutoSizeColumn(0, true);
sheet1.AutoSizeColumn(1, true);
ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");
row2 = sheet2.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet2.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Wyznaczenia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet2.AddMergedRegion(crs);
row2 = sheet2.CreateRow(1);
row2.CreateCell(5).SetCellValue("C");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("Ns");
row2.CreateCell(8).SetCellValue("Nc");
row2.CreateCell(9).SetCellValue("Co");
row2.CreateCell(10).SetCellValue("Cps");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet2.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// czwarty sheet
view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
// table.Columns.Remove("id_tabeli");
// table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_09");
table.Columns.Remove("d_06");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");
row2 = sheet3.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet3.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet3.AddMergedRegion(crs);
row2 = sheet3.CreateRow(1);
row2.CreateCell(5).SetCellValue("C");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("Ns");
row2.CreateCell(8).SetCellValue("Nc");
row2.CreateCell(9).SetCellValue("Co");
row2.CreateCell(10).SetCellValue("Cps");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
// row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet3.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
}
public void Report(string FileName)
{
DivideByFactory();
int pos = 0;
//Export to excel
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
#region Create fonts and styles
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFFont HeaderF2 = hssfworkbook.CreateFont();
HeaderF2.FontHeightInPoints = 10;
HeaderF2.Boldweight = 10 * 256;
HeaderF2.FontName = "Calibri";
HSSFFont HeaderF3 = hssfworkbook.CreateFont();
HeaderF3.FontHeightInPoints = 9;
HeaderF3.Boldweight = 9 * 256;
HeaderF3.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountDecCS = hssfworkbook.CreateCellStyle();
CountDecCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.000");
CountDecCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountDecCS.BottomBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountDecCS.LeftBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountDecCS.RightBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountDecCS.TopBorderColor = HSSFColor.BLACK.index;
CountDecCS.SetFont(SimpleF);
HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle();
CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS.BottomBorderColor = HSSFColor.BLACK.index;
CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS.LeftBorderColor = HSSFColor.BLACK.index;
CountCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS.RightBorderColor = HSSFColor.BLACK.index;
CountCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS.TopBorderColor = HSSFColor.BLACK.index;
CountCS.SetFont(SimpleF);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
//SimpleHeaderCS.WrapText = true;
SimpleHeaderCS.SetFont(HeaderF3);
#endregion Create fonts and styles
HSSFCell Cell1;
if (FrontsProfilDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, Профиль");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Фасад");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Квадратура");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < FrontsProfilDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr36"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr37"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr38"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr1"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToDouble(FrontsProfilDT.Rows[i]["Square"]));
Cell1.CellStyle = CountDecCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (FrontsTPSDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, ТПС");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Фасад");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Квадратура");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < FrontsTPSDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr36"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr37"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr38"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr1"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToDouble(FrontsTPSDT.Rows[i]["Square"]));
Cell1.CellStyle = CountDecCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (DecorProfilDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, Профиль");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Артикул");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Высота");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ширина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < DecorProfilDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Height"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Width"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (DecorTPSDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, ТПС");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Артикул");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Высота");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ширина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < DecorTPSDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Height"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Width"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
string tempFolder = System.Environment.GetEnvironmentVariable("TEMP");
FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls");
int j = 1;
while (file.Exists == true)
{
file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls");
}
FileStream NewFile = new FileStream(file.FullName, FileMode.Create);
hssfworkbook.Write(NewFile);
NewFile.Close();
System.Diagnostics.Process.Start(file.FullName);
ClearReport();
}
/// <summary>DataTable导出到Excel的MemoryStream</summary>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string[] str)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
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;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontName = str[5]; //[5]
font.FontHeightInPoints = Convert.ToInt16(str[0]); //[0]
font.Boldweight = Convert.ToInt16(str[1]); //[1]
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontName = str[6]; //[6]
font.FontHeightInPoints = Convert.ToInt16(str[2]); //[2]
font.Boldweight = Convert.ToInt16(str[3]); //[3]
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) * Convert.ToInt32(str[4])); //[4]
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
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();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
string result = drValue;
newCell.SetCellValue(result);
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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet;
//workbook.Dispose();
return(ms);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig)
{
int colint = 0;
for (int i = 0; i < dtSource.Columns.Count;)
{
DataColumn column = dtSource.Columns[i];
if (excelConfig.ColumnEntity[colint].Column != column.ColumnName)
{
dtSource.Columns.Remove(column.ColumnName);
}
else
{
i++;
if (colint < excelConfig.ColumnEntity.Count - 1)
{
colint++;
}
}
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#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 = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[dtSource.Columns.Count];
string[] arrColName = new string[dtSource.Columns.Count]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource.Columns)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
arrColName[item.Ordinal] = item.ColumnName.ToString();
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[item.Ordinal] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
columnStyle.Alignment = getAlignment(columnentity.Alignment);
}
}
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig.IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
int rowIndex = 0;
#region 表头及样式
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535)
{
sheet = workbook.CreateSheet();
rowIndex = 0;
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(rowIndex);
rowIndex++;
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
}
#endregion
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/********************************************************分割线往下方法不用与导入导出*********************************************************/
#region DataGridViewToExcel(DataGridView myDgv, string strHeaderText) DataTable导出到Excel的MemoryStream
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="myDgv">DataGridView控件名称</param>
/// <param name="strHeaderText">第一行标题头</param>
/// <param name="titleNames">列名称数组</param>
/// <returns>内存流</returns>
private static MemoryStream DataGridViewToExcel(DataGridView myDgv, string strHeaderText = null,
string[] titleNames = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#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 = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[myDgv.Columns.Count];
foreach (DataGridViewColumn item in myDgv.Columns)
{
arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText).Length;
}
for (int i = 0; i < myDgv.Rows.Count; i++)
{
for (int j = 0; j < myDgv.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataGridViewRow row in myDgv.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
if (!string.IsNullOrEmpty(strHeaderText))
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
font.FontName = "宋体";
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
rowIndex++;
}
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 14;
font.Boldweight = 500;
font.FontName = "宋体";
headStyle.SetFont(font);
if (titleNames != null)
{
if (titleNames.Length > 0)
{
for (int i = 0; i < titleNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(titleNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 100);
}
}
}
else
{
foreach (DataGridViewColumn column in myDgv.Columns)
{
headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
headerRow.GetCell(column.Index).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 100);
}
}
rowIndex++;
// headerRow.Dispose();
}
#endregion
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
if (row.Index > 0)
{
foreach (DataGridViewColumn column in myDgv.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Index);
string drValue = myDgv[column.Index, row.Index - 1].Value.ToString();
switch (column.ValueType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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;
}
}
}
else
{
rowIndex--;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// List导出到Excel的MemoryStream
/// </summary>
/// <param name="list">数据源</param>
/// <param name="sHeaderText">表头文本</param>
/// <param name="columns">需要导出的属性</param>
private MemoryStream CreateExportMemoryStream(List <T> list, string sHeaderText, string[] columns)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = ReflectionHelper.GetProperties(type, columns);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
//单元格填充循环外设定单元格格式，避免4000行异常
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[properties.Length];
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
//GBK对应的code page是CP936
arrColWidth[columnIndex] = properties[columnIndex].Name.Length;
}
#endregion
for (int rowIndex = 0; rowIndex < list.Count; rowIndex++)
{
#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(sHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.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, properties.Length - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
// 类属性如果有Description就用Description当做列名
DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(properties[columnIndex], typeof(DescriptionAttribute));
string description = properties[columnIndex].Name;
if (customAttribute != null)
{
description = customAttribute.Description;
}
headerRow.CreateCell(columnIndex).SetCellValue(description);
headerRow.GetCell(columnIndex).CellStyle = headStyle;
//根据表头设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
}
}
#endregion
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex + 2); // 前面2行已被占用
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
newCell.CellStyle = contentStyle;
string drValue = properties[columnIndex].GetValue(list[rowIndex], null).ParseToString();
//根据单元格内容设定列宽
int length = Math.Min(253, Encoding.UTF8.GetBytes(drValue).Length + 1) * 256;
if (sheet.GetColumnWidth(columnIndex) < length && !drValue.IsEmpty())
{
sheet.SetColumnWidth(columnIndex, length);
}
switch (properties[columnIndex].PropertyType.ToString())
{
case "System.String":
newCell.SetCellValue(drValue);
break;
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
newCell.SetCellValue(drValue.ParseToDateTime());
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
newCell.SetCellValue(drValue.ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
case "System.Int16":
case "System.Nullable`1[System.Int16]":
case "System.Int32":
case "System.Nullable`1[System.Int32]":
newCell.SetCellValue(drValue.ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
newCell.SetCellValue(drValue.ParseToString());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.Single":
case "System.Nullable`1[System.Single]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.DBNull":
newCell.SetCellValue(string.Empty);
break;
default:
newCell.SetCellValue(string.Empty);
break;
}
}
#endregion
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
workbook.Close();
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
#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 = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[properties.Length];
string[] arrColName = new string[properties.Length]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
int i = 0;
foreach (PropertyInfo column in properties)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
arrColName[i] = column.Name;
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
if (columnentity != null)
{
arrColName[i] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[i] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
}
}
arryColumStyle[i] = columnStyle;
i++;
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (T item in lists)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
int headIndex = 0;
foreach (PropertyInfo column in properties)
{
headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
headIndex++;
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int ordinal = 0;
foreach (PropertyInfo column in properties)
{
ICell newCell = dataRow.CreateCell(ordinal);
newCell.CellStyle = arryColumStyle[ordinal];
string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString();
SetCell(newCell, dateStyle, column.PropertyType, drValue);
ordinal++;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
static void ExportToExcel(object sender, ExportExcelEventArgs e)
{
var workBook = new HSSFWorkbook();
var sheet = workBook.CreateSheet("exported-data");
if (e.Cannelled)
{
return;
}
var titleStyle1 = CreateTitleStyle(workBook);
var dataStyle = CreateDataStyle(workBook);
var dataStyle1 = workBook.CreateCellStyle(); // 文本。
dataStyle1.CloneStyleFrom(dataStyle);
var dataStyle2 = workBook.CreateCellStyle(); // 整数。
dataStyle2.CloneStyleFrom(dataStyle);
dataStyle2.DataFormat = workBook.CreateDataFormat().GetFormat("0");
dataStyle2.Alignment = HorizontalAlignment.Right;
var dataStyle3 = workBook.CreateCellStyle(); // 整数金额。
dataStyle3.CloneStyleFrom(dataStyle);
dataStyle3.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0");
dataStyle3.Alignment = HorizontalAlignment.Right;
var dataStyle4 = workBook.CreateCellStyle(); // 金额。
dataStyle4.CloneStyleFrom(dataStyle);
dataStyle4.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00");
dataStyle4.Alignment = HorizontalAlignment.Right;
var dataStyle5 = workBook.CreateCellStyle(); // 日期。
dataStyle5.CloneStyleFrom(dataStyle);
dataStyle5.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/MM/dd");
var dataStyle6 = workBook.CreateCellStyle(); // 日期时间。
dataStyle6.CloneStyleFrom(dataStyle);
dataStyle6.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/MM/dd HH:mm:ss");
var dataStyle7 = workBook.CreateCellStyle(); // 百分比
dataStyle7.CloneStyleFrom(dataStyle);
dataStyle7.DataFormat = workBook.CreateDataFormat().GetFormat("0.00%");
var dataStyle8 = workBook.CreateCellStyle(); // 是否。
dataStyle8.CloneStyleFrom(dataStyle);
// 创建空白行
sheet.CreateRow(0);
if (e.Cannelled)
{
return;
}
// 创建标题行。
var titleRow = sheet.CreateRow(1);
for (var i = 0; i < e.Columns.Count; ++i)
{
if (e.Cannelled)
{
return;
}
var colIndex = i + 1; // Excel表格中的列序号。
var col = e.Columns[i];
var cell = titleRow.CreateCell(colIndex);
cell.SetCellValue(col.Title);
cell.CellStyle = titleStyle1;
sheet.SetColumnWidth(colIndex, col.Width * 50 /* 1/5 of character width */);
}
var total = e.Binding.DataTable.Count;
for (int j = 0; j < total; ++j)
{
if (e.Cannelled)
{
return;
}
var rowIndex = j + 2; // Excel表格中的行序号。
var dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < e.Columns.Count; ++i)
{
if (e.Cannelled)
{
return;
}
var colIndex = i + 1; // Excel表格中的列序号。
var col = e.Columns[i];
var cell = dataRow.CreateCell(colIndex);
var value = e.Binding.GetCellValue(j, col.DataKey);
if (value == null || string.IsNullOrWhiteSpace(value.ToString()))
{
continue;
}
switch (col.Type)
{
case MyGridColumnType.Boolean:
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(value + "");
cell.CellStyle = dataStyle8;
break;
case MyGridColumnType.Date:
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(Convert.ToDateTime(value));
cell.CellStyle = dataStyle5;
break;
case MyGridColumnType.DateTime:
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(Convert.ToDateTime(value));
cell.CellStyle = dataStyle6;
break;
case MyGridColumnType.Money:
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(value));
cell.CellStyle = dataStyle4;
break;
case MyGridColumnType.IntMoney:
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(value));
cell.CellStyle = dataStyle3;
break;
case MyGridColumnType.Percent:
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(value));
cell.CellStyle = dataStyle7;
break;
case MyGridColumnType.Number:
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(value));
cell.CellStyle = dataStyle2;
break;
default:
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(Convert.ToString(value));
cell.CellStyle = dataStyle1;
break;
}
}
e.UpdateProgress(j + 1);
}
if (e.Cannelled)
{
return;
}
using (var fs = File.OpenWrite(e.FileName)) {
e.UpdateProgress(total, "保存文件...");
workBook.Write(fs);
}
e.UpdateProgress(total, "已保存");
} // end of Export.
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource"></param>
/// <param name="dateFormat"></param>
/// <returns></returns>
private static MemoryStream DataTableToExcel(DataTable dtSource, string dateFormat)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(dateFormat);
//取得列宽
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 = (HSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
headStyle.Alignment = HorizontalAlignment.Center; //水平居中
headStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
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);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle.Alignment = HorizontalAlignment.Center;
newCell.CellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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();
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="html">表头html结构</param>
/// <param name="dateFormat">时间格式（默认：yyyy-MM-dd）</param>
private static MemoryStream DataTableToComplexExcel(DataTable dtSource, string html, string dateFormat)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(dateFormat);
//设置单元格样式及字体
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10; //字体大小
font.Boldweight = 700; //字体粗细
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
//取得列宽
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 = (HSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
{
//获取页面html并对tr进行筛选
MatchCollection rowCollection = Regex.Matches(html, @"<tr[^>]*>[\s\S]*?<\/tr>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
//写在tr循环中
for (int i = 0; i < rowCollection.Count; i++)
{
HSSFRow rowTitle = (HSSFRow)sheet.CreateRow(i);
string rowContent = rowCollection[i].Value;
//对td进行筛选
MatchCollection columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[\s\S]*?<\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
//遍历td
for (int j = 0; j < columnCollection.Count; j++)
{
var match = Regex.Match(columnCollection[j].Value, "<td.*?rowspan=\"(?<row>.*?)\".*?colspan=\"(?<col>.*?)\".*?row=\"(?<row1>.*?)\".*?col=\"(?<col1>.*?)\".*?class=\"(?<class>.*?)\">(?<value>.*?)<\\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
if (match.Success)
{
int rowspan = Convert.ToInt32(match.Groups["row"].Value); //表格跨行
int colspan = Convert.ToInt32(match.Groups["col"].Value); //表格跨列
int rowNo = Convert.ToInt32(match.Groups["row1"].Value); //所在行
int colNo = Convert.ToInt32(match.Groups["col1"].Value); //所在列
string value = match.Groups["value"].Value;
if (colspan == 1) //判断是否跨列
{
var cell = rowTitle.CreateCell(colNo); //创建列
cell.SetCellValue(value); //设置列的值
cell.CellStyle = cellStyle;
if (value.Length > 0)
{
int width = value.Length * 25 / 6;
if (width > 255)
{
width = 250;
}
sheet.SetColumnWidth(colNo, width * 256);
}
}
//判断是否跨行、跨列
if (rowspan > 1 || colspan > 1)
{
int firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 0;
if (rowspan > 1)//跨行
{
firstRow = rowNo;
lastRow = firstRow + rowspan - 1;
}
else
{
firstRow = lastRow = i;
}
if (colspan > 1)//跨列
{
firstCol = colNo;
int cols = colNo + colspan;
for (; colNo < cols; colNo++)
{
var cell = rowTitle.CreateCell(colNo);
cell.SetCellValue(value);
cell.CellStyle = cellStyle;
}
lastCol = colNo - 1;
}
else
{
firstCol = lastCol = colNo;
}
//设置起始行数，结束行数，起始列数，结束列数
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
}
}
}
rowIndex = rowCollection.Count;
}
#endregion
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle.Alignment = HorizontalAlignment.Center; //水平居中;
newCell.CellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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();
return(ms);
}
}
public ActionResult ExportData(Pagination pagination, string queryJson)
{
try {
pagination.page = 1;
pagination.rows = 100000000;
pagination.p_kid = "v_info.userid";
pagination.p_fields = @"";
pagination.p_tablename = "v_userinfo v_info left join base_department d on v_info.departmentid = d.departmentid left join (select * from HJB_PERSONSET where MODULETYPE = 1) t on v_info.userid = t.userid";
pagination.conditionJson = " Account!='System'";
pagination.sidx = "DEPTSORTss,v_info.DEPTSORT asc,v_info.deptcode asc,v_info.userid";
pagination.sord = "desc";
var data = hikinoutlogbll.GetTableUserRole(pagination, queryJson);
var queryParam = queryJson.ToJObject();
foreach (DataRow dr in data.Rows)
{
if (dr["nature"].ToString() == "专业" || dr["nature"].ToString() == "班组")
{
//DataTable dt = departmentBLL.GetDataTable(string.Format("select fullname from base_department where encode=(select encode from base_department t where instr('{0}',encode)=1 and nature='{1}' and organizeid='{2}') or encode='{0}' order by deptcode", dr["DEPARTMENTCODE"], "部门", dr["organizeid"]));
//if (dt.Rows.Count > 0)
//{
// string name = "";
// foreach (DataRow dr1 in dt.Rows)
// {
// name += dr1["fullname"].ToString() + "/";
// }
// dr["dutyname"] = name.TrimEnd('/');
//}
dr["dutyname"] = dr["dutyname"] + "(" + dr["DEPTNAME"] + ")";
}
if (dr["userid"] != null)
{
string sTime = queryParam["sTime"].ToString();
string eTime = queryParam["eTime"].ToString();
string userId = dr["userid"].ToString();
string sql = string.Format(@"select COUNT(a.USERID) as num from(select DISTINCT userid, inout, devicename, CREATEDATE from bis_hikinoutlog) a
left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 0 and
a.CREATEDATE >= to_date('{0}', 'yyyy-MM-dd') and a.CREATEDATE <= to_date('{1}', 'yyyy-MM-dd')
and(case when(b.ISEPIBOLY = '否' and b.DEPTTYPE is null and length(b.deptcode) > 20) then(select fullname from base_department d where d.deptcode = substr(b.deptcode, 1, 20))
else b.DEPTNAME end) = (select case when v.nature = '班组' then v.parentname else v.DEPTNAME end as bmname from v_userinfo v where v.USERID = '{2}')" , sTime, eTime, userId);
DataTable dt = operticketmanagerbll.GetDataTable(sql);
if (dt.Rows.Count > 0)
{
dr["bmname"] = dr["bmname"] + "(进门总次数：" + dt.Rows[0]["num"].ToString();
}
string sql2 = string.Format(@"select COUNT(a.USERID) as num from(select DISTINCT userid, inout, devicename, CREATEDATE from bis_hikinoutlog) a
left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 1 and
a.CREATEDATE >= to_date('{0}', 'yyyy-MM-dd') and a.CREATEDATE <= to_date('{1}', 'yyyy-MM-dd')
and(case when(b.ISEPIBOLY = '否' and b.DEPTTYPE is null and length(b.deptcode) > 20) then(select fullname from base_department d where d.deptcode = substr(b.deptcode, 1, 20))
else b.DEPTNAME end) = (select case when v.nature = '班组' then v.parentname else v.DEPTNAME end as bmname from v_userinfo v where v.USERID = '{2}')" , sTime, eTime, userId);
DataTable dt2 = operticketmanagerbll.GetDataTable(sql2);
if (dt2.Rows.Count > 0)
{
dr["bmname"] = dr["bmname"] + "出门总次数：" + dt2.Rows[0]["num"].ToString() + ")";
}
}
}
//设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = "人员进出门禁数据信息";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 25;
//excelconfig.FileName = "人员进出门禁数据导出.xls";
//excelconfig.IsAllSizeColumn = true;
////每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "bmname".ToLower(), ExcelColumn = "单位(部门)" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname".ToLower(), ExcelColumn = "姓名" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "intnum".ToLower(), ExcelColumn = "进门次数(次)" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "outnum".ToLower(), ExcelColumn = "出门次数(次)" });
////调用导出方法
//ExcelHelper.ExcelDownload(data, excelconfig);
//导出excel
string title = "人员进出门禁数据信息";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 24 * 20;
int column = data.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.HeightInPoints = 30;
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 25;
font.FontName = "微软雅黑";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
//headerFont.FontHeightInPoints = 4;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("单位(部门)");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("岗位名称");
row1.GetCell(2).CellStyle = headerStyle;
row1.CreateCell(3).SetCellValue("进门次数(次)");
row1.GetCell(3).CellStyle = headerStyle;
row1.CreateCell(4).SetCellValue("出门次数(次)");
row1.GetCell(4).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
//font1.Boldweight = 25;
//font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < data.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
//rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(data.Rows[i]["bmname"].ToString());
rowTemp.CreateCell(1).SetCellValue(data.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(data.Rows[i]["dutyname"].ToString());
rowTemp.CreateCell(3).SetCellValue(data.Rows[i]["intnum"].ToString());
rowTemp.CreateCell(4).SetCellValue(data.Rows[i]["outnum"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
rowTemp.GetCell(3).CellStyle = bodyStyle;
rowTemp.GetCell(4).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
sheet.AutoSizeColumn(4);
//合并单元格
MergeCells(sheet, data, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex) {
}
return(Success("导出成功。"));
}
/// <summary>
/// Excel导出文件流（服务项目调用）
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelHeadTitle">文件第一行标题</param>
public static MemoryStream GetExcelStream(DataTable dtSource, string excelHeadTitle = "")
{
HSSFWorkbook workbook = new HSSFWorkbook();
ExcelInformation information = new ExcelInformation();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = information.Company;
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = information.Author; //填加xls文件作者信息
si.ApplicationName = information.ApplicationName; //填加xls文件创建程序信息
si.LastAuthor = information.LastAuthor; //填加xls文件最后保存者信息
si.Comments = information.Comments; //填加xls文件作者信息
si.Title = information.Title; //填加xls文件标题信息
si.Subject = information.Subject; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
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();
}
#region 表头及样式
if (!string.IsNullOrWhiteSpace(excelHeadTitle))
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelHeadTitle);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.IsBold = true;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = string.IsNullOrWhiteSpace(excelHeadTitle) ? sheet.CreateRow(0) : sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.IsBold = true;
//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;
var arr = (arrColWidth[column.Ordinal] + 1) * 256;
//限定宽度
if (arrColWidth[column.Ordinal] > 100)
{
arrColWidth[column.Ordinal] = 100;
}
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = string.IsNullOrWhiteSpace(excelHeadTitle) ? 1 : 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.BaseType.FullName)
{
case "System.Enum": //枚举类型
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
continue;
}
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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":
//long intV = 0;
//long.TryParse(drValue, out intV);
newCell.SetCellValue(drValue);
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();
return(ms);
}
}
/// <summary>
/// 生成模板
/// </summary>
/// <param name="displayName">文件名</param>
/// <returns>生成的模版文件</returns>
public byte[] GenerateTemplate(out string displayName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
InitExcelData();
CreateDataTable(); //add by dufei
SetTemplateDataValus(); //add by dufei
if (!string.IsNullOrEmpty(FileDisplayName))
{
displayName = FileDisplayName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
else
{
displayName = this.GetType().Name + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
//模板sheet页
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
workbook.SetSheetName(0, string.IsNullOrEmpty(FileDisplayName) ? this.GetType().Name : FileDisplayName);
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.HeightInPoints = 20;
HSSFSheet enumSheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow enumSheetRow1 = (HSSFRow)enumSheet.CreateRow(0);
enumSheetRow1.CreateCell(0).SetCellValue("是");
enumSheetRow1.CreateCell(1).SetCellValue("否");
enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号
HSSFSheet dataSheet = (HSSFSheet)workbook.CreateSheet();
#region 设置excel模板列头
//默认灰色
var headerStyle = GetCellStyle(workbook);
headerStyle.IsLocked = true;
//黄色
var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow);
yellowStyle.IsLocked = true;
//红色
var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red);
redStyle.IsLocked = true;
//取得所有ExcelPropety
var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
int _currentColunmIndex = 0;
bool IsProtect = false;
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this);
ColumnDataType dateType = excelPropety.DataType;
if (excelPropety.ReadOnly)
{
IsProtect = true;
}
//给必填项加星号
string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(colName);
//修改列头样式
switch (excelPropety.BackgroudColor)
{
case BackgroudColorEnum.Yellow:
row.Cells[_currentColunmIndex].CellStyle = yellowStyle;
break;
case BackgroudColorEnum.Red:
row.Cells[_currentColunmIndex].CellStyle = redStyle;
break;
default:
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
break;
}
var dataStyle = workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
if (dateType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex];
string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName);
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
if (dynamicCol.ReadOnly)
{
IsProtect = true;
}
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置单元格样式及数据类型
dataStyle.IsLocked = excelPropety.ReadOnly;
dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
else
{
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置是否锁定
dataStyle.IsLocked = excelPropety.ReadOnly;
//设置单元格样式及数据类型
excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
#endregion
#region 添加模版数据 add by dufei
if (TemplateDataTable.Rows.Count > 0)
{
for (int i = 0; i < TemplateDataTable.Rows.Count; i++)
{
DataRow tableRow = TemplateDataTable.Rows[i];
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(1 + i);
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
string colName = propetys[porpetyIndex].Name;
tableRow[colName].ToString();
dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString());
}
}
}
#endregion
//冻结行
sheet.CreateFreezePane(0, 1, 0, 1);
//锁定excel
if (IsProtect)
{
sheet.ProtectSheet("password");
}
workbook.SetSheetHidden(1, true);
workbook.SetSheetHidden(2, true);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
private void generate_my_data()
{
ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");//*
DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
DataTable dT = (DataTable)Session["header_01"];
table.TableName = "Załatwienia";
table.Columns.Remove("id_");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
var crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0);
IRow row0 = sheet0.CreateRow(0);
#region tabela1
foreach (DataRow dR in getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(1);
foreach (DataRow dR in getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(2);
foreach (DataRow dR in getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
int rol = 3;
foreach (DataRow rowik in table.Rows)
{
row0 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row0.CreateCell(i).SetCellValue(ji);
row0.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
#endregion tabela1
#region drugi arkusz
// druga tabela
view = (DataView)dane_do_tabeli_2.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.TableName = "Załatwienia";
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
//
//robienie
int ro = 2;
//-----------------
IDataFormat format = hssfworkbook.CreateDataFormat();
ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
IRow row2 = sheet1.CreateRow(0);
dT.Clear();
dT = (DataTable)Session["header_02"];
//===========
foreach (DataRow dR in getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(1);
foreach (DataRow dR in getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(2);
foreach (DataRow dR in getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
rol = 3;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
foreach (DataRow rowik in table.Rows)
{
row2 = sheet1.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
#endregion drugi arkusz
// trzeci sheet
view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
sheet1.AutoSizeColumn(0, true);
sheet1.AutoSizeColumn(1, true);
ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");
row2 = sheet2.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet2.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Wyznaczenia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet2.AddMergedRegion(crs);
row2 = sheet2.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet2.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// czwarty sheet
view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
//table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_09");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");
row2 = sheet3.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet3.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet3.AddMergedRegion(crs);
row2 = sheet3.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
// row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet3.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
}
/// <summary>
/// 设置日期格式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static short SetDateFormat(this HSSFWorkbook workbook)
{
HSSFDataFormat format = workbook.CreateDataFormat();
return(format.GetFormat("yyyy-mm-dd"));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <Author> 2010-5-8 22:21:41</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, Dictionary <string, string> columnNames, Dictionary <string, string> dataformats)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "kakake"; //填加xls文件作者信息
si.ApplicationName = ""; //填加xls文件创建程序信息
si.LastAuthor = ""; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now.ToCstTime();
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
HSSFCellStyle customStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat customformat = (HSSFDataFormat)workbook.CreateDataFormat();
//取得列宽
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;
int index = 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);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
if (columnNames.Count > 0)
{
sheet.AddMergedRegion(new Region(0, 0, 0, columnNames.Count - 1));
}
else
{
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
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);
index = 0;
foreach (DataColumn column in dtSource.Columns)
{
if (columnNames.Count > 0)
{
if (columnNames.ContainsKey(column.ColumnName))
{
headerRow.CreateCell(index).SetCellValue(columnNames[column.ColumnName]);
headerRow.GetCell(index).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(index, (Encoding.GetEncoding(936).GetBytes(columnNames[column.ColumnName]).Length + 1) * 256);
index++;
}
}
else
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
index = 0;
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
if (columnNames.Count > 0)
{
if (columnNames.ContainsKey(column.ColumnName))
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(index);
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);
//HSSFCellStyle celldoubleStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//int pos = Convert.ToString(doubV).Length - Convert.ToString(doubV).IndexOf('.') - 1;
//if (pos == 4)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000");
//}
//else if (pos == 2)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//}
//newCell.CellStyle = celldoubleStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
if (dataformats.ContainsKey(column.ColumnName))
{
customStyle.DataFormat = customformat.GetFormat(dataformats[column.ColumnName]);
newCell.CellStyle = customStyle;
}
index++;
}
}
else
{
HSSFCell newCell = (HSSFCell)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);
//HSSFCellStyle celldoubleStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//int pos = Convert.ToString(doubV).Length - Convert.ToString(doubV).IndexOf('.') - 1;
//if (pos == 4)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000");
//}
//else if (pos == 2)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//}
//newCell.CellStyle = celldoubleStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
if (dataformats.ContainsKey(column.ColumnName))
{
customStyle.DataFormat = customformat.GetFormat(dataformats[column.ColumnName]);
newCell.CellStyle = customStyle;
}
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Workbook.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放 sheet
return(ms);
}
}
/// <summary>DataSet导出到Excel的MemoryStream</summary>
/// <param name="ds">源DataSet</param>
/// <param name="strHeaderTexts">表格头文本值集合</param>
/// <param name="sheetCombineColIndexs">每个表格的要垂直合并的列的序号如：{"0,1","2"}表示表1的第0和1列进行合并,表2的第2列进行合并</param>
/// <returns></returns>
public static MemoryStream ExportDS(DataSet ds, List <string> strHeaderTexts, List <string> sheetCombineColIndexs)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = null;
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.jack.com/";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Jack"; //填加xls文件作者信息
si.ApplicationName = "jackExcel"; //填加xls文件创建程序信息
si.LastAuthor = "Jack"; //填加xls文件最后保存者信息
si.Comments = "Jack导出的excel"; //填加xls文件作者信息
si.Title = "Jack导出的excel"; //填加xls文件标题信息
si.Subject = "Jack导出的excel"; //填加文件主题信息
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 HH:mm:ss.fff");
for (int i = 0; i < ds.Tables.Count; i++)
{
#region 填充单个sheet
int contentRowStartIndex = 0;//表格内容的内容数据起始行,用于合并同列多行之间的合并
sheet = workbook.CreateSheet(ds.Tables[i].TableName.StartsWith("Table") ? "Sheet" + (i + 1).ToString() : ds.Tables[i].TableName) as HSSFSheet;
sheet.DefaultRowHeight = 22 * 20;
DataTable dtSource = ds.Tables[i];
//取得列宽
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 ii = 0; ii < dtSource.Rows.Count; ii++)
{
//遍历数据内容,根据每一列的数据最大长度设置列宽
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[ii][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
#region 填充表头,列头,数据内容
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 表头及样式
if (strHeaderTexts != null && strHeaderTexts.Count - 1 >= i)
{
if (!string.IsNullOrWhiteSpace(strHeaderTexts[i]))
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderTexts[i]);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 15;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
rowIndex++;
//headerRow.Dispose();
}
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(rowIndex) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headerRow.HeightInPoints = 23;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 11;
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;
//设置列宽，这里我多加了10个字符的长度
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 10) * 256);
}
rowIndex++;
contentRowStartIndex = rowIndex;//记住数据内容的起始行
//headerRow.Dispose();
}
#endregion
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
dataRow.HeightInPoints = 22;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
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++;
}
#endregion
Hashtable ht = new Hashtable();
#region 列中多行之间的合并
if (sheetCombineColIndexs != null && sheetCombineColIndexs.Count > i)
{
List <int> combineColIndexs = new List <int>();
string[] strarr = sheetCombineColIndexs[i].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var item in strarr)
{
combineColIndexs.Add(int.Parse(item));
}
for (int j = contentRowStartIndex; j < rowIndex; j++)
{
for (int jj = 0; jj < combineColIndexs.Count; jj++)
{
int cloIndex = combineColIndexs[jj];
if (j == contentRowStartIndex)
{
Entry entry = new Entry();
entry.startIndex = contentRowStartIndex;
object o = GetCellValue(sheet.GetRow(contentRowStartIndex).Cells[cloIndex]);
entry.combineValue = o == null ? "" : o.ToString();
ht.Add(cloIndex, entry);
continue;
}
object obj = GetCellValue(sheet.GetRow(j).Cells[cloIndex]);
string value = obj == null ? "" : obj.ToString();
Entry en = (Entry)ht[cloIndex];
if (en.combineValue != value)
{
//如果发生不相等的情况则满足合并条件(最少是2行)就会合并
if (en.startIndex + 1 < j)
{
sheet.AddMergedRegion(new Region(en.startIndex, cloIndex, j - 1, cloIndex));
ICell cell = sheet.GetRow(en.startIndex).Cells[cloIndex];
ICellStyle cellstyle = workbook.CreateCellStyle(); //设置垂直居中格式
cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cell.CellStyle = cellstyle;
}
en.combineValue = value;
en.startIndex = j;
}
else
{
//如果相等了,再判断是不是最后一行,如果是最后一行也要合并
if (j == rowIndex - 1)
{
sheet.AddMergedRegion(new Region(en.startIndex, cloIndex, j, cloIndex));
ICell cell = sheet.GetRow(en.startIndex).Cells[cloIndex];
ICellStyle cellstyle = workbook.CreateCellStyle(); //设置垂直居中格式
cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cell.CellStyle = cellstyle;
}
}
}
}
}
#endregion
#endregion
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream NpoiExport(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
//GBK对应的code page是CP936
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;
}
}
}
#endregion
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;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
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);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = contentStyle;
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);
}
}
/// <summary>
/// 由DataSet导出Excel(带有格式)
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataSetToFormatExcel(DataSet sourceDs, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
string[] sheetNames = sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); //分割符
for (int i = 0; i < sheetNames.Length; i++)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]);
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count];
foreach (DataColumn item in sourceDs.Tables[i].Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int k = 0; k < sourceDs.Tables[i].Rows.Count; k++)
{
for (int j = 0; j < sourceDs.Tables[i].Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(sourceDs.Tables[i].Rows[k][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
int sheetnum = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
#region 创建表头
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheetnum++;
sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i] + "-" + sheetnum.ToString());
}
var headerRow = sheet.CreateRow(0);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//设置边框
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headStyle.BottomBorderColor = HSSFColor.BLACK.index;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
headStyle.LeftBorderColor = HSSFColor.BLACK.index;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
headStyle.RightBorderColor = HSSFColor.BLACK.index;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headStyle.TopBorderColor = HSSFColor.BLACK.index;
//设置背景色
//headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIME.index;
//headStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.BIG_SPOTS;
//headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index;
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
rowIndex = 1;
}
#endregion
#region 创建内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
case "MySql.Data.Types.MySqlDateTime": //MySql类型
if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue))
{
//当时间为空，防止生成的execl 中是一串“#######”号，所有赋值为空字符串
newCell.SetCellValue("");
}
else
{
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++;
}
//设置首行首列冻结
//第一个参数表示要冻结的列数
//第二个参数表示要冻结的行数
//第三个参数表示右边区域可见的首列序号，从1开始计算
//第四个参数表示下边区域可见的首行序号，也是从1开始计算
//sheet.CreateFreezePane(1, 1, 0,0);
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return(ms);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
private static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#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
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)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++)
{
if (dtSource.Rows[i][j] == null)
{
continue;
}
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 = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)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;
if ((arrColWidth[column.Ordinal] + 1) * 256 >= 10000)
{
sheet.SetColumnWidth(column.Ordinal, 10000);
}
else
{
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
/// <summary>
/// 带格式化的
/// </summary>
/// <param name="dtSource">数据源</param>
/// <param name="sheetName">创建的Sheet名称</param>
/// <returns></returns>
private static Stream ExportDataTableToFormatExcel(DataTable dtSource, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet(sheetName);
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
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;
int sheetnum = 1;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheetnum++;
sheet = workbook.CreateSheet(sheetName + "-" + sheetnum.ToString());
}
#region 列头及样式
{
var headerRow = sheet.CreateRow(0);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//设置背景色
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
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);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
case "MySql.Data.Types.MySqlDateTime": //MySql类型
if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue))
{
//当时间为空，防止生成的execl 中是一串“#######”号，所有赋值为空字符串
newCell.SetCellValue("");
}
else
{
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;
return(ms);
}
}
public ActionResult ExportData(string state)
{
try {
var curuser = OperatorProvider.Provider.Current();
string sql = @"select case when b.NATURE = '班组' then b.PARENTNAME else b.DEPTNAME END as DEPTNAME,ID,a.userid,b.REALNAME,b.gender,b.dutyname,a.devicename,TO_CHAR(a.CREATEDATE,'yyyy-mm-dd hh24:mi:ss') as datetime,case when (length(b.deptcode)>20) then (select d.SORTCODE from base_department d where d.deptcode = substr(b.deptcode,1,20)) else b.DEPTSORT end as DEPTSORTss from
bis_hikinoutlog a left join V_USERINFO b on a.userid = b.userid left join(select* from HJB_PERSONSET where MODULETYPE = 0) t on a.userid = t.userid
where REALNAME is not NULL and a.inout = 0 and not exists(select 1 from bis_hikinoutlog d where d.userid = a.userid and d.CREATEDATE + 0 > a.CREATEDATE + 0)
";
//判断当前登陆用户是什么级别
if (!curuser.RoleName.IsEmpty())
{
string RoleName = curuser.RoleName.ToString();
if (ViewBag.IsAppointAccount != 1)
{
if (RoleName.Contains("承包商级用户"))
{
//承包商级用户只可查看本单位门禁数据
sql += string.Format(" and (t.ISREFER is NULL or t.userid = '{0}')", curuser.UserId);
sql += string.Format(@" and b.ROLENAME like '%{0}%' and b.DEPTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{1}')", RoleName, curuser.UserId);
}
}
else if (RoleName.Contains("厂级部门用户") || RoleName.Contains("安全管理员") || RoleName.Contains("公司领导") || RoleName.Contains("公司管理员") || RoleName.Contains("公司级用户") || RoleName.Contains("超级管理员") || curuser.UserId.Contains("1521c21a-62c9-4aa1-9093-a8bda503ea89"))
{
//此级别的用户可查看所有数据
}
else
{
sql += string.Format(" and (t.ISREFER is NULL or t.userid = '{0}')", curuser.UserId);
if (state == "0")
{
sql += string.Format(@" and (b.DEPTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{0}') or b.PARENTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname
from v_userinfo a where a.USERID = '{0}'))", curuser.UserId);
}
}
}
if (state == "0")
{
sql += " and DEPTTYPE is NULL ORDER BY DEPTSORTss, b.deptsort,b.DEPTCODE,b.userid desc";
}
else
{
sql += " and DEPTTYPE is not NULL ORDER BY DEPTTYPE,DEPTSORTss,b.deptsort,b.DEPTCODE,b.userid desc";
}
DataTable data = operticketmanagerbll.GetDataTable(sql);
//导出excel
string title = "在厂人员统计信息";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 24 * 20;
int column = data.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.HeightInPoints = 30;
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 25;
font.FontName = "微软雅黑";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
//headerFont.FontHeightInPoints = 4;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("部门名称");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("性别");
row1.GetCell(2).CellStyle = headerStyle;
row1.CreateCell(3).SetCellValue("岗位名称");
row1.GetCell(3).CellStyle = headerStyle;
row1.CreateCell(4).SetCellValue("门禁通道名称");
row1.GetCell(4).CellStyle = headerStyle;
row1.CreateCell(5).SetCellValue("进厂时间");
row1.GetCell(5).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
//font1.Boldweight = 25;
//font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < data.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
//rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(data.Rows[i]["deptname"].ToString());
rowTemp.CreateCell(1).SetCellValue(data.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(data.Rows[i]["gender"].ToString());
rowTemp.CreateCell(3).SetCellValue(data.Rows[i]["dutyname"].ToString());
rowTemp.CreateCell(4).SetCellValue(data.Rows[i]["devicename"].ToString());
rowTemp.CreateCell(5).SetCellValue(data.Rows[i]["datetime"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
rowTemp.GetCell(3).CellStyle = bodyStyle;
rowTemp.GetCell(4).CellStyle = bodyStyle;
rowTemp.GetCell(5).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
sheet.AutoSizeColumn(4);
sheet.AutoSizeColumn(5);
//合并单元格
MergeCells(sheet, data, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex) {
}
return(Success("导出成功。"));
//设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = (state == "0" ? "内部" : "外部") + "人员统计信息";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 25;
//excelconfig.FileName = "实时在厂人员统计导出.xls";
//excelconfig.IsAllSizeColumn = true;
////每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//ColumnEntity columnentity = new ColumnEntity();
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "deptname".ToLower(), ExcelColumn = "部门名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname".ToLower(), ExcelColumn = "姓名" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "gender".ToLower(), ExcelColumn = "性别" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "devicename".ToLower(), ExcelColumn = "门禁通道名称" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "datetime".ToLower(), ExcelColumn = "进厂时间" });
////调用导出方法
//ExcelHelper.ExcelDownload(data, excelconfig);
}
public void SetUp()
{
// One or more test methods depends on the american culture.
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
// create the formatter to Test
formatter = new HSSFDataFormatter();
// create a workbook to Test with
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDataFormat format = wb.CreateDataFormat();
// create a row and Put some cells in it
IRow row = sheet.CreateRow(0);
// date value for July 8 1901 1:19 PM
double dateNum = 555.555;
// date value for July 8 1901 11:23 AM
double timeNum = 555.47431;
//valid date formats -- all should have "Jul" in output
String[] goodDatePatterns =
{
"[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",
"mmm/d/yy\\ h:mm PM;@",
"mmmm/d/yy\\ h:mm;@",
"mmmm/d;@",
"mmmm/d/yy;@",
"mmm/dd/yy;@",
"[$-409]d\\-mmm;@",
"[$-409]d\\-mmm\\-yy;@",
"[$-409]dd\\-mmm\\-yy;@",
"[$-409]mmm\\-yy;@",
"[$-409]mmmm\\-yy;@",
"[$-409]mmmm\\ d\\,\\ yyyy;@",
"[$-409]mmm/d/yy\\ h:mm:ss;@",
"[$-409]mmmm/d/yy\\ h:mm:ss am;@",
"[$-409]mmmmm;@",
"[$-409]mmmmm\\-yy;@",
"mmmm/d/yyyy;@",
"[$-409]d\\-mmm\\-yyyy;@"
};
//valid time formats - all should have 11:23 in output
String[] goodTimePatterns =
{
"HH:MM",
"HH:MM:SS",
"HH:MM;HH:MM;HH:MM",
// This is fun - blue if positive time,
// red if negative time or green for zero!
"[BLUE]HH:MM;[RED]HH:MM;[GREEN]HH:MM",
"yyyy-mm-dd hh:mm",
"yyyy-mm-dd hh:mm:ss",
};
// valid number formats
String[] goodNumPatterns =
{
"#,##0.0000",
"#,##0;[Red]#,##0",
"(#,##0.00_);(#,##0.00)",
"($#,##0.00_);[Red]($#,##0.00)",
"$#,##0.00",
"[$-809]#,##0.00", // international format
"[$-2]#,##0.00", // international format
"0000.00000%",
"0.000E+00",
"0.00E+00",
"[BLACK]0.00;[COLOR 5]##.##",
};
// invalid date formats -- will throw exception in DecimalFormat ctor
String[] badNumPatterns =
{
"#,#$'#0.0000",
"'#','#ABC#0;##,##0",
"000 '123 4'5'6 000",
"#''0#0'1#10L16EE"
};
// create cells with good date patterns
for (int i = 0; i < goodDatePatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dateNum);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodDatePatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(1);
// create cells with time patterns
for (int i = 0; i < goodTimePatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(timeNum);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodTimePatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(2);
// create cells with num patterns
for (int i = 0; i < goodNumPatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(-1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodNumPatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(3);
// create cells with bad num patterns
for (int i = 0; i < badNumPatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(badNumPatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
// Built in formats
{ // Zip + 4 format
row = sheet.CreateRow(4);
ICell cell = row.CreateCell(0);
cell.SetCellValue(123456789);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("00000-0000"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // Phone number format
row = sheet.CreateRow(5);
ICell cell = row.CreateCell(0);
cell.SetCellValue(5551234567D);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("[<=9999999]###-####;(###) ###-####"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // SSN format
row = sheet.CreateRow(6);
ICell cell = row.CreateCell(0);
cell.SetCellValue(444551234);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("000-00-0000"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // formula cell
row = sheet.CreateRow(7);
ICell cell = row.CreateCell(0);
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(12.25,12.25)/100");
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("##.00%;"));
cell.CellStyle = (/*setter*/ cellStyle);
}
}
/// <summary>
/// 获取Excel内存数据
/// </summary>
/// <param name="dataSource">数据源</param>
/// <param name="sheetName">Excel名字</param>
/// <returns></returns>
private static MemoryStream GetExcelData(DataTable dataSource, string sheetName)
{
if (dataSource == null)
{
throw new ArgumentException("dataSource is null");
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "UnKown";
}
//打开Excel对象
HSSFWorkbook _book = new HSSFWorkbook();
DocumentSummaryInformation _dom_summary_info = PropertySetFactory.CreateDocumentSummaryInformation();
_dom_summary_info.Company = "";
_book.DocumentSummaryInformation = _dom_summary_info;
SummaryInformation _summary_info = PropertySetFactory.CreateSummaryInformation();
_summary_info.Subject = "";
_book.SummaryInformation = _summary_info;
//Excel的Sheet对象
ISheet _sheet = _book.CreateSheet(sheetName);
DataColumnCollection _columns = dataSource.Columns;
DataRowCollection _rows = dataSource.Rows;
IRow _row = _sheet.CreateRow(0);
// 用于格式化单元格的数据
HSSFDataFormat format = (HSSFDataFormat)_book.CreateDataFormat();
// 设置列头字体
HSSFFont font = (HSSFFont)_book.CreateFont();
font.FontHeightInPoints = 18; //字体高度
//font.Color = (short)FontColor.RED;
font.FontName = "隶书"; //字体
font.Boldweight = (short)FontBoldWeight.Bold;
//font.IsItalic = true; //是否使用斜体
//font.IsStrikeout = true; //是否使用划线
//设置文本字体
HSSFFont fontContent = (HSSFFont)_book.CreateFont();
fontContent.FontHeightInPoints = 10;
fontContent.FontName = "宋体";
// 设置单元格类型
HSSFCellStyle cellStyle = (HSSFCellStyle)_book.CreateCellStyle();
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center; //水平布局：居中
cellStyle.WrapText = false;
// 添加单元格注释
// 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器.
HSSFPatriarch patr = (HSSFPatriarch)_sheet.CreateDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5));
// 设置注释内容
comment.String = new HSSFRichTextString("列标题");
// 设置注释作者. 当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.Author = "天蓝海";
//set date format
ICellStyle cellStyleDate = _book.CreateCellStyle();
cellStyleDate.SetFont(fontContent);
cellStyleDate.Alignment = HorizontalAlignment.Center; //水平布局：居中
cellStyleDate.DataFormat = format.GetFormat("yyyy-m-d HH:MM:SS");
//数据格式
ICellStyle cellStyleNumber = _book.CreateCellStyle();
cellStyleNumber.SetFont(fontContent);
cellStyleNumber.Alignment = HorizontalAlignment.Center; //水平布局：居中
cellStyleNumber.DataFormat = format.GetFormat("0.00");
//生成列名，根据DataTable的列名
for (int i = 0; i < _columns.Count; i++)
{
// 创建单元格
ICell cell = _row.CreateCell(i);
HSSFRichTextString hssfString = new HSSFRichTextString(_columns[i].ColumnName);
cell.SetCellValue(hssfString); //设置单元格内容
cell.CellStyle = cellStyle; //设置单元格样式
cell.SetCellType(CellType.String); //指定单元格格式：数值、公式或字符串
cell.CellComment = comment; //添加注释
}
//填充数据
for (int j = 0; j < _rows.Count; j++)
{
_row = _sheet.CreateRow(j + 1);
for (int k = 0; k < _columns.Count; k++)
{
ICell cell = _row.CreateCell(k);
switch (_columns[k].DataType.ToString())
{
case "System.String":
HSSFRichTextString hssfString = new HSSFRichTextString(_rows[j][k].ToString());
cell.SetCellValue(hssfString);
cell.SetCellType(CellType.String);
break;
case "System.Decimal":
cell.SetCellValue((double)(decimal)_rows[j][k]);
cell.CellStyle = cellStyleNumber;
break;
case "System.DateTime":
cell.SetCellValue((DateTime)_rows[j][k]);
cell.CellStyle = cellStyleDate;
break;
}
//自动换行
if (_rows[j][k].ToString().Contains("\r\n"))
{
cell.CellStyle.WrapText = true;
}
}
}
//设置自动列宽
for (int i = 0; i < _columns.Count; i++)
{
_sheet.AutoSizeColumn(i);
}
//保存excel文档
_sheet.ForceFormulaRecalculation = true;
MemoryStream _stream = new MemoryStream();
_book.Write(_stream);
return(_stream);
}
/// <summary>
/// 輸出Excel(重複不顯示)
/// </summary>
/// <param name="FileName">輸出檔名</param>
/// <param name="sheetName">Sheet名稱</param>
/// <param name="Datas">填入之資料</param>
/// <param name="mappings">填入之資料</param>
public static HSSFWorkbook GenerateExcel(DataTable dt, List <tblExcelMapping> mappings)
{
string ExcelName = mappings[0].ExcelName;
// 取得代碼轉換資料
using (vwCodeMappingRepository rep = new vwCodeMappingRepository())
{
codeMap = rep.query("", "EXPORT", "EXCEL", ExcelName, "");
}
HSSFWorkbook book = new HSSFWorkbook();
IEnumerable <string> sheetNames = mappings.Select(x => x.SheetName).Distinct();
foreach (var name in sheetNames)
{
ISheet sheet;
sheet = (HSSFSheet)book.GetSheet(name);
if (sheet == null)
{
sheet = (HSSFSheet)book.CreateSheet(name);
}
sheet.DisplayZeros = true;
List <tblExcelMapping> Columns = mappings.Where(x => x.SheetName.Equals(name, StringComparison.OrdinalIgnoreCase)).OrderBy(x => x.X).ToList();
#region 表頭
IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
for (int i = 0; i < Columns.Count(); i++)
{
int X = Columns[i].X - 1;
ICell cell = headerrow.CreateCell(X);
cell.CellStyle = style;
cell.SetCellValue(Columns[i].ColumnName);
}
#endregion
#region 填入內容
for (int i = 0; i < Columns.Count(); i++)
{
int X = Columns[i].X - 1;
ICellStyle dataStyle = book.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.Center;
dataStyle.VerticalAlignment = VerticalAlignment.Center;
if (!string.IsNullOrEmpty(Columns[i].NewLineChar))
{
dataStyle.WrapText = true; // 有設定換行字元時，啟動自動換行
}
if (Columns[i].DataType == "Date")
{
//顯示日期格式
HSSFDataFormat dateFormat = (HSSFDataFormat)book.CreateDataFormat();
dataStyle.DataFormat = dateFormat.GetFormat("yyyy-MM-dd");
}
if (Columns[i].DataType == "DateTime")
{
////顯示日期格式
HSSFDataFormat dateFormat = (HSSFDataFormat)book.CreateDataFormat();
dataStyle.DataFormat = dateFormat.GetFormat("yyyy-MM-dd HH:mm:ss");
}
else if (Columns[i].DataType == "Integer")
{
////顯示有逗號區分的數值資料
HSSFDataFormat numericformat = (HSSFDataFormat)book.CreateDataFormat();
dataStyle.DataFormat = numericformat.GetFormat("###,##0");
}
else if (Columns[i].DataType == "Decimal")
{
////顯示有逗號區分的小數資料
HSSFDataFormat decimalFormat = (HSSFDataFormat)book.CreateDataFormat();
dataStyle.DataFormat = decimalFormat.GetFormat("###,##0.0000");
}
int fixRow = -1;
for (int row = 0; row < dt.Rows.Count; row++)
{
IRow dataRow;
if (i == 0)
{
dataRow = sheet.CreateRow(row + 1);
}
else
{
dataRow = sheet.GetRow(row + 1);
}
string value = string.Empty;
if (!string.IsNullOrEmpty(Columns[i].FieldName) || !string.IsNullOrEmpty(Columns[i].DefaultValue))
{
if (string.IsNullOrEmpty(Columns[i].FieldName))
{
value = Columns[i].DefaultValue;
}
else
{
value = (string.IsNullOrEmpty(dt.Rows[row][Columns[i].FieldName].ToString())) ? Columns[i].DefaultValue : dt.Rows[row][Columns[i].FieldName].ToString();
}
// 代碼轉換
if (codeMap.Where(x => x.FieldName.Equals(Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase)).Count() > 0 &&
codeMap.Where(x => x.BeforeValue.Equals(value, StringComparison.OrdinalIgnoreCase)).Count() > 0)
{
vwCodeMapping map = codeMap.Find(x => x.FieldName.Equals(Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase) && x.BeforeValue.Equals(value, StringComparison.OrdinalIgnoreCase));
if (map != null)
{
value = map.AfterValue;
}
}
if (!string.IsNullOrEmpty(Columns[i].NewLineChar))
{
value = value.Replace(Columns[i].NewLineChar, "\n");
}
}
// 沒有指定 Field 時，代入 Default Value
if (string.IsNullOrEmpty(Columns[i].FieldName))
{
ICell cell = dataRow.CreateCell(X);
cell.CellStyle = dataStyle;
SetCellValue(ref cell, Columns[i].DataType, value);
}
// 判斷是否和上筆資料一致
else if (row == 0 || Columns[i].CanRepeat || (dt.Rows[row][Columns[i].FieldName].ToString() != dt.Rows[row - 1][Columns[i].FieldName].ToString()))
{
ICell cell = dataRow.CreateCell(X);
cell.CellStyle = dataStyle;
SetCellValue(ref cell, Columns[i].DataType, value);
}
}
sheet.AutoSizeColumn(X);
}
#endregion
}
return(book);
}
public void ExportExcel(DataTable dt)
{
try
{
//创建一个工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建一个 sheet 表
ISheet sheet = workbook.CreateSheet(dt.TableName);
//创建一行
IRow rowH = sheet.CreateRow(0);
//创建一个单元格
ICell cell = null;
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//创建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
cellStyle.DataFormat = dataFormat.GetFormat("@");
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowH.Cells[col.Ordinal].CellStyle = cellStyle;
}
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
//设置导出文件路径
string path = HttpContext.Current.Server.MapPath("/ImportExcel/");
//设置新建文件路径及名称
string savePath = path + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//创建文件
FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//还可以调用下面的方法，把流输出到浏览器下载
OutputClient(bytes);
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
workbook.Close();
sheet = null;
workbook = null;
}
catch (Exception ex)
{
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param Name="dtSource">DataTable数据源</param>
/// <param Name="strHeaderText">Excel表头文本（例如：信息工程学院2014－2015学年第一学期教学检查听课安排）</param>
private MemoryStream Export(List <ExportExcelModel> dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "破晓技术团队";
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 = System.DateTime.Now;
workbook.SummaryInformation = si;
}
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int j = 0;
foreach (ExportExcelModel row in dtSource)
{
// 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
// 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dcs.Count - 1)); // ------------------
}
//
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
font.FontName = "宋体";
headStyle.SetFont(font);
int index = 0;
foreach (string column in dcs)
{
headerRow.CreateCell(index).SetCellValue(column);
headerRow.GetCell(index).CellStyle = headStyle;
sheet.AutoSizeColumn(index);
//设置列宽
//sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256);
//sheet.SetColumnWi
index++;
}
}
rowIndex = 2;
}
//填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
//！！！！！！！！！！！！！！！！！！！！！！！如果没有职称可能会异常
DistinctSupervisor(dtSource[j].supervisors, ListSupervisor);//去督导员的职称
for (int i = 0; i < dcs.Count; i++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
columnIndex++;
if (i != dcs.Count - 1)
{
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
switch (i)
{
case 0:
//序号
newCell.SetCellValue((j + 1).ToString() + "\t");
break;
case 1:
//课程
newCell.SetCellValue(dtSource[j].classname.ToString() + "\t");
break;
case 2:
//授课内容
newCell.SetCellValue(dtSource[j].classcontent.ToString() + "\t");
break;
case 3:
//授课方式
newCell.SetCellValue(dtSource[j].classtype.ToString() + "\t");
break;
case 4:
//专业
newCell.SetCellValue(dtSource[j].major.ToString() + "\t");
break;
case 5:
//教室
newCell.SetCellValue(dtSource[j].classroom.ToString() + "\t");
break;
case 6:
//教师
newCell.SetCellValue(dtSource[j].teachername.ToString() + "\t");
break;
case 7:
//周次
newCell.SetCellValue(dtSource[j].week.ToString() + "\t");
break;
case 8:
//听课时间
newCell.SetCellValue(dtSource[j].time + "\t");
break;
case 9:
//听课人员安排
newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t");
break;
case 10:
//分数
newCell.SetCellValue(" " + "\t");
break;
}
}
else
{
//申报
newCell.SetCellValue(" ");
}
}
j++;
rowIndex++;
}
adjustcolum(sheet); //调整列宽
AddBorder(sheet, workbook); //加边框
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerTextList">表头摘要信息</param>
public MemoryStream Export(DataTable dtSource, List<String> headerTextList)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
//设置Excel文件属性信息
SetFileProperty(workbook);
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)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();
}
#region 表头及样式
for (int i = 0; i < headerTextList.Count; i++)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i);
headerRow.HeightInPoints = 18;
headerRow.CreateCell(0).SetCellValue(headerTextList[i]);
HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Left;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 14;
//font.Boldweight = 700;
headerStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)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);
}
}
#endregion
rowIndex = headerTextList.Count + 1;
}
#endregion
#region 填充表格内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)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;
return ms;
}
}
/// <summary>
/// 创建Sheet表
/// </summary>
private static ISheet WorkbookCreateSheet <T>(List <T> dataSource, string xlsSheetName, List <FileFieldMapping> FieldMapList, HSSFWorkbook workbook) where T : class, new()
{
if (xlsSheetName == string.Empty)
{
xlsSheetName = "Sheet1";
}
ISheet sheet = workbook.CreateSheet(xlsSheetName);
ICellStyle cellSytleDate = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellSytleDate.DataFormat = format.GetFormat("yyyy年mm月dd日");
#region 填充列头区域
IRow rowHeader = sheet.CreateRow(0);
//设置表头样式
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont cellFontHeader = workbook.CreateFont();
cellFontHeader.Boldweight = 700;
cellFontHeader.FontHeightInPoints = 12;
headStyle.SetFont(cellFontHeader);
int forEachindex = 0;
if (FieldMapList == null || FieldMapList.Count < 1)
{
Type t = dataSource[0].GetType();
PropertyInfo[] pis = t.GetProperties();
for (int colIndex = 0; colIndex < pis.Length; colIndex++)
{
ICell cell = rowHeader.CreateCell(colIndex);
cell.SetCellValue(pis[colIndex].Name);
cell.CellStyle = headStyle;
}
}
else
{
FieldMapList.ForEach(e =>
{
ICell cell = rowHeader.CreateCell(forEachindex);
cell.SetCellValue(e.FieldDiscretion);
cell.CellStyle = headStyle;
forEachindex++;
});
}
#endregion 填充列头区域
#region 对所需字段依数 填充内容区域
for (int rowIndex = 0; rowIndex < dataSource.Count; rowIndex++)
{
IRow rowContent = sheet.CreateRow(rowIndex + 1);
T entity = dataSource[rowIndex];
Type tentity = entity.GetType();
PropertyInfo[] tpis = tentity.GetProperties();
int colIndex = 0;
if (FieldMapList == null || FieldMapList.Count < 1)
{
for (int Index = 0; Index < tpis.Length; Index++)
{
FillIcell <T>(cellSytleDate, rowContent, entity, tpis, Index, Index);
}
}
else
{
FieldMapList.ForEach(e =>
{
//添加项次序号
if (e.FieldDiscretion == "项次")
{
ICell cellContent = rowContent.CreateCell(colIndex);
cellContent.SetCellValue((rowIndex + 1).ToString());
colIndex++;
}
else
{
for (int tipsIndex = 0; tipsIndex < tpis.Length; tipsIndex++)
{ //如不是所需字段 跳过
if (e.FieldName == tpis[tipsIndex].Name)
{
FillIcell <T>(cellSytleDate, rowContent, entity, tpis, tipsIndex, colIndex);
colIndex++;
break;
}
}
}
});
}
}
#endregion 填充内容区域
return(sheet);
}
