public static NPOI.SS.UserModel.ICellStyle GetCellStyle(this NPOI.SS.UserModel.IWorkbook workbook, short backColorIndex, short fontColorIndex, System.Drawing.Font font, NPOI.SS.UserModel.HorizontalAlignment horizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General, NPOI.SS.UserModel.VerticalAlignment verticalAlignment = NPOI.SS.UserModel.VerticalAlignment.None, NPOI.SS.UserModel.BorderStyle borderLeft = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderTop = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderRight = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderBottom = NPOI.SS.UserModel.BorderStyle.None) { NPOI.SS.UserModel.ICellStyle cellStyle = workbook.CreateCellStyle(); if (backColorIndex >= 8 && backColorIndex <= 63) { cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; cellStyle.FillForegroundColor = backColorIndex; } cellStyle.Alignment = horizontalAlignment; cellStyle.VerticalAlignment = verticalAlignment; cellStyle.BorderLeft = borderLeft; cellStyle.BorderTop = borderTop; cellStyle.BorderRight = borderRight; cellStyle.BorderBottom = borderBottom; if (font != null) { NPOI.SS.UserModel.IFont cellFont = workbook.CreateFont(); if (fontColorIndex >= 8 && fontColorIndex <= 63) { cellFont.Color = fontColorIndex; } cellFont.FontName = font.Name; cellFont.FontHeight = font.Size; cellFont.Boldweight = (short)(font.Bold ? NPOI.SS.UserModel.FontBoldWeight.Bold : NPOI.SS.UserModel.FontBoldWeight.Normal); cellStyle.SetFont(cellFont); } return(cellStyle); }
/// <summary> /// 导出excel /// </summary> /// <param name="response"></param> /// <param name="dt"></param> /// <param name="fileName"></param> /// <param name="sheetname"></param> public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell = null; NPOI.SS.UserModel.IFont font = book.CreateFont(); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "微软雅黑"; style.SetFont(font); for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { string strColDataType = dt.Columns[j].DataType.ToString(); if (strColDataType.Equals("System.Int32")) { int intValue = 0; int.TryParse(dt.Rows[i][j].ToString(), out intValue); row2.CreateCell(j).SetCellValue(intValue); } else if (strColDataType.Equals("System.String")) { row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } else if (strColDataType.Equals("System.Double")) { double dblValue = 0; double.TryParse(dt.Rows[i][j].ToString(), out dblValue); row2.CreateCell(j).SetCellValue(dblValue); } } } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls")); response.ContentType = "application/vnd.ms-excel"; //response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
/// <summary> /// 写入DataTable到Excel /// </summary> /// <param name="dt"></param> /// <param name="excelFile"></param> public static void writeDataTableToExcel(DataTable dt, string excelFile) { //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region 设置Excel样式 //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); #endregion //写入基本数据 writeSheet(workbook, cellStyleA, cellStyleB, dt); #region 输出文件 //输出到流 workbook.Write(memoryStream); //写Excel文件 File.WriteAllBytes(excelFile, memoryStream.ToArray()); #endregion }
public void SaveAsXls(string xlsFilepath) { HSSFWorkbook workbook = new HSSFWorkbook(); //create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Hever Translations"; workbook.DocumentSummaryInformation = dsi; //create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "Statistics - auto generated file"; workbook.SummaryInformation = si; HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("statistics"); NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); headerRow.CreateCell(0).SetCellValue("Text"); headerRow.CreateCell(1).SetCellValue("Words in Text"); headerRow.CreateCell(2).SetCellValue("Instances of Text"); //HSSFFont headerFont = workbook.CreateFont(); NPOI.SS.UserModel.IFont headerFont = workbook.CreateFont(); headerFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; headerRow.Cells[0].CellStyle = workbook.CreateCellStyle(); headerRow.Cells[0].CellStyle.SetFont(headerFont); headerRow.Cells[1].CellStyle = workbook.CreateCellStyle(); headerRow.Cells[1].CellStyle.SetFont(headerFont); headerRow.Cells[2].CellStyle = workbook.CreateCellStyle(); headerRow.Cells[2].CellStyle.SetFont(headerFont); int totalWordsInText = 0; int totalInstances = 0; int rowNumber = 0; foreach (KeyValuePair <string, int> kvp in m_elementsCount) { string text = kvp.Key; int count = kvp.Value; int wordsCount = m_elementsWordCount[kvp.Key]; totalWordsInText += wordsCount; totalInstances += count; rowNumber++; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowNumber); row.CreateCell(0).SetCellValue(text); row.CreateCell(1).SetCellValue(wordsCount); row.CreateCell(2).SetCellValue(count); } rowNumber++; rowNumber++; sheet.CreateRow(rowNumber).CreateCell(0).SetCellValue("Total words: " + totalWordsInText.ToString()); rowNumber++; sheet.CreateRow(rowNumber).CreateCell(0).SetCellValue("Total instances: " + totalInstances.ToString()); sheet.CreateFreezePane(0, 1, 0, 1); FileStream file = new FileStream(xlsFilepath, FileMode.Create); workbook.Write(file); file.Close(); }
public byte[] ExportXls(List <GroupOperation> list, GroupByEnum groupBy) { NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Plan 1"); int rowNumer = 0; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowNumer); NPOI.SS.UserModel.ICell cell; NPOI.SS.UserModel.IFont hFont = workbook.CreateFont(); hFont.FontHeightInPoints = 12; hFont.FontName = "Arial"; NPOI.SS.UserModel.ICellStyle styleHeader = workbook.CreateCellStyle(); styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleHeader.SetFont(hFont); NPOI.SS.UserModel.ICellStyle styleDisabled = workbook.CreateCellStyle(); styleDisabled.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; styleDisabled.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; styleDisabled.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; var listColumns = new List <ExportColumnInfo>(); listColumns.Add(new ExportColumnInfo() { Name = groupBy.GetDescription() }); listColumns.Add(new ExportColumnInfo() { Name = "Quantidade" }); listColumns.Add(new ExportColumnInfo() { Name = "Preço Médio" }); for (int i = 0; i < listColumns.Count; i++) { cell = row.CreateCell(i); cell.SetCellValue(listColumns[i].Name); cell.CellStyle = styleHeader; } //---- row foreach (var item in list) { rowNumer++; row = sheet.CreateRow(rowNumer); row.CreateCell(0).SetCellValue(item.AccountNumber ?? item.Active ?? item.OperationType); row.CreateCell(1).SetCellValue(item.Quantity); row.CreateCell(2).SetCellValue((double)item.AveragePrice); } for (int i = 0; i < listColumns.Count; i++) { sheet.AutoSizeColumn(i); } byte[] byteArray; using (System.IO.MemoryStream stream = new System.IO.MemoryStream()) { workbook.Write(stream); byteArray = stream.ToArray(); } return(byteArray); }
/// <summary> /// 下载模板excel(弃用) /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void DOWNLOADEXCEL(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { try { string strName = string.Empty; if (P1 == "KHGL") { strName = "CRM_客户_导入模板.xls"; } else if (P1 == "KHLXR") { strName = "CRM_客户联系人_导入模板.xls"; } else if (P1 == "HTGL") { strName = "CRM_合同_导入模板.xls"; } object curContext = object.Current; string headrow = context.Request["headrow"] ?? "0";//头部开始行下标 string path = curContext.Server.MapPath(@"/ViewV5/base/" + strName); FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read); string suffix = path.Substring(path.LastIndexOf(".") + 1).ToLower(); IWorkbook workbook = null; if (suffix == "xlsx") // 2007版本 { workbook = new XSSFWorkbook(file); } else if (suffix == "xls") // 2003版本 { workbook = new HSSFWorkbook(file); } ISheet sheet = workbook.GetSheetAt(0); IRow headerRow = sheet.GetRow(int.Parse(headrow)); IRow oneRow = sheet.GetRow(int.Parse(headrow) + 1); int icolIndex = headerRow.Cells.Count; DataTable dtExtColumn = new JH_Auth_ExtendModeB().GetExtColumnAll(UserInfo.QYinfo.ComId, P1); foreach (DataRow drExt in dtExtColumn.Rows) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(drExt["TableFiledName"].ToString()); cell.CellStyle = headerRow.Cells[icolIndex - 1].CellStyle; ICell onecell = oneRow.CreateCell(icolIndex); onecell.SetCellValue(""); onecell.CellStyle = oneRow.Cells[icolIndex - 1].CellStyle; icolIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } if (P1 == "KHGL") { //表头样式 ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = BorderStyle.Thin; HeadercellStyle.BorderLeft = BorderStyle.Thin; HeadercellStyle.BorderRight = BorderStyle.Thin; HeadercellStyle.BorderTop = BorderStyle.Thin; HeadercellStyle.Alignment = HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; headerfont.FontHeightInPoints = 12; HeadercellStyle.SetFont(headerfont); //单元格样式 ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; headerfont.FontHeightInPoints = 10; cellStyle.SetFont(cellfont); for (int i = 10; i < 15; i++) { string strZTName = string.Empty; if (i == 10) { strZTName = "客户类型"; } if (i == 11) { strZTName = "跟进状态"; } if (i == 12) { strZTName = "客户来源"; } if (i == 13) { strZTName = "所属行业"; } if (i == 14) { strZTName = "人员规模"; } ISheet sheet1 = workbook.CreateSheet(strZTName); IRow headerRow1 = sheet1.CreateRow(0); ICell cell1 = headerRow1.CreateCell(0); cell1.SetCellValue(strZTName); cell1.CellStyle = HeadercellStyle; int rowindex1 = 1; foreach (var l in new JH_Auth_ZiDianB().GetEntities(p => p.ComId == UserInfo.QYinfo.ComId && p.Class == i)) { IRow DataRow = sheet1.CreateRow(rowindex1); ICell cell = DataRow.CreateCell(0); cell.SetCellValue(l.TypeName); cell.CellStyle = cellStyle; rowindex1++; } sheet1.AutoSizeColumn(0); } } if (P1 == "HTGL") { //表头样式 ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = BorderStyle.Thin; HeadercellStyle.BorderLeft = BorderStyle.Thin; HeadercellStyle.BorderRight = BorderStyle.Thin; HeadercellStyle.BorderTop = BorderStyle.Thin; HeadercellStyle.Alignment = HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; headerfont.FontHeightInPoints = 12; HeadercellStyle.SetFont(headerfont); //单元格样式 ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; headerfont.FontHeightInPoints = 10; cellStyle.SetFont(cellfont); for (int i = 16; i < 18; i++) { string strZTName = string.Empty; if (i == 16) { strZTName = "合同类型"; } if (i == 17) { strZTName = "付款方式"; } ISheet sheet1 = workbook.CreateSheet(strZTName); IRow headerRow1 = sheet1.CreateRow(0); ICell cell1 = headerRow1.CreateCell(0); cell1.SetCellValue(strZTName); cell1.CellStyle = HeadercellStyle; int rowindex1 = 1; foreach (var l in new JH_Auth_ZiDianB().GetEntities(p => p.ComId == UserInfo.QYinfo.ComId && p.Class == i)) { IRow DataRow = sheet1.CreateRow(rowindex1); ICell cell = DataRow.CreateCell(0); cell.SetCellValue(l.TypeName); cell.CellStyle = cellStyle; rowindex1++; } sheet1.AutoSizeColumn(0); } } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); //object curContext = object.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strName, Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); curContext.Response.End(); workbook = null; ms.Close(); ms.Dispose(); } } catch { msg.ErrorMsg = "下载失败!"; } }
/// <summary> /// 根据用户设置的Style来决定是否要新建ExcelStyle /// </summary> /// <param name="style"></param> /// <returns></returns> private NPOI.SS.UserModel.ICellStyle GetExcelStyle(Style style) { int index = this.style.FindIndex(s => s.BackColor == style.BackColor && s.Border == style.Border && s.Font.FontFamily.Name == style.Font.FontFamily.Name && s.Font.Size == style.Font.Size && s.Font.Bold == style.Font.Bold && s.ForeColor == style.ForeColor && s.TextAlign == style.TextAlign); if (index < 0) { this.style.Add(style.DeepClone()); NPOI.SS.UserModel.ICellStyle cellStyle = workBook.CreateCellStyle(); excelStyle.Add(excelStyle.Count, cellStyle); //背景色 cellStyle.FillForegroundColor = (short)style.BackColor; cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; //文本色 NPOI.SS.UserModel.IFont fontStyle = workBook.CreateFont(); fontStyle.Color = (short)style.ForeColor; cellStyle.SetFont(fontStyle); //字体 fontStyle.FontName = style.Font.FontFamily.Name; fontStyle.FontHeightInPoints = (short)style.Font.Size; fontStyle.IsBold = style.Font.Bold; //fontStyle. //文本位置 switch (style.TextAlign) { case System.Drawing.ContentAlignment.TopCenter: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top; break; case System.Drawing.ContentAlignment.TopLeft: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top; break; case System.Drawing.ContentAlignment.TopRight: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top; break; case System.Drawing.ContentAlignment.BottomCenter: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Bottom; break; case System.Drawing.ContentAlignment.BottomLeft: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Bottom; break; case System.Drawing.ContentAlignment.BottomRight: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Bottom; break; case System.Drawing.ContentAlignment.MiddleCenter: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; break; case System.Drawing.ContentAlignment.MiddleLeft: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; break; case System.Drawing.ContentAlignment.MiddleRight: cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; break; } //边框 if (style.Border) { cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; } //((NPOI.HSSF.UserModel.HSSFSheet)table).SetEnclosedBorderOfRegion(cra, // style.Border ? NPOI.SS.UserModel.BorderStyle.Thin : NPOI.SS.UserModel.BorderStyle.None, (short)Color.Black); return(cellStyle); } return(excelStyle[index]); }
/// <summary> /// Applies a font to the specified Chars of a string. /// </summary> /// <param name="startIndex">The start index to apply the font to (inclusive).</param> /// <param name="endIndex"> The end index to apply to font to (exclusive).</param> /// <param name="font">The index of the font to use.</param> public void ApplyFont(int startIndex, int endIndex, NPOI.SS.UserModel.IFont font) { ApplyFont(startIndex, endIndex, font.Index); }
public void GenerateExcelReport(string exportFileName) { DataSet.Payroll_KTPFundStatement dataSet = CreateDataSource(); int lastRowIndex = 0; // Set column style NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("KTPF Contribution Report"); // Date format NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat(); NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; dateCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; dateCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; dateCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Numeric format NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; numericStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; numericStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; numericStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // String left format NPOI.HSSF.UserModel.HSSFCellStyle stringLeftStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); stringLeftStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; stringLeftStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; stringLeftStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; stringLeftStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // String center format NPOI.HSSF.UserModel.HSSFCellStyle stringCenterStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); stringCenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; stringCenterStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; stringCenterStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; stringCenterStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 0 style NPOI.HSSF.UserModel.HSSFCellStyle column0Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column0Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; column0Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; column0Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column0Style.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 10, 11 style NPOI.HSSF.UserModel.HSSFCellStyle column10To11Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column10To11Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column10To11Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column10To11Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 4 style NPOI.HSSF.UserModel.HSSFCellStyle column4Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column4Style.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); column4Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column4Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column4Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Column 8 style NPOI.HSSF.UserModel.HSSFCellStyle column8Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); column8Style.DataFormat = format.GetFormat("yyyy-MM-dd"); column8Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; column8Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; column8Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; column8Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED; // Align right NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; // Bottom border NPOI.HSSF.UserModel.HSSFCellStyle bottomBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); bottomBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; // Bold style NPOI.HSSF.UserModel.HSSFCellStyle boldStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); NPOI.SS.UserModel.IFont boldFont = workbook.CreateFont(); boldFont.Boldweight = 700; boldStyle.SetFont(boldFont); // Header Border NPOI.HSSF.UserModel.HSSFCellStyle headerBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; headerBorderStyle.SetFont(boldFont); headerBorderStyle.WrapText = true; headerBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Header Left Border NPOI.HSSF.UserModel.HSSFCellStyle headerLeftBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerLeftBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; headerLeftBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED; headerLeftBorderStyle.SetFont(boldFont); headerLeftBorderStyle.WrapText = true; headerLeftBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Header Right Border NPOI.HSSF.UserModel.HSSFCellStyle headerRightBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); headerRightBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; headerRightBorderStyle.SetFont(boldFont); headerRightBorderStyle.WrapText = true; headerRightBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Total numeric format NPOI.HSSF.UserModel.HSSFCellStyle totalNumericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); totalNumericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-"); totalNumericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; totalNumericStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; // Grey color NPOI.HSSF.UserModel.HSSFCellStyle grey25Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); grey25Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; grey25Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; grey25Style.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; grey25Style.SetFont(boldFont); // Yellow color NPOI.HSSF.UserModel.HSSFCellStyle yellowStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); yellowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; yellowStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index; yellowStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; yellowStyle.SetFont(boldFont); // Set column width worksheet.SetColumnWidth(0, 12 * 256); worksheet.SetColumnWidth(1, 20 * 256); worksheet.SetColumnWidth(2, 15 * 256); worksheet.SetColumnWidth(3, 15 * 256); worksheet.SetColumnWidth(4, 15 * 256); worksheet.SetColumnWidth(5, 7 * 256); worksheet.SetColumnWidth(6, 15 * 256); worksheet.SetColumnWidth(7, 15 * 256); worksheet.SetColumnWidth(8, 15 * 256); worksheet.SetColumnWidth(9, 15 * 256); worksheet.SetColumnWidth(10, 15 * 256); worksheet.SetColumnWidth(11, 25 * 256); // Set column title NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0); NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue("KTPF Contribution Report"); headerCell.CellStyle = boldStyle; headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(1); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); if (dataSet.ORSOPlan.Rows.Count > 0) { Payroll_KTPFundStatement.ORSOPlanRow m_orsoPlan = (Payroll_KTPFundStatement.ORSOPlanRow)dataSet.ORSOPlan.Rows[0]; headerCell.SetCellValue(m_orsoPlan.ORSOPlanCompanyName); } headerCell.CellStyle = boldStyle; string m_reportPeriod = ""; if (_payPeriodFr.Month != _payPeriodTo.Month) { m_reportPeriod = _payPeriodFr.ToString("dd MMMM yyyy") + " - " + _payPeriodTo.ToString("dd MMMM yyyy"); } else { m_reportPeriod = _payPeriodFr.ToString("MMMM yyyy"); } headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(2); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue(m_reportPeriod); headerCell.CellStyle = boldStyle; // Merge header headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(3); // Merge cell from 5-8 NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 5, (short)8); worksheet.AddMergedRegion(cellRangeAddress); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5); headerCell.SetCellValue("For New Joiner"); headerCell.CellStyle = yellowStyle; ((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); // Merge cell from 9-10 cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 9, (short)10); worksheet.AddMergedRegion(cellRangeAddress); headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9); headerCell.SetCellValue("For Resigned Staff"); headerCell.CellStyle = grey25Style; ((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(4); headerRow.HeightInPoints = 40; // column A headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0); headerCell.SetCellValue("Member ID"); headerCell.CellStyle = headerLeftBorderStyle; // column B headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(1); headerCell.SetCellValue("Employee Name"); headerCell.CellStyle = headerBorderStyle; // column C headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(2); headerCell.SetCellValue("Basic Salary"); headerCell.CellStyle = headerBorderStyle; // column D headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(3); headerCell.SetCellValue("KTPF Contribution"); headerCell.CellStyle = headerBorderStyle; // column E headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(4); headerCell.SetCellValue("Employer MPF Contribution"); headerCell.CellStyle = headerRightBorderStyle; // column F headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5); headerCell.SetCellValue("Sex"); headerCell.CellStyle = headerBorderStyle; // column G headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(6); headerCell.SetCellValue("Date Of Birth"); headerCell.CellStyle = headerBorderStyle; // column H headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(7); headerCell.SetCellValue("Date Join"); headerCell.CellStyle = headerBorderStyle; // column I headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(8); headerCell.SetCellValue("Effective Date"); headerCell.CellStyle = headerRightBorderStyle; // column J headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9); headerCell.SetCellValue("Termination Date"); headerCell.CellStyle = headerBorderStyle; // column K headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(10); headerCell.SetCellValue("Termination Mode"); headerCell.CellStyle = headerRightBorderStyle; // column L headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(11); headerCell.SetCellValue("Remarks"); headerCell.CellStyle = headerRightBorderStyle; // Create total int length = dataSet.ExistingMember.Rows.Count + 5; NPOI.HSSF.UserModel.HSSFRow totalRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + length); NPOI.HSSF.UserModel.HSSFCell totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(0); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(5); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(6); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(7); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(8); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(9); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(10); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(11); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(1); totalCell.SetCellValue("Total"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(2); totalCell.SetCellFormula("SUM(C5:C" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(3); totalCell.SetCellFormula("SUM(D5:D" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(4); totalCell.SetCellFormula("SUM(E5:E" + totalRow.RowNum.ToString("0") + ")"); totalCell.CellStyle = totalNumericStyle; int rowLength = 0; // Set value for every row dataSet.ExistingMember.DefaultView.Sort = "EmpName"; DataTable m_table = dataSet.ExistingMember.DefaultView.ToTable(); foreach (DataRow m_row in m_table.Rows) //foreach (DataRow row in tmpDataTable.Rows) { NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 5); rowLength++; if (lastRowIndex == (m_table.Rows.Count)) { detailRow.RowStyle = bottomBorderStyle; } NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(0); cell.SetCellValue(m_row["MemberID"].ToString()); cell.CellStyle = column0Style; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(1); cell.SetCellValue(m_row["EmpName"].ToString()); cell.CellStyle = stringLeftStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(2); cell.SetCellValue((double)m_row["RelevantIncome"]); cell.CellStyle = numericStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(3); cell.SetCellValue((double)m_row["ER"]); cell.CellStyle = numericStyle; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(4); cell.SetCellValue((double)m_row["MpfMCER"]); cell.CellStyle = column4Style; DateTime m_periodFrom = (DateTime)m_row["PeriodFrom"]; DateTime m_effDate = (DateTime)m_row["OrsoEffDate"]; { cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(5); cell.CellStyle = stringCenterStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { cell.SetCellValue(m_row["EmpSex"].ToString()); } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(6); cell.CellStyle = dateCellStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["EmpDOB"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(7); cell.CellStyle = dateCellStyle; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["EmpDateJoin"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8); cell.CellStyle = column8Style; if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["OrsoEffDate"]); } catch { } } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9); cell.CellStyle = dateCellStyle; cell.SetCellValue("");// put something into the cell and so as the border line can be shown if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month) { try { cell.SetCellValue((DateTime)m_row["LastEmploymentDate"]); } catch { } } cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(10); cell.SetCellValue(m_row["TermCode"].ToString()); cell.CellStyle = column10To11Style; cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11); //cell.SetCellValue(row[FIELD_REMARKS].ToString()); cell.CellStyle = column10To11Style; lastRowIndex++; } System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workbook.Write(file); file.Close(); }
private static int PreencherLinhaTotalizacaoGeral(List <ColunasGridKendo> colunasGrid, NPOI.SS.UserModel.ISheet sheet, ref int indiceLinha, ref List <KeyValuePair <string, object> > listaAggregates) { NPOI.SS.UserModel.IFont font = sheet.Workbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; var rowTotal = sheet.CreateRow(indiceLinha++); if (colunasGrid.Count > 0 && colunasGrid[0].field == null) { colunasGrid.RemoveAt(0); } foreach (var coluna in colunasGrid) { //Lista com os aggregates da coluna no grid List <KeyValuePair <string, object> > listaValoresColuna = listaAggregates .Where(it => it.Key == coluna.field) .ToList(); decimal valorTotalColuna = 0; if (listaValoresColuna.Count > 0) { string tipoAggregate = ((Dictionary <string, object>)listaValoresColuna.FirstOrDefault().Value).FirstOrDefault().Key.ToString(); //Lista de todos os valores das linhas do grid para a coluna atual List <decimal> listaTotal = listaValoresColuna .Select(it => Convert.ToDecimal(((Dictionary <string, object>)it.Value).FirstOrDefault().Value)) .ToList(); switch (tipoAggregate) { case "Sum": valorTotalColuna = listaTotal.Sum(); break; case "Average": valorTotalColuna = listaTotal.Average(); break; case "Min": valorTotalColuna = listaTotal.Min(); break; case "Max": valorTotalColuna = listaTotal.Max(); break; case "Count": valorTotalColuna = listaTotal.Count(); break; default: valorTotalColuna = 0; break; } int indiceColuna = colunasGrid.IndexOf(colunasGrid.Where(it => it.field == coluna.field).FirstOrDefault()); var celula = rowTotal.CreateCell(indiceColuna); if (valorTotalColuna.GetType() == typeof(decimal)) { valorTotalColuna = System.Math.Round((decimal)valorTotalColuna, 2); } celula.SetCellValue(System.Convert.ToDouble(valorTotalColuna)); celula.CellStyle = sheet.Workbook.CreateCellStyle(); celula.CellStyle.SetFont(font); } } return(indiceLinha); }
private static void MontarAgrupamentos(List <ColunasGridKendo> colunasGrid, NPOI.SS.UserModel.ISheet sheet, ref int indiceLinha, IEnumerable grupos, ref List <KeyValuePair <string, object> > listaAggregates) { NPOI.SS.UserModel.IFont font = sheet.Workbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; foreach (Kendo.Mvc.Infrastructure.AggregateFunctionsGroup grupo in grupos) { //Titulo do Grupo var row = sheet.CreateRow(indiceLinha++); var valorChaveGrupo = grupo.Member + ": " + (grupo.Key == null ? String.Empty : grupo.Key).ToString(); row.CreateCell(0).SetCellValue(valorChaveGrupo.ToString()); //Marca o título do grupo com negrito row.Cells[0].CellStyle = sheet.Workbook.CreateCellStyle(); row.Cells[0].CellStyle.SetFont(font); if (colunasGrid.Count > 0 && colunasGrid[0].field == null) { colunasGrid.RemoveAt(0); } if (grupo.HasSubgroups) { //Mapeia os subgrupos recursivamente MontarAgrupamentos(colunasGrid, sheet, ref indiceLinha, grupo.Items, ref listaAggregates); } else { PreencherDadosPlanilha(colunasGrid, grupo.Items, sheet, ref indiceLinha); //Se há linha de totalização do grupo if (grupo.Aggregates.Count > 0) { row = sheet.CreateRow(indiceLinha++); listaAggregates.AddRange(grupo.Aggregates); foreach (var total in grupo.Aggregates) { var valor = ((Dictionary <string, object>)total.Value).FirstOrDefault().Value; if (valor.GetType() == typeof(decimal)) { valor = System.Math.Round((decimal)valor, 2); } int indiceColuna = colunasGrid.IndexOf(colunasGrid.Where(it => it.field == total.Key).FirstOrDefault()); var celula = row.CreateCell(indiceColuna); celula.SetCellValue(Convert.ToDouble(valor)); celula.CellStyle = sheet.Workbook.CreateCellStyle(); celula.CellStyle.SetFont(font); } row = sheet.CreateRow(indiceLinha++); } } } return; }
private void btnExportExcel_Click(object sender, EventArgs e) { if (sfdReporters.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { //输出的Excel路径 string excelFile = sfdReporters.FileName; //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region 设置Excel样式 //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); #endregion #region 生成详细页 //创建一张数据表 DataTable dt = new DataTable(); #region 添加列字段 dt.Columns.Add("领域", typeof(string)); dt.Columns.Add("技术方向代码", typeof(string)); dt.Columns.Add("技术方向名称", typeof(string)); dt.Columns.Add("项目名称", typeof(string)); dt.Columns.Add("密级", typeof(string)); dt.Columns.Add("关键词", typeof(string)); dt.Columns.Add("单位名称", typeof(string)); dt.Columns.Add("单位常用名", typeof(string)); dt.Columns.Add("联系人", typeof(string)); dt.Columns.Add("联系电话", typeof(string)); dt.Columns.Add("项目负责人", typeof(string)); dt.Columns.Add("项目负责人电话", typeof(string)); dt.Columns.Add("身份证号", typeof(string)); dt.Columns.Add("通信地址", typeof(string)); dt.Columns.Add("研究周期", typeof(string)); dt.Columns.Add("研究经费", typeof(string)); dt.Columns.Add("研究目标", typeof(string)); dt.Columns.Add("项目摘要", typeof(string)); dt.Columns.Add("基础性问题", typeof(string)); string[] chsNumbers = new string[] { "一", "二", "三", "四", "五", "六", "七", "八", "九", "十" }; for (int k = 0; k < 10; k++) { dt.Columns.Add("课题" + chsNumbers[k] + "名称", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "密级", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "负责人", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "身份证号", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "承担单位名称", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "研究经费", typeof(string)); } dt.Columns.Add("项目负责人具体情况", typeof(string)); dt.Columns.Add("与有关计划关系", typeof(string)); #endregion //拼凑数据 List <Catalog> nowCatalogs = ConnectionManager.Context.table("Catalog").select("*").getList <Catalog>(new Catalog()); foreach (Catalog catalog in nowCatalogs) { //项目信息 Project mainProj = ConnectionManager.Context.table("Project").where ("CatalogID = '" + catalog.CatalogID + "' and Type='项目'").select("*").getItem <Project>(new Project()); //承担单位 Unit mainUnit = ConnectionManager.Context.table("Unit").where ("ID = '" + mainProj.UnitID + "' and CatalogID = '" + catalog.CatalogID + "'").select("*").getItem <Unit>(new Unit()); //项目负责人 Person mainPerson = ConnectionManager.Context.table("Person").where ("ID in (select PersonID from Task where Type = '项目' and CatalogID = '" + catalog.CatalogID + "') and CatalogID = '" + catalog.CatalogID + "'").select("*").getItem <Person>(new Person()); if (string.IsNullOrEmpty(mainProj.ID) || string.IsNullOrEmpty(mainUnit.ID) || string.IsNullOrEmpty(mainPerson.ID)) { MessageBox.Show("对不起,项目(" + catalog.ProjectName + ")信息不全无法改出!"); //信息不全 continue; } else { //拼装每个项目的信息 List <object> cells = new List <object>(); cells.Add(mainProj.Domain); cells.Add(mainProj.DirectionCode); cells.Add(mainProj.Direction); cells.Add(mainProj.Name); cells.Add(mainProj.SecretLevel); cells.Add(mainProj.Keywords); cells.Add(mainUnit.UnitName); cells.Add(mainUnit.NormalName); cells.Add(mainUnit.ContactName); cells.Add(mainUnit.Telephone); cells.Add(mainPerson.Name); cells.Add(mainPerson.MobilePhone); cells.Add(mainPerson.IDCard); cells.Add(mainPerson.Address); cells.Add(mainProj.TotalTime); cells.Add(mainProj.TotalMoney); cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_4.rtf")); cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_0.rtf")); cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_5.rtf")); //创建课题信息 List <Project> subjectList = ConnectionManager.Context.table("Project").where ("Type='课题' and CatalogID = '" + catalog.CatalogID + "'").select("*").getList <Project>(new Project()); foreach (Project sub in subjectList) { Unit subUnits = ConnectionManager.Context.table("Unit").where ("CatalogID = '" + catalog.CatalogID + "' and ID = '" + sub.UnitID + "'").select("*").getItem <Unit>(new Unit()); Task subTasks = ConnectionManager.Context.table("Task").where ("Role = '负责人' and CatalogID = '" + catalog.CatalogID + "' and ProjectID = '" + sub.ID + "'").select("*").getItem <Task>(new Task()); if (string.IsNullOrEmpty(subUnits.ID) || string.IsNullOrEmpty(subTasks.ID)) { MessageBox.Show("对不起,项目(" + catalog.ProjectName + ")中的课题(" + sub.Name + ")信息不全!"); //信息不全 continue; } else { Person subPersons = ConnectionManager.Context.table("Person").where ("CatalogID = '" + catalog.CatalogID + "' and ID = '" + subTasks.PersonID + "'").select("*").getItem <Person>(new Person()); if (string.IsNullOrEmpty(subPersons.ID)) { MessageBox.Show("对不起,项目(" + catalog.ProjectName + ")中的课题(" + sub.Name + ")信息不全!"); //信息不全 continue; } else { cells.Add(sub.Name); cells.Add(sub.SecretLevel); cells.Add(subPersons.Name); cells.Add(subPersons.IDCard); cells.Add(subUnits.UnitName); cells.Add(subTasks.TotalMoney); } } } //给其它的课题信息项填充空格 for (int kk = 0; kk < (60 - (subjectList.Count * 6)); kk++) { cells.Add(string.Empty); } cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_14.rtf")); cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_19.rtf")); dt.Rows.Add(cells.ToArray()); } } writeSheet(workbook, cellStyleA, cellStyleB, dt); #endregion //输出到Excel文件 workbook.Write(memoryStream); File.WriteAllBytes(excelFile, memoryStream.ToArray()); MessageBox.Show("导出完成!路径:" + excelFile, "提示"); //打开Excel文件 System.Diagnostics.Process.Start(excelFile); } catch (Exception ex) { MessageBox.Show("对不起,导出失败!Ex:" + ex.ToString()); } } }
/// <summary> /// 写错误日志的Excel文件 /// </summary> private string writeErrorExcelFile() { try { //输出的Excel路径 string excelFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "缺少的文件_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"); //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); //输出Sheet表格 //MainForm.writeSheet(workbook, cellStyleA, cellStyleB, errorFileList); //输出到Excel文件 workbook.Write(memoryStream); File.WriteAllBytes(excelFile, memoryStream.ToArray()); if (errorFileList != null && errorFileList.Count >= 1) { MessageBox.Show("缺少文件列表生成完成!路径:" + excelFile); try { System.Diagnostics.Process.Start(excelFile); } catch (Exception ex) { } } return(excelFile); } catch (Exception ex) { MainForm.writeLog(ex.ToString()); return(string.Empty); } }
private void btnExportToExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.FileName = string.Empty; sfd.Filter = "*.xlsx|*.xlsx"; if (sfd.ShowDialog() == DialogResult.OK) { try { //输出的Excel路径 string excelFile = sfd.FileName; //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region 设置Excel样式 //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); #endregion //基本数据 DataTable dtBase = new DataTable(); //金额数据 DataTable dtMoney = new DataTable(); //人员数据 DataTable dtPerson = new DataTable(); #region 输出基本数据 //生成列 dtBase.Columns.Add("项目名称", typeof(string)); dtBase.Columns.Add("项目领域", typeof(string)); dtBase.Columns.Add("合同牵头单位", typeof(string)); dtBase.Columns.Add("项目负责人", typeof(string)); dtBase.Columns.Add("项目负责人电话", typeof(string)); dtBase.Columns.Add("研究周期", typeof(string)); dtBase.Columns.Add("项目总经费", typeof(string)); //dtBase.Columns.Add("研究目标", typeof(string)); //dtBase.Columns.Add("研究内容", typeof(string)); dtBase.Columns.Add("课题名称", typeof(string)); dtBase.Columns.Add("课题单位", typeof(string)); dtBase.Columns.Add("课题负责人", typeof(string)); dtBase.Columns.Add("课题负责人电话", typeof(string)); List <Catalog> catalogList = ConnectionManager.Context.table("Catalog").where ("CatalogType='建议书'").select("*").getList <Catalog>(new Catalog()); //生成内容 foreach (Catalog c in catalogList) { List <object> cells = new List <object>(); //项目信息 Project p = ConnectionManager.Context.table("Project").where ("CatalogID = '" + c.CatalogID + "'").select("*").getItem <Project>(new Project()); //项目名称 cells.Add(p.ProjectName); //项目领域 cells.Add(p.Domains); //项目牵头单位 cells.Add(p.DutyUnit); Person projectMaster = ConnectionManager.Context.table("Person").where ("IsProjectMaster='true' and JobInProject='负责人' and CatalogID = '" + c.CatalogID + "'").select("*").getItem <Person>(new Person()); //项目负责人 cells.Add(projectMaster.PersonName); //项目负责人电话 cells.Add(projectMaster.Telephone); //研究周期 cells.Add(p.TotalTime); //总经费 cells.Add(p.TotalMoney); ////研究目标 //cells.Add(getTxtContent(c, "研究目标")); ////研究内容 //cells.Add(string.Empty); StringBuilder sbSubjectNames = new StringBuilder(); StringBuilder sbSubjectUnits = new StringBuilder(); StringBuilder sbSubjectPersons = new StringBuilder(); StringBuilder sbSubjectPhones = new StringBuilder(); //课题列表 List <Subject> subjectList = ConnectionManager.Context.table("Subject").where ("CatalogID = '" + c.CatalogID + "'").select("*").getList <Subject>(new Subject()); foreach (Subject s in subjectList) { sbSubjectNames.AppendLine(s.SubjectName); sbSubjectUnits.AppendLine(s.DutyUnit); Person subjectMaster = ConnectionManager.Context.table("Person").where ("JobInProject='负责人' and CatalogID = '" + c.CatalogID + "' and SubjectID = '" + s.SubjectID + "'").select("*").getItem <Person>(new Person()); sbSubjectPersons.AppendLine(subjectMaster.PersonName); sbSubjectPhones.AppendLine(subjectMaster.Telephone); } cells.Add(sbSubjectNames.ToString()); cells.Add(sbSubjectUnits.ToString()); cells.Add(sbSubjectPersons.ToString()); cells.Add(sbSubjectPhones.ToString()); dtBase.Rows.Add(cells.ToArray()); } #endregion #region 输出金额数据 //生成列 dtMoney.Columns.Add("项目领域", typeof(string)); dtMoney.Columns.Add("项目名称", typeof(string)); //dtMoney.Columns.Add("合同编号", typeof(string)); dtMoney.Columns.Add("研究周期", typeof(string)); dtMoney.Columns.Add("总经费", typeof(string)); dtMoney.Columns.Add("各年度经费(万元)", typeof(string)); //dtMoney.Columns.Add("各年度拨付时间", typeof(string)); //生成内容 foreach (Catalog c in catalogList) { List <object> cells = new List <object>(); //项目信息 Project p = ConnectionManager.Context.table("Project").where ("CatalogID = '" + c.CatalogID + "'").select("*").getItem <Project>(new Project()); cells.Add(p.Domains); cells.Add(p.ProjectName); //cells.Add(p.ProjectNumber); cells.Add(p.TotalTime); cells.Add(p.TotalMoney); //生成各年度经费和时间字符串 StringBuilder sbMoneyNum = new StringBuilder(); //StringBuilder sbMoneyTime = new StringBuilder(); for (int kkk = 1; kkk <= 5; kkk++) { sbMoneyNum.AppendLine(ConnectionManager.Context.table("Dicts").where ("CatalogID='" + c.CatalogID + "' and DictName='Year" + kkk + "'").select("DictValue").getValue <string>(string.Empty)); //sbMoneyTime.AppendLine(ConnectionManager.Context.table("Dicts").where("CatalogID='" + c.CatalogID + "' and DictName='Year" + kkk + "_SendDate'").select("DictValue").getValue<string>(string.Empty)); } cells.Add(sbMoneyNum.ToString()); //cells.Add(sbMoneyTime.ToString()); dtMoney.Rows.Add(cells.ToArray()); } #endregion #region 输出人员数据 #endregion //写入基本数据 writeSheet(workbook, cellStyleA, cellStyleB, dtBase); //写入金额数据 writeSheet(workbook, cellStyleA, cellStyleB, dtMoney); //写入人员数据 writeSheet(workbook, cellStyleA, cellStyleB, dtPerson); #region 输出文件并打开文件 //输出到流 workbook.Write(memoryStream); //写Excel文件 File.WriteAllBytes(excelFile, memoryStream.ToArray()); //显示提示 MessageBox.Show("导出完成!路径:" + excelFile, "提示"); //打开Excel文件 System.Diagnostics.Process.Start(excelFile); #endregion } catch (Exception ex) { MessageBox.Show("对不起,导出失败!Ex:" + ex.ToString()); } } }
public static bool DataGridview2Sheet(System.Windows.Forms.DataGridView dataGridView1, string tableName) { NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet c = wb.CreateSheet(tableName); List <DataGridViewColumn> ListColumns = new List <DataGridViewColumn>(); foreach (DataGridViewColumn i in dataGridView1.Columns) { if (i.Visible == true) { ListColumns.Add(i); } } if (dataGridView1.Rows.Count <= 0) { return(false); } foreach (DataGridViewColumn dc in ListColumns) { if (dc.Visible == false) { continue; } if (dc.ValueType == typeof(int) || dc.ValueType == typeof(decimal) || dc.ValueType == typeof(double)) { c.SetColumnWidth(dc.Index, 10 * 256); } else { c.SetColumnWidth(dc.Index, 20 * 256); } } #region 表头 NPOI.SS.UserModel.IRow RowHeader = c.CreateRow(0); var FirstCell = RowHeader.CreateCell(0); FirstCell.SetCellValue(BugsBox.Pharmacy.AppClient.Common.AppClientContext.Config.Store.Name + tableName); NPOI.SS.UserModel.ICellStyle cellstyleHeader = wb.CreateCellStyle(); cellstyleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cellstyleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cellstyleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cellstyleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.SS.UserModel.IFont CellFontHeader = wb.CreateFont(); CellFontHeader.FontName = "微软雅黑"; CellFontHeader.FontHeightInPoints = 16; cellstyleHeader.SetFont(CellFontHeader); cellstyleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; FirstCell.CellStyle = cellstyleHeader; c.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, ListColumns.Count - 1)); #endregion #region 标题行 居中并且有框线 NPOI.SS.UserModel.ICellStyle CellStyleTitles = wb.CreateCellStyle(); CellStyleTitles.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleTitles.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleTitles.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleTitles.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleTitles.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.SS.UserModel.IRow RowTitle = c.CreateRow(1); int cindex = 0; foreach (DataGridViewColumn hc in ListColumns) { if (!hc.Visible) { continue; } NPOI.SS.UserModel.ICell cell = RowTitle.CreateCell(cindex); cindex++; cell.CellStyle = CellStyleTitles; if (!string.IsNullOrEmpty(hc.HeaderText)) { cell.SetCellValue(hc.HeaderText); } } #endregion #region 列表 有框线,默认左对齐 NPOI.SS.UserModel.ICellStyle CellStyleLeftAlignmentCell = wb.CreateCellStyle(); CellStyleLeftAlignmentCell.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleLeftAlignmentCell.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleLeftAlignmentCell.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleLeftAlignmentCell.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; foreach (DataGridViewRow i in dataGridView1.Rows) { NPOI.SS.UserModel.IRow row = c.CreateRow(i.Index + 2); cindex = 0; foreach (DataGridViewCell col in i.Cells) { if (!col.Visible) { continue; } NPOI.SS.UserModel.ICell xcell = row.CreateCell(cindex); cindex++; //设置居中对齐,如果是string则左对齐 if (col.ValueType == typeof(string) || col.ValueType == typeof(Guid)) { xcell.CellStyle = CellStyleLeftAlignmentCell;//默认左对齐的风格 } else { xcell.CellStyle = CellStyleTitles;//默认居中对齐的风格,与标题栏一致 } if (col.Value == null) { continue; } if (col.ValueType == typeof(string)) { xcell.SetCellValue(col.Value.ToString()); } else if (col.ValueType == typeof(decimal) || col.ValueType == typeof(int) || col.ValueType == typeof(double)) { xcell.SetCellValue(double.Parse(col.Value.ToString())); } else { xcell.SetCellValue(col.Value.ToString()); } } } #endregion using (SaveFileDialog sfd = new SaveFileDialog()) { sfd.Filter = "Excel电子表格|*.xls"; sfd.FileName = tableName + DateTime.Now.Ticks.ToString(); if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { using (System.IO.FileStream fs = System.IO.File.OpenWrite(sfd.FileName)) { wb.Write(fs); MessageBox.Show("导出成功!"); } } catch (System.IO.IOException ex) { MessageBox.Show("导出失败!\n" + ex.Message); return(false); } } } return(true); }
/// <summary> /// 获取EXCEL数组 /// </summary> /// <param name="dt"></param> public static byte[] GetExcelFileByte(DataSet ds) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //设置样式 NPOI.SS.UserModel.ICellStyle style1 = book.CreateCellStyle(); NPOI.SS.UserModel.IFont font1 = book.CreateFont(); style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; font1.IsBold = true; style1.SetFont(font1); NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle(); style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; byte[] fileByte = null; for (int k = 0; k < ds.Tables.Count; k++) { try { DataTable dt = ds.Tables[k]; if (null != dt && dt.Rows.Count > 0) { if (dt.TableName == null || dt.TableName == "") { dt.TableName = "Sheet1"; } NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName); sheet.DefaultColumnWidth = 20; NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); row.Cells[i].CellStyle = style1; } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); row2.Cells[j].CellStyle = style2; } } } } catch (Exception ex) { throw new Exception(ex.Message); } } try { // 写入到客户端 using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); fileByte = ms.GetBuffer(); //using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) //{ // byte[] data = ms.ToArray(); // fs.Write(data, 0, data.Length); // fs.Flush(); //} } } catch (Exception) { throw; } finally { if (null != book) { book.Close(); book = null; } } return(fileByte); }
/// <summary> /// 导出模板excel /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <param name="P1"></param> /// <param name="P2"></param> /// <param name="UserInfo"></param> public void EXPORTTOEXCEL(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo) { try { List <JH_Auth_ExtendDataB.IMPORTYZ> yz = new List <JH_Auth_ExtendDataB.IMPORTYZ>(); yz = new JH_Auth_ExtendDataB().GetTable(P1, UserInfo.QYinfo.ComId);//获取字段 if (yz.Count > 0) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = BorderStyle.Thin; HeadercellStyle.BorderLeft = BorderStyle.Thin; HeadercellStyle.BorderRight = BorderStyle.Thin; HeadercellStyle.BorderTop = BorderStyle.Thin; HeadercellStyle.Alignment = HorizontalAlignment.Center; HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index; HeadercellStyle.FillPattern = FillPattern.SolidForeground; HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; headerfont.FontHeightInPoints = 12; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (var l in yz) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(l.Name); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); string strDataJson = new JH_Auth_ExtendDataB().GetExcelData(P1); if (strDataJson != "") { string[] strs = strDataJson.Split(','); //建立内容行 int iCellIndex = 0; IRow DataRow = sheet.CreateRow(1); for (int i = 0; i < strs.Length; i++) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(strs[i]); cell.CellStyle = cellStyle; iCellIndex++; } } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); object curContext = object.Current; string strName = string.Empty; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("CRM_" + strName + "_模板文件.xls", Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); curContext.Response.End(); workbook = null; ms.Close(); ms.Dispose(); } } } catch { msg.ErrorMsg = "导入失败!"; } }
/// <summary> /// Sets the font of the entire string. /// </summary> /// <param name="font">The font to use.</param> public void ApplyFont(NPOI.SS.UserModel.IFont font) { ApplyFont(0, _string.CharCount, font); }
private void btnExportYearMoneyToExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.FileName = string.Empty; sfd.Filter = "*.xlsx|*.xlsx"; if (sfd.ShowDialog() == DialogResult.OK) { try { //输出的Excel路径 string excelFile = sfd.FileName; //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region 设置Excel样式 //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); #endregion //基本数据 DataTable dtBase = new DataTable(); #region 输出基本数据 //生成列 dtBase.Columns.Add("合同编号", typeof(string)); dtBase.Columns.Add("合同名称", typeof(string)); dtBase.Columns.Add("承担单位", typeof(string)); dtBase.Columns.Add("项目负责人", typeof(string)); dtBase.Columns.Add("联系电话", typeof(string)); dtBase.Columns.Add("职务职称", typeof(string)); dtBase.Columns.Add("周期", typeof(string)); dtBase.Columns.Add("总经费", typeof(string)); dtBase.Columns.Add("第一年度经费", typeof(string)); dtBase.Columns.Add("第二年度经费", typeof(string)); dtBase.Columns.Add("第三年度经费", typeof(string)); dtBase.Columns.Add("第四年度经费", typeof(string)); dtBase.Columns.Add("第五年度经费", typeof(string)); dtBase.Columns.Add("密级", typeof(string)); dtBase.Columns.Add("起止时间", typeof(string)); dtBase.Columns.Add("单位通信地址", typeof(string)); List <Catalog> catalogList = ConnectionManager.Context.table("Catalog").where ("CatalogType='合同书'").select("*").getList <Catalog>(new Catalog()); //生成内容 foreach (Catalog c in catalogList) { List <object> cells = new List <object>(); //项目信息 Project p = ConnectionManager.Context.table("Project").where ("CatalogID = '" + c.CatalogID + "'").select("*").getItem <Project>(new Project()); //合同编号 cells.Add(p.ProjectNumber); //合同名称 cells.Add(p.ProjectName); //承担单位 cells.Add(p.DutyUnit); Person projectMaster = ConnectionManager.Context.table("Person").where ("IsProjectMaster='true' and JobInProject='负责人' and CatalogID = '" + c.CatalogID + "'").select("*").getItem <Person>(new Person()); //项目负责人 cells.Add(projectMaster.PersonName); //联系电话 cells.Add(projectMaster.Telephone); //职务职称 cells.Add(projectMaster.PersonJob); //周期 cells.Add(p.TotalTime); //总经费 cells.Add(p.TotalMoney); //第一年度经费,第二年度经费,第三年度经费,第四年度经费,第五年度经费 for (int kkk = 1; kkk <= 5; kkk++) { cells.Add(ConnectionManager.Context.table("Dicts").where ("CatalogID='" + c.CatalogID + "' and DictName='Year" + kkk + "'").select("DictValue").getValue <string>(string.Empty)); } //密级 cells.Add(p.SecretLevel); //起止时间(拨付时间) List <DateTime> dtList = new List <DateTime>(); DataList dlMoneySendList = ConnectionManager.Context.table("MoneySends").where ("CatalogID='" + c.CatalogID + "'").orderBy("WillTime").select("WillTime").getDataList(); if (dlMoneySendList.getRowCount() >= 1) { foreach (DataItem diiii in dlMoneySendList.getRows()) { DateTime willTime = diiii.get("WillTime") != null?DateTime.Parse(diiii.get("WillTime").ToString()) : DateTime.Now; dtList.Add(willTime); } } if (dtList.Count >= 2) { cells.Add(dtList[0].ToString("yyyy年MM月dd日") + "~" + dtList[dtList.Count - 1].ToString("yyyy年MM月dd日")); } else if (dtList.Count == 1) { cells.Add(dtList[0].ToString("yyyy年MM月dd日") + "~"); } else { cells.Add(""); } //单位通信地址 cells.Add(p.DutyUnitAddress); dtBase.Rows.Add(cells.ToArray()); } #endregion //写入基本数据 writeSheet(workbook, cellStyleA, cellStyleB, dtBase); #region 输出文件并打开文件 //输出到流 workbook.Write(memoryStream); //写Excel文件 File.WriteAllBytes(excelFile, memoryStream.ToArray()); //显示提示 MessageBox.Show("导出完成!路径:" + excelFile, "提示"); //打开Excel文件 System.Diagnostics.Process.Start(excelFile); #endregion } catch (Exception ex) { MessageBox.Show("对不起,导出失败!Ex:" + ex.ToString()); } } }