/// <summary> /// 利用office组件导出Excel表格文件 /// </summary> //public static void ExportExcelFile(DataTable ExcelTable) //{ // try // { // string SaveExcelName = string.Empty;//保存的Excel文件名称 // SaveFileDialog SFDialog = new SaveFileDialog(); // SFDialog.DefaultExt = "xls"; // SFDialog.Filter = "Excel文件(*.xls)|*.xls"; // SFDialog.ShowDialog(); // SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称 // if (SaveExcelName.IndexOf(":") < 0) return; // Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序 // object missing = System.Reflection.Missing.Value; // if (XlsApp == null) // { // MessageBoxEx.Show("无法创建Excel表格文件,您的电脑可能未安装Excel软件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); // return; // } // else // { // Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像 // Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板 // Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1; // Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格 // int TotalCount = ExcelTable.Rows.Count; // //int rowRead = 0;//读取行数 // //float PercentRead = 0;//导入百分比 // //写入字段名 // for (int i = 0; i < ExcelTable.Columns.Count; i++) // { // WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称 // Ran = (Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1, i + 1];//列名称写入单元格 // Ran.Interior.ColorIndex = 15; // Ran.Font.Bold = true;//标题加粗 // } // //ProgressBarMsg ProgBarMsg = new ProgressBarMsg(); // //ProgBarMsg.MaxNum = TotalCount;//获取总记录行项 // //ProgBarMsg.ShowDialog();//显示进度条 // //写字段值 // for (int j = 0; j < ExcelTable.Rows.Count; j++) // { // for (int k = 0; k < ExcelTable.Columns.Count; k++) // { // WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值 // } // //rowRead++; // //PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比 // //ProgressBarMsg.PercentMsg = rowRead; // //Thread.Sleep(200); // Application.DoEvents();//处理当前在消息队列中所有windows消息 // } // WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing, missing); // Ran = WkSheet.get_Range((object)WkSheet.Cells[2, 1], (object)WkSheet.Cells[ExcelTable.Rows.Count + 1, ExcelTable.Columns.Count]);//给工作表指定区域 // //设置Excel表格边框样式 // Ran.BorderAround2(missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, // Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, missing, missing); // Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色 // Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框 // Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度 // if (ExcelTable.Columns.Count > 1) // {//设置垂直表格颜色索引 // Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic; // } // WkBk.Save();//保存Excel表数据 // //关闭表格对像,并退出应用程序域 // WkBk.Close(missing, missing, missing); // XlsApp.Quit(); // XlsApp = null; // GC.Collect();//强制关闭 // MessageBoxEx.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); // } // } // catch (Exception ex) // { // MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); // } //} /// <summary> /// 设置导出Excel标题样式 /// </summary> /// <param name="HeaderRow">标题行</param> /// <param name="WkBk">工作表</param> /// <param name="ColNum">列序号</param> private static void SetXlsHeaderStyle(HSSFRow HeaderRow, HSSFWorkbook WkBk, int ColNum) { try { HSSFCellStyle HeaderStyle = (HSSFCellStyle)WkBk.CreateCellStyle(); //创建Excel表格标题样式 HSSFFont fontStyle = (HSSFFont)WkBk.CreateFont(); //创建Excel表格字体样式 HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //设置居中 HeaderStyle.FillForegroundColor = HSSFColor.Lime.Index; //设置背景色 HeaderStyle.FillPattern = FillPattern.SolidForeground; //设置填充样式 HeaderStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //设置数据格式 fontStyle.FontHeightInPoints = 11; fontStyle.Boldweight = 700; HeaderStyle.SetFont(fontStyle); HeaderRow.GetCell(ColNum).CellStyle = (ICellStyle)HeaderStyle;//设置列样式 } catch (Exception ex) { MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); } }
/// <summary> /// 导出Excel包含单元格样式的方法 /// 注:要求在需要有样式的单元格加上 @?@,字体颜色变红(特用) /// </summary> /// <param name="dataset"></param> /// <param name="fileName"></param> public static void ExportExcelFileContainCss(DataSet dataset, string fileName) { InitializeWorkbook(); foreach (DataTable dt in dataset.Tables) { HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet(dt.TableName); //设置标题行样式 HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle(); HSSFFont font = (HSSFFont)hssfworkbook.CreateFont(); font.Color = 10;//代表红色 style.SetFont(font); HSSFRow row; HSSFCell cell; HSSFRow rowCap; //首先设置表列名称 rowCap = (HSSFRow)sheet1.CreateRow(0); for (int j = 0; j < dt.Columns.Count; j++) { cell = (HSSFCell)rowCap.CreateCell(j); cell.SetCellValue(dt.Columns[j].Caption); } for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; row = (HSSFRow)sheet1.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { cell = (HSSFCell)row.CreateCell(j); if (dr[j].ToString().Contains("@?@")) { dr[j] = dr[j].ToString().Replace("@?@", ""); cell.CellStyle = style; } cell.SetCellValue(dr[j].ToString()); } } } SaveToFile(fileName); }
/// <summary> /// DataTable转Excel 读取DataTable Columns做为表头 /// </summary> /// <param name="dt">DataTable</param> /// <returns></returns> public static byte[] TableToExcel(DataTable dt) { IWorkbook wk = new HSSFWorkbook(); ISheet sheet = wk.CreateSheet(); IRow headerRow = sheet.CreateRow(0); // 表头 // 处理标题 foreach (DataColumn column in dt.Columns) { ICell cell = headerRow.CreateCell(column.Ordinal); cell.SetCellValue(column.ColumnName); // 设置样式 加粗居中 HSSFCellStyle cellStyle = (HSSFCellStyle)wk.CreateCellStyle(); HSSFFont font = (HSSFFont)wk.CreateFont(); font.Boldweight = 600; cellStyle.SetFont(font); cellStyle.Alignment = HorizontalAlignment.Center; cell.CellStyle = cellStyle; } // 处理body部分 int rowIndex = 1; foreach (DataRow row in dt.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column.ColumnName].ToString()); } rowIndex++; } // 冻结首行 sheet.CreateFreezePane(0, 1); MemoryStream ms = new MemoryStream(); wk.Write(ms); sheet = null; headerRow = null; return(ms.GetBuffer()); }
/// <summary> /// 自定义单元格样式 /// </summary> /// <returns></returns> public HSSFCellStyle CreateStyle(HSSFWorkbook hssfworkbook, short fontHeight = 13 * 14, BorderStyle borderTop = BorderStyle.None, BorderStyle borderBottom = BorderStyle.None, BorderStyle borderLeft = BorderStyle.None, BorderStyle borderRight = BorderStyle.None, HorizontalAlignment alignment = HorizontalAlignment.Center, VerticalAlignment verticalAlignment = VerticalAlignment.Center, bool wrapText = false, string fontName = "新宋体", FontBoldWeight boldweight = FontBoldWeight.Normal ) { HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle(); HSSFFont font = (HSSFFont)hssfworkbook.CreateFont(); font.FontName = fontName; font.FontHeight = fontHeight; //设置字体大小 font.Boldweight = (short)boldweight; style.SetFont(font); //样式中加载字体样式 style.Alignment = alignment; //水平居中 style.VerticalAlignment = verticalAlignment; //居中 style.BorderBottom = borderBottom; //设置单元格边框 style.BorderRight = borderRight; style.BorderLeft = borderLeft; style.BorderTop = borderTop; style.WrapText = wrapText; return(style); }
/// <summary> /// 获取通用列样式 /// </summary> /// <param name="hs"></param> /// <returns></returns> public static ICellStyle GetCommonStyle(HSSFWorkbook hs) { //样式 ICellStyle commonCellStyle = hs.CreateCellStyle(); //创建样式 commonCellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 //commonCellStyle.Alignment = HorizontalAlignment.CenterSelection;//设置居中 commonCellStyle.Alignment = HorizontalAlignment.Center; //设置居中 HSSFFont commonFont = (HSSFFont)hs.CreateFont(); //创建字体 commonFont.FontName = "宋体"; commonFont.FontHeight = 10; commonFont.FontHeightInPoints = 10; commonCellStyle.SetFont(commonFont); commonCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下边框线 commonCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左边框线 commonCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右边框线 commonCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上边框线 return(commonCellStyle); }
private void GenerateHeader(HSSFSheet sheet, HSSFWorkbook workbook, int startRow, int cellIndex) { FontRecord rec = new FontRecord() { FontHeight = 16 }; rec.BoldWeight = short.MaxValue; HSSFFont font = new HSSFFont(10, rec); //font.Index = 16; var style = workbook.CreateCellStyle(); SetCellStyle(style); style.SetFont(font); foreach (var header in _headers) { HSSFRow row = sheet.CreateRow(startRow); var cell = row.CreateCell(cellIndex++); cell.SetCellValue(Resource.GetString(header)); cell.CellStyle = style; } }
/// <summary> /// 获取头部样式 /// </summary> /// <param name="hs"></param> /// <returns></returns> public static ICellStyle GetHeaderStyle(HSSFWorkbook hs) { //样式 ICellStyle headerStyle = hs.CreateCellStyle(); //创建样式 headerStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 方法1 //style.Alignment = HorizontalAlignment.CenterSelection;//设置居中 方法2 headerStyle.Alignment = HorizontalAlignment.Center; //设置居中 方法3 HSSFFont headerfont = (HSSFFont)hs.CreateFont(); //创建字体 headerfont.Color = HSSFColor.Black.Index; //给字体设置颜色 headerfont.FontName = "宋体"; headerfont.IsBold = true; headerfont.FontHeight = 11; headerfont.FontHeightInPoints = 11; headerStyle.SetFont(headerfont); headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下边框线 headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左边框线 headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右边框线 headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上边框线 return(headerStyle); }
static HSSFCellStyle GetColumnStyle(string colHeadFont, short colHeadSize, short colHeadColor, bool colHeadBorder , HSSFCellStyle cellStyle, HSSFFont font) { cellStyle = workbook.CreateCellStyle() as HSSFCellStyle; cellStyle.Alignment = HorizontalAlignment.CENTER; //居中 if (colHeadBorder == true) { //边框 cellStyle.BorderBottom = BorderStyle.THIN; cellStyle.BorderLeft = BorderStyle.THIN; cellStyle.BorderRight = BorderStyle.THIN; cellStyle.BorderTop = BorderStyle.THIN; } //font font = workbook.CreateFont() as HSSFFont; font.FontName = colHeadFont; font.FontHeightInPoints = colHeadSize; font.Color = colHeadColor; font.Boldweight = 700; cellStyle.SetFont(font); return(cellStyle); }
/// <summary> /// 设置单元格样式 /// </summary> /// <param name="workbook">excel表格</param> /// <param name="horizontal">水平布局</param> /// <param name="vertical">垂直布局</param> /// <param name="boldWeight">字体加粗</param> /// <param name="fontPoint">字体大小</param> /// <param name="isBorder">是否需要边框</param> /// <param name="fontName">字体名称:宋体、黑体、微软雅黑,默认微软雅黑</param> /// <returns>返回单元格样式</returns> public static HSSFCellStyle CreateStyle(HSSFWorkbook workbook, HorizontalAlignment horizontal, VerticalAlignment vertical, short boldWeight, short fontPoint, bool isBorder, string fontName = "微软雅黑") { HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); cellStyle.Alignment = horizontal; cellStyle.VerticalAlignment = vertical; if (isBorder) { cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; } //创建字体 HSSFFont cellStyleFont = (HSSFFont)workbook.CreateFont(); //字体加粗 cellStyleFont.Boldweight = boldWeight; cellStyleFont.FontHeightInPoints = fontPoint; cellStyleFont.FontName = fontName; cellStyle.SetFont(cellStyleFont); return(cellStyle); }
/// <summary> /// 表头的样色设置 /// </summary> /// <param name="hssfworkbookDown"></param> /// <returns></returns> private static ICellStyle TopRow(HSSFWorkbook hssfworkbookDown) { ///字体 HSSFFont fontS9 = (HSSFFont)hssfworkbookDown.CreateFont(); fontS9.FontName = "黑体"; fontS9.FontHeightInPoints = 14; fontS9.Boldweight = (short)FontBoldWeight.Normal; //样式 ICellStyle table0 = hssfworkbookDown.CreateCellStyle(); table0.BorderLeft = BorderStyle.Thin; table0.BorderTop = BorderStyle.Thin; table0.BorderBottom = BorderStyle.Thin; table0.BorderRight = BorderStyle.Thin; table0.WrapText = true; table0.FillForegroundColor = 47; table0.FillPattern = FillPattern.SolidForeground; table0.VerticalAlignment = VerticalAlignment.Center; //垂直对齐 table0.Alignment = HorizontalAlignment.Center; //水平对齐 table0.SetFont(fontS9); return(table0); }
/// <summary> /// 设置数据格式 /// </summary> protected virtual void SetDataCellStyle() { //设置窗格的格式 mDateStyle = mWorkbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = mWorkbook.CreateDataFormat() as HSSFDataFormat; mDateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //表头及样式 mTableHeadStyle = mWorkbook.CreateCellStyle() as HSSFCellStyle; mTableHeadStyle.Alignment = HorizontalAlignment.CENTER; HSSFFont font = mWorkbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; mTableHeadStyle.SetFont(font); //列头及样式 mColumnHeadStyle = mWorkbook.CreateCellStyle() as HSSFCellStyle; mColumnHeadStyle.Alignment = HorizontalAlignment.CENTER; font = mWorkbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; mColumnHeadStyle.SetFont(font); }
private HSSFCellStyle PhaseCellStyle(CellStyle paramCellStyle) { HSSFCellStyle style = null; //try //{ style = (HSSFCellStyle)_workbook.CreateCellStyle(); HSSFFont font = (HSSFFont)_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; HSSFDataFormat format = (HSSFDataFormat)_workbook.GetCreationHelper().CreateDataFormat(); short index = format.GetFormat("@"); style.DataFormat = index; #endregion //} //catch(Exception ex) //{ // string ss = ex.Message; // return null; //} return(style); }
/// <summary> /// 将List转换成excel /// </summary> /// <typeparam name="T">泛型类型</typeparam> /// <param name="list">数据集合</param> /// <param name="tableHeaders">表头 k--属性 v--属性显示名称</param> /// <param name="sheet">sheet名称</param> /// <returns>文件流</returns> public static byte[] ExportDataListToExcel <T>(List <T> list, List <ExcelTitle> tableHeaders, string sheetPrefix) where T : class, new() { HSSFWorkbook hssWorkBook = new HSSFWorkbook(); // 创建工作薄 int sheetNums = (int)Math.Ceiling(list.Count / 50000.00); ICellStyle cellStyle = hssWorkBook.CreateCellStyle(); Dictionary <string, ICellStyle> dataFormatMap = new Dictionary <string, ICellStyle>(); for (int i = 0; i < sheetNums; i++) { ISheet sheet = hssWorkBook.CreateSheet(sheetPrefix + (i + 1)); // 创建sheet表 // 创建表头 IRow rowHead = sheet.CreateRow(0); int rowId = 0; foreach (var title in tableHeaders) { ICell cell = rowHead.CreateCell(rowId); cell.SetCellValue(title.Title); // 设置样式 加粗居中 HSSFFont font = (HSSFFont)hssWorkBook.CreateFont(); font.Boldweight = 600; cellStyle.SetFont(font); cellStyle.Alignment = HorizontalAlignment.Center; cell.CellStyle = cellStyle; sheet.AutoSizeColumn(rowId); rowId++; } rowId = 1; // 创建数据行 PropertyInfo[] properties = typeof(T).GetProperties(); // 计算每个sheet中记录数 var pagedList = list.Skip(i * 50000).Take(50000).ToList(); foreach (T item in pagedList) { IRow rowBody = sheet.CreateRow(rowId); int cellIndex = 0; foreach (var title in tableHeaders) { foreach (PropertyInfo property in properties) { if (string.Equals(property.Name, title.PropertyName, StringComparison.CurrentCultureIgnoreCase)) { ICell cell = rowBody.CreateCell(cellIndex); FillCellValue(cell, property.GetValue(item, null)); if (!string.IsNullOrWhiteSpace(title.DataFormat)) { if (dataFormatMap.ContainsKey(title.DataFormat)) { SetCellDataFormat(dataFormatMap[title.DataFormat], cell, title.DataFormat); } else { ICellStyle cellFormat = hssWorkBook.CreateCellStyle(); dataFormatMap.Add(title.DataFormat, cellFormat); SetCellDataFormat(cellFormat, cell, title.DataFormat); } } break; } } cellIndex++; } rowId++; } // 冻结首行 sheet.CreateFreezePane(0, 1); } MemoryStream file = new MemoryStream(); hssWorkBook.Write(file); return(file.GetBuffer()); }
/// <summary> /// list转Excel /// </summary> /// <typeparam name="T">传入list 类型 </typeparam> /// <param name="SheetName">sheet名称</param> /// <param name="list">list集合</param> /// <param name="FiedNames">名称序列</param> /// <returns></returns> public static HSSFWorkbook ListtoExcel <T>(string SheetName, List <T> list, Dictionary <string, string> FiedNames) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(SheetName); //创建工作表 sheet.CreateFreezePane(0, 1); //冻结列头行 HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行 row_Title.HeightInPoints = 30.5F; //设置列头行高 HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式 cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体 cs_Title_Font.IsBold = true; //字体加粗 cs_Title_Font.FontHeightInPoints = 14; //字体大小 cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式 #region 生成列头 int ii = 0; foreach (string key in FiedNames.Keys) { HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(ii); //创建单元格 cell_Title.CellStyle = cs_Title; //将样式绑定到单元格 cell_Title.SetCellValue(key); sheet.SetColumnWidth(ii, 25 * 256); //设置列宽 ii++; } #endregion //获取 实体类 类型对象 Type t = typeof(T); // model.GetType(); //获取 实体类 所有的 公有属性 List <PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList(); //创建 实体属性 字典集合 Dictionary <string, PropertyInfo> dictPros = new Dictionary <string, PropertyInfo>(); //将 实体属性 中要修改的属性名 添加到 字典集合中 键:属性名 值:属性对象 proInfos.ForEach(p => { if (FiedNames.Values.Contains(p.Name)) { dictPros.Add(p.Name, p); } }); HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式 cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 for (int i = 0; i < list.Count; i++) { HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行 row_Content.HeightInPoints = 20; int jj = 0; foreach (string proName in FiedNames.Values) { if (dictPros.ContainsKey(proName)) { HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(jj); //创建单元格 cell_Conent.CellStyle = cs_Content; //如果存在,则取出要属性对象 PropertyInfo proInfo = dictPros[proName]; //获取对应属性的值 object value = proInfo.GetValue(list[i], null); //object newValue = model.uName; string cell_value = value == null ? "" : value.ToString(); cell_Conent.SetCellValue(cell_value); jj++; } } } return(wb); }
public void TestOptimiseStyles() { HSSFWorkbook wb = new HSSFWorkbook(); // Two fonts Assert.AreEqual(4, wb.NumberOfFonts); IFont f1 = wb.CreateFont(); f1.FontHeight = ((short)11); f1.FontName = ("Testing"); IFont f2 = wb.CreateFont(); f2.FontHeight = ((short)22); f2.FontName = ("Also Testing"); Assert.AreEqual(6, wb.NumberOfFonts); // Several styles Assert.AreEqual(21, wb.NumCellStyles); NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle(); cs1.SetFont(f1); NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle(); cs2.SetFont(f2); NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle(); cs3.SetFont(f1); NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle(); cs4.SetFont(f1); cs4.Alignment = HorizontalAlignment.CenterSelection;// ((short)22); NPOI.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle(); cs5.SetFont(f2); cs5.Alignment = HorizontalAlignment.Fill; //((short)111); NPOI.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle(); cs6.SetFont(f2); Assert.AreEqual(27, wb.NumCellStyles); // Use them NPOI.SS.UserModel.ISheet s = wb.CreateSheet(); IRow r = s.CreateRow(0); r.CreateCell(0).CellStyle = (cs1); r.CreateCell(1).CellStyle = (cs2); r.CreateCell(2).CellStyle = (cs3); r.CreateCell(3).CellStyle = (cs4); r.CreateCell(4).CellStyle = (cs5); r.CreateCell(5).CellStyle = (cs6); r.CreateCell(6).CellStyle = (cs1); r.CreateCell(7).CellStyle = (cs2); Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex); Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex); Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex); // Optimise HSSFOptimiser.OptimiseCellStyles(wb); // Check Assert.AreEqual(6, wb.NumberOfFonts); Assert.AreEqual(25, wb.NumCellStyles); // cs1 -> 21 Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex); // cs2 -> 22 Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex); Assert.AreEqual(22, r.GetCell(1).CellStyle.GetFont(wb).FontHeight); // cs3 = cs1 -> 21 Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex); // cs4 --> 24 -> 23 Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex); // cs5 --> 25 -> 24 Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex); // cs6 = cs2 -> 22 Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex); // cs1 -> 21 Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex); // cs2 -> 22 Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex); // Add a new duplicate, and two that aren't used HSSFCellStyle csD = (HSSFCellStyle)wb.CreateCellStyle(); csD.SetFont(f1); r.CreateCell(8).CellStyle = (csD); HSSFFont f3 = (HSSFFont)wb.CreateFont(); f3.FontHeight = ((short)23); f3.FontName = ("Testing 3"); HSSFFont f4 = (HSSFFont)wb.CreateFont(); f4.FontHeight = ((short)24); f4.FontName = ("Testing 4"); HSSFCellStyle csU1 = (HSSFCellStyle)wb.CreateCellStyle(); csU1.SetFont(f3); HSSFCellStyle csU2 = (HSSFCellStyle)wb.CreateCellStyle(); csU2.SetFont(f4); // Check before the optimise Assert.AreEqual(8, wb.NumberOfFonts); Assert.AreEqual(28, wb.NumCellStyles); // Optimise, should remove the two un-used ones and the one duplicate HSSFOptimiser.OptimiseCellStyles(wb); // Check Assert.AreEqual(8, wb.NumberOfFonts); Assert.AreEqual(25, wb.NumCellStyles); // csD -> cs1 -> 21 Assert.AreEqual(21, ((HSSFCell)r.GetCell(8)).CellValueRecord.XFIndex); }
/// <summary> /// 使用模版导出EXCEL /// </summary> /// <param name="dt1"></param> /// <param name="dt2"></param> /// <param name="excelTemplatePath"></param> /// <param name="sheetName1"></param> /// <param name="sheetName2"></param> /// <returns></returns> public static MemoryStream ExportFromTemplate(DataTable dt1, DataTable dt2 , string excelTemplatePath , string sheetName1, string sheetName2) { THOK.Common.NPOI.Common.ExportExcelHelper.BrowserLoad(sheetName1); FileStream file = new FileStream(excelTemplatePath, FileMode.Open, FileAccess.Read);//读入excel模板 HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet(sheetName1); HSSFSheet sheet2 = (HSSFSheet)hssfworkbook.GetSheet(sheetName2); sheet1.PrintSetup.FitHeight = 0; sheet2.PrintSetup.FitHeight = 0; string exportDate = "导出时间:" + System.DateTime.Now.ToString("yyyy-MM-dd"); sheet1.GetRow(0).GetCell(0).SetCellValue(sheetName1); sheet1.GetRow(1).GetCell(0).SetCellValue(exportDate); sheet2.GetRow(0).GetCell(0).SetCellValue(sheetName2); sheet2.GetRow(1).GetCell(0).SetCellValue(exportDate); #region 取得列宽 表一 int[] arrColWidth = new int[dt1.Columns.Count]; foreach (DataColumn item in dt1.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;//936是指GB2312编码 } for (int i = 0; i < dt1.Rows.Count; i++) { for (int j = 0; j < dt1.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt1.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } #endregion #region 取得列宽 表二 int[] arrColWidth2 = new int[0]; if (dt2 != null && sheetName2 != null) { arrColWidth2 = new int[dt2.Columns.Count]; foreach (DataColumn item in dt2.Columns) { arrColWidth2[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dt2.Rows.Count; i++) { for (int j = 0; j < dt2.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt2.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth2[j]) { arrColWidth2[j] = intTemp; } } } } #endregion #region dt1 int rowIndex1 = 2, colIndex1 = 0; foreach (DataRow row in dt1.Rows) { rowIndex1++; colIndex1 = 0; HSSFRow xlsRow = sheet1.CreateRow(rowIndex1) as HSSFRow; HSSFCellStyle contentStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle; foreach (DataColumn column in dt1.Columns) { HSSFCell newCell = xlsRow.CreateCell(column.Ordinal) as HSSFCell; contentStyle.BorderBottom = BorderStyle.THIN; contentStyle.BorderLeft = BorderStyle.THIN; contentStyle.BorderRight = BorderStyle.THIN; contentStyle.BorderTop = BorderStyle.THIN; xlsRow.GetCell(column.Ordinal).CellStyle = contentStyle; FillContent(row, column, xlsRow, newCell); sheet1.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth[column.Ordinal] + 0.5) * 256)); colIndex1++; } } #endregion #region dt2 int rowIndex2 = 2, colIndex2 = 0; foreach (DataRow row in dt2.Rows) { rowIndex2++; colIndex2 = 0; HSSFRow xlsRow = sheet2.CreateRow(rowIndex2) as HSSFRow; HSSFCellStyle contentStyle1 = hssfworkbook.CreateCellStyle() as HSSFCellStyle; HSSFCellStyle contentStyle2 = hssfworkbook.CreateCellStyle() as HSSFCellStyle; HSSFFont font = hssfworkbook.CreateFont() as HSSFFont; foreach (DataColumn column in dt2.Columns) { HSSFCell newCell = xlsRow.CreateCell(column.Ordinal) as HSSFCell; if (column.Ordinal == 5 || column.Ordinal == 7 || column.Ordinal == 10) { contentStyle2.BorderBottom = BorderStyle.THIN; contentStyle2.BorderLeft = BorderStyle.THIN; contentStyle2.BorderRight = BorderStyle.THIN; contentStyle2.BorderTop = BorderStyle.THIN; font.Color = HSSFColor.RED.index; contentStyle2.SetFont(font); xlsRow.GetCell(column.Ordinal).CellStyle = contentStyle2; } else { contentStyle1.BorderBottom = BorderStyle.THIN; contentStyle1.BorderLeft = BorderStyle.THIN; contentStyle1.BorderRight = BorderStyle.THIN; contentStyle1.BorderTop = BorderStyle.THIN; xlsRow.GetCell(column.Ordinal).CellStyle = contentStyle1; } FillContent(row, column, xlsRow, newCell); sheet2.SetColumnWidth(column.Ordinal, Convert.ToInt32((arrColWidth2[column.Ordinal] + 0.5) * 256)); colIndex2++; } } #endregion MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); ms.Flush(); ms.Position = 0; file.Close(); return(ms); }
void OnGUI() { GUIStyle style = new GUIStyle(); GUI.Label(new Rect(10, 10, 100, 30), "Sheet Name:"); MySheetName = GUI.TextField(new Rect(90, 10, 200, 30), MySheetName, 25); style.richText = true; FileStream MyAddress = new FileStream(Application.dataPath + "1.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite); if (GUI.Button(new Rect(300, 10, 200, 30), "Create Excel Files With " + "<color=red>NPOI</color>")) { HSSFWorkbook MyWorkbook = new HSSFWorkbook(); HSSFSheet Sheet01 = (HSSFSheet)MyWorkbook.CreateSheet(MySheetName); for (int i = 0; i < 5; i++) { HSSFRow Row = (HSSFRow)Sheet01.CreateRow((short)i); HSSFCell cell = (HSSFCell)Row.CreateCell((short)0); cell.SetCellValue(MyCellArray[i]); if (i < MyCellArray02.Count) { HSSFCell cell02 = (HSSFCell)Row.CreateCell((short)1); cell02.SetCellValue(MyCellArray02[i]); } else { HSSFCell cell02 = (HSSFCell)Row.CreateCell((short)1); cell02.SetCellValue(""); } Row.RowStyle = MyWorkbook.CreateCellStyle(); Row.RowStyle.BorderBottom = BorderStyle.Double; cell.CellStyle = MyWorkbook.CreateCellStyle(); cell.CellStyle.BorderRight = BorderStyle.Thin; cell.CellStyle.BorderBottom = BorderStyle.Dashed; cell.CellStyle.BottomBorderColor = HSSFColor.Red.Index; HSSFFont MyFont = (HSSFFont)MyWorkbook.CreateFont(); MyFont.FontName = "Tahoma"; MyFont.FontHeightInPoints = 14; MyFont.Color = HSSFColor.Gold.Index; MyFont.Boldweight = (short)FontBoldWeight.Bold; cell.CellStyle.SetFont(MyFont); //PrefabUtility.CreateEmptyPrefab("aq"); } MyWorkbook.Write(MyAddress); MyWorkbook.Close(); } if (GUI.Button(new Rect(60, 120, 200, 30), "Reading Excel Files With " + "<color=yellow>NPOI</color>")) { ReadExcelEnable_NPOI = true; ReadExcelEnable_ExcelDataReader = false; MyString.Clear(); } if (ReadExcelEnable_NPOI == true) { HSSFWorkbook MyBook; using (FileStream MyAddress_Read = new FileStream(Application.dataPath + "/1.xls", FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { MyBook = new HSSFWorkbook(MyAddress_Read); } ISheet Sheet_Read = MyBook.GetSheet(MySheetName); for (int row = 0; row <= Sheet_Read.LastRowNum; row++) { IRow Row_Read = Sheet_Read.GetRow(row); for (int cells = 0; cells < Row_Read.LastCellNum; cells++) { GUI.Label(new Rect(400 + cells * 200, 100 + row * 40, 200, 20), Row_Read.GetCell(cells).ToString()); //Arabic And Persian Support //GUI.Label(new Rect(400 + cells * 200,100+ row * 40, 200, 20),ArabicFixer.Fix(Row_Read.GetCell(cells).ToString())); } } } if (GUI.Button(new Rect(10, 160, 300, 30), "Reading Excel Files With " + "<color=yellow>ExcelDataReader</color>")) { ReadExcelEnable_ExcelDataReader = true; ReadExcelEnable_NPOI = false; MyString.Clear(); } if (ReadExcelEnable_ExcelDataReader == true) { IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(MyAddress); DataSet Result = excelReader.AsDataSet(); excelReader.IsFirstRowAsColumnNames = true; int NumberOfRows = Result.Tables[0].Rows.Count; int NumberOfCols = Result.Tables[0].Columns.Count; for (int i = 0; i < NumberOfRows; i++) { for (int j = 0; j < NumberOfCols; j++) { MyString.Add(Result.Tables[0].Rows[i][j].ToString()); } } ReadExcelEnable_ExcelDataReader = false; } for (int b = 0; b < MyString.Count; b++) { GUI.Label(new Rect(400, 100 + 20 * b, 200, 20), MyString[b]); //Arabic And Persian Support //GUI.Label(new Rect(400, 100 + 20 * b, 200, 20), ArabicFixer.Fix(MyString[b])); } #if UNITY_EDITOR AssetDatabase.Refresh(); #endif }
public void Report(string FileName) { int pos = 0; //Export to excel HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ////create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; #region Create fonts and styles HSSFFont HeaderF1 = hssfworkbook.CreateFont(); HeaderF1.FontHeightInPoints = 11; HeaderF1.Boldweight = 11 * 256; HeaderF1.FontName = "Calibri"; HSSFFont HeaderF2 = hssfworkbook.CreateFont(); HeaderF2.FontHeightInPoints = 10; HeaderF2.Boldweight = 10 * 256; HeaderF2.FontName = "Calibri"; HSSFFont HeaderF3 = hssfworkbook.CreateFont(); HeaderF3.FontHeightInPoints = 9; HeaderF3.Boldweight = 9 * 256; HeaderF3.FontName = "Calibri"; HSSFFont SimpleF = hssfworkbook.CreateFont(); SimpleF.FontHeightInPoints = 10; SimpleF.FontName = "Calibri"; HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle(); SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN; SimpleCS.BottomBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN; SimpleCS.LeftBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN; SimpleCS.RightBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN; SimpleCS.TopBorderColor = HSSFColor.BLACK.index; SimpleCS.SetFont(SimpleF); HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle(); CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00"); CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN; CountCS.BottomBorderColor = HSSFColor.BLACK.index; CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN; CountCS.LeftBorderColor = HSSFColor.BLACK.index; CountCS.BorderRight = HSSFCellStyle.BORDER_THIN; CountCS.RightBorderColor = HSSFColor.BLACK.index; CountCS.BorderTop = HSSFCellStyle.BORDER_THIN; CountCS.TopBorderColor = HSSFColor.BLACK.index; CountCS.SetFont(SimpleF); HSSFCellStyle WeightCS = hssfworkbook.CreateCellStyle(); WeightCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00"); WeightCS.BorderBottom = HSSFCellStyle.BORDER_THIN; WeightCS.BottomBorderColor = HSSFColor.BLACK.index; WeightCS.BorderLeft = HSSFCellStyle.BORDER_THIN; WeightCS.LeftBorderColor = HSSFColor.BLACK.index; WeightCS.BorderRight = HSSFCellStyle.BORDER_THIN; WeightCS.RightBorderColor = HSSFColor.BLACK.index; WeightCS.BorderTop = HSSFCellStyle.BORDER_THIN; WeightCS.TopBorderColor = HSSFColor.BLACK.index; WeightCS.SetFont(SimpleF); HSSFCellStyle PriceBelCS = hssfworkbook.CreateCellStyle(); PriceBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0"); PriceBelCS.BorderBottom = HSSFCellStyle.BORDER_THIN; PriceBelCS.BottomBorderColor = HSSFColor.BLACK.index; PriceBelCS.BorderLeft = HSSFCellStyle.BORDER_THIN; PriceBelCS.LeftBorderColor = HSSFColor.BLACK.index; PriceBelCS.BorderRight = HSSFCellStyle.BORDER_THIN; PriceBelCS.RightBorderColor = HSSFColor.BLACK.index; PriceBelCS.BorderTop = HSSFCellStyle.BORDER_THIN; PriceBelCS.TopBorderColor = HSSFColor.BLACK.index; PriceBelCS.SetFont(SimpleF); HSSFCellStyle PriceForeignCS = hssfworkbook.CreateCellStyle(); PriceForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00"); PriceForeignCS.BorderBottom = HSSFCellStyle.BORDER_THIN; PriceForeignCS.BottomBorderColor = HSSFColor.BLACK.index; PriceForeignCS.BorderLeft = HSSFCellStyle.BORDER_THIN; PriceForeignCS.LeftBorderColor = HSSFColor.BLACK.index; PriceForeignCS.BorderRight = HSSFCellStyle.BORDER_THIN; PriceForeignCS.RightBorderColor = HSSFColor.BLACK.index; PriceForeignCS.BorderTop = HSSFCellStyle.BORDER_THIN; PriceForeignCS.TopBorderColor = HSSFColor.BLACK.index; PriceForeignCS.SetFont(SimpleF); HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle(); ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM; ReportCS1.BottomBorderColor = HSSFColor.BLACK.index; ReportCS1.SetFont(HeaderF1); HSSFCellStyle ReportCS2 = hssfworkbook.CreateCellStyle(); ReportCS2.SetFont(HeaderF1); HSSFCellStyle SummaryWithoutBorderBelCS = hssfworkbook.CreateCellStyle(); SummaryWithoutBorderBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0"); SummaryWithoutBorderBelCS.SetFont(HeaderF2); HSSFCellStyle SummaryWithoutBorderForeignCS = hssfworkbook.CreateCellStyle(); SummaryWithoutBorderForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00"); SummaryWithoutBorderForeignCS.SetFont(HeaderF2); HSSFCellStyle SummaryWeightCS = hssfworkbook.CreateCellStyle(); SummaryWeightCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00"); SummaryWeightCS.SetFont(HeaderF2); HSSFCellStyle SummaryWithBorderBelCS = hssfworkbook.CreateCellStyle(); SummaryWithBorderBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0"); SummaryWithBorderBelCS.BorderBottom = HSSFCellStyle.BORDER_THIN; SummaryWithBorderBelCS.BottomBorderColor = HSSFColor.BLACK.index; SummaryWithBorderBelCS.BorderLeft = HSSFCellStyle.BORDER_THIN; SummaryWithBorderBelCS.LeftBorderColor = HSSFColor.BLACK.index; SummaryWithBorderBelCS.BorderRight = HSSFCellStyle.BORDER_THIN; SummaryWithBorderBelCS.RightBorderColor = HSSFColor.BLACK.index; SummaryWithBorderBelCS.BorderTop = HSSFCellStyle.BORDER_THIN; SummaryWithBorderBelCS.TopBorderColor = HSSFColor.BLACK.index; SummaryWithBorderBelCS.WrapText = true; SummaryWithBorderBelCS.SetFont(HeaderF2); HSSFCellStyle SummaryWithBorderForeignCS = hssfworkbook.CreateCellStyle(); SummaryWithBorderForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00"); SummaryWithBorderForeignCS.BorderBottom = HSSFCellStyle.BORDER_THIN; SummaryWithBorderForeignCS.BottomBorderColor = HSSFColor.BLACK.index; SummaryWithBorderForeignCS.BorderLeft = HSSFCellStyle.BORDER_THIN; SummaryWithBorderForeignCS.LeftBorderColor = HSSFColor.BLACK.index; SummaryWithBorderForeignCS.BorderRight = HSSFCellStyle.BORDER_THIN; SummaryWithBorderForeignCS.RightBorderColor = HSSFColor.BLACK.index; SummaryWithBorderForeignCS.BorderTop = HSSFCellStyle.BORDER_THIN; SummaryWithBorderForeignCS.TopBorderColor = HSSFColor.BLACK.index; SummaryWithBorderForeignCS.WrapText = true; SummaryWithBorderForeignCS.SetFont(HeaderF2); HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle(); SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index; //SimpleHeaderCS.WrapText = true; SimpleHeaderCS.SetFont(HeaderF3); #endregion Create fonts and styles HSSFSheet sheet1 = hssfworkbook.CreateSheet("Маркетинг"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); int DisplayIndex = 0; sheet1.SetColumnWidth(DisplayIndex++, 40 * 256); sheet1.SetColumnWidth(DisplayIndex++, 9 * 256); sheet1.SetColumnWidth(DisplayIndex++, 15 * 256); sheet1.SetColumnWidth(DisplayIndex++, 14 * 256); sheet1.SetColumnWidth(DisplayIndex++, 14 * 256); sheet1.SetColumnWidth(DisplayIndex++, 14 * 256); sheet1.SetColumnWidth(DisplayIndex++, 9 * 256); sheet1.SetColumnWidth(DisplayIndex++, 9 * 256); sheet1.SetColumnWidth(DisplayIndex++, 9 * 256); sheet1.SetColumnWidth(DisplayIndex++, 13 * 256); sheet1.SetColumnWidth(DisplayIndex++, 13 * 256); HSSFCell Cell1; DisplayIndex = 0; if (AdditionalCostDT.Rows.Count > 0) { pos += 2; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Менеджер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Дата согласования"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Сумма дополнительно"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Сумма заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Валюта"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Курс"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Расчетный курс"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Итого сумма дополнительно"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++); Cell1.SetCellValue("Итого сумма заказа"); Cell1.CellStyle = SimpleHeaderCS; pos++; int ColumnCount = AdditionalCostDT.Columns.Count; for (int x = 0; x < AdditionalCostDT.Rows.Count; x++) { for (int y = 0; y < ColumnCount; y++) { Type t = AdditionalCostDT.Rows[x][y].GetType(); if (t.Name == "Decimal") { HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y); cell.SetCellValue(Convert.ToDouble(AdditionalCostDT.Rows[x][y])); cell.CellStyle = CountCS; continue; } if (t.Name == "Int32") { HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y); cell.SetCellValue(Convert.ToInt32(AdditionalCostDT.Rows[x][y])); cell.CellStyle = SimpleCS; continue; } if (t.Name == "Boolean") { bool b = Convert.ToBoolean(AdditionalCostDT.Rows[x][y]); string str = "Да"; if (!b) { str = "Нет"; } HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y); cell.SetCellValue(str); cell.CellStyle = SimpleCS; continue; } if (t.Name == "DateTime") { string dateTime = Convert.ToDateTime(AdditionalCostDT.Rows[x][y]).ToShortDateString(); HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y); cell.SetCellValue(dateTime); cell.CellStyle = SimpleCS; continue; } if (t.Name == "String" || t.Name == "DBNull") { HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y); cell.SetCellValue(AdditionalCostDT.Rows[x][y].ToString()); cell.CellStyle = SimpleCS; continue; } } pos++; } string tempFolder = System.Environment.GetEnvironmentVariable("TEMP"); FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls"); int j = 1; while (file.Exists == true) { file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls"); } FileStream NewFile = new FileStream(file.FullName, FileMode.Create); hssfworkbook.Write(NewFile); NewFile.Close(); System.Diagnostics.Process.Start(file.FullName); } }
private void button1_Click(object sender, EventArgs e) { //查询数据,显示到表格上 ManagerInfoBll miBll = new ManagerInfoBll(); var list = miBll.GetList(); dataGridView1.DataSource = list; //进行excel生成创建操作 //1、创建workbook,不指定参数,表示创建一个新的工作本 HSSFWorkbook workbook = new HSSFWorkbook(); //2、创建sheet HSSFSheet sheet = workbook.CreateSheet("管理员"); //3、创建row HSSFRow row = sheet.CreateRow(0); //4、创建cell HSSFCell cell0 = row.CreateCell(0); cell0.SetCellValue("管理员列表"); //5、设置合并单元格 sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(0, 0, 0, 3)); //6、设置单元格居中 HSSFCellStyle styleTitle = workbook.CreateCellStyle(); styleTitle.Alignment = 2;//居中 cell0.CellStyle = styleTitle; //6.1设置字体 HSSFFont fontTitle = workbook.CreateFont(); fontTitle.FontHeightInPoints = 14; styleTitle.SetFont(fontTitle); //7、创建标题行 //7.1创建行 HSSFRow rowTitle = sheet.CreateRow(1); //7.2创建单元格 HSSFCell cellTitle0 = rowTitle.CreateCell(0); cellTitle0.SetCellValue("编号"); cellTitle0.CellStyle = styleTitle; HSSFCell cellTitle1 = rowTitle.CreateCell(1); cellTitle1.SetCellValue("姓名"); cellTitle1.CellStyle = styleTitle; HSSFCell cellTitle2 = rowTitle.CreateCell(2); cellTitle2.SetCellValue("密码"); cellTitle2.CellStyle = styleTitle; HSSFCell cellTitle3 = rowTitle.CreateCell(3); cellTitle3.SetCellValue("类型"); cellTitle3.CellStyle = styleTitle; //8、遍历集合,创建正文数据 //8.1遍历集合 int rowIndex = 2; foreach (var mi in list) { //8.2创建行 HSSFRow rowData = sheet.CreateRow(rowIndex++); //8.3创建数据单元格 HSSFCell cellData0 = rowData.CreateCell(0); cellData0.SetCellValue(mi.MId); HSSFCell cellData1 = rowData.CreateCell(1); cellData1.SetCellValue(mi.MName); HSSFCell cellData2 = rowData.CreateCell(2); cellData2.SetCellValue(mi.MPwd); HSSFCell cellData3 = rowData.CreateCell(3); cellData3.SetCellValue(mi.MType == 1?"经理":"店员"); } //保存工作本 FileStream stream = new FileStream(@"C:\Users\q1\Desktop\t1.xls", FileMode.Create); workbook.Write(stream); stream.Close(); stream.Dispose(); }
public static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary <Int32, HSSFCellStyle> styleMap, Dictionary <short, short> paletteMap, Boolean keepFormulas) { if (styleMap != null) { if (oldCell.CellStyle != null) { if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook) { newCell.CellStyle = oldCell.CellStyle; } else { int styleHashCode = oldCell.CellStyle.GetHashCode(); if (styleMap.ContainsKey(styleHashCode)) { newCell.CellStyle = styleMap[styleHashCode]; } else { HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle(); newCellStyle.CloneStyleFrom(oldCell.CellStyle); RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually newCell.CellStyle = newCellStyle; //Clone of cell style always clones the font. This makes my life easier IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook); if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color)) { theFont.Color = paletteMap[theFont.Color]; //Remap font color } styleMap.Add(styleHashCode, newCellStyle); } } } else { newCell.CellStyle = null; } } switch (oldCell.CellType) { case CellType.String: HSSFRichTextString rts = oldCell.RichStringCellValue as HSSFRichTextString; newCell.SetCellValue(rts); if (rts != null) { for (int j = 0; j < rts.NumFormattingRuns; j++) { short fontIndex = rts.GetFontOfFormattingRun(j); int startIndex = rts.GetIndexOfFormattingRun(j); int endIndex = 0; if (j + 1 == rts.NumFormattingRuns) { endIndex = rts.Length; } else { endIndex = rts.GetIndexOfFormattingRun(j + 1); } FontRecord fr = newCell.BoundWorkbook.CreateNewFont(); fr.CloneStyleFrom(oldCell.BoundWorkbook.GetFontRecordAt(fontIndex)); HSSFFont font = new HSSFFont((short)(newCell.BoundWorkbook.GetFontIndex(fr)), fr); newCell.RichStringCellValue.ApplyFont(startIndex, endIndex, font); } } break; case CellType.Numeric: newCell.SetCellValue(oldCell.NumericCellValue); break; case CellType.Blank: newCell.SetCellType(CellType.Blank); break; case CellType.Boolean: newCell.SetCellValue(oldCell.BooleanCellValue); break; case CellType.Error: newCell.SetCellValue(oldCell.ErrorCellValue); break; case CellType.Formula: if (keepFormulas) { newCell.SetCellType(CellType.Formula); newCell.CellFormula = oldCell.CellFormula; } else { try { newCell.SetCellType(CellType.Numeric); newCell.SetCellValue(oldCell.NumericCellValue); } catch (Exception ex) { newCell.SetCellType(CellType.String); newCell.SetCellValue(oldCell.ToString()); } } break; default: break; } }
//private static WriteLog wl = new WriteLog(); #region 从datatable中将数据导出到excel /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> static MemoryStream ExportDT(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet; #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 HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } #region 表头及样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.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 列头及样式 { HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.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 rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 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; sheet.Dispose(); workbook.Dispose(); return(ms); } }
/// <summary> /// DataTable转换成Excel文件 /// </summary> /// <param name="dt">DataTable</param> /// <param name="titles">标题</param> /// <param name="exprotName">文件名</param> /// <param name="max">导出最多行数</param> /// <returns></returns> public static byte[] TableToExcel(DataTable dt, object[,] titles, string exprotName, int max = int.MaxValue) { /** * titles demo * public string SayHello(string msg) * object[,] titles = new object[,] { * { "OrderCode", "订单编号", "" }, * { "OrderState", "订单状态", new Func<string, string>(SayHello) }, * { "SalePrice", "订单金额", new object[]{ new Func<string, string>(getjine), "SalePrice", "DeliveryFee" } } * }; */ HSSFWorkbook hssfworkbook = new HSSFWorkbook(); // 新建一个Excel页签 ISheet sheet = hssfworkbook.CreateSheet("Sheet1"); IRow rowhead = sheet.CreateRow(0); Dictionary <string, object[]> titDic = new Dictionary <string, object[]>(); // key-属性名称 value-调用方法或空 string strTemp = string.Empty; // 设置表头 for (int i = 0; i < titles.GetLength(0); i++) { ICell cell = rowhead.CreateCell(i); cell.SetCellValue((string)titles[i, 1]); // 设置样式 加粗居中 HSSFCellStyle cellStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle(); HSSFFont font = (HSSFFont)hssfworkbook.CreateFont(); font.Boldweight = 600; cellStyle.SetFont(font); cellStyle.Alignment = HorizontalAlignment.Center; cell.CellStyle = cellStyle; if (!titDic.ContainsKey(strTemp = ((string)titles[i, 0]).ToLower())) // 执行if判断之后,把值赋给变量 { titDic.Add(strTemp, new object[2] { titles[i, 1], titles[i, 2] }); // key=>字段 value=>字段中文名 字段值获取方法 } } int index; for (int i = 0; i < dt.Rows.Count && i < max; i++) { IRow rowbody = sheet.CreateRow(i + 1); index = 0; foreach (KeyValuePair <string, object[]> item in titDic) { object[] val = titDic[dt.Columns[item.Key].ToString().ToLower()]; object fun = val[1]; ICell cell = rowbody.CreateCell(index++); if (fun is Func <string, string> ) { cell.SetCellValue((fun as Func <string, string>)(dt.Rows[i][item.Key].ToString())); } else if (fun is object[]) { cell.SetCellValue((((object[])fun)[0] as Func <string, string, string>)(dt.Rows[i][(string)((object[])fun)[1]].ToString(), dt.Rows[i][(string)((object[])fun)[2]].ToString())); } else { FillCellValue(cell, dt.Rows[i][item.Key]); } } } // 冻结首行 sheet.CreateFreezePane(0, 1); MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); sheet = null; return(file.GetBuffer()); }
public static bool ToSingleMbExcels(string savePath, string mbPath, List <SingleMb> dataList, string gwdcode, int rownum) { try { IWorkbook workbook; try { using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003 } } catch (Exception ex) { using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007 } } #region 样式 HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle(); style.WrapText = true; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HSSFFont font = (HSSFFont)workbook.CreateFont(); //font.FontName = "宋体"; font.FontName = "Arial Narrow"; font.FontHeightInPoints = 9; style.SetFont(font); HSSFFont fontSmall = (HSSFFont)workbook.CreateFont(); fontSmall.FontName = "Arial Narrow"; fontSmall.FontHeightInPoints = 8; #endregion ISheet sheet = workbook.GetSheetAt(0); ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.WrapText = true;//换行 foreach (SingleMb item in dataList) { IRow row = sheet.GetRow(item.rowIndex - 1); if (row == null) { row = sheet.CreateRow(item.rowIndex - 1); } ICell cell = row.GetCell(item.cellIndex - 1, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 //cell.CellStyle = cellStyle; cell.SetCellValue(item.value); //循环往第二行的单元格中添加数据 } #region 插入图片 string gwdgz = gwdcode; string imageFilePath = HttpContext.Current.Server.MapPath("/Content/TASK/images/"); if (System.IO.File.Exists(imageFilePath + gwdgz + ".png")) { byte[] bytes = System.IO.File.ReadAllBytes(imageFilePath + gwdgz + ".png"); //int pictureIdx = myWorkbook.AddPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG); // Create the drawing patriarch. This is the top level container for all shapes. HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //add a picture //dx1:起始单元格的x偏移量,如例子中的255表示直线起始位置距A1单元格左侧的距离; //dy1:起始单元格的y偏移量,如例子中的125表示直线起始位置距A1单元格上侧的距离; //dx2:终止单元格的x偏移量,如例子中的1023表示直线起始位置距C3单元格左侧的距离; //dy2:终止单元格的y偏移量,如例子中的150表示直线起始位置距C3单元格上侧的距离; //col1:起始单元格列序号,从0开始计算; //row1:起始单元格行序号,从0开始计算,如例子中col1=0,row1=0就表示起始单元格为A1; //col2:终止单元格列序号,从0开始计算; //row2:终止单元格行序号,从0开始计算,如例子中col2=2,row2=2就表示起始单元格为C3; HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 13, 0, 14, 4); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //anchor = new HSSFClientAnchor(0, 0, 0, 0, 13, 11, 15, 14); //HSSFPicture pict1 = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //pict.Resize(); } #endregion #region 放入问题表格边框 for (int i = 23; i < rownum - 1; i++) { for (int j = 0; j <= 13; j++) { sheet.GetRow(i).GetCell(j).CellStyle = style; if (j == 0) { sheet.GetRow(i).GetCell(j).CellStyle.SetFont(fontSmall); } } } #endregion using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } } catch (Exception) { return(false); } return(true); }
/// <summary> /// 导出Excel /// </summary> public void OutPutExcel(List <CostReportInfo> list) { var workbook = new HSSFWorkbook(); var ms = new MemoryStream(); var sheet = new HSSFSheet[1];// 增加sheet。 #region Excel样式 //标题样式styletitle HSSFFont fonttitle = workbook.CreateFont(); fonttitle.FontHeightInPoints = 10; fonttitle.Boldweight = HSSFFont.BOLDWEIGHT_BOLD; HSSFCellStyle styletitle = workbook.CreateCellStyle(); styletitle.BorderBottom = HSSFCellStyle.BORDER_THIN; styletitle.BorderLeft = HSSFCellStyle.BORDER_THIN; styletitle.BorderRight = HSSFCellStyle.BORDER_THIN; styletitle.BorderTop = HSSFCellStyle.BORDER_THIN; styletitle.SetFont(fonttitle); //内容字体styleContent HSSFFont fontcontent = workbook.CreateFont(); fontcontent.FontHeightInPoints = 9; fontcontent.Color = HSSFColor.BLACK.index; HSSFCellStyle styleContent = workbook.CreateCellStyle(); styleContent.BorderBottom = HSSFCellStyle.BORDER_THIN; styleContent.BorderLeft = HSSFCellStyle.BORDER_THIN; styleContent.BorderRight = HSSFCellStyle.BORDER_THIN; styleContent.BorderTop = HSSFCellStyle.BORDER_THIN; styleContent.SetFont(fontcontent); #endregion #region 将值插入sheet sheet[0] = workbook.CreateSheet("押金回收列表");//添加sheet名 sheet[0].DefaultColumnWidth = 40; sheet[0].DefaultRowHeight = 20; HSSFRow rowtitle = sheet[0].CreateRow(0); HSSFCell celltitie = rowtitle.CreateCell(0); const string SHEET_TITLE = "押金回收列表"; celltitie.SetCellValue(SHEET_TITLE); HSSFCellStyle style = workbook.CreateCellStyle(); style.Alignment = HSSFCellStyle.ALIGN_CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Color = HSSFColor.BLACK.index; font.Boldweight = 2; style.SetFont(font); celltitie.CellStyle = style; sheet[0].AddMergedRegion(new Region(0, 0, 0, 2)); #region //列名 HSSFRow rowTitle = sheet[0].CreateRow(3); HSSFCell cell1 = rowTitle.CreateCell(0); HSSFCell cell2 = rowTitle.CreateCell(1); HSSFCell cell3 = rowTitle.CreateCell(2); HSSFCell cell4 = rowTitle.CreateCell(3); HSSFCell cell5 = rowTitle.CreateCell(4); HSSFCell cell6 = rowTitle.CreateCell(5); HSSFCell cell7 = rowTitle.CreateCell(6); HSSFCell cell8 = rowTitle.CreateCell(7); HSSFCell cell9 = rowTitle.CreateCell(8); HSSFCell cell10 = rowTitle.CreateCell(9); HSSFCell cell11 = rowTitle.CreateCell(10); cell1.SetCellValue("公司"); cell2.SetCellValue("账户"); cell3.SetCellValue("申请人"); cell4.SetCellValue("预借款单据号"); cell5.SetCellValue("付款金额"); cell6.SetCellValue("付款时间"); cell7.SetCellValue("回收单据号"); cell8.SetCellValue("回收金额"); cell9.SetCellValue("回收时间"); cell10.SetCellValue("备注说明"); cell11.SetCellValue("状态"); cell1.CellStyle = styletitle; cell2.CellStyle = styletitle; cell3.CellStyle = styletitle; cell4.CellStyle = styletitle; cell5.CellStyle = styletitle; cell6.CellStyle = styletitle; cell7.CellStyle = styletitle; cell8.CellStyle = styletitle; cell9.CellStyle = styletitle; cell10.CellStyle = styletitle; cell11.CellStyle = styletitle; cell1.CellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER; #endregion #region //内容 int row = 4; if (list.Count > 0) { foreach (CostReportInfo info in list) { var depositRecoveryList = _costReportDepositRecovery.GetDepositRecoveryList(info.ReportId); if (depositRecoveryList.Count > 0) { foreach (var depositRecovery in depositRecoveryList) { HSSFRow rowt = sheet[0].CreateRow(row); HSSFCell c1 = rowt.CreateCell(0); HSSFCell c2 = rowt.CreateCell(1); HSSFCell c3 = rowt.CreateCell(2); HSSFCell c4 = rowt.CreateCell(3); HSSFCell c5 = rowt.CreateCell(4); HSSFCell c6 = rowt.CreateCell(5); HSSFCell c7 = rowt.CreateCell(6); HSSFCell c8 = rowt.CreateCell(7); HSSFCell c9 = rowt.CreateCell(8); HSSFCell c10 = rowt.CreateCell(9); HSSFCell c11 = rowt.CreateCell(10); c1.SetCellValue(CacheCollection.Filiale.GetFilialeNameAndFilialeId(info.PayBankAccountId).Split(',')[0]); c2.SetCellValue(info.BankAccount); c3.SetCellValue(new PersonnelManager().GetName(info.ReportPersonnelId)); c4.SetCellValue(info.ReportNo); c5.SetCellValue(info.RealityCost.ToString()); c6.SetCellValue(info.ExecuteDate.ToString("yyyy-MM-dd") == "1900-01-01" ? "" : info.ExecuteDate.ToString("yyyy-MM-dd")); c7.SetCellValue(depositRecovery.ReportNo); c8.SetCellValue(depositRecovery.RecoveryCost.ToString()); c9.SetCellValue(depositRecovery.RecoveryDate.ToString("yyyy-MM-dd")); c10.SetCellValue(depositRecovery.RecoveryRemarks); c11.SetCellValue(ddl_State.SelectedItem.ToString()); c1.CellStyle = styleContent; c2.CellStyle = styleContent; c3.CellStyle = styleContent; c4.CellStyle = styleContent; c5.CellStyle = styleContent; c6.CellStyle = styleContent; c7.CellStyle = styleContent; c8.CellStyle = styleContent; c9.CellStyle = styleContent; c10.CellStyle = styleContent; c11.CellStyle = styleContent; c1.CellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER; row++; } } else { HSSFRow rowt = sheet[0].CreateRow(row); HSSFCell c1 = rowt.CreateCell(0); HSSFCell c2 = rowt.CreateCell(1); HSSFCell c3 = rowt.CreateCell(2); HSSFCell c4 = rowt.CreateCell(3); HSSFCell c5 = rowt.CreateCell(4); HSSFCell c6 = rowt.CreateCell(5); HSSFCell c7 = rowt.CreateCell(6); HSSFCell c8 = rowt.CreateCell(7); HSSFCell c9 = rowt.CreateCell(8); HSSFCell c10 = rowt.CreateCell(9); HSSFCell c11 = rowt.CreateCell(10); c1.SetCellValue(CacheCollection.Filiale.GetFilialeNameAndFilialeId(info.PayBankAccountId).Split(',')[0]); c2.SetCellValue(info.BankAccount); c3.SetCellValue(new PersonnelManager().GetName(info.ReportPersonnelId)); c4.SetCellValue(info.ReportNo); c5.SetCellValue(info.RealityCost.ToString()); c6.SetCellValue(info.ExecuteDate.ToString("yyyy-MM-dd") == "1900-01-01" ? "" : info.ExecuteDate.ToString("yyyy-MM-dd")); c7.SetCellValue(""); c8.SetCellValue(""); c9.SetCellValue(""); c10.SetCellValue(""); c11.SetCellValue(ddl_State.SelectedItem.ToString()); c1.CellStyle = styleContent; c2.CellStyle = styleContent; c3.CellStyle = styleContent; c4.CellStyle = styleContent; c5.CellStyle = styleContent; c6.CellStyle = styleContent; c7.CellStyle = styleContent; c8.CellStyle = styleContent; c9.CellStyle = styleContent; c10.CellStyle = styleContent; c11.CellStyle = styleContent; c1.CellStyle.Alignment = HSSFCellStyle.ALIGN_CENTER; row++; } } } else { HSSFRow rtotal = sheet[0].CreateRow(row); HSSFCell t0 = rtotal.CreateCell(0); t0.SetCellValue("无数据显示"); } sheet[0].DisplayGridlines = false; #endregion #region 输出 workbook.Write(ms); Response.ContentEncoding = Encoding.GetEncoding("utf-8"); Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("押金回收列表" + DateTime.Now.ToString("yyyyMMdd") + ".xls", Encoding.UTF8)); Response.BinaryWrite(ms.ToArray()); ms.Close(); ms.Dispose(); GC.Collect(); #endregion #endregion }
private void BalancesMarketingReport(bool bZOV) { int pos = 0; string SheetName = "Маркетинг"; string ConnectionString = ConnectionStrings.MarketingOrdersConnectionString; #region Create fonts and styles HSSFFont HeaderF = hssfworkbook.CreateFont(); HeaderF.FontHeightInPoints = 9; HeaderF.Boldweight = 9 * 256; HeaderF.FontName = "Calibri"; HSSFFont SimpleF = hssfworkbook.CreateFont(); SimpleF.FontHeightInPoints = 10; SimpleF.FontName = "Calibri"; HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle(); SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN; SimpleCS.BottomBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN; SimpleCS.LeftBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN; SimpleCS.RightBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN; SimpleCS.TopBorderColor = HSSFColor.BLACK.index; SimpleCS.SetFont(SimpleF); HSSFCellStyle CountCS1 = hssfworkbook.CreateCellStyle(); CountCS1.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00"); CountCS1.BorderBottom = HSSFCellStyle.BORDER_THIN; CountCS1.BottomBorderColor = HSSFColor.BLACK.index; CountCS1.BorderLeft = HSSFCellStyle.BORDER_THIN; CountCS1.LeftBorderColor = HSSFColor.BLACK.index; CountCS1.BorderRight = HSSFCellStyle.BORDER_THIN; CountCS1.RightBorderColor = HSSFColor.BLACK.index; CountCS1.BorderTop = HSSFCellStyle.BORDER_THIN; CountCS1.TopBorderColor = HSSFColor.BLACK.index; CountCS1.SetFont(SimpleF); HSSFCellStyle CountCS2 = hssfworkbook.CreateCellStyle(); CountCS2.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.0000"); CountCS2.BorderBottom = HSSFCellStyle.BORDER_THIN; CountCS2.BottomBorderColor = HSSFColor.BLACK.index; CountCS2.BorderLeft = HSSFCellStyle.BORDER_THIN; CountCS2.LeftBorderColor = HSSFColor.BLACK.index; CountCS2.BorderRight = HSSFCellStyle.BORDER_THIN; CountCS2.RightBorderColor = HSSFColor.BLACK.index; CountCS2.BorderTop = HSSFCellStyle.BORDER_THIN; CountCS2.TopBorderColor = HSSFColor.BLACK.index; CountCS2.SetFont(SimpleF); HSSFCellStyle CountCS3 = hssfworkbook.CreateCellStyle(); CountCS3.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0"); CountCS3.BorderBottom = HSSFCellStyle.BORDER_THIN; CountCS3.BottomBorderColor = HSSFColor.BLACK.index; CountCS3.BorderLeft = HSSFCellStyle.BORDER_THIN; CountCS3.LeftBorderColor = HSSFColor.BLACK.index; CountCS3.BorderRight = HSSFCellStyle.BORDER_THIN; CountCS3.RightBorderColor = HSSFColor.BLACK.index; CountCS3.BorderTop = HSSFCellStyle.BORDER_THIN; CountCS3.TopBorderColor = HSSFColor.BLACK.index; CountCS3.SetFont(SimpleF); HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle(); SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.SetFont(HeaderF); #endregion Create fonts and styles HSSFSheet sheet1 = hssfworkbook.CreateSheet(SheetName); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); sheet1.SetColumnWidth(0, 20 * 256); sheet1.SetColumnWidth(1, 30 * 256); sheet1.SetColumnWidth(2, 20 * 256); sheet1.SetColumnWidth(3, 20 * 256); HSSFCell Cell1; string SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length = - 1 AND dbo.DecorOrders.Height = - 1 AND dbo.DecorOrders.Width = - 1 INNER JOIN dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName ORDER BY ProductName, Decor, Color, Patina"; using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString)) { BalancesDT.Dispose(); BalancesDT = new DataTable(); DA.Fill(BalancesDT); } if (BalancesDT.Rows.Count > 0) { Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < BalancesDT.Rows.Count; i++) { Cell1 = sheet1.CreateRow(pos).CreateCell(0); Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(1); Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(2); Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(3); Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"])); Cell1.CellStyle = CountCS3; pos++; } pos += 2; } SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina, dbo.DecorOrders.Length, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND dbo.DecorOrders.Width = - 1 INNER JOIN dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName, dbo.DecorOrders.Length ORDER BY ProductName, Decor, Color, Patina, dbo.DecorOrders.Length"; using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString)) { BalancesDT.Dispose(); BalancesDT = new DataTable(); DA.Fill(BalancesDT); } if (BalancesDT.Rows.Count > 0) { Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue("Длина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(5); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < BalancesDT.Rows.Count; i++) { Cell1 = sheet1.CreateRow(pos).CreateCell(0); Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(1); Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(2); Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(3); Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Length"])); Cell1.CellStyle = CountCS3; Cell1 = sheet1.CreateRow(pos).CreateCell(5); Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"])); Cell1.CellStyle = CountCS3; pos++; } pos += 2; } SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina, SUM(CAST(dbo.DecorOrders.Length * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(10,2))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND dbo.DecorOrders.Width <> - 1 INNER JOIN dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN infiniu2_catalog.dbo.DecorConfig ON dbo.DecorOrders.DecorConfigID = infiniu2_catalog.dbo.DecorConfig.DecorConfigID AND infiniu2_catalog.dbo.DecorConfig.MeasureID = 2 INNER JOIN infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName ORDER BY ProductName, Decor, Color, Patina"; using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString)) { BalancesDT.Dispose(); BalancesDT = new DataTable(); DA.Fill(BalancesDT); foreach (DataRow item in BalancesDT.Rows) { item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000; } } if (BalancesDT.Rows.Count > 0) { Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue("м.п."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(5); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < BalancesDT.Rows.Count; i++) { Cell1 = sheet1.CreateRow(pos).CreateCell(0); Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(1); Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(2); Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(3); Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"])); Cell1.CellStyle = CountCS1; Cell1 = sheet1.CreateRow(pos).CreateCell(5); Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"])); Cell1.CellStyle = CountCS3; pos++; } pos += 2; } SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina, SUM(CAST(dbo.DecorOrders.Length * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(12,4))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND dbo.DecorOrders.Width <> - 1 INNER JOIN dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN infiniu2_catalog.dbo.DecorConfig ON dbo.DecorOrders.DecorConfigID = infiniu2_catalog.dbo.DecorConfig.DecorConfigID AND infiniu2_catalog.dbo.DecorConfig.MeasureID = 1 INNER JOIN infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName ORDER BY ProductName, Decor, Color, Patina"; using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString)) { BalancesDT.Dispose(); BalancesDT = new DataTable(); DA.Fill(BalancesDT); foreach (DataRow item in BalancesDT.Rows) { item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000000; } } if (BalancesDT.Rows.Count > 0) { Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue("м.кв."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(5); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < BalancesDT.Rows.Count; i++) { Cell1 = sheet1.CreateRow(pos).CreateCell(0); Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(1); Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(2); Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(3); Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"])); Cell1.CellStyle = CountCS2; Cell1 = sheet1.CreateRow(pos).CreateCell(5); Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"])); Cell1.CellStyle = CountCS3; pos++; } } SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina, SUM(CAST(dbo.DecorOrders.Height * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(12,4))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length = - 1 AND dbo.DecorOrders.Height <> - 1 AND dbo.DecorOrders.Width <> - 1 INNER JOIN dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName ORDER BY ProductName, Decor, Color, Patina"; using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString)) { BalancesDT.Dispose(); BalancesDT = new DataTable(); DA.Fill(BalancesDT); foreach (DataRow item in BalancesDT.Rows) { item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000000; } } if (BalancesDT.Rows.Count > 0) { for (int i = 0; i < BalancesDT.Rows.Count; i++) { Cell1 = sheet1.CreateRow(pos).CreateCell(0); Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(1); Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(2); Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(3); Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(4); Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"])); Cell1.CellStyle = CountCS2; Cell1 = sheet1.CreateRow(pos).CreateCell(5); Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"])); Cell1.CellStyle = CountCS3; pos++; } pos += 1; } }
private void ExportExcel() { Response.ContentType = "application/vnd.ms-excel"; string datetime = DateTime.Now.ToString().Replace("/", "").Replace(" ", "").Replace(":", ""); string projectName = "ALL"; projectName = ddlProject.SelectedValue == "0" ? projectName : ddlProject.SelectedItem.Text; string ticketType = "ALL"; ticketType = ddlTicketType.SelectedValue == "-1" ? ticketType : ddlTicketType.SelectedItem.Text; string ticketStatu = "ALL"; ticketStatu = ddlStatus.SelectedValue == "-1" ? ticketStatu : ddlStatus.SelectedItem.Text; string filename = string.Empty; if (projectName == "ALL" && ticketType == "ALL" && ticketStatu == "ALL") { filename = string.Format("ALL_{0}.xls", datetime); } else { projectName = projectName == "ALL" ? "AllProject" : projectName; ticketType = ticketType == "ALL" ? "ALLType" : ticketType; ticketStatu = ticketStatu == "ALL" ? "ALLStatus" : ticketStatu; filename = string.Format("{0}_{1}_{2}.xls", projectName, ticketStatu, ticketType); } Response.AddHeader("Content-Disposition", "attachment;filename=" + filename); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); //Set Sheet ColumnWidth sheet.SetColumnWidth(0, 30 * 256); sheet.SetColumnWidth(2, 50 * 256); sheet.SetColumnWidth(3, 20 * 256); sheet.SetColumnWidth(4, 20 * 256); sheet.SetColumnWidth(5, 20 * 256); sheet.SetColumnWidth(6, 20 * 256); sheet.SetColumnWidth(7, 20 * 256); sheet.SetColumnWidth(8, 100 * 256); sheet.DisplayGridlines = false; HSSFRow row_head = (HSSFRow)sheet.CreateRow(0); row_head.HeightInPoints = 20; row_head.CreateCell((short)0).SetCellValue("Project"); row_head.CreateCell((short)1).SetCellValue("Type"); row_head.CreateCell((short)2).SetCellValue("Ticket Code / Ticket Title"); row_head.CreateCell((short)3).SetCellValue("Created Date"); row_head.CreateCell((short)4).SetCellValue("Updated Date"); row_head.CreateCell((short)5).SetCellValue("Status"); row_head.CreateCell((short)6).SetCellValue("Priority"); row_head.CreateCell((short)7).SetCellValue("Created By"); row_head.CreateCell((short)8).SetCellValue("Description"); HSSFCellStyle hStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //hStyle.BorderTop = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN; hStyle.BorderRight = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN; //hStyle.BorderLeft = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN; hStyle.BorderBottom = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN; //Set head row background color hStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.TEAL.index; hStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SQUARES; hStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.TEAL.index; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index; font.FontName = "Verdana"; font.FontHeightInPoints = 12; hStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; hStyle.SetFont(font); foreach (var c in row_head.Cells) { c.CellStyle = hStyle; } SearchTicketsResponse response = GetResponse(false); int i = 1; foreach (ExpandTicketsEntity ticket in response.ResultList) { HSSFRow rownumber = (HSSFRow)sheet.CreateRow(i); rownumber.CreateCell((short)0).SetCellValue(ticket.ProjectTitle); rownumber.CreateCell((short)1).SetCellValue(ticket.TicketType.ToString()); rownumber.CreateCell((short)2).SetCellValue(ticket.TicketID + " " + ticket.Title); rownumber.CreateCell((short)3).SetCellValue(string.Format("{0:M/d/yyyy}", ticket.CreatedOn)); rownumber.CreateCell((short)4).SetCellValue(string.Format("{0:M/d/yyyy}", ticket.ModifiedOn)); rownumber.CreateCell((short)5).SetCellValue( GetClientStatusNameBySatisfyStatus((int)ticket.Status, ticket.TicketID, false)); rownumber.CreateCell((short)6).SetCellValue(ticket.Priority.ToString()); rownumber.CreateCell((short)7).SetCellValue(string.Format("{0} {1}", ticket.FirstName, ticket.LastName)); rownumber.CreateCell((short)8).SetCellValue(ticket.FullDescription); HSSFCellStyle rStyle = (HSSFCellStyle)workbook.CreateCellStyle(); rStyle.BorderRight = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN; rStyle.BorderBottom = (NPOI.SS.UserModel.CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN; rStyle.WrapText = true; rStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; rStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; foreach (var c in rownumber.Cells) { c.CellStyle = rStyle; } i++; } workbook.Write(Response.OutputStream); }
/// <summary>导出EXCEL /// </summary> public void OutPutExcel(Dictionary <GoodsBaseModel, List <GoodsBaseModel> > dic) { var workbook = new HSSFWorkbook(); var ms = new MemoryStream(); var sheet = new HSSFSheet[1]; // 增加sheet。 #region Excel样式 //标题样式styletitle HSSFFont fonttitle = workbook.CreateFont(); fonttitle.FontHeightInPoints = 12; fonttitle.Color = HSSFColor.RED.index; HSSFCellStyle styletitle = workbook.CreateCellStyle(); styletitle.BorderBottom = HSSFCellStyle.BORDER_THIN; styletitle.BorderLeft = HSSFCellStyle.BORDER_THIN; styletitle.BorderRight = HSSFCellStyle.BORDER_THIN; styletitle.BorderTop = HSSFCellStyle.BORDER_THIN; styletitle.SetFont(fonttitle); //内容字体styleContent HSSFFont fontcontent = workbook.CreateFont(); fontcontent.FontHeightInPoints = 9; fontcontent.Color = HSSFColor.BLACK.index; HSSFCellStyle styleContent = workbook.CreateCellStyle(); styleContent.BorderBottom = HSSFCellStyle.BORDER_THIN; styleContent.BorderLeft = HSSFCellStyle.BORDER_THIN; styleContent.BorderRight = HSSFCellStyle.BORDER_THIN; styleContent.BorderTop = HSSFCellStyle.BORDER_THIN; styleContent.SetFont(fontcontent); //总计 styletotal HSSFFont fonttotal = workbook.CreateFont(); fonttotal.FontHeightInPoints = 12; fonttotal.Color = HSSFColor.RED.index; fonttotal.Boldweight = 2; HSSFCellStyle styletotal = workbook.CreateCellStyle(); styletotal.SetFont(fonttotal); #endregion #region [模板及sheet名字] sheet[0] = workbook.CreateSheet("框架赠品绑定" + DateTime.Now.ToString("yyyy-MM-dd")); //添加sheet名 sheet[0].DefaultColumnWidth = 30; sheet[0].DefaultRowHeight = 20; HSSFRow rowtitle = sheet[0].CreateRow(0); HSSFCell celltitie = rowtitle.CreateCell(0); celltitie.SetCellValue("框架赠品绑定" + DateTime.Now.ToString("yyyy-MM-dd")); HSSFCellStyle style = workbook.CreateCellStyle(); style.Alignment = HSSFCellStyle.ALIGN_CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Color = HSSFColor.BLACK.index; font.Boldweight = 2; style.SetFont(font); celltitie.CellStyle = style; sheet[0].AddMergedRegion(new Region(0, 0, 0, 7)); #endregion #region [列名] HSSFRow rowtitles = sheet[0].CreateRow(1); HSSFCell ct1 = rowtitles.CreateCell(0); HSSFCell ct2 = rowtitles.CreateCell(1); ct1.SetCellValue("商品名名称"); ct2.SetCellValue("赠品商品名称"); ct1.CellStyle = styletitle; ct2.CellStyle = styletitle; #endregion int row = 2; foreach (var item in dic.Keys) { HSSFRow rowt = sheet[0].CreateRow(row); HSSFCell c1 = rowt.CreateCell(0); HSSFCell c2 = rowt.CreateCell(1); c1.SetCellValue(item.GoodsName); //商品名 var giftList = dic.FirstOrDefault(ent => ent.Key.GoodsId == item.GoodsId).Value; StringBuilder giftGoodsName = new StringBuilder(); foreach (var goodsBaseModel in giftList) { giftGoodsName.Append(" [ ").Append(goodsBaseModel.GoodsName).Append(" ] "); } c2.SetCellValue(giftGoodsName.ToString()); //赠品商品 c1.CellStyle = styleContent; c2.CellStyle = styleContent; c1.CellStyle.Alignment = HSSFCellStyle.ALIGN_LEFT; row++; } sheet[0].DisplayGridlines = false; workbook.Write(ms); Response.ContentEncoding = Encoding.GetEncoding("utf-8"); Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("框架赠品绑定" + DateTime.Now.ToString("yyyyMMdd") + ".xls", Encoding.UTF8)); Response.BinaryWrite(ms.ToArray()); ms.Close(); ms.Dispose(); GC.Collect(); }
private FileContentResult SendToExcel2(FormCollection coll) { DateTime firstDay = DateTime.Parse(coll["txtDateFrom"]); DateTime lastDay = DateTime.Parse(coll["txtDateTo"]); string User_CAI = coll["ddlUsers"]; ReportPackage rp = timedb.GetReportByUser(User_CAI, firstDay, lastDay); // FileStream fs = new FileStream(Server.MapPath("~/Content/Template.xls"), FileMode.Open, FileAccess.Read); // Getting the complete workbook... HSSFWorkbook wb = new HSSFWorkbook(); // Getting the worksheet by its name... HSSFSheet sheet = wb.CreateSheet(User_CAI + "_" + coll["txtDateFrom"] + "_" + coll["txtDateTo"]); sheet.SetColumnWidth(2, 20 * 256); sheet.SetColumnWidth(3, 20 * 256); sheet.SetColumnWidth(4, 30 * 256); HSSFFont font1 = wb.CreateFont(); font1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD; font1.Color = HSSFColor.BLACK.index; font1.FontHeightInPoints = 10; HSSFFont font2 = wb.CreateFont(); font2.Color = HSSFColor.BLACK.index; font2.FontHeightInPoints = 8; HSSFFont sacofont = wb.CreateFont(); sacofont.Color = HSSFColor.WHITE.index; sacofont.FontHeightInPoints = 8; HSSFFont font2Bold = wb.CreateFont(); font2Bold = font2; font2Bold.Boldweight = HSSFFont.BOLDWEIGHT_BOLD; HSSFFont font_Red_Bold = wb.CreateFont(); font_Red_Bold.Color = HSSFColor.RED.index; font_Red_Bold.Boldweight = HSSFFont.BOLDWEIGHT_BOLD; font_Red_Bold.FontHeightInPoints = 10; HSSFCellStyle globalstyle = wb.CreateCellStyle(); globalstyle.BorderBottom = HSSFCellStyle.BORDER_THIN; globalstyle.BottomBorderColor = HSSFColor.BLACK.index; globalstyle.BorderLeft = HSSFCellStyle.BORDER_THIN; globalstyle.LeftBorderColor = HSSFColor.BLACK.index; globalstyle.BorderRight = HSSFCellStyle.BORDER_THIN; globalstyle.RightBorderColor = HSSFColor.BLACK.index; globalstyle.BorderTop = HSSFCellStyle.BORDER_THIN; globalstyle.TopBorderColor = HSSFColor.BLACK.index; HSSFCellStyle headerstyle = wb.CreateCellStyle(); headerstyle.CloneStyleFrom(globalstyle); headerstyle.Alignment = HSSFCellStyle.ALIGN_CENTER; headerstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; headerstyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND; headerstyle.SetFont(font1); HSSFCellStyle dayStyle = wb.CreateCellStyle(); dayStyle.CloneStyleFrom(headerstyle); dayStyle.SetFont(font1); HSSFCellStyle datastyle = wb.CreateCellStyle(); datastyle.CloneStyleFrom(globalstyle); datastyle.SetFont(font2); HSSFCellStyle footerStyle = wb.CreateCellStyle(); footerStyle.CloneStyleFrom(headerstyle); footerStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT; footerStyle.SetFont(font2); HSSFCellStyle ErrorStyle = wb.CreateCellStyle(); ErrorStyle.CloneStyleFrom(headerstyle); ErrorStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT; ErrorStyle.SetFont(font_Red_Bold); HSSFCellStyle SacofooterStyle = wb.CreateCellStyle(); SacofooterStyle.CloneStyleFrom(globalstyle); SacofooterStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT; SacofooterStyle.FillForegroundColor = HSSFColor.LIGHT_BLUE.index; SacofooterStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND; SacofooterStyle.SetFont(sacofont); //Header HSSFRow row = sheet.CreateRow(0); row.CreateCell(0).CellStyle = headerstyle; row.GetCell(0).SetCellValue("Status"); row.CreateCell(1).CellStyle = headerstyle; row.GetCell(1).SetCellValue("Prj No."); row.CreateCell(2).CellStyle = headerstyle; row.GetCell(2).SetCellValue("SAP"); row.CreateCell(3).CellStyle = headerstyle; row.GetCell(3).SetCellValue("Cost Code"); row.CreateCell(4).CellStyle = headerstyle; row.GetCell(4).SetCellValue("Project Name"); int colcount = 5; for (DateTime dt = rp.DateFrom; dt < rp.DateTo.AddDays(1); dt = dt.AddDays(1)) { row.CreateCell(colcount).CellStyle = dayStyle; row.GetCell(colcount).CellStyle.WrapText = true; row.GetCell(colcount).SetCellValue(new DateTime(dt.Year, dt.Month, dt.Day).ToString("ddd") + "\n" + new DateTime(dt.Year, dt.Month, dt.Day).ToString("dd") + "\n" + new DateTime(dt.Year, dt.Month, dt.Day).ToString("MMM")); colcount++; } row.CreateCell(colcount).CellStyle = headerstyle; row.GetCell(colcount).SetCellValue("Total"); //Details int rowcount = 1; foreach (var ts in rp.TimeSheets) { sheet.CreateRow(rowcount); sheet.GetRow(rowcount).CreateCell(0).CellStyle = datastyle; sheet.GetRow(rowcount).GetCell(0).SetCellValue(ts.Status); sheet.GetRow(rowcount).CreateCell(1).CellStyle = datastyle; sheet.GetRow(rowcount).GetCell(1).SetCellValue(ts.Project_Number.ToString()); sheet.GetRow(rowcount).CreateCell(2).CellStyle = datastyle; sheet.GetRow(rowcount).GetCell(2).SetCellValue(ts.SAP); sheet.GetRow(rowcount).CreateCell(3).CellStyle = datastyle; sheet.GetRow(rowcount).GetCell(3).SetCellValue(ts.WBS); sheet.GetRow(rowcount).CreateCell(4).CellStyle = datastyle; sheet.GetRow(rowcount).GetCell(4).CellStyle.WrapText = true; sheet.GetRow(rowcount).GetCell(4).SetCellValue(ts.Project_Name); colcount = 5; foreach (var d in ts.Days) { sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = datastyle; if (d.HoursWorked.HasValue) { sheet.GetRow(rowcount).GetCell(colcount).SetCellValue((double)d.HoursWorked.Value); } else { sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty); } colcount++; } sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = datastyle; sheet.GetRow(rowcount).GetCell(colcount).SetCellValue((double)ts.Total); rowcount++; } //Footer //Insert blank spaces sheet.CreateRow(rowcount); sheet.GetRow(rowcount).CreateCell(0).CellStyle = footerStyle; sheet.GetRow(rowcount).CreateCell(1).CellStyle = footerStyle; sheet.GetRow(rowcount).CreateCell(2).CellStyle = footerStyle; sheet.GetRow(rowcount).CreateCell(3).CellStyle = footerStyle; sheet.GetRow(rowcount).CreateCell(4).CellStyle = footerStyle; colcount = 5; //Start at column 5 inserting values int loopindex = 0; foreach (var d in rp.DayTotals) { sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = footerStyle; if (d.HoursWorked.HasValue && (decimal)d.HoursWorked.Value > 0) { loopindex = d.Number - 1; if (rp.DayTotals[loopindex].HoursWorked.Value > rp.DaySacoTotals[loopindex].HoursWorked.Value) { sheet.GetRow(rowcount).GetCell(colcount).CellStyle = ErrorStyle; } sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)d.HoursWorked.Value).ToString("#0.00")); } else { sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty); } colcount++; } sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = footerStyle; sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)rp.GrandTotal).ToString("#0.00")); rowcount++; //SACO Footer //Insert blank spaces sheet.CreateRow(rowcount); sheet.GetRow(rowcount).CreateCell(0).CellStyle = SacofooterStyle; sheet.GetRow(rowcount).GetCell(0).SetCellValue("Saco"); sheet.GetRow(rowcount).CreateCell(1).CellStyle = SacofooterStyle; sheet.GetRow(rowcount).CreateCell(2).CellStyle = SacofooterStyle; sheet.GetRow(rowcount).CreateCell(3).CellStyle = SacofooterStyle; sheet.GetRow(rowcount).CreateCell(4).CellStyle = SacofooterStyle; colcount = 5; //Start at column 5 inserting values foreach (var d in rp.DaySacoTotals) { sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = SacofooterStyle; if (d.HoursWorked.HasValue && (decimal)d.HoursWorked.Value > 0) { sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)d.HoursWorked.Value).ToString("#0.00")); } else { sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty); } colcount++; } sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = SacofooterStyle; sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)rp.DaySacoTotals.Sum(t => t.HoursWorked)).ToString("#0.00")); MemoryStream ms = new MemoryStream(); // Writing the workbook content to the FileStream... wb.Write(ms); // Sending the server processed data back to the user computer... return(File(ms.ToArray(), "application/vnd.ms-excel", coll["ddlUsers"] + "_" + coll["txtDateFrom"] + "_" + coll["txtDateTo"] + "_TimeSheetReport.xls")); }
/// <summary> /// Take a cell, and apply a font to it /// </summary> /// <param name="cell">the cell to Set the alignment for</param> /// <param name="workbook">The workbook that is being worked with.</param> /// <param name="font">The HSSFFont that you want to Set...</param> public static void SetFont(ICell cell, HSSFWorkbook workbook, HSSFFont font) { SetCellStyleProperty(cell, workbook, FONT, font); }
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Yeli"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "yelioa"; //填加xls文件作者信息 si.ApplicationName = title; //填加xls文件创建程序信息 si.LastAuthor = "yelioa"; //填加xls文件最后保存者信息 si.Comments = "说明信息"; //填加xls文件作者信息 si.Title = title; //填加xls文件标题信息 si.Subject = ""; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); //dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd 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 = (HSSFSheet)workbook.CreateSheet(); } #region 表头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //string[] chineseHeaders; //if(strHeaderText=="部门积分信息") //{ // chineseHeaders =new string[] { "姓名", "部门", "月度积分", "季度积分", "年度积分", "总积分" }; //} //else // chineseHeaders = new string[] {"序号", "编号", "提交日期", "审批日期", "财务审批日期", // "提交人", "部门", "费用归属部门", "产品", "费用明细", "金额", "实报金额", "状态", "审批人", "抄送人", "备注", "审批意见", "审批结果"}; for (int i = 0; i < dtSource.Columns.Count; i++) { int colWidth = sheet.GetColumnWidth(i) * 2; if (colWidth < 255 * 256) { sheet.SetColumnWidth(i, colWidth < 3000 ? 3000 : colWidth); } else { sheet.SetColumnWidth(i, 6000); } DataColumn column = dtSource.Columns[i]; headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 255); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); #region 填充内容 foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 if ("".Equals(drValue)) { newCell.SetCellValue(drValue); break; } DateTime date = Convert.ToDateTime(drValue); drValue = date.ToString("yyyy-MM-dd HH:mm:ss"); //DateTime dateV; //DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(drValue); //newCell.SetCellType(HSSFCellType.FORMULA); //newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose(); return(ms); } }