/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="xssfworkbook">Excel操作类</param>
/// <param name="font">单元格字体</param>
/// <param name="fillForegroundColor">图案的颜色</param>
/// <param name="fillPattern">图案样式</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">垂直对齐方式</param>
/// <returns></returns>
public static ICellStyle GetCellStyle(XSSFWorkbook xssfworkbook, IFont font, XSSFColor fillForegroundColor, FillPattern fillPattern, XSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
{
XSSFCellStyle cellstyle = xssfworkbook.CreateCellStyle() as XSSFCellStyle;
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.SetFillForegroundColor(fillForegroundColor);
}
if (fillBackgroundColor != null)
{
cellstyle.SetFillBackgroundColor(fillBackgroundColor);
}
if (font != null)
{
cellstyle.SetFont(font);
}
return(cellstyle);
}
public void TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont());
Assert.AreEqual(18, orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont());
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
clone.Alignment = HorizontalAlignment.Left;
clone.DataFormat = 17;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(18, orig.DataFormat);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
private XSSFCellStyle createHeaderStyle(XSSFWorkbook workbook)
{
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = false;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "宋体";
font.Boldweight = (short)FontBoldWeight.Bold;
XSSFColor color = new XSSFColor(new byte[] { 255, 0, 0 });
font.SetColor(color);
style.SetFont(font);
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
return(style);
}
public XSSFCellStyle CsSet3(short size, bool x)
{
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontName = "微軟正黑體";
font.FontHeightInPoints = size;
XSSFCellStyle cs = (XSSFCellStyle)workbook.CreateCellStyle();
cs.SetFont(font);
cs.WrapText = true;
cs.VerticalAlignment = VerticalAlignment.Center;
cs.Alignment = HorizontalAlignment.Left;
if (x)
{
cs.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cs.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cs.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cs.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cs.WrapText = true;
}
return(cs);
}
private XSSFCellStyle createWarningStyle(XSSFWorkbook workbook)
{
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = false;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "宋体";
style.SetFont(font);
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
XSSFColor color = new XSSFColor(new byte[] { 255, 255, 0 });
style.FillForegroundColorColor = color;
style.FillPattern = FillPattern.SolidForeground;
return(style);
}
public void FillReg17SpreadSheet(Dictionary <string, Reg17Record> records)
{
xssfwb = new XSSFWorkbook();
XSSFFont myFont = (XSSFFont)xssfwb.CreateFont();
myFont.FontHeightInPoints = 12;
myFont.Boldweight = (short)FontBoldWeight.Bold;
//myFont.FontName = "Tahoma";
XSSFFont myFont2 = (XSSFFont)xssfwb.CreateFont();
myFont2.FontHeightInPoints = 18;
myFont2.Boldweight = (short)FontBoldWeight.Bold;
//myFont2.FontName = "Tahoma";
IFont boldFont = xssfwb.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
var color = new XSSFColor(new byte[] { 196, 215, 155 });
XSSFCellStyle borderedCellStyle = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle.SetFont(myFont);
borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
XSSFCellStyle borderedCellStyle2 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle2.SetFont(myFont2);
borderedCellStyle2.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle2.Alignment = HorizontalAlignment.Center;
borderedCellStyle2.SetFillForegroundColor(color);
borderedCellStyle2.SetFillBackgroundColor(color);
borderedCellStyle2.FillBackgroundXSSFColor = color;
borderedCellStyle2.FillForegroundXSSFColor = color;
borderedCellStyle2.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle borderedCellStyle3 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle3.SetFont(myFont);
borderedCellStyle3.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle3.Alignment = HorizontalAlignment.Center;
borderedCellStyle3.SetFillForegroundColor(color);
borderedCellStyle3.SetFillBackgroundColor(color);
borderedCellStyle3.FillBackgroundXSSFColor = color;
borderedCellStyle3.FillForegroundXSSFColor = color;
borderedCellStyle3.FillPattern = FillPattern.SolidForeground;
borderedCellStyle3.WrapText = true;
ISheet Sheet = xssfwb.CreateSheet("Report");
Sheet.SetColumnWidth(0, 22 * 256);
Sheet.SetColumnWidth(1, 17 * 256);
Sheet.SetColumnWidth(2, 25 * 256);
Sheet.SetColumnWidth(3, 15 * 256);
Sheet.SetColumnWidth(4, 96 * 256);
Sheet.SetColumnWidth(5, 35 * 256);
//Creat The Headers of the excel
IRow row1 = Sheet.CreateRow(0);
//styling
ICellStyle boldStyle = xssfwb.CreateCellStyle();
boldStyle.SetFont(boldFont);
//Create The Actual Cells
row1.CreateCell(0).SetCellValue("Sistema PCI DSS");
row1.GetCell(0).CellStyle = boldStyle;
IRow row2 = Sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("REG-17");
row2.GetCell(0).CellStyle = boldStyle;
IRow row3 = Sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("Informe de Aplicación de Parches");
row3.GetCell(0).CellStyle = borderedCellStyle2;
var cra = new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 5);
Sheet.AddMergedRegion(cra);
row2.CreateCell(0).SetCellValue("REG-17");
row2.GetCell(0).CellStyle = boldStyle;
IRow row4 = Sheet.CreateRow(4);
row4.CreateCell(0).SetCellValue("Servidores");
row4.GetCell(0).CellStyle = borderedCellStyle;
row4.CreateCell(1).SetCellValue("SQLPCI");
row4.GetCell(1).CellStyle = boldStyle;
row4.CreateCell(2).SetCellValue("Fecha de Aplicación");
row4.GetCell(2).CellStyle = borderedCellStyle;
row4.CreateCell(3).SetCellValue(DateTime.Now.ToString());
row4.GetCell(3).CellStyle = borderedCellStyle;
IRow row5 = Sheet.CreateRow(5);
row5.CreateCell(1).SetCellValue("SRVFILE");
row5.GetCell(1).CellStyle = boldStyle;
IRow row6 = Sheet.CreateRow(6);
row6.CreateCell(1).SetCellValue("Site-Transfer");
row6.GetCell(1).CellStyle = boldStyle;
IRow row7 = Sheet.CreateRow(7);
row7.CreateCell(1).SetCellValue("WEBPCI");
row7.GetCell(1).CellStyle = boldStyle;
IRow row8 = Sheet.CreateRow(8);
row8.CreateCell(1).SetCellValue("Proxy Inverso");
row8.GetCell(1).CellStyle = boldStyle;
IRow row9 = Sheet.CreateRow(9);
row9.CreateCell(1).SetCellValue("Proxy Servicios");
row9.GetCell(1).CellStyle = boldStyle;
IRow row10 = Sheet.CreateRow(10);
row10.CreateCell(1).SetCellValue("Proxy Web");
row10.GetCell(1).CellStyle = boldStyle;
IRow row11 = Sheet.CreateRow(11);
row11.CreateCell(1).SetCellValue("Parches");
row11.GetCell(1).CellStyle = boldStyle;
IRow row12 = Sheet.CreateRow(12);
row12.CreateCell(1).SetCellValue("ADMPCI");
row12.GetCell(1).CellStyle = boldStyle;
IRow row13 = Sheet.CreateRow(13);
row13.CreateCell(1).SetCellValue("File Gateway");
row13.GetCell(1).CellStyle = boldStyle;
IRow row14 = Sheet.CreateRow(14);
row14.CreateCell(1).SetCellValue("Volume Gateway");
row14.GetCell(1).CellStyle = boldStyle;
IRow row15 = Sheet.CreateRow(15);
row15.CreateCell(1).SetCellValue("RD Gateway");
row15.GetCell(1).CellStyle = boldStyle;
IRow row16 = Sheet.CreateRow(16);
row16.CreateCell(1).SetCellValue("Data Analysis");
row16.GetCell(1).CellStyle = boldStyle;
IRow row18 = Sheet.CreateRow(18);
row18.CreateCell(0).SetCellValue("Código Parche");
row18.GetCell(0).CellStyle = borderedCellStyle3;
row18.CreateCell(1).SetCellValue("Fecha Publicación");
row18.GetCell(1).CellStyle = borderedCellStyle3;
row18.CreateCell(2).SetCellValue("Producto");
row18.GetCell(2).CellStyle = borderedCellStyle3;
row18.CreateCell(3).SetCellValue("Clasificación");
row18.GetCell(3).CellStyle = borderedCellStyle3;
row18.CreateCell(4).SetCellValue("Mejoras y Correcciones");
row18.GetCell(4).CellStyle = borderedCellStyle3;
row18.CreateCell(5).SetCellValue("Opinión Impacto del Parche");
row18.GetCell(5).CellStyle = borderedCellStyle3;
XSSFFont myFont4 = (XSSFFont)xssfwb.CreateFont();
myFont4.FontHeightInPoints = 10;
//myFont4.Boldweight = (short)FontBoldWeight.Bold;
//myFont.FontName = "Tahoma";
XSSFFont myFont5 = (XSSFFont)xssfwb.CreateFont();
myFont5.FontHeightInPoints = 8.5;
//myFont5.Boldweight = (short)FontBoldWeight.Bold;
//myFont2.FontName = "Tahoma";
boldFont.Boldweight = (short)FontBoldWeight.Bold;
XSSFCellStyle borderedCellStyle4 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle4.SetFont(myFont4);
borderedCellStyle4.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle4.Alignment = HorizontalAlignment.Center;
XSSFCellStyle borderedCellStyle5 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle5.SetFont(myFont5);
borderedCellStyle5.VerticalAlignment = VerticalAlignment.Center;
//borderedCellStyle5.Alignment = HorizontalAlignment.Center;
borderedCellStyle5.WrapText = true;
XSSFCellStyle borderedCellStyle6 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle6.SetFont(myFont4);
borderedCellStyle6.VerticalAlignment = VerticalAlignment.Center;
//borderedCellStyle6.Alignment = HorizontalAlignment.Center;
borderedCellStyle6.WrapText = true;
int i = 19;
//List<Reg17Record> records = new List<Reg17Record>();
foreach (KeyValuePair <string, Reg17Record> entry in records)
{
// do something with entry.Value or entry.Key
Reg17Record record = (Reg17Record)entry.Value;
IRow rowi = Sheet.CreateRow(i);
rowi.CreateCell(0).SetCellValue(record.PatchCode);
rowi.GetCell(0).CellStyle = borderedCellStyle4;
rowi.CreateCell(1).SetCellValue(record.PublicationDate);
rowi.GetCell(1).CellStyle = borderedCellStyle4;
rowi.CreateCell(2).SetCellValue(record.Product);
rowi.GetCell(2).CellStyle = borderedCellStyle4;
rowi.CreateCell(3).SetCellValue(record.Classification);
rowi.GetCell(3).CellStyle = borderedCellStyle4;
rowi.CreateCell(4).SetCellValue(record.EnhancementsAndCorrections);
rowi.GetCell(4).CellStyle = borderedCellStyle5;
rowi.CreateCell(5).SetCellValue(record.ImpactOpinion);
rowi.GetCell(5).CellStyle = borderedCellStyle6;
i++;
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static void ExportDT(DataTable dtSource, string strHeaderText, string sheetName, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet;
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
else
{
sheet = workbook.CreateSheet(sheetName) as XSSFSheet;
}
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.UTF8.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.UTF8.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)
{
#region 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
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));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
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++;
}
workbook.Write(fs);
fs.Close();
}
/// <summary>
///传入ds直接生成excel在服务器目录上
/// </summary>
/// <param name="ds"></param>
/// <param name="strPath"></param>
/// <param name="strFileName"></param>
/// <returns></returns>
public static XSSFWorkbook ExportXlsxByDataSet(DataSet ds, string ReportHeader = "")
{
//NPOI
XSSFWorkbook hssfworkbook2 = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)hssfworkbook2.CreateSheet("sheet1");
//定义字体 font 设置字体类型和大小
XSSFFont font = (XSSFFont)hssfworkbook2.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
//定义单元格格式；单元格格式style1 为font的格式
XSSFCellStyle style1 = (XSSFCellStyle)hssfworkbook2.CreateCellStyle();
style1.SetFont(font);
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
XSSFCellStyle style2 = (XSSFCellStyle)hssfworkbook2.CreateCellStyle();
style2.SetFont(font);
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//style2.WrapText = true;
//设置大标题行
int RowCount = 0;
int arrFlag = 0;
string TileName1 = "";
string TileName2 = "";
string s = ReportHeader;
string[] sArray = s.Split('|');
if (ReportHeader != "")
{
foreach (string i in sArray)
{
string str1 = i.ToString();
string[] subArray = str1.Split('@');
foreach (string k in subArray)
{
Console.WriteLine(k.ToString());
if (arrFlag == 0)
{
TileName1 = k.ToString();
}
else
{
TileName2 = k.ToString();
}
arrFlag = arrFlag + 1;
}
XSSFRow row0 = (XSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列
row0.CreateCell(0).SetCellValue(TileName1);
row0.CreateCell(1).SetCellValue(TileName2);
RowCount = RowCount + 1;
arrFlag = 0;
}
}
//设置全局列宽和行高
sheet.DefaultColumnWidth = 14; //全局列宽
sheet.DefaultRowHeightInPoints = 15; //全局行高
//设置标题行数据
int a = 0;
string mColumnName = "";
XSSFRow row1 = (XSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列
for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
{
mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString();
row1.CreateCell(a).SetCellValue(mColumnName);
row1.Cells[a].CellStyle = style2;
a++;
}
//填写ds数据进excel
//数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)//写6行数据
{
XSSFRow row2 = (XSSFRow)sheet.CreateRow(i + RowCount + 1);
int b = 0;
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
string DgvValue = "";
DgvValue = ds.Tables[0].Rows[i][j].ToString();;
row2.CreateCell(b).SetCellValue(DgvValue);
b++;
}
}
return(hssfworkbook2);
}
/// <summary>
///
/// </summary>
/// <param name="error"></param>
/// <param name="strFileName"></param>
/// <param name="dsSource"></param>
/// <param name="pwd"></param>
/// <param name="rowIndex"></param>
/// <param name="wait"></param>
/// <param name="isAppend"></param>
/// <param name="dateFormat"></param>
public static void DataTableToExcel(ref string error, string strFileName, DataSet dsSource, string pwd, ref int rowIndex, WaitDialogForm wait = null, bool isAppend = false, string dateFormat = "yyyy-MM-dd")
{
try {
error = string.Empty;
FileStream fs = null;
XSSFWorkbook workbook = null;
if (!isAppend)
{
fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write);
workbook = new XSSFWorkbook();
}
else
{
fs = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
workbook = new XSSFWorkbook(fs);//将文件读到内存，在内存中操作excel
}
fs.Close();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Xw";
// workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "xw"; //填加xls文件作者信息
si.ApplicationName = "xw"; //填加xls文件创建程序信息
si.LastAuthor = "xw"; //填加xls文件最后保存者信息
si.Comments = "xw"; //填加xls文件作者信息
si.Title = "xw"; //填加xls文件标题信息
si.Subject = "xw"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
// workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(dateFormat);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFCellStyle rowStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont fontRow = (XSSFFont)workbook.CreateFont();
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "微软雅黑";
//font.Boldweight = 700;
headStyle.SetFont(font);
fontRow.FontHeightInPoints = 9;
fontRow.FontName = "微软雅黑";
rowStyle.SetFont(fontRow);
for (int k = 0; k < dsSource.Tables.Count; k++)
{
var dtSource = dsSource.Tables[k];
XSSFSheet sheet = null;
if (!isAppend)
{
rowIndex = 0;
sheet = (XSSFSheet)workbook.CreateSheet();
if (!string.IsNullOrEmpty(pwd))
{
sheet.ProtectSheet(pwd);
}
workbook.SetSheetName(k, dtSource.TableName);
}
else
{
sheet = (XSSFSheet)workbook.GetSheet(dtSource.TableName);
if (sheet == null)
{
isAppend = false;
rowIndex = 0;
sheet = (XSSFSheet)workbook.CreateSheet();
if (!string.IsNullOrEmpty(pwd))
{
sheet.ProtectSheet(pwd);
}
workbook.SetSheetName(k, dtSource.TableName);
}
}
if (wait != null)
{
wait.SetCaption(string.Format("正在创建第 {0} / {1} Excel的Sheet中", (k + 1), dsSource.Tables.Count));//"（" + (k + 1) + " /" + dsSource.Tables.Count + "）Excel的Sheet中...");
}
//取得列宽
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;
}
}
}
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (!isAppend)
{
//if (rowIndex != 0) {
//sheet = (XSSFSheet)workbook.CreateSheet();
//if (!string.IsNullOrEmpty(pwd)) {
// sheet.ProtectSheet(pwd);
//}
//}
#region 表头及样式
// if (!string.IsNullOrEmpty(strHeaderText)) {
// rowIndex++;
// XSSFRow headerRow = (XSSFRow)sheet.CreateRow(rowIndex);
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// // headStyle.Alignment = CellHorizontalAlignment.CENTER;
// XSSFFont font = (XSSFFont)workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
// }
#endregion
#region 列头及样式
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//设置列宽
// sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
isAppend = true;
rowIndex = 1;
}
#endregion
if (wait != null)
{
wait.SetCaption(string.Format("正在将数据写入Excel第 {0} / {1} 行中...", rowIndex, dtSource.Rows.Count));//"正在写入第（" + (k + 1) + "/" + dsSource.Tables.Count + "个ExcelSheet表格中的第 " + rowIndex + "/" + dtSource.Rows.Count + " 行数据...");
}
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = rowStyle;
//newCell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
var obj = row[column];
if (obj == null)
{
continue;
}
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double tempVal = 0;
var isflg = double.TryParse(drValue, out tempVal);
if (isflg)
{
newCell.SetCellValue(tempVal);
newCell.SetCellType(CellType.Numeric);
}
else
{
newCell.SetCellValue(drValue);
newCell.SetCellType(CellType.String);
}
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
isflg = System.DateTime.TryParse(drValue, out dateV);
if (isflg)
{
newCell.SetCellValue(drValue);
}
else
{
newCell.SetCellValue(string.Empty);
}
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.UInt16": //整型
case "System.UInt32":
case "System.UInt64":
int intV = 0;
isflg = int.TryParse(drValue, out intV);
if (isflg)
{
newCell.SetCellValue(intV);
newCell.SetCellType(CellType.Numeric);
}
break;
case "System.Single": //浮点型
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
isflg = double.TryParse(drValue, out doubV);
if (isflg)
{
newCell.SetCellValue(doubV);
newCell.SetCellType(CellType.Numeric);
}
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
newCell.SetCellType(CellType.String);
break;
default:
tempVal = 0;
isflg = double.TryParse(drValue, out tempVal);
if (isflg)
{
newCell.SetCellValue(tempVal);
newCell.SetCellType(CellType.Numeric);
}
else
{
newCell.SetCellValue(drValue);
newCell.SetCellType(CellType.String);
}
break;
}
}
#endregion
rowIndex++;
}
if (sheet != null)
{
sheet.ForceFormulaRecalculation = true;
}
}
//将内存数据写到文件
using (FileStream fs1 = File.OpenWrite(strFileName)) {
workbook.Write(fs1);
workbook.Close();
}
} catch (Exception ex) {
error = $"导出数据出错{ex.Message}";
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
//XSSFWorkbook workbook = new XSSFWorkbook();(.xls用该格式限制列为256列）
//(.xlsx 16384列)
string sheetname = dtSource.TableName;
XSSFSheet sheet = workbook.CreateSheet(sheetname) as XSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
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)//超过6355就新建一个表
{
sheet = workbook.CreateSheet(sheetname) as XSSFSheet;
}
#region 表头及样式
//{
// XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
// headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// HSSFFont font = workbook.CreateFont() as HSSFFont;
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
//}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.SetFillForegroundColor(new XSSFColor(new byte[] { 153, 204, 204 }));
headStyle.FillPattern = FillPattern.SolidForeground;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontName = "宋体";
font.FontHeightInPoints = 11;
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) * 320);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
XSSFCellStyle rowStyle = workbook.CreateCellStyle() as XSSFCellStyle;
rowStyle.Alignment = HorizontalAlignment.Center;
XSSFFont cellfont = workbook.CreateFont() as XSSFFont;
cellfont.FontName = "宋体";
cellfont.FontHeightInPoints = 11;
rowStyle.SetFont(cellfont);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
newCell.CellStyle = rowStyle;
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++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
// ms.Flush();
//ms.Position = 0;
// workbook.Dispose();
return(ms);
}
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream，2007格式
/// </summary>
/// <param name="dtSource">源DataTable</param>
public static MemoryStream ExportDT2007(DataTable dtSource)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
#region 右击文件 属性信息
{
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
//dsi.Company = "http://www.huobanplus.com";
//workbook.DocumentSummaryInformation = dsi;
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = "杭州火图科技"; //填加xls文件作者信息
//si.ApplicationName = "伙伴商城"; //填加xls文件创建程序信息
//si.LastAuthor = "voidarea"; //填加xls文件最后保存者信息
//si.Comments = ""; //填加xls文件作者信息
//si.Title = ""; //填加xls文件标题信息
//si.Subject = ""; //填加文件主题信息
//si.CreateDateTime = DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
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() as XSSFSheet;
}
#region 表头及样式
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
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] > 255)
{
arrColWidth[column.Ordinal] = 254;
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
#endregion
//rowIndex = 2;
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
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;
//workbook.Dispose();
return(ms);
}
}
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
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)
{
if (rowIndex == 0)
{
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
rowIndex = 1;
}
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
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;
}
}
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
private static void workbookData(List <DataHolder> dict)
{
_numOfFarms = ConsolUtil.getNumberofFarms();
for (int row = 0; row < _rowLabels.Count; row++)
{
rowLabeler(row, dict);
}
int col = 1;
foreach (DataHolder b in dict)
{
ICell cell;
string stripper = b.Data_array.Substring(1, b.Data_array.Length - 2);
string[] sArray = stripper.Split(',');
{
XSSFCellStyle style = (XSSFCellStyle)_wb.CreateCellStyle();
XSSFFont font = (XSSFFont)_wb.CreateFont();
font.FontName = "Arial";
cell = _sheet.GetRow(0).CreateCell(col);
style.SetFillForegroundColor(new XSSFColor(Color.FromArgb(255, 242, 204)));
style.FillPattern = FillPattern.SolidForeground;
style.Alignment = HorizontalAlignment.Left;
try
{
cell.SetCellValue(ConsolUtil.GetFarmName(b.Branch_id));
}
catch
{
cell.SetCellValue("N/A");
}
font.FontHeightInPoints = 14;
style.SetFont(font);
cell.CellStyle = style;
}
for (int i = 0; i < sArray.Length; i++)
{
XSSFCellStyle style = (XSSFCellStyle)_wb.CreateCellStyle();
XSSFFont font = (XSSFFont)_wb.CreateFont();
cell = _sheet.GetRow(_dataCells[i]).CreateCell(col);
style.Alignment = HorizontalAlignment.Left;
ConsolUtil.InputDataToSheet(sArray[i], cell);
font.FontHeightInPoints = 11;
style.SetFont(font);
cell.CellStyle = style;
}
col++;
}
_sheet.AutoSizeColumn(1);
_sheet.AutoSizeColumn(2);
_sheet.AutoSizeColumn(1);
}
public static void ExportToFile(GridView gv, string excelName)//匯出Excel
{
try
{
//建立WorkBook及試算表
XSSFWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
XSSFSheet mySheet1 = (XSSFSheet)workbook.CreateSheet(excelName);
//建立標題列Header
XSSFRow rowHeader = (XSSFRow)mySheet1.CreateRow(0);
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
string strValue = gv.HeaderRow.Cells[i].Text;
XSSFCell cell = (XSSFCell)rowHeader.CreateCell(i);
cell.SetCellValue(HttpUtility.HtmlDecode(strValue).Trim());
//建立新的CellStyle
ICellStyle CellsStyle = workbook.CreateCellStyle();
//建立字型
IFont StyleFont = workbook.CreateFont();
//設定文字字型
StyleFont.FontName = "微軟正黑體";
//設定文字大小
StyleFont.FontHeightInPoints = 12; //設定文字大小為10pt
CellsStyle.SetFont(StyleFont);
cell.CellStyle = CellsStyle;
}
//建立內容列 DataRow
for (int i = 0; i < gv.Rows.Count; i++)
{
XSSFRow rowItem = (XSSFRow)mySheet1.CreateRow(i + 1);
for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
{
Label lb = null; // 因為GridView中有TemplateField，所以要將Label.Text讀出來
if (gv.Rows[i].Cells[j].Controls.Count > 1)
{
lb = gv.Rows[i].Cells[j].Controls[1] as Label;
}
string value1 = (lb != null) ? HttpUtility.HtmlDecode(lb.Text) : HttpUtility.HtmlDecode(gv.Rows[i].Cells[j].Text).Trim();
int intry = 0;
bool isNumeric = !value1.StartsWith("0") && int.TryParse(value1, out intry);
XSSFCell cell = (XSSFCell)rowItem.CreateCell(j);
if (string.IsNullOrEmpty(value1.Trim()))
{
//空白
cell.SetCellValue(Convert.ToString(""));
}
else if (!isNumeric)
{
if (value1.Length > 10)
{
//文字格式
mySheet1.SetColumnWidth(j, 50 * 256); //欄位寬度設為50
}
else if (value1.Length > 3)
{
//文字格式
mySheet1.SetColumnWidth(j, 30 * 256); //欄位寬度設為30
}
else
{
//文字格式
mySheet1.SetColumnWidth(j, 15 * 256); //欄位寬度設為15
}
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); // 給cell style
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("@"); // 文字格式
//建立字型
IFont StyleFont = workbook.CreateFont();
//設定文字字型
StyleFont.FontName = "微軟正黑體";
//設定文字大小
StyleFont.FontHeightInPoints = 12; //設定文字大小為12pt
cellStyle.SetFont(StyleFont);
//cellStyle.WrapText = true; //文字自動換列
cell.CellStyle = cellStyle;
cell.SetCellValue(value1);
}
{
cell.SetCellValue(value1);
}
}
}
//匯出
workbook.Write(ms);
//此為匯出副檔名xlsx
//方法一
/*
* HttpContext.Current.Response.Clear();
* HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
* HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "AAA.xlsx"));
* HttpContext.Current.Response.BinaryWrite(ms.ToArray());
* HttpContext.Current.Response.Flush();
* HttpContext.Current.Response.End();*/
//方法二
using (FileStream fs = new FileStream(@"C:\Users\TPE-Intern001\Desktop\20210205.xls", FileMode.Create, FileAccess.Write, FileShare.None, 4096, true))//写入指定的文件
{
byte[] b = ms.ToArray();
fs.Write(b, 0, b.Length);
ms.Close();
fs.Flush();
fs.Close();
}
//釋放資源
workbook = null;
ms.Close();
ms.Dispose();
}
catch (Exception)
{ }
}
/// <summary>
/// 生成任务的Excel报表
/// </summary>
/// <param name="units"></param>
private void TaskBuildExcel(List <DeviceListOutput> devices)
{
/////文件生成存放路径
string filePath = this._appFolders.ExcelFolder + "/导出报表/设备列表.xlsx";
//内存中创建一个hssfworkbook对象流（创建一个Excel文件）
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
//创建sheet
XSSFSheet Sheet1 = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
#region
//第一行
XSSFRow fcell = (XSSFRow)Sheet1.CreateRow(0);
fcell.CreateCell(0).SetCellValue("设备明细表");
//合并单元格
Sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 23));
#endregion
XSSFCellStyle fCellStyle = (XSSFCellStyle)xssfworkbook.CreateCellStyle();
XSSFFont ffont = (XSSFFont)xssfworkbook.CreateFont();
ffont.FontHeightInPoints = 16;
ffont.FontName = "宋体";
ffont.Boldweight = (short)FontBoldWeight.Bold;//加粗;
fCellStyle.SetFont(ffont);
fCellStyle.Alignment = HorizontalAlignment.Left;
fCellStyle.VerticalAlignment = VerticalAlignment.Center;
fCellStyle.BorderBottom = BorderStyle.Thin;
fCellStyle.BorderLeft = BorderStyle.Thin;
fCellStyle.BorderRight = BorderStyle.Thin;
fCellStyle.BorderTop = BorderStyle.Thin;
Sheet1.GetRow(0).Height = 30 * 20;
#region 标题风格
fcell.GetCell(0).CellStyle = fCellStyle;
fcell.CreateCell(1).CellStyle = fCellStyle;
fcell.CreateCell(2).CellStyle = fCellStyle;
fcell.CreateCell(3).CellStyle = fCellStyle;
fcell.CreateCell(4).CellStyle = fCellStyle;
fcell.CreateCell(5).CellStyle = fCellStyle;
fcell.CreateCell(6).CellStyle = fCellStyle;
fcell.CreateCell(7).CellStyle = fCellStyle;
fcell.CreateCell(8).CellStyle = fCellStyle;
fcell.CreateCell(9).CellStyle = fCellStyle;
fcell.CreateCell(10).CellStyle = fCellStyle;
fcell.CreateCell(11).CellStyle = fCellStyle;
fcell.CreateCell(12).CellStyle = fCellStyle;
fcell.CreateCell(13).CellStyle = fCellStyle;
fcell.CreateCell(14).CellStyle = fCellStyle;
fcell.CreateCell(15).CellStyle = fCellStyle;
fcell.CreateCell(16).CellStyle = fCellStyle;
fcell.CreateCell(17).CellStyle = fCellStyle;
fcell.CreateCell(18).CellStyle = fCellStyle;
fcell.CreateCell(19).CellStyle = fCellStyle;
fcell.CreateCell(20).CellStyle = fCellStyle;
fcell.CreateCell(21).CellStyle = fCellStyle;
fcell.CreateCell(22).CellStyle = fCellStyle;
fcell.CreateCell(23).CellStyle = fCellStyle;
#endregion 标题风格
//设置Sheet1的每一列的宽度
#region 每一列的宽度
Sheet1.SetColumnWidth(0, 7 * 256);
Sheet1.SetColumnWidth(1, 11 * 256);
Sheet1.SetColumnWidth(2, 23 * 256);
Sheet1.SetColumnWidth(3, 11 * 256);
Sheet1.SetColumnWidth(4, 9 * 256);
Sheet1.SetColumnWidth(5, 9 * 256);
Sheet1.SetColumnWidth(6, 9 * 256);
Sheet1.SetColumnWidth(7, 9 * 256);
Sheet1.SetColumnWidth(8, 9 * 256);
Sheet1.SetColumnWidth(9, 9 * 256);
Sheet1.SetColumnWidth(10, 18 * 256);
Sheet1.SetColumnWidth(11, 9 * 256);
Sheet1.SetColumnWidth(12, 9 * 256);
Sheet1.SetColumnWidth(13, 9 * 256);
Sheet1.SetColumnWidth(14, 9 * 256);
Sheet1.SetColumnWidth(15, 9 * 256);
Sheet1.SetColumnWidth(16, 9 * 256);
Sheet1.SetColumnWidth(17, 9 * 256);
Sheet1.SetColumnWidth(18, 9 * 256);
Sheet1.SetColumnWidth(19, 9 * 256);
Sheet1.SetColumnWidth(20, 9 * 256);
Sheet1.SetColumnWidth(21, 9 * 256);
Sheet1.SetColumnWidth(22, 9 * 256);
Sheet1.SetColumnWidth(23, 18 * 256);
#endregion 每一列的宽度
//创建第二行
XSSFRow rowob2 = (XSSFRow)Sheet1.CreateRow(1);
#region 字段名
rowob2.CreateCell(0).SetCellValue("序号");
rowob2.CreateCell(1).SetCellValue("设备编号");
rowob2.CreateCell(2).SetCellValue("设备描述");
rowob2.CreateCell(3).SetCellValue("使用状态");
rowob2.CreateCell(4).SetCellValue("泊位状态(综合)");
rowob2.CreateCell(5).SetCellValue("心跳周期");
rowob2.CreateCell(6).SetCellValue("检查周期");
rowob2.CreateCell(7).SetCellValue("温度值");
rowob2.CreateCell(8).SetCellValue("电压");
rowob2.CreateCell(9).SetCellValue("信号强度");
rowob2.CreateCell(10).SetCellValue("更新时间");
rowob2.CreateCell(11).SetCellValue("地磁检测状态");
rowob2.CreateCell(12).SetCellValue("雷达检测状态");
rowob2.CreateCell(13).SetCellValue("射频故障代码");
rowob2.CreateCell(14).SetCellValue("磁地和雷达故障代码");
rowob2.CreateCell(15).SetCellValue("存储器故障代码");
rowob2.CreateCell(16).SetCellValue("电池故障代码");
rowob2.CreateCell(17).SetCellValue("设备健康状态");
rowob2.CreateCell(18).SetCellValue("磁场三轴采样");
rowob2.CreateCell(19).SetCellValue("雷达三轴采样");
rowob2.CreateCell(20).SetCellValue("雷达检测距离值");
rowob2.CreateCell(21).SetCellValue("设备软件版本");
rowob2.CreateCell(22).SetCellValue("设备硬件版本");
rowob2.CreateCell(23).SetCellValue("添加时间");
#endregion 字段名
//设置第二行的样式
XSSFCellStyle Style = (XSSFCellStyle)xssfworkbook.CreateCellStyle();
XSSFFont ffont1 = (XSSFFont)xssfworkbook.CreateFont();
ffont1.FontHeightInPoints = 10;
ffont1.Boldweight = (short)FontBoldWeight.Bold;//加粗;
ffont1.FontName = "宋体";
Style.Alignment = HorizontalAlignment.Center;
Style.VerticalAlignment = VerticalAlignment.Center;
Style.WrapText = true;
Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
Style.FillPattern = FillPattern.SolidForeground;
Style.BorderBottom = BorderStyle.Thin;
Style.BorderLeft = BorderStyle.Thin;
Style.BorderRight = BorderStyle.Thin;
Style.BorderTop = BorderStyle.Thin;
Style.SetFont(ffont1);
#region 字段名风格
rowob2.GetCell(0).CellStyle = Style;
rowob2.GetCell(1).CellStyle = Style;
rowob2.GetCell(2).CellStyle = Style;
rowob2.GetCell(3).CellStyle = Style;
rowob2.GetCell(4).CellStyle = Style;
rowob2.GetCell(5).CellStyle = Style;
rowob2.GetCell(6).CellStyle = Style;
rowob2.GetCell(7).CellStyle = Style;
rowob2.GetCell(8).CellStyle = Style;
rowob2.GetCell(9).CellStyle = Style;
rowob2.GetCell(10).CellStyle = Style;
rowob2.GetCell(11).CellStyle = Style;
rowob2.GetCell(12).CellStyle = Style;
rowob2.GetCell(13).CellStyle = Style;
rowob2.GetCell(14).CellStyle = Style;
rowob2.GetCell(15).CellStyle = Style;
rowob2.GetCell(16).CellStyle = Style;
rowob2.GetCell(17).CellStyle = Style;
rowob2.GetCell(18).CellStyle = Style;
rowob2.GetCell(19).CellStyle = Style;
rowob2.GetCell(20).CellStyle = Style;
rowob2.GetCell(21).CellStyle = Style;
rowob2.GetCell(22).CellStyle = Style;
rowob2.GetCell(23).CellStyle = Style;
#endregion 字段名风格
//设置表格内容显示样式1
#region
XSSFCellStyle ContentStyle = (XSSFCellStyle)xssfworkbook.CreateCellStyle();
XSSFFont contentFfont = (XSSFFont)xssfworkbook.CreateFont();
contentFfont.FontHeightInPoints = 9;
contentFfont.FontName = "宋体";
ContentStyle.Alignment = HorizontalAlignment.Left;
ContentStyle.VerticalAlignment = VerticalAlignment.Top;
ContentStyle.WrapText = true;
ContentStyle.BorderBottom = BorderStyle.Thin;
ContentStyle.BorderLeft = BorderStyle.Thin;
ContentStyle.BorderRight = BorderStyle.Thin;
ContentStyle.BorderTop = BorderStyle.Thin;
ContentStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("text");
ContentStyle.SetFont(contentFfont);
#endregion
//设置表格内容显示样式2
#region
XSSFCellStyle ContentStyle2 = (XSSFCellStyle)xssfworkbook.CreateCellStyle();
XSSFFont contentFfont2 = (XSSFFont)xssfworkbook.CreateFont();
contentFfont2.FontHeightInPoints = 9;
contentFfont2.FontName = "宋体";
ContentStyle2.Alignment = HorizontalAlignment.Center;
ContentStyle2.VerticalAlignment = VerticalAlignment.Center;
ContentStyle2.WrapText = true;
ContentStyle2.BorderBottom = BorderStyle.Thin;
ContentStyle2.BorderLeft = BorderStyle.Thin;
ContentStyle2.BorderRight = BorderStyle.Thin;
ContentStyle2.BorderTop = BorderStyle.Thin;
ContentStyle2.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("text");
ContentStyle2.SetFont(contentFfont2);
#endregion
int index = 1;
foreach (var device in devices)
{
XSSFRow row = (XSSFRow)Sheet1.CreateRow(index + 1);
//序号
row.CreateCell(0).SetCellValue(index);
#region 写值
row.CreateCell(1).SetCellValue(device.DeviceNo); //设备编号
row.CreateCell(2).SetCellValue(device.DeviceDescribe); //设备描述
switch (device.DeviceStatus) //使用状态
{
case 0:
row.CreateCell(3).SetCellValue("新增(未连接)");
break;
case 1:
row.CreateCell(3).SetCellValue("在线");
break;
case 2:
row.CreateCell(3).SetCellValue("离线");
break;
default:
row.CreateCell(3).SetCellValue("");
break;
}
switch (device.SynStatus)
{
case 0:
row.CreateCell(4).SetCellValue("无车");
break;
case 1:
row.CreateCell(4).SetCellValue("有车");
break;
case 2:
row.CreateCell(4).SetCellValue("等待激活");
break;
case 3:
row.CreateCell(4).SetCellValue("初始化中");
break;
default:
row.CreateCell(4).SetCellValue("");
break;
} //综合状态
//心跳周期
if (device.HbeatT != null)
{
row.CreateCell(5).SetCellValue(device.HbeatT + "分钟");
}
else
{
row.CreateCell(5).SetCellValue("");
}
//故障检查周期
if (device.healthT != null)
{
row.CreateCell(6).SetCellValue(device.healthT + "分钟");
}
else
{
row.CreateCell(6).SetCellValue("");
}
//温度值
if (device.Temperature != null)
{
row.CreateCell(7).SetCellValue(device.Temperature + "℃");
}
else
{
row.CreateCell(7).SetCellValue("");
}
//电压
if (device.BatVoltage != null)
{
row.CreateCell(8).SetCellValue(device.BatVoltage + "V");
}
else
{
row.CreateCell(8).SetCellValue("");
}
row.CreateCell(9).SetCellValue(device.NbSignal); //NB 信号强度
if (device.StatusUpdateTime == null) //设备更新时间
{
row.CreateCell(10).SetCellValue("");
}
else
{
row.CreateCell(10).SetCellValue(device.StatusUpdateTime.Value.ToString("yyyy/MM/dd HH:mm:ss"));
}
switch (device.MagStatus) //地磁检测状态
{
case 0:
row.CreateCell(11).SetCellValue("无车");
break;
case 1:
row.CreateCell(11).SetCellValue("有车");
break;
case 2:
row.CreateCell(11).SetCellValue("等待激活");
break;
case 3:
row.CreateCell(11).SetCellValue("强磁");
break;
default:
row.CreateCell(11).SetCellValue("");
break;
}
switch (device.RadaStatus) //雷达检测状态
{
case 0:
row.CreateCell(12).SetCellValue("无车");
break;
case 1:
row.CreateCell(12).SetCellValue("有车");
break;
case 2:
row.CreateCell(12).SetCellValue("遮挡");
break;
default:
row.CreateCell(12).SetCellValue("失效");
break;
}
switch (device.NbSignalCode) //射频故障代码
{
case 0:
row.CreateCell(13).SetCellValue("00(正常)");
break;
case 1:
row.CreateCell(13).SetCellValue("01(信号差)");
break;
default:
row.CreateCell(13).SetCellValue("");
break;
}
switch (device.SensorCode) //磁地和雷达传感器故障代码
{
case 0:
row.CreateCell(14).SetCellValue("00(正常)");
break;
case 1:
row.CreateCell(14).SetCellValue(device.FlashCode + "01(磁传感器故障)");
break;
case 2:
row.CreateCell(14).SetCellValue(device.FlashCode + "02(雷达传感器故障)");
break;
case 3:
row.CreateCell(14).SetCellValue(device.FlashCode + "03(磁和雷达传感器故障)");
break;
default:
row.CreateCell(14).SetCellValue("");
break;
}
switch (device.FlashCode) //存储器故障代码
{
case 0:
row.CreateCell(15).SetCellValue("01(正常)");
break;
case 1:
row.CreateCell(15).SetCellValue("02(无法读写)");
break;
case 2:
row.CreateCell(15).SetCellValue("03(已写满)");
break;
default:
row.CreateCell(15).SetCellValue("");
break;
}
switch (device.BatteryCode) //电池故障代码
{
case 0:
row.CreateCell(16).SetCellValue("00(正常)");
break;
case 1:
row.CreateCell(16).SetCellValue("01(电池电压低)");
break;
default:
row.CreateCell(16).SetCellValue("");
break;
}
switch (device.DevHealth) //设备健康状态
{
case 0:
row.CreateCell(17).SetCellValue("00(设备无故障)");
break;
case 1:
row.CreateCell(17).SetCellValue("01(电池电量低)");
break;
case 2:
row.CreateCell(17).SetCellValue("02(地磁故障)");
break;
default:
row.CreateCell(17).SetCellValue("");
break;
}
row.CreateCell(18).SetCellValue(device.MagneticXYZ); //磁场三轴采样
row.CreateCell(19).SetCellValue(device.RadarXYZ); //雷达三轴采样
//雷达检测到的距离值
if (device.Distance != null)
{
row.CreateCell(20).SetCellValue(device.Distance + "mm");
}
else
{
row.CreateCell(20).SetCellValue("");
}
row.CreateCell(21).SetCellValue(device.FwVer); //设备软件版本
row.CreateCell(22).SetCellValue(device.HwVer); //设备硬件版本
//设备添加时间
if (device.CreationTime == null)
{
row.CreateCell(23).SetCellValue("");
}
else
{
row.CreateCell(23).SetCellValue(device.CreationTime.Value.ToString("yyyy/MM/dd HH:mm:ss"));
}
#endregion 写值
Sheet1.GetRow(index + 1).Height = 30 * 15;
row.GetCell(0).CellStyle = ContentStyle2;
row.GetCell(1).CellStyle = ContentStyle2;
row.GetCell(2).CellStyle = ContentStyle;
row.GetCell(3).CellStyle = ContentStyle2;
row.GetCell(4).CellStyle = ContentStyle2;
row.GetCell(5).CellStyle = ContentStyle2;
row.GetCell(6).CellStyle = ContentStyle2;
row.GetCell(7).CellStyle = ContentStyle2;
row.GetCell(8).CellStyle = ContentStyle2;
row.GetCell(9).CellStyle = ContentStyle2;
row.GetCell(10).CellStyle = ContentStyle;
row.GetCell(11).CellStyle = ContentStyle2;
row.GetCell(12).CellStyle = ContentStyle2;
row.GetCell(13).CellStyle = ContentStyle2;
row.GetCell(14).CellStyle = ContentStyle2;
row.GetCell(15).CellStyle = ContentStyle2;
row.GetCell(16).CellStyle = ContentStyle2;
row.GetCell(17).CellStyle = ContentStyle2;
row.GetCell(18).CellStyle = ContentStyle2;
row.GetCell(19).CellStyle = ContentStyle2;
row.GetCell(20).CellStyle = ContentStyle2;
row.GetCell(21).CellStyle = ContentStyle2;
row.GetCell(22).CellStyle = ContentStyle2;
row.GetCell(23).CellStyle = ContentStyle;
index++;
}
string directory = Path.GetDirectoryName(filePath);
// 如果不存在该目录就创建该目录
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
FileStream fs = new FileStream(filePath, FileMode.Create);
xssfworkbook.Write(fs);
fs.Close();
}
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <returns>Excel工作表</returns>
public static MemoryStream ExportToExcel(DataTable sourceTable, string sheetName = "对账单明细表")
{
if (sourceTable.Rows.Count <= 0)
{
return(null);
}
IWorkbook workbook = new XSSFWorkbook();
// ICellStyle cellStyle = GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//取得列宽
int[] arrColWidth = new int[sourceTable.Columns.Count];
foreach (DataColumn item in sourceTable.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < sourceTable.Rows.Count; i++)
{
for (int j = 0; j < sourceTable.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(sourceTable.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
if (intTemp > 30)
{
break;
}
arrColWidth[j] = intTemp;
}
}
}
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// handling value.
int rowIndex = 1;
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
string drValue = row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
//newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal,CellType.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;
}
}
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
//ms.Flush();
//ms.Position = 0;
return(ms);
}
}
private void SetupStyles()
{
var myBlueColour = new XSSFColor(new byte[] { 31, 73, 125 });
var styleTable = _workbook.GetStylesSource();
// fonts
var headerFont = _workbook.CreateFont() as XSSFFont;
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerFont.Color = HSSFColor.White.Index;
var generalFont = _workbook.CreateFont() as XSSFFont;
generalFont.SetColor(myBlueColour);
var boldFont = _workbook.CreateFont() as XSSFFont;
boldFont.SetColor(myBlueColour);
boldFont.Boldweight = (short)FontBoldWeight.Bold;
// header
_headerStyle = styleTable.CreateCellStyle();
_headerStyle.SetFont(headerFont);
_headerStyle.FillForegroundXSSFColor = myBlueColour;
_headerStyle.FillPattern = FillPattern.SolidForeground;
//general data
_generalStyle = styleTable.CreateCellStyle();
_generalStyle.SetFont(generalFont);
//general bold data
_generalBoldStyle = styleTable.CreateCellStyle();
_generalBoldStyle.SetFont(boldFont);
//general grid data
_generalGridStyle = styleTable.CreateCellStyle();
_generalGridStyle.SetFont(generalFont);
_generalGridStyle.BorderLeft = BorderStyle.Thin;
_generalGridStyle.SetBorderColor(BorderSide.LEFT, myBlueColour);
_generalGridStyle.BorderTop = BorderStyle.Thin;
_generalGridStyle.SetBorderColor(BorderSide.TOP, myBlueColour);
_generalGridStyle.BorderRight = BorderStyle.Thin;
_generalGridStyle.SetBorderColor(BorderSide.RIGHT, myBlueColour);
_generalGridStyle.BorderBottom = BorderStyle.Thin;
_generalGridStyle.SetBorderColor(BorderSide.BOTTOM, myBlueColour);
_generalGridStyle.SetVerticalAlignment((short)VerticalAlignment.Top);
_generalGridStyle.WrapText = true;
//Date grid data
_dateGridStyle = styleTable.CreateCellStyle();
_dateGridStyle.SetFont(generalFont);
_dateGridStyle.BorderLeft = BorderStyle.Thin;
_dateGridStyle.SetBorderColor(BorderSide.LEFT, myBlueColour);
_dateGridStyle.BorderTop = BorderStyle.Thin;
_dateGridStyle.SetBorderColor(BorderSide.TOP, myBlueColour);
_dateGridStyle.BorderRight = BorderStyle.Thin;
_dateGridStyle.SetBorderColor(BorderSide.RIGHT, myBlueColour);
_dateGridStyle.BorderBottom = BorderStyle.Thin;
_dateGridStyle.SetBorderColor(BorderSide.BOTTOM, myBlueColour);
_dateGridStyle.DataFormat = _workbook.GetCreationHelper()
.CreateDataFormat()
.GetFormat("DateTimeFormat".GetUiTranslation());
_dateGridStyle.Alignment = HorizontalAlignment.Center;
_dateGridStyle.SetVerticalAlignment((short)VerticalAlignment.Top);
}
public void AddCellStyle(String StyleName, String FontName = "Arial", Int16 FontSize = 8,
Boolean IsItalic = false, FontUnderlineType UnderlineType = FontUnderlineType.None,
FontBoldWeight BoldWeight = FontBoldWeight.None, HorizontalAlignment HorizontalAlign = HorizontalAlignment.Left,
VerticalAlignment VerticalAlign = VerticalAlignment.Top, BorderStyle TopBorder = BorderStyle.None,
BorderStyle BottomBorder = BorderStyle.None, BorderStyle RightBorder = BorderStyle.None,
BorderStyle LeftBorder = BorderStyle.None, IndexedColors FontColor = null,
IndexedColors BackgroundColor = null, short HSSFBackgroundColorIndex = 64, byte[] XSSFColorByte = null)
{
IFont font = this.hssworkbook.CreateFont();
font.Color = ((FontColor == null) ? IndexedColors.Black.Index : FontColor.Index);
font.FontName = FontName;
font.FontHeightInPoints = FontSize;
font.IsItalic = IsItalic;
if (font.Underline != FontUnderlineType.None)
{
font.Underline = UnderlineType;
}
font.Boldweight = (short)BoldWeight;
if (this.IsNewFormat)
{
XSSFCellStyle style = (XSSFCellStyle)this.hssworkbook.CreateCellStyle();
style.SetFont(font);
style.Alignment = HorizontalAlign;
style.VerticalAlignment = VerticalAlign;
style.BorderTop = TopBorder;
style.BorderBottom = BottomBorder;
style.BorderRight = RightBorder;
style.BorderLeft = LeftBorder;
if (BackgroundColor != null)
{
style.FillForegroundColor = BackgroundColor.Index;
style.FillPattern = FillPattern.SolidForeground;
}
if (XSSFColorByte != null)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundXSSFColor = new XSSFColor(XSSFColorByte);
}
if (!this.XFontStyle.ContainsKey(StyleName))
{
this.XFontStyle.Add(StyleName, style);
}
else
{
this.XFontStyle[StyleName] = style;
}
}
else
{
HSSFCellStyle style2 = (HSSFCellStyle)this.hssworkbook.CreateCellStyle();
style2.SetFont(font);
style2.Alignment = HorizontalAlign;
style2.VerticalAlignment = VerticalAlign;
style2.BorderTop = TopBorder;
style2.BorderBottom = BottomBorder;
style2.BorderRight = RightBorder;
style2.BorderLeft = LeftBorder;
if (BackgroundColor != null)
{
style2.FillForegroundColor = BackgroundColor.Index;
style2.FillPattern = FillPattern.SolidForeground;
}
else if (HSSFBackgroundColorIndex != 64)
{
style2.FillPattern = FillPattern.SolidForeground;
style2.FillForegroundColor = HSSFBackgroundColorIndex;
}
if (!this.HFontStyle.ContainsKey(StyleName))
{
this.HFontStyle.Add(StyleName, style2);
}
else
{
this.HFontStyle[StyleName] = style2;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
/// <param name="startAddress"></param>
/// <param name="dataTable"></param>
/// <param name="iWorkbook"></param>
public void FillDataFromTable(XSSFWorkbook iWorkbook, XSSFSheet sheet, string startAddress, DataTable dataTable)
{
XSSFCellStyle headerCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
CT_Color ctColor = new CT_Color();
ctColor.SetRgb(112, 173, 71);
XSSFColor xssfColor = new XSSFColor(ctColor);
headerCellStyle.SetFillBackgroundColor(xssfColor);
headerCellStyle.SetFillForegroundColor(xssfColor);
XSSFFont hssfFont = iWorkbook.CreateFont() as XSSFFont;
hssfFont.FontHeightInPoints = 10;
hssfFont.FontName = "宋体";
hssfFont.Boldweight = 700;
headerCellStyle.SetFont(hssfFont);
XSSFCellStyle contentCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
XSSFFont contentHssfFont = iWorkbook.CreateFont() as XSSFFont;
contentHssfFont.FontHeightInPoints = 10;
contentHssfFont.FontName = "宋体";
contentCellStyle.SetFont(contentHssfFont);
string rowIndexStr = string.Empty;
string cellIndexStr = string.Empty;
int cellIndex = 0;
for (int i = 0; i < startAddress.Length; i++)
{
int tempNum;
if (int.TryParse(startAddress[i].ToString(), out tempNum))
{
rowIndexStr += "" + tempNum;
}
else
{
cellIndexStr += "" + startAddress[i];
}
}
var rowIndex = Convert.ToInt32(rowIndexStr);
for (int i = cellIndexStr.Length - 1; i >= 0; i--)
{
if (i == cellIndexStr.Length - 1)
{
cellIndex += cellIndexStr[i] - 65;
}
else
{
cellIndex += (cellIndexStr[i] - 64) * 26;
}
}
cellIndex = 0;
//textBox1.Text += "\r\n 共有数据:" + _DataTable.Rows.Count;
int tempCellIndex = cellIndex;
try
{
//sheet分开包含表头
if (!ckbSheet.Checked)
{
rowIndex = sheet.LastRowNum;
if (rowIndex != 0)
{
rowIndex++;
}
}
//是否包含表头
XSSFRow excelRow;
XSSFCell excelCell;
if (ckbIsIncludeHeader.Checked && rowIndex <= 1)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (string.IsNullOrEmpty(dataColumn.ColumnName))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataColumn.ColumnName));
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
}
rowIndex++;
tempCellIndex = cellIndex;
}
//填充数据
foreach (DataRow dataRow in dataTable.Rows)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (dataRow[dataColumn] == DBNull.Value || string.IsNullOrEmpty(Convert.ToString(dataRow[dataColumn])))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = contentCellStyle;
tempCellIndex++;
continue;
}
if (dataRow[dataColumn] is decimal || dataRow[dataColumn] is int)
{
excelCell.SetCellType(CellType.Numeric);
excelCell.SetCellValue(Convert.ToDouble(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
tempCellIndex++;
}
tempCellIndex = cellIndex;
rowIndex++;
}
}
catch (Exception ex)
{
throw new Exception("行" + rowIndex + "列" + tempCellIndex + "_" + ex.Message, ex);
}
}
private void FlatFileExcel(string sourceConn, string sourceData, string sourceSQL, string destFile)
{
string dir = Path.GetDirectoryName(destFile);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using (SqlConnection srcConn = new SqlConnection(string.Format(sourceConn, sourceData)))
{
SqlCommand srcCmd = new SqlCommand(sourceSQL, srcConn);
srcCmd.CommandTimeout = CmdTimeout;
srcConn.Open();
using (FileStream stream = new FileStream(destFile, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
XSSFCellStyle headerStyle1 = (XSSFCellStyle)wb.CreateCellStyle();
XSSFFont headerFont1 = (XSSFFont)wb.CreateFont();
headerFont1.Color = XSSFFont.DEFAULT_FONT_COLOR;
headerFont1.IsBold = true;
headerStyle1.SetFont(headerFont1);
headerStyle1.SetFillForegroundColor(new XSSFColor(Color.LightGreen));
headerStyle1.FillPattern = FillPattern.SolidForeground;
headerStyle1.BorderRight = BorderStyle.Thin;
headerStyle1.BorderLeft = BorderStyle.Thin;
headerStyle1.BorderBottom = BorderStyle.Thin;
XSSFCellStyle normalStyle = (XSSFCellStyle)wb.CreateCellStyle();
XSSFFont normalFont = (XSSFFont)wb.CreateFont();
normalFont.Color = XSSFFont.DEFAULT_FONT_COLOR;
normalStyle.SetFont(normalFont);
normalStyle.BorderRight = BorderStyle.Thin;
normalStyle.BorderLeft = BorderStyle.Thin;
normalStyle.BorderBottom = BorderStyle.Thin;
normalStyle.SetDataFormat(HSSFDataFormat.GetBuiltinFormat("#,##0.00"));
using (SqlDataReader reader = srcCmd.ExecuteReader())
{
int i = 0;
IRow row = sheet.CreateRow(0);
List <string> list = new List <string>();
for (; i < reader.FieldCount; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(reader.GetName(i));
cell.CellStyle = headerStyle1;
}
i = 1;
while (reader.Read())
{
IRow rowLoop = sheet.CreateRow(i);
for (int j = 0; j < reader.FieldCount; j++)
{
ICell cell = rowLoop.CreateCell(j);
cell.SetCellValue(reader[j].ToString());
cell.CellStyle = normalStyle;
}
i++;
}
for (i = 0; i < reader.FieldCount; i++)
{
sheet.AutoSizeColumn(i);
}
}
wb.Write(stream);
}
srcConn.Close();
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportToMemorySteram(DataTable dtSource, string strHeaderText)
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet() as XSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "Lee"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "宋轶"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
var dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
var format = workbook.CreateDataFormat() as HSSFDataFormat;
if (dateStyle != null)
{
if (format != null)
{
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
}
//取得列宽
var arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (var i = 0; i < dtSource.Rows.Count; i++)
{
for (var j = 0; j < dtSource.Columns.Count; j++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
var rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
{
if (sheet != null)
{
var headerRow = sheet.CreateRow(0) as XSSFRow;
if (headerRow != null)
{
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
if (headStyle != null)
{
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont() as XSSFFont;
if (font != null)
{
font.FontHeightInPoints = 20;
font.Boldweight = 700;
font.IsBold = true;
headStyle.SetFont(font);
}
headerRow.GetCell(0).CellStyle = headStyle;
}
}
}
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
if (sheet != null)
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
if (sheet != null)
{
XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
if (headStyle != null)
{
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont() as XSSFFont;
if (font != null)
{
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
}
foreach (DataColumn column in dtSource.Columns)
{
if (headerRow != null)
{
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 填充内容
if (sheet != null)
{
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var 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;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
}
#endregion
rowIndex++;
}
}
using (var 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 DataTableToExcel(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)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
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)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 = (XSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(1);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)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) * 300);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
private XSSFCellStyle PhaseCellStyle(CellStyle paramCellStyle)
{
XSSFCellStyle style = null;
//try
//{
style = (XSSFCellStyle)_workbook.CreateCellStyle();
XSSFFont font = (XSSFFont)_workbook.CreateFont();
font.FontName = paramCellStyle.FontName.ToString();
font.FontHeightInPoints = paramCellStyle.FontHeightInPoints;
if (paramCellStyle.FontBoldFlag)
{
font.Boldweight = 700;
}
style.SetFont(font);
//设置水平位置
switch (paramCellStyle.HorizontalAlignment.ToString())
{
case "Center":
style.Alignment = HorizontalAlignment.Center;
break;
case "Left":
style.Alignment = HorizontalAlignment.Left;
break;
case "Right":
style.Alignment = HorizontalAlignment.Right;
break;
default:
style.Alignment = HorizontalAlignment.Center;
break;
}
//设置垂直位置
switch (paramCellStyle.VerticalAlignment.ToString())
{
case "Center":
style.VerticalAlignment = VerticalAlignment.Center;
break;
case "Top":
style.VerticalAlignment = VerticalAlignment.Top;
break;
case "Botton":
style.VerticalAlignment = VerticalAlignment.Bottom;
break;
default:
style.VerticalAlignment = VerticalAlignment.Center;
break;
}
//设置表格线样式宽度
switch (paramCellStyle.BorderStyle.ToString())
{
case "Thin":
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
break;
default:
break;
}
//设置表格线颜色
switch (paramCellStyle.BorderStyle.ToString())
{
case "Black":
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
break;
default:
break;
}
//设置单元格是否换行
#region 设置为文本
style.WrapText = paramCellStyle.WrapText;
XSSFDataFormat format = (XSSFDataFormat)_workbook.GetCreationHelper().CreateDataFormat();
short index = format.GetFormat("@");
style.DataFormat = index;
#endregion
//}
//catch(Exception ex)
//{
// string ss = ex.Message;
// return null;
//}
return(style);
}
private static MemoryStream ExportExcel2007(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
//格式日期
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//格式数字
XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat;
decimelStyle.DataFormat = decimelformat.GetFormat("0.00####");
// 取得列宽
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)
{
if (rowIndex == 1048576 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
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 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.IsLocked = true;
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] > 255 ? 254 : arrColWidth[column.Ordinal] + 1) * 256);
}
//sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
}
rowIndex = 2;
#endregion 列头及样式
rowIndex = 2;
}
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (drValue.Length > 0)
{
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);
newCell.CellStyle = decimelStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion 填充内容
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
/// <summary>
/// 导出Xlsx
/// </summary>
/// <param name="localFilePath">文件保存路径</param>
/// <param name="dtSource">数据源</param>
public static void ExportXlsx(string localFilePath, System.Data.DataTable dtSource)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)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 = (XSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)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 = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)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 (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
public void OutExcelData()
{
string ExportFileName = "EmpData.xlsx"; string ExportFileTitle = "Data"; Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
XSSFWorkbook NpoiWB = new XSSFWorkbook();
XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat();
xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0"));
XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true;
cellStyleFontColor.SetFont(font1);
/// 進行產生Excel檔案流程
ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle);
List <string> listColumn = edModel.listEmployeeColumn;
/// 建立標題列
IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40;
for (int i = 0; i < listColumn.Count; i++)
{
ICell xCellT = xRowT.CreateCell(i); xCellT.SetCellValue(listColumn[i]);
}
/// 讀取資料庫資料
List <listEmployeeDetail> ListEmpData = new List <listEmployeeDetail>();
ListEmpData = edModel.ReListEmployeeDetail();
if (ListEmpData != null && ListEmpData.Count > 0)
{
for (int i = 0; i < ListEmpData.Count; i++)
{
listEmployeeDetail item = ListEmpData[i];
List <string> list = new List <string>();
list.Add(item.lEmpIndex.ToString());
list.Add(item.lEmpName.ToString());
list.Add(item.lEmpSex.ToString());
list.Add(item.lEmpEmail.ToString());
list.Add(item.lEmpAddress.ToString());
list.Add(item.lEmpMobile.ToString());
list.Add(item.lEmpPhone.ToString());
list.Add(item.lEmpNotation.ToString());
list.Add(item.lEmpRemark.ToString());
list.Add(item.lEmpStatus.ToString());
list.Add(item.lEmpJoinDate.ToString());
list.Add(item.lEmpLeaveDate.ToString());
IRow xRowD = xSheet.CreateRow(i + 1); xRowD.HeightInPoints = 30;
for (int b = 0; b < list.Count; b++)
{
ICell xCellData = xRowD.CreateCell(b); xCellData.SetCellValue(list[b]);
}
}
}
MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS);
Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + "");
Response.BinaryWrite(MS.ToArray());
// ----------------------------------------------------------------------------------------------
// 釋放記憶體參數
NpoiWB = null; MS.Close(); MS.Dispose();
Response.Flush(); Response.End();
}
public static Stream DataTableToExcel(string fileName, DataTable data,
List <ExcelColumnFormat> colFmt,
string cols,
Func <object, string, object> tranfunc = null,
bool isColumnWritten = true, string sheetName = "Sheet1", string tableTitle = null)
{
if (data.Rows.Count == 0)
{
return(null);
} //data为空，直接退出
int i = 0;
int j = 0;
int count = 0;
IWorkbook workbook = null;
ISheet sheet = null;
NPOIMemoryStream ms = null;
try
{
//转换对齐
Func <System.Drawing.ContentAlignment?, HorizontalAlignment> _transHAlignment = alignment =>
{
switch (alignment)
{
case System.Drawing.ContentAlignment.BottomRight:
case System.Drawing.ContentAlignment.MiddleRight:
case System.Drawing.ContentAlignment.TopRight:
return(HorizontalAlignment.Right);
case System.Drawing.ContentAlignment.BottomCenter:
case System.Drawing.ContentAlignment.MiddleCenter:
case System.Drawing.ContentAlignment.TopCenter:
return(HorizontalAlignment.Center);
}
return(HorizontalAlignment.Left);
};
//设置值的格式
Action <IRow, int, object, string> _val = (row, columnIndex, v, colCode) =>
{
string rlt = "";
string vstr = Ass.P.PStr(v);
string tv = null;
if (tranfunc != null)
{
tv = Ass.P.PStr(tranfunc(v, colCode));
}
ExcelColumnFormat fmt = null;
if (tv != null && tv != vstr)
{
rlt = tv;
}
else if (colFmt.Any(m => m.ColumnNameCode == colCode))
{
rlt = vstr;
string v0 = "";
fmt = colFmt.Find(m => m.ColumnNameCode == colCode);
if (v != null && fmt.ToStringFormat.IsNotEmpty())
{
v0 = string.Format("{0:" + fmt.ToStringFormat + "}", v);
if (v0 != vstr)
{
rlt = v0;
}
}
}
else
{
rlt = vstr;
}
//设置值
var cell = row.CreateCell(j);
var t = rlt.GetStringType();
double dbval = 0; DateTime dtval;
if (double.TryParse(rlt, out dbval))
{
cell.SetCellValue(dbval);
}
else if (DateTime.TryParse(rlt, out dtval))
{
cell.SetCellValue(rlt);
}
else
{
cell.SetCellValue(rlt);
}
};
using (ms = new NPOIMemoryStream())
{
if (!GetWorkbook(fileName, out workbook))
{
return(null);
}
List <string> colList = cols.IsEmpty()?new List <string>(): (cols + "").Split(',').ToList();//获取列名集合
sheet = workbook.CreateSheet(sheetName);
//表标题
if (tableTitle.IsNotEmpty())
{
IRow row = sheet.CreateRow(count++);
ICell cell = row.CreateCell(0);
XSSFCellStyle ztStyle = (XSSFCellStyle)workbook.CreateCellStyle();
IFont ztFont = workbook.CreateFont();
ztFont.FontHeightInPoints = 14;
ztFont.Underline = FontUnderlineType.DoubleAccounting;
ztStyle.SetFont(ztFont);
ztStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = ztStyle;
cell.SetCellValue(tableTitle);
var colnum = colList.Count > 0 ? colList.Count : data.Columns.Count; //列数
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colnum - 1)); //合并单元格
}
Dictionary <int, ICellStyle> _columeCellStyles = new Dictionary <int, ICellStyle>();
//写入DataTable的列名
if (isColumnWritten == true)
{
IRow row = sheet.CreateRow(count++);
if (colList.Count > 0) //设定了输出的列
{
for (j = 0; j < colList.Count(); j++)
{
var key = colList[j];
var keyname = colFmt.Any(m => m.ColumnNameCode == key) ? colFmt.Find(m => m.ColumnNameCode == key).ColumnName : key;
var cell = row.CreateCell(j);
var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
cell.SetCellValue(keyname);
//设置对齐与数据格式
var cStyle = workbook.CreateCellStyle(); bool bfmt = false;
if (cellfmt.Alignment != null)
{
bfmt = true;
cStyle.Alignment = _transHAlignment(cellfmt.Alignment);
}
if (cellfmt.DataFormat.IsNotEmpty())
{
bfmt = true;
short?dfmt = null; IDataFormat format = workbook.CreateDataFormat();
switch (cellfmt.DataFormat)
{
case "Price": dfmt = format.GetFormat("#,##0.00"); break;
case "Date": dfmt = HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd"); break;
case "DateTime": dfmt = 0x16; break; // HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd hh:mm:ss"); break;
case "Id": dfmt = HSSFDataFormat.GetBuiltinFormat("@"); break;
}
cStyle.DataFormat = dfmt.Value;
}
if (bfmt)
{
_columeCellStyles.Add(j, cStyle);
}
//设置列宽
if (cellfmt.ColumnWidth.HasValue)
{
sheet.SetColumnWidth(j, (int)((cellfmt.ColumnWidth.Value + 0.72) * 256));
}
}
}
else
{
for (j = 0; j < data.Columns.Count; j++)
{
string key = data.Columns[j].ColumnName;
if (colFmt.Any(m => m.ColumnNameCode == key))//改列名为中文
{
var cell = row.CreateCell(j);
var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
cell.SetCellValue(cellfmt.ColumnName);
//设置对齐
if (cellfmt.Alignment != null)
{
sheet.GetColumnStyle(j).Alignment = _transHAlignment(cellfmt.Alignment);
}
//设置列宽
if (cellfmt.ColumnWidth.HasValue)
{
sheet.SetColumnWidth(j, cellfmt.ColumnWidth.Value);
}
}
else
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
}
}
}
//写入数据
if (colList.Count > 0)
{
for (i = 0; i < data.Rows.Count; i++, count++)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < colList.Count; j++)
{
_val(row, j, data.Rows[i][colList[j]], colList[j]);
}
}
}
else
{
for (i = 0; i < data.Rows.Count; i++, count++)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; j++)
{
_val(row, j, data.Rows[i][j], data.Columns[j].ColumnName);
}
}
}
//设置列格式
foreach (int colIndex in _columeCellStyles.Keys)
{
sheet.SetDefaultColumnStyle(colIndex, _columeCellStyles[colIndex]);
}
workbook.Write(ms); //写入到excel
ms.Flush();
ms.Position = 0;
return(ms);
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception: " + ex.Message);
return(null);
}
}
public static bool DataSetToExcel(DataSet ds, string Path)
{
bool result = false;
FileStream fs = null;
XSSFWorkbook workbook = new XSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
////自定义表头
string name = ds.Tables[i].TableName;
//for (var j = 0; j < dataList[name].ToArray().Count(); j++)
//{
// headerRow.CreateCell(j).SetCellValue(dataList[name].ToArray()[j]);
// headerRow.GetCell(j).CellStyle = headStyle;
//}
}
#endregion
rowIndex = 1;
}
#endregion
foreach (DataRow row in ds.Tables[i].Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in ds.Tables[i].Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
newCell.SetCellValue(GetValue(row[column].ToString(), type));
}
#endregion
rowIndex++;
}
}
using (fs = File.OpenWrite(Path))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
return(result);
}
public XSSFWorkbook ExportReportData(string id)
{
var baseInfoData = DC.Set <SmEntEvaluationGeneral>().FromSql("SmEnt_Get_EvaluationGeneralInfo @baseId = {0}", id).FirstOrDefault();
var evaluationTeamDetailData = DC.Set <EvaluationTeamInfo>().FromSql("SmEnt_Get_EvaluationTeamInfo @baseId = {0}", id).ToList();
var fileDetailData = DC.Set <SmEntEvaluationTemplate>().FromSql("SmEnt_Get_EvaluationReport @baseId = {0},@type = {1}", id, 0).ToList();
var sceneDetailData = DC.Set <SmEntEvaluationTemplate>().FromSql("SmEnt_Get_EvaluationReport @baseId = {0},@type = {1}", id, 1).ToList();
XSSFWorkbook input;
using (FileStream file = new FileStream(FinalReportPath, FileMode.Open, FileAccess.Read))
{
input = new XSSFWorkbook(file);
}
ISheet templatesheet = input.GetSheet("报告");
XSSFFont myFont = (XSSFFont)input.CreateFont();
myFont.FontHeightInPoints = (short)16;
myFont.FontName = "Calibri";
//set style
XSSFCellStyle noborderedCellStyle = (XSSFCellStyle)input.CreateCellStyle();
noborderedCellStyle.SetFont(myFont);
noborderedCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//set middle
noborderedCellStyle.Alignment = HorizontalAlignment.Left;
noborderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
IRow row;
row = templatesheet.GetRow(15);
ICell page1cell = row.CreateCell(3);
page1cell.SetCellValue(baseInfoData.ComapanyName);
page1cell.CellStyle = noborderedCellStyle;
row = templatesheet.GetRow(17);
page1cell = row.CreateCell(3);
page1cell.SetCellValue(baseInfoData.Industry);
page1cell.CellStyle = noborderedCellStyle;
row = templatesheet.GetRow(19);
page1cell = row.CreateCell(3);
page1cell.SetCellValue(baseInfoData.EvaluationStartDate.ToString("yyyy-MM-dd"));
page1cell.CellStyle = noborderedCellStyle;
var teamLeader = evaluationTeamDetailData.Where(y => y.Position == "组长").FirstOrDefault();
XSSFFont myFont02 = (XSSFFont)input.CreateFont();
myFont02.FontHeightInPoints = (short)12;
myFont02.FontName = "宋体";
//set style
XSSFCellStyle borderedCellStyle = (XSSFCellStyle)input.CreateCellStyle();
borderedCellStyle.SetFont(myFont02);
//set middle
borderedCellStyle.Alignment = HorizontalAlignment.Center;
borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
borderedCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium;
borderedCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
borderedCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
row = templatesheet.GetRow(56);
ICell evaTeam = row.CreateCell(2);
if (teamLeader != null)
{
evaTeam.SetCellValue(teamLeader.Name);
evaTeam.CellStyle = borderedCellStyle;
evaTeam = row.CreateCell(3);
evaTeam.SetCellValue(teamLeader.Position);
evaTeam.CellStyle = borderedCellStyle;
evaTeam = row.CreateCell(4);
evaTeam.SetCellValue(teamLeader.Mobile);
evaTeam.CellStyle = borderedCellStyle;
}
var evaTeamList = evaluationTeamDetailData.Where(y => y.Position == "成员").ToList();
var i = 57;
foreach (var obj in evaTeamList)
{
row = templatesheet.GetRow(i);
evaTeam = row.CreateCell(2);
evaTeam.SetCellValue(obj.Name);
evaTeam.CellStyle = borderedCellStyle;
evaTeam = row.CreateCell(3);
evaTeam.SetCellValue(obj.Position);
evaTeam.CellStyle = borderedCellStyle;
evaTeam = row.CreateCell(4);
evaTeam.SetCellValue(obj.Mobile);
evaTeam.CellStyle = borderedCellStyle;
i++;
}
//Applied Enterprise Info
row = templatesheet.GetRow(64);
ICell appliedEnt = row.CreateCell(1);
appliedEnt.SetCellValue(baseInfoData.ComapanyName);
appliedEnt.CellStyle = borderedCellStyle;
row = templatesheet.GetRow(65);
appliedEnt = row.CreateCell(1);
appliedEnt.SetCellValue(baseInfoData.LegalRepresentative);
appliedEnt.CellStyle = borderedCellStyle;
appliedEnt = row.CreateCell(3);
appliedEnt.SetCellValue(baseInfoData.LegalRepTel);
appliedEnt.CellStyle = borderedCellStyle;
appliedEnt = row.CreateCell(6);
appliedEnt.SetCellValue(baseInfoData.LegalRepMobile);
appliedEnt.CellStyle = borderedCellStyle;
row = templatesheet.GetRow(66);
appliedEnt = row.CreateCell(1);
appliedEnt.SetCellValue(baseInfoData.ContactName);
appliedEnt.CellStyle = borderedCellStyle;
appliedEnt = row.CreateCell(3);
appliedEnt.SetCellValue(baseInfoData.ContactTel);
appliedEnt.CellStyle = borderedCellStyle;
appliedEnt = row.CreateCell(6);
appliedEnt.SetCellValue(baseInfoData.ContactFax);
appliedEnt.CellStyle = borderedCellStyle;
row = templatesheet.GetRow(67);
appliedEnt = row.CreateCell(3);
appliedEnt.SetCellValue(baseInfoData.ContactMobile);
appliedEnt.CellStyle = borderedCellStyle;
appliedEnt = row.CreateCell(6);
appliedEnt.SetCellValue(baseInfoData.ContactEmail);
appliedEnt.CellStyle = borderedCellStyle;
//write scene info
XSSFFont myFont03 = (XSSFFont)input.CreateFont();
myFont03.FontHeightInPoints = (short)12;
myFont03.FontName = "宋体";
//set style
XSSFCellStyle listInfoStyle = (XSSFCellStyle)input.CreateCellStyle();
listInfoStyle.SetFont(myFont03);
byte[] rgb = new byte[3] {
255, 255, 255
};
XSSFColor white = new XSSFColor(rgb);
//set middle
listInfoStyle.Alignment = HorizontalAlignment.Left;
listInfoStyle.VerticalAlignment = VerticalAlignment.Center;
listInfoStyle.FillPattern = FillPattern.SolidForeground;
((XSSFCellStyle)listInfoStyle).SetFillBackgroundColor(white);
((XSSFCellStyle)listInfoStyle).SetFillForegroundColor(white);
listInfoStyle.BorderBottom = BorderStyle.None;
listInfoStyle.BorderLeft = BorderStyle.None;
listInfoStyle.BorderRight = BorderStyle.None;
listInfoStyle.BorderTop = BorderStyle.None;
//write file info
ICell listcell;
i = 82;
foreach (var obj in fileDetailData)
{
row = templatesheet.GetRow(i);
listcell = row.CreateCell(1);
listcell.SetCellValue(obj.UnMatchedItemDescription);
listcell.CellStyle = listInfoStyle;
i++;
}
i = 95;
foreach (var obj in sceneDetailData)
{
row = templatesheet.GetRow(i);
listcell = row.CreateCell(1);
listcell.SetCellValue(obj.UnMatchedItemDescription);
listcell.CellStyle = listInfoStyle;
i++;
}
return(input);
}