public async Task <ActionResult> ExportToExcel(WIPDisplayDetailQueryViewModel model) { IList <Lot> lstLot = new List <Lot>(); using (LotQueryServiceClient client = new LotQueryServiceClient()) { await Task.Run(() => { PagingConfig cfg = new PagingConfig() { IsPaging = false, OrderBy = "CreateTime DESC", Where = GetQueryCondition(model) }; MethodReturnResult <IList <Lot> > result = client.Get(ref cfg); if (result.Code == 0) { lstLot = result.Data; } }); } //创建工作薄。 IWorkbook wb = new HSSFWorkbook(); //设置EXCEL格式 ICellStyle style = wb.CreateCellStyle(); style.FillForegroundColor = 10; //有边框 style.BorderBottom = BorderStyle.THIN; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.THIN; IFont font = wb.CreateFont(); font.Boldweight = 10; style.SetFont(font); ISheet ws = null; for (int j = 0; j < lstLot.Count; j++) { if (j % 65535 == 0) { ws = wb.CreateSheet(); IRow row = ws.CreateRow(0); #region //列名 ICell cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("序号"); //序号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("批次号(组件序列号)"); //批次号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("工单号"); //工单号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("物料编码"); //产品号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("数量"); //数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("等级"); //等级 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("花色"); //花色 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("线别"); //线别代码 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("设备代码"); //设备代码 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("车间名称"); //车间名称 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("工艺流程"); //工艺流程 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("工序"); //工序 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("状态"); //状态 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("批次类型"); //批次类型 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("暂停?"); //暂停标志 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("包装号"); //包装号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("返修?"); //返修标志 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("返工?"); //返工标志 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("返工时间"); //返工时间 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("返工操作人"); //返工操作人 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("创建时间"); //创建时间 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("创建人"); //创建人 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("编辑时间"); //编辑时间 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("编辑人"); //编辑人 #endregion font.Boldweight = 5; } Lot obj = lstLot[j]; IRow rowData = ws.CreateRow(j + 1); #region //数据 ICell cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(j + 1); //序号 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.Key); //批次号 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.OrderNumber); //工单号 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.MaterialCode); //产品号 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.Quantity); //数量 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.Grade); //等级 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.Color); //花色 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.LineCode); //线别代码 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.EquipmentCode); //设备代码 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.LocationName); //车间名称 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.RouteName); //工艺流程 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.RouteStepName); //工步 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.StateFlag.GetDisplayName()); //批次状态 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.LotType.GetDisplayName()); //批次类型 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.HoldFlag ? StringResource.Yes : StringResource.No); //暂停标志 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.PackageNo); //包装号 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.RepairFlag); //返修次数 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.ReworkFlag); //返工次数 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.ReworkTime)); //返工时间 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.Reworker); //返工操作人 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.CreateTime)); //创建时间 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.Creator); //创建人 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //编辑时间 cellData = rowData.CreateCell(rowData.Cells.Count); cellData.CellStyle = style; cellData.SetCellValue(obj.Editor); //编辑人 #endregion } MemoryStream ms = new MemoryStream(); wb.Write(ms); ms.Flush(); ms.Position = 0; return(File(ms, "application/vnd.ms-excel", "WIPDetailData.xls")); }
/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param> public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "刘晓雷"; //填加xls文件作者信息 si.ApplicationName = "力软信息"; //填加xls文件创建程序信息 si.LastAuthor = "刘晓雷"; //填加xls文件最后保存者信息 si.Comments = "刘晓雷"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion #region 设置标题样式 ICellStyle headStyle = workbook.CreateCellStyle(); int[] arrColWidth = new int[properties.Length]; string[] arrColName = new string[properties.Length]; //列名 ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表 headStyle.Alignment = HorizontalAlignment.Center; // ------------------ if (excelConfig.Background != new Color()) { if (excelConfig.Background != new Color()) { headStyle.FillPattern = FillPattern.SolidForeground; headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background); } } IFont font = workbook.CreateFont(); font.FontHeightInPoints = excelConfig.TitlePoint; if (excelConfig.ForeColor != new Color()) { font.Color = GetXLColour(workbook, excelConfig.ForeColor); } font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 列头及样式 ICellStyle cHeadStyle = workbook.CreateCellStyle(); cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont cfont = workbook.CreateFont(); cfont.FontHeightInPoints = excelConfig.HeadPoint; cHeadStyle.SetFont(cfont); #endregion #region 设置内容单元格样式 int i = 0; foreach (PropertyInfo column in properties) { ICellStyle columnStyle = workbook.CreateCellStyle(); columnStyle.Alignment = HorizontalAlignment.Center; arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length; arrColName[i] = column.Name; if (excelConfig.ColumnEntity != null) { ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name); if (columnentity != null) { arrColName[i] = columnentity.ExcelColumn; if (columnentity.Width != 0) { arrColWidth[i] = columnentity.Width; } if (columnentity.Background != new Color()) { if (columnentity.Background != new Color()) { columnStyle.FillPattern = FillPattern.SolidForeground; columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background); } } if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color()) { IFont columnFont = workbook.CreateFont(); columnFont.FontHeightInPoints = 10; if (columnentity.Font != null) { columnFont.FontName = columnentity.Font; } if (columnentity.Point != 0) { columnFont.FontHeightInPoints = columnentity.Point; } if (columnentity.ForeColor != new Color()) { columnFont.Color = GetXLColour(workbook, columnentity.ForeColor); } columnStyle.SetFont(font); } } } arryColumStyle[i] = columnStyle; i++; } #endregion #region 填充数据 #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 0; foreach (T item in lists) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(0); if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------ } } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出 int headIndex = 0; foreach (PropertyInfo column in properties) { headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]); headerRow.GetCell(headIndex).CellStyle = cHeadStyle; //设置列宽 sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256); headIndex++; } #endregion } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); int ordinal = 0; foreach (PropertyInfo column in properties) { ICell newCell = dataRow.CreateCell(ordinal); newCell.CellStyle = arryColumStyle[ordinal]; string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString(); SetCell(newCell, dateStyle, column.PropertyType, drValue); ordinal++; } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="strHeaderText">Excel表头文本(例如:车辆列表)</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------ } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
public void ExportItemSourceToExcel(List <ProductiveTaskListModel> lists, string filePath) { if (lists.Count == 0) { return; } string part1 = lists[0].FProductionDate.ToLongDateString().ToString(); string fName = Path.Combine(filePath, $"{part1}生产任务清单.xls"); //如果存在此文件则添加1 if (File.Exists(fName)) { fName = fName.Replace(".xls", DateTime.Now.ToString("HHmmss") + ".xls"); } IWorkbook wb = new HSSFWorkbook(); //定义单元格格式 #region 缩小填充 ICellStyle fitStyle = wb.CreateCellStyle(); fitStyle.ShrinkToFit = true; fitStyle.BorderBottom = BorderStyle.Thin; fitStyle.BorderLeft = BorderStyle.Thin; fitStyle.BorderTop = BorderStyle.Thin; fitStyle.BorderRight = BorderStyle.Thin; IFont fitFont = wb.CreateFont(); fitFont.FontHeight = 14 * 20; fitFont.FontName = "宋体"; fitStyle.SetFont(fitFont); #endregion #region 数字左对齐 ICellStyle numberStyle = wb.CreateCellStyle(); numberStyle.ShrinkToFit = true; numberStyle.BorderBottom = BorderStyle.Thin; numberStyle.BorderLeft = BorderStyle.Thin; numberStyle.BorderTop = BorderStyle.Thin; numberStyle.BorderRight = BorderStyle.Thin; numberStyle.Alignment = HorizontalAlignment.Right; //numberStyle.DataFormat= HSSFDataFormat.GetBuiltinFormat("0"); IFont numberFont = wb.CreateFont(); numberFont.FontHeight = 14 * 20; numberFont.FontName = "宋体"; numberStyle.SetFont(numberFont); #endregion #region 红色数字 ICellStyle warningStyle = wb.CreateCellStyle(); warningStyle.ShrinkToFit = true; warningStyle.BorderBottom = BorderStyle.Thin; numberStyle.BorderLeft = BorderStyle.Thin; numberStyle.BorderTop = BorderStyle.Thin; numberStyle.BorderRight = BorderStyle.Thin; IFont warningFont = wb.CreateFont(); warningFont.FontHeight = 14 * 20; warningFont.FontName = "宋体"; warningFont.Color = HSSFColor.Red.Index; warningStyle.SetFont(warningFont); #endregion #region 生产类型为返工时候,值为"灌",设置样式 ICellStyle guanStyle = wb.CreateCellStyle(); guanStyle.Alignment = HorizontalAlignment.Center; guanStyle.ShrinkToFit = true; IFont guanFont = wb.CreateFont(); guanFont.Color = (short)FontColor.Red; guanFont.FontName = "宋体"; guanFont.FontHeight = 15 * 20; guanStyle.SetFont(guanFont); #endregion #region 标题样式 IFont titleFont = wb.CreateFont(); titleFont.FontName = "宋体"; titleFont.Boldweight = (short)FontBoldWeight.Bold; titleFont.FontHeight = 24 * 20; //和行高一样? 也是20倍 ICellStyle titleStyle = wb.CreateCellStyle(); titleStyle.Alignment = HorizontalAlignment.Center; titleStyle.VerticalAlignment = VerticalAlignment.Center; titleStyle.SetFont(titleFont); #endregion IDataFormat format = wb.CreateDataFormat(); //cellsylenumber.DataFormat = format.GetFormat("0.00"); //获取不同的类型 var countGroup = lists.Where(m => m.FType != null).GroupBy(m => m.FType).Select(t => new { FType = t.Key, Count = t.Count() }); foreach (var item in countGroup) { var typedlists = lists.Where(n => n.FType == item.FType).ToList(); string previousBatch = string.Empty; double batchQuantity = 0; //string prevoousProductionModel = string.Empty; ISheet sheet = wb.CreateSheet(item.FType); sheet.ForceFormulaRecalculation = true; sheet.SetColumnWidth(0, 20); //6.设置列宽 SetColumnWidth(列的索引号从0开始, N * 256) 第二个参数的单位是1/256个字符宽度。例:将第四列宽度设置为了30个字符 //7.设置行高 Height的单位是1/20个点。例:设置高度为50个点 row0.Height = 50 * 20; sheet.SetColumnWidth(0, (int)19 * 256); sheet.SetColumnWidth(1, (int)14 * 256); sheet.SetColumnWidth(2, (int)11.5 * 256); sheet.SetColumnWidth(3, (int)4 * 256); sheet.SetColumnWidth(4, (int)13 * 256); sheet.SetColumnWidth(5, (int)25.35 * 256); sheet.SetColumnWidth(6, (int)6 * 256); sheet.SetColumnWidth(7, (int)19.49 * 256); sheet.SetColumnWidth(8, (int)18.2 * 256); sheet.SetColumnWidth(9, (int)5.35 * 256); sheet.SetColumnWidth(10, (int)9.92 * 256); sheet.SetColumnWidth(11, (int)6 * 256); sheet.SetColumnWidth(12, (int)7 * 256); sheet.SetColumnWidth(13, (int)18 * 256); //灌和做货 sheet.SetColumnWidth(14, 8 * 256); //差额 sheet.SetColumnWidth(15, 0 * 256); //第几次做货 sheet.SetColumnWidth(16, 15 * 256); //订单号 sheet.SetColumnWidth(17, 40 * 256); //唯一值 sheet.SetColumnWidth(18, 14 * 256); //安全编号 sheet.SetColumnWidth(19, 10 * 256); //样油重量 #region Logo ExportImgToExcel(sheet, (HSSFWorkbook)wb); #endregion #region 标题 IRow row0 = sheet.CreateRow(0); row0.Height = 30 * 20; //CellRangeAddress region = new CellRangeAddress(0, 0, 0, 13);//CellRangeAddress rg = new CellRangeAddress(j + 2, j + 2, 8, 11); //sheet.AddMergedRegion(region); ICell cell = row0.CreateCell(5); cell.SetCellValue(typedlists[0].FProductionDate.ToString("yyyy年MM月dd日") + typedlists[0].FType + "生产任务清单"); /*第一个参数:从第几行开始合并 第二个参数:到第几行结束合并 第三个参数:从第几列开始合并 第四个参数:到第几列结束合并 */ cell.CellStyle = titleStyle; #endregion #region 表头 IRow row1 = sheet.CreateRow(1); row1.Height = (short)20.5 * 20; var A = row1.CreateCell(0); A.SetCellValue("产品型号"); A.CellStyle = fitStyle; var B = row1.CreateCell(1); B.SetCellValue("产品批号"); B.CellStyle = fitStyle; var C = row1.CreateCell(2); C.SetCellValue("生产数量"); C.CellStyle = fitStyle; var D = row1.CreateCell(3); D.SetCellValue("小料"); D.CellStyle = fitStyle; var E = row1.CreateCell(4); E.SetCellValue("包装桶数"); E.CellStyle = fitStyle; var F = row1.CreateCell(5); F.SetCellValue("包装桶"); F.CellStyle = fitStyle; var G = row1.CreateCell(6); G.SetCellValue("客户代码"); G.CellStyle = fitStyle; var H = row1.CreateCell(7); H.SetCellValue("标签型号"); H.CellStyle = fitStyle; var I = row1.CreateCell(8); I.SetCellValue("备注"); I.CellStyle = fitStyle; var J = row1.CreateCell(9); J.SetCellValue("留样"); J.CellStyle = fitStyle; var K = row1.CreateCell(10); K.SetCellValue("合格证"); K.CellStyle = fitStyle; var L = row1.CreateCell(11); L.SetCellValue("接收人"); L.CellStyle = fitStyle; var M = row1.CreateCell(12); M.SetCellValue("残液"); M.CellStyle = fitStyle; var N = row1.CreateCell(13); N.SetCellValue(""); N.CellStyle = guanStyle;//灌不灌 var O = row1.CreateCell(14); O.SetCellValue("差额"); O.CellStyle = fitStyle; var P = row1.CreateCell(15); P.SetCellValue("生产次数"); P.CellStyle = numberStyle; var Q = row1.CreateCell(16); Q.SetCellValue("订单号"); Q.CellStyle = fitStyle; var R = row1.CreateCell(17); R.SetCellValue("唯一值"); R.CellStyle = fitStyle; var S = row1.CreateCell(18); S.SetCellValue("安全编号"); S.CellStyle = fitStyle; var T = row1.CreateCell(19); T.SetCellValue("样油重量(g)"); T.CellStyle = fitStyle; #endregion for (int j = 0; j < typedlists.Count; j++) { IRow row = sheet.CreateRow(j + 2); row.Height = (short)20.5 * 20; string currentBatch = typedlists[j].FBatchNo; var A0 = row.CreateCell(0); var B0 = row.CreateCell(1); var C0 = row.CreateCell(2); var D0 = row.CreateCell(3); var M0 = row.CreateCell(12); var O0 = row.CreateCell(14); if (currentBatch != previousBatch) { batchQuantity = Convert.ToDouble(typedlists[j].FQuantity) - Convert.ToDouble(typedlists[j].RowQuantity) + Convert.ToDouble(typedlists[j].FResidue); A0.SetCellValue(typedlists[j].FitemName); B0.SetCellValue(currentBatch); C0.SetCellValue(Convert.ToDouble(typedlists[j].FQuantity)); D0.SetCellValue(typedlists[j].FHasSmallMaterial); M0.SetCellValue(Convert.ToDouble(typedlists[j].FResidue.ToString("0.0"))); O0.CellStyle = fitStyle; } else { batchQuantity -= Convert.ToDouble(typedlists[j].RowQuantity); O0.CellStyle = warningStyle; } O0.SetCellValue(batchQuantity); A0.CellStyle = fitStyle; B0.CellStyle = fitStyle; C0.CellStyle = numberStyle; M0.CellStyle = numberStyle; D0.CellStyle = fitStyle; var E0 = row.CreateCell(4); E0.SetCellValue(typedlists[j].FPackage); E0.CellStyle = fitStyle; var F0 = row.CreateCell(5); F0.SetCellValue(typedlists[j].FBucketName); F0.CellStyle = fitStyle; var G0 = row.CreateCell(6); G0.SetCellValue(typedlists[j].FOrgID); G0.CellStyle = fitStyle; var H0 = row.CreateCell(7); H0.SetCellValue(typedlists[j].FLabel); H0.CellStyle = fitStyle; #region 备注字体设置 var I0 = row.CreateCell(8); string value = typedlists[j].FNote; I0.SetCellValue(value); ICellStyle noteStyle = wb.CreateCellStyle(); noteStyle.BorderBottom = BorderStyle.Thin; noteStyle.BorderLeft = BorderStyle.Thin; noteStyle.BorderTop = BorderStyle.Thin; IFont noteFont = wb.CreateFont(); noteFont.FontName = "宋体"; int dataLength = UniversalFunction.GetLength(value); if (dataLength >= 50) { noteFont.FontHeight = 7 * 20; } else if (dataLength >= 40) { noteFont.FontHeight = 8.5 * 20; } else if (dataLength >= 30) { noteFont.FontHeight = 10 * 20; } else if (dataLength >= 25) { noteFont.FontHeight = 12 * 20; } else { noteFont.FontHeight = 14 * 20; } noteStyle.SetFont(noteFont); I0.CellStyle = noteStyle; #endregion #region 留样开始3列不赋值(赋值空都会有个空格) ICellStyle blankStyle = wb.CreateCellStyle(); blankStyle.BorderLeft = BorderStyle.Thin; blankStyle.BorderTop = BorderStyle.Thin; blankStyle.BorderBottom = BorderStyle.Thin; var J0 = row.CreateCell(9); J0.CellStyle = blankStyle; var K0 = row.CreateCell(10); K0.CellStyle = blankStyle; var L0 = row.CreateCell(11); L0.CellStyle = blankStyle; #endregion string productionNumber = typedlists[j].ProductionNumber > 0 && typedlists[j].ProductionNumber <= 3 ? $"第{typedlists[j].ProductionNumber}次生产" : ""; string guan = typedlists[j].ProductionType == "返工" ? "灌" : ""; var N0 = row.CreateCell(13); N0.SetCellValue(guan + " " + productionNumber); N0.CellStyle = guanStyle;//灌不灌 //var P0 = row.CreateCell(15); P0.SetCellValue(); P0.CellStyle = fitStyle; // 第多少次生产和 灌写入同一个单元格,本单元格已设置宽度为0, var Q0 = row.CreateCell(16); Q0.SetCellValue(typedlists[j].FBillNo); Q0.CellStyle = fitStyle; var R0 = row.CreateCell(17); R0.SetCellValue(UniversalFunction.ToHexString(typedlists[j].RowHashValue)); R0.CellStyle = fitStyle; var S0 = row.CreateCell(18); S0.SetCellValue(typedlists[j].SafeCode); S0.CellStyle = fitStyle; var T0 = row.CreateCell(19); T0.SetCellValue(Convert.ToInt32(typedlists[j].PaintSampleTotal)); T0.CellStyle = numberStyle; previousBatch = currentBatch.Length != 0 ? currentBatch : previousBatch; } } FileStream fs = new FileStream(fName, FileMode.Create); //新建才不会报错 wb.Write(fs); //会自动关闭流文件 //fs.Flush(); fs.Close(); }
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 + "/My First Excel.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); } 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 + "/My First Excel.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++) { if (i == NumberOfRows - 1 && j == NumberOfCols - 1) { ReadExcelEnable_ExcelDataReader = false; } MyString.Add(Result.Tables[0].Rows[i][j].ToString()); } } } 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])); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText) { try { var invisibleColumnCount = dtSource.Columns.Cast <DataColumn>().Count(item => item.Caption == "invisible"); for (int i = 0; i < invisibleColumnCount; i++) { dtSource.Columns.RemoveAt(0); } HSSFWorkbook workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "Robin van Persie"; //填加xls文件作者信息 si.ApplicationName = "for downloading."; //填加xls文件创建程序信息 si.LastAuthor = "RvP"; //填加xls文件最后保存者信息 si.Comments = "RvP"; //填加xls文件作者信息 si.Title = "Title"; //填加xls文件标题信息 si.Subject = "Subject"; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { var headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; var font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { var headerRow = sheet.CreateRow(1); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; var font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = 2; } #endregion #region 填充内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook.Close();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } } catch (Exception ex) { LogBN.WriteLog(typeof(NpoiHelper), "导出Excel的方法MemoryStream Export 程序段的异常" + ex); return(null); } }
private static void exportExcel <T>(this List <T> data, string path) where T : class { if (data == null) { throw new ArgumentNullException("data"); } var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet("Sheet1"); var props = typeof(T).GetProperties(); #region build row0 var row0 = sheet.CreateRow(0); for (var i = 0; i < props.Length; i++) { var prop = props[i]; var attr = prop.GetCustomAttribute <DisplayAttribute>(); var name = attr != null ? attr.Name : prop.Name; if (name.Equals("#Ignore#")) { continue; } row0.CreateCell(i).SetCellValue(name); } #endregion var dataFormat = workbook.CreateDataFormat(); var cellStyle = workbook.CreateCellStyle(); #region build rows for (int i = 0; i < data.Count; i++) { var row = sheet.CreateRow(i + 1); for (var o = 0; o < props.Length; o++) { var prop = props[o]; var attr = prop.GetCustomAttribute <DisplayAttribute>(); var name = attr != null ? attr.Name : prop.Name; if (name.Equals("#Ignore#")) { continue; } var value = prop.GetValue(data[i]); var dataType = prop.PropertyType; if (value == null) { row.CreateCell(o); continue; } if (dataType == typeof(bool) || dataType == typeof(bool?)) { switch ((bool)value) { case true: row.CreateCell(o).SetCellValue("Yes"); break; default: row.CreateCell(o).SetCellValue("No"); break; } } else if (dataType == typeof(int) || dataType == typeof(int?)) { if (name == "Gender" || name == "Giới tính") { switch (value) { case 1: row.CreateCell(o).SetCellValue("Male"); break; case 0: row.CreateCell(o).SetCellValue("Female"); break; default: row.CreateCell(o).SetCellValue(string.Empty); break; } } else { var intValue = 0; if (int.TryParse(value.ToString(), out intValue)) { row.CreateCell(o).SetCellValue(intValue); } } } else if (dataType == typeof(long) || dataType == typeof(long?)) { long longValue = 0; if (long.TryParse(value.ToString(), out longValue)) { row.CreateCell(o).SetCellValue(longValue); } } else if (dataType == typeof(double) || dataType == typeof(double?)) { double doubleValue = 0; if (double.TryParse(value.ToString(), out doubleValue)) { row.CreateCell(o).SetCellValue(doubleValue); } } else if (dataType == typeof(decimal) || dataType == typeof(decimal?)) { double doubleValue = 0; if (double.TryParse(value.ToString(), out doubleValue)) { row.CreateCell(o).SetCellValue(doubleValue); } } else if (dataType == typeof(DateTime) || dataType == typeof(DateTime?)) { DateTime dateTimeValue; if (DateTime.TryParse(value.ToString(), out dateTimeValue)) { row.CreateCell(o).SetCellValue(dateTimeValue); if (name == "Birthday" || name == "Ngày sinh") { var birthdaystyle = workbook.CreateCellStyle(); row.Cells[o - 1].CellStyle = birthdaystyle; row.Cells[o - 1].CellStyle.DataFormat = dataFormat.GetFormat("dd-MM-yyyy"); } else { row.Cells[o - 1].CellStyle = cellStyle; row.Cells[o - 1].CellStyle.DataFormat = dataFormat.GetFormat("dd-MM-yyyy HH:mm:ss"); } } } else { if (dataType == typeof(String)) { if (value.ToString().Length > 32767) { value = value.ToString().Substring(0, 32767); } } row.CreateCell(o).SetCellValue(value.ToString()); } } } #endregion using (var stream = new MemoryStream()) { workbook.Write(stream); using (var fileStream = File.Create(path)) { stream.Seek(0, SeekOrigin.Begin); stream.CopyTo(fileStream); } } }
/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param> public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig) { for (int i = 0; i < dtSource.Columns.Count;) { bool IsExists = false; DataColumn column = dtSource.Columns[i]; for (int j = 0; j < excelConfig.ColumnEntity.Count; j++) { if (excelConfig.ColumnEntity[j].Column == column.ColumnName) { IsExists = true; break; } } if (!IsExists) { dtSource.Columns.Remove(column); } else { i++; } } HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "江苏华康"; //填加xls文件作者信息 si.ApplicationName = "江苏华康"; //填加xls文件创建程序信息 si.LastAuthor = "江苏华康"; //填加xls文件最后保存者信息 si.Comments = "江苏华康"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion #region 设置标题样式 ICellStyle headStyle = workbook.CreateCellStyle(); int[] arrColWidth = new int[dtSource.Columns.Count]; string[] arrColName = new string[dtSource.Columns.Count]; //列名 ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表 headStyle.Alignment = HorizontalAlignment.Center; // ------------------ if (excelConfig.Background != new Color()) { if (excelConfig.Background != new Color()) { headStyle.FillPattern = FillPattern.SolidForeground; headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background); } } IFont font = workbook.CreateFont(); font.FontHeightInPoints = excelConfig.TitlePoint; if (excelConfig.ForeColor != new Color()) { font.Color = GetXLColour(workbook, excelConfig.ForeColor); } font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 列头及样式 ICellStyle cHeadStyle = workbook.CreateCellStyle(); cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont cfont = workbook.CreateFont(); cfont.FontHeightInPoints = excelConfig.HeadPoint; cHeadStyle.SetFont(cfont); #endregion #region 设置内容单元格样式 foreach (DataColumn item in dtSource.Columns) { ICellStyle columnStyle = workbook.CreateCellStyle(); columnStyle.Alignment = HorizontalAlignment.Center; arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; arrColName[item.Ordinal] = item.ColumnName.ToString(); if (excelConfig.ColumnEntity != null) { ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName); if (columnentity != null) { arrColName[item.Ordinal] = columnentity.ExcelColumn; if (columnentity.Width != 0) { arrColWidth[item.Ordinal] = columnentity.Width; } if (columnentity.Background != new Color()) { if (columnentity.Background != new Color()) { columnStyle.FillPattern = FillPattern.SolidForeground; columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background); } } if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color()) { IFont columnFont = workbook.CreateFont(); columnFont.FontHeightInPoints = 10; if (columnentity.Font != null) { columnFont.FontName = columnentity.Font; } if (columnentity.Point != 0) { columnFont.FontHeightInPoints = columnentity.Point; } if (columnentity.ForeColor != new Color()) { columnFont.Color = GetXLColour(workbook, columnentity.ForeColor); } columnStyle.SetFont(font); } columnStyle.Alignment = getAlignment(columnentity.Alignment); } } arryColumStyle[item.Ordinal] = columnStyle; } if (excelConfig.IsAllSizeColumn) { #region 根据列中最长列的长度取得列宽 for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { if (arrColWidth[j] != 0) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } } #endregion } #endregion #region 填充数据 #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(0); if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------ } } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出 foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]); headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } #endregion } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); newCell.CellStyle = arryColumStyle[column.Ordinal]; string drValue = row[column].ToString(); SetCell(newCell, dateStyle, column.DataType, drValue); } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
HSSFWorkbook CreateWorkbook() { var workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("NPOI demo sheet"); //create styles IFont fontBold = workbook.CreateFont(); fontBold.Boldweight = (short)FontBoldWeight.Bold; ICellStyle headerStyle = workbook.CreateCellStyle(); headerStyle.Alignment = HorizontalAlignment.Center; headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightOrange.Index; headerStyle.FillPattern = FillPattern.SolidForeground; headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium; headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium; headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium; headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium; ICellStyle itemStyle = workbook.CreateCellStyle(); itemStyle.Alignment = HorizontalAlignment.Center; itemStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium; itemStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium; itemStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium; itemStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium; ICellStyle summaryStyle = workbook.CreateCellStyle(); summaryStyle.Alignment = HorizontalAlignment.Center; summaryStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; summaryStyle.FillPattern = FillPattern.SolidForeground; summaryStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium; summaryStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium; summaryStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium; summaryStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium; summaryStyle.SetFont(fontBold); //create header row var headerRow = sheet.CreateRow(1); var headerRow_Cell1 = headerRow.CreateCell(1); headerRow_Cell1.SetCellValue("Header 1"); headerRow_Cell1.CellStyle = headerStyle; var headerRow_Cell2 = headerRow.CreateCell(2); headerRow_Cell2.SetCellValue("Header 2"); headerRow_Cell2.CellStyle = headerStyle; var headerRow_Cell3 = headerRow.CreateCell(3); headerRow_Cell3.SetCellValue("Header 3"); headerRow_Cell3.CellStyle = headerStyle; var headerRow_Cell4 = headerRow.CreateCell(4); headerRow_Cell4.SetCellValue("Header 4"); headerRow_Cell4.CellStyle = headerStyle; int rowNo = 0; //create items rows for (var i = 1; i < 11; i++) { rowNo = i + 1; var itemRow = sheet.CreateRow(rowNo); var itemRow_Cell1 = itemRow.CreateCell(1); itemRow_Cell1.SetCellValue("Row [" + i + "] Cell [1]"); itemRow_Cell1.CellStyle = itemStyle; var itemRow_Cell2 = itemRow.CreateCell(2); itemRow_Cell2.SetCellValue("Row [" + i + "] Cell [2]"); itemRow_Cell2.CellStyle = itemStyle; var itemRow_Cell3 = itemRow.CreateCell(3); itemRow_Cell3.SetCellValue("Row [" + i + "] Cell [3]"); itemRow_Cell3.CellStyle = itemStyle; var itemRow_Cell4 = itemRow.CreateCell(4); itemRow_Cell4.SetCellValue(10 * rowNo); itemRow_Cell4.CellStyle = itemStyle; } //create summary row var summaryRow = sheet.CreateRow(++rowNo); var summaryRowCell = summaryRow.CreateCell(4); summaryRowCell.SetCellType(CellType.Formula); summaryRowCell.CellFormula = "sum(e3:e12)"; summaryRowCell.CellStyle = summaryStyle; //define column width sheet.SetColumnWidth(1, 4000); sheet.SetColumnWidth(2, 4000); sheet.SetColumnWidth(3, 4000); sheet.SetColumnWidth(4, 4000); return(workbook); }
public static string FmtExcel(DateTime dt, string fileName) { HSSFWorkbook workBook = new HSSFWorkbook(); ISheet st = workBook.CreateSheet(dt.ToString("yyyy.M.d")); //先创建cellstyle ICellStyle r1s = workBook.CreateCellStyle(); r1s.FillForegroundColor = HSSFColor.Grey40Percent.Index; r1s.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; r1s.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; r1s.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; r1s.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; IFont font = workBook.CreateFont(); font.IsBold = true; font.FontName = "宋体"; font.FontHeightInPoints = 14; r1s.SetFont(font); r1s.FillPattern = FillPattern.SolidForeground; ICellStyle r2s = workBook.CreateCellStyle(); r2s.FillForegroundColor = HSSFColor.LemonChiffon.Index; r2s.FillPattern = FillPattern.SolidForeground; r2s.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; r2s.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; r2s.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; r2s.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; IFont font2 = workBook.CreateFont(); font2.IsBold = true; font2.FontHeightInPoints = 12; font2.FontName = "宋体"; r2s.SetFont(font2); ICellStyle r3s = workBook.CreateCellStyle(); r3s.FillForegroundColor = HSSFColor.Grey25Percent.Index; r3s.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; r3s.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; r3s.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; r3s.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; r3s.FillPattern = FillPattern.SolidForeground; IFont font3 = workBook.CreateFont(); font3.FontName = "宋体"; font3.IsBold = true; font3.FontHeightInPoints = 10; r3s.SetFont(font3); ICellStyle rfs = workBook.CreateCellStyle(); //rfs.FillForegroundColor = HSSFColor.LemonChiffon.Index; //rfs.FillPattern = FillPattern.SolidForeground; rfs.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; rfs.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; rfs.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; rfs.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; ICellStyle rf0s = workBook.CreateCellStyle(); rf0s.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; rf0s.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; rf0s.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; rf0s.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; IFont frfs = workBook.CreateFont(); frfs.FontName = "宋体"; IFont frf0s = workBook.CreateFont(); frf0s.FontName = "宋体"; frfs.FontHeightInPoints = 10; frf0s.FontHeightInPoints = 10; frf0s.IsBold = true; rf0s.SetFont(frf0s); rfs.SetFont(frfs); //榆社化石博物馆传感监测典型数据统计(2017 / 8 / 6) IRow r0 = st.CreateRow(0); ICellStyle r0s = workBook.CreateCellStyle(); IFont font0 = workBook.CreateFont(); font0.IsBold = true; font0.FontHeightInPoints = 18; font0.FontName = "黑体"; r0s.SetFont(font0); ICell cr0 = r0.CreateCell(0); cr0.SetCellValue("榆社化石博物馆传感监测典型数据统计(" + dt.ToString("yyyy/M/d") + ")"); cr0.CellStyle = r0s; CellRangeAddress crar0 = new CellRangeAddress(0, 0, 0, 5); st.AddMergedRegion(crar0); st.CreateRow(1).Height = 200; foreach (var g1 in ConfigData.GroupConfigRoot.GroupConfig1s) { IRow row = st.CreateRow(st.LastRowNum + 1); ICell c0 = row.CreateCell(0); c0.SetCellValue(g1.Name); ICell c0sp = row.CreateCell(1); c0sp.SetCellValue(""); ICell c1 = row.CreateCell(2); c1.SetCellValue("数值"); ICell c2 = row.CreateCell(3); c2.SetCellValue("节点号"); ICell c3 = row.CreateCell(4); c3.SetCellValue("时间"); ICell c4 = row.CreateCell(5); c4.SetCellValue("备注"); CellRangeAddress cra = new CellRangeAddress(c0.RowIndex, c0.RowIndex, c0.ColumnIndex, c0.ColumnIndex + 1); st.AddMergedRegion(cra); c0sp.CellStyle = r1s; c0.CellStyle = r1s; c1.CellStyle = r1s; c2.CellStyle = r1s; c3.CellStyle = r1s; c4.CellStyle = r1s; foreach (var g2 in g1.GroupConfigs) { IRow row2 = st.CreateRow(st.LastRowNum + 1); ICell c20 = row2.CreateCell(0); c20.CellStyle = r2s; c20.SetCellValue(" " + g2.Name); ICell c21 = row2.CreateCell(1); c21.CellStyle = r2s; c21.SetCellValue(""); ICell c22 = row2.CreateCell(2); c22.CellStyle = r2s; c22.SetCellValue(""); ICell c23 = row2.CreateCell(3); c23.CellStyle = r2s; c23.SetCellValue(""); ICell c24 = row2.CreateCell(4); c24.CellStyle = r2s; c24.SetCellValue(""); ICell c25 = row2.CreateCell(5); c25.CellStyle = r2s; c25.SetCellValue(""); CellRangeAddress cra20 = new CellRangeAddress(c20.RowIndex, c20.RowIndex, 0, 1); st.AddMergedRegion(cra20); foreach (var g3 in g2.GroupConfigs) { IRow row3 = st.CreateRow(st.LastRowNum + 1); ICell c30 = row3.CreateCell(0); c30.SetCellValue(" " + g3.Name); c30.CellStyle = r3s; ICell c31 = row3.CreateCell(1); c31.SetCellValue(""); c31.CellStyle = r3s; ICell c32 = row3.CreateCell(2); c32.SetCellValue(""); c32.CellStyle = r3s; ICell c33 = row3.CreateCell(3); c33.SetCellValue(""); c33.CellStyle = r3s; ICell c34 = row3.CreateCell(4); c34.SetCellValue(""); c34.CellStyle = r3s; ICell c35 = row3.CreateCell(5); c35.SetCellValue(""); c35.CellStyle = r3s; bool ir1 = true; foreach (var s in g3.Sensors) { foreach (var f in s.Model.Fields) { if (!f.Realtime) { continue; } IRow rf = st.CreateRow(st.LastRowNum + 1); ICell cf0 = rf.CreateCell(0); cf0.CellStyle = rf0s; if (ir1) { ir1 = false; cf0.SetCellValue("包括:"); } else { cf0.SetCellValue(""); } ICell cf1 = rf.CreateCell(1); cf1.SetCellValue(f.Name + "(" + f.Unit + ")"); cf1.CellStyle = rfs; ICell cf2 = rf.CreateCell(2); cf2.SetCellValue(f.GetValue(dt)); cf2.CellStyle = rfs; ICell cf3 = rf.CreateCell(3); cf3.SetCellValue(s.NodeId.ToString()); cf3.CellStyle = rfs; ICell cf4 = rf.CreateCell(4); cf4.SetCellValue(dt.ToString("H:mm")); cf4.CellStyle = rfs; ICell cf5 = rf.CreateCell(5); cf5.SetCellValue(s.Comment); cf5.CellStyle = rfs; } } } } } //自动宽度 st.AutoSizeColumn(1); st.AutoSizeColumn(2); st.AutoSizeColumn(3); st.AutoSizeColumn(4); st.SetColumnWidth(0, 4000); st.SetColumnWidth(5, 9000); //16_00榆社化石博物馆传感监测典型数据统计表2017.8.6.xls using (FileStream fs = File.OpenWrite(fileName)) { workBook.Write(fs); } return(fileName); }
protected void btn_Marker_Export_Click(object sender, EventArgs e) { //string sql = "select PCON_BCODE,PCON_SCH,(PCON_PJID+'/'+PCON_PJNAME) AS PCON_PJNAME,d.BR_KPJE,PCON_ENGNAME,PCON_YFK,PCON_JINE,PCON_SPJE,PCON_STATE,PCON_JECHG,PCON_QTCHG,PCON_FHSJ,PCON_ERROR,PCON_CUSTMNAME,PCON_BALANCEACNT,CASE WHEN convert(decimal(18,2),PCON_BALANCEACNT)=0 THEN PCON_JINE ELSE PCON_BALANCEACNT END AS PCON_HTZJ,PCON_YZHTH,OTHER_MONUNIT,PCON_MONUNIT,PCON_KPJE,PCON_RESPONSER,PCON_NOTE,CR_PSZT,c.* from TBPM_CONPCHSINFO as a left join TBCR_CONTRACTREVIEW as b on a.PCON_REVID=b.CR_ID left join TBPM_DETAIL as c on a.PCON_BCODE=c.CM_CONTR left join TBPM_BUSBILLRECORD as d on a.PCON_BCODE=d.BR_HTBH order by PCON_BCODE"; string sql = "select PCON_CUSTMNAME,PCON_BCODE,TSA_ID,PCON_YZHTH,b.CM_PROJ,b.CM_ENGNAME,b.CM_MAP,b.CM_MATERIAL,b.CM_PRICE,b.CM_PRICE,b.CM_COUNT,b.CM_NUMBER,b.CM_UNIT,b.CM_WEIGHT,b.CM_ALL,b.CM_SIGN,b.CM_JIAO,b.CM_DUTY,b.CM_NOTE,b.CM_DTSJ,b.CM_CPRK,b.CM_TZFH,b.CM_CKSJ,b.CM_DFSM,PCON_YFK,CASE WHEN convert(decimal(18,2),PCON_BALANCEACNT)=0 THEN PCON_JINE ELSE PCON_BALANCEACNT END AS PCON_HTZJ,PCON_JINE,BR_KPJE from TBPM_CONPCHSINFO as a left join TBPM_DETAIL as b on a.PCON_BCODE=b.CM_CONTR left join (select BR_HTBH, sum(BR_KPJE)as BR_KPJE from TBPM_BUSBILLRECORD group by BR_HTBH) as d on a.PCON_BCODE=d.BR_HTBH order by PCON_BCODE"; DataTable dt = DBCallCommon.GetDTUsingSqlText(sql); List <string> list = new List <string>(); string filename = "销售合同导出" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename))); HttpContext.Current.Response.Clear(); //1.读取Excel到FileStream using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("销售合同导出模板.xls"))) { IWorkbook wk = new HSSFWorkbook(fs); ISheet sheet0 = wk.GetSheetAt(0); #region 写入数据 IFont font1 = wk.CreateFont(); font1.FontName = "仿宋"; //字体 font1.FontHeightInPoints = 11; //字号 ICellStyle cells = wk.CreateCellStyle(); cells.Alignment = HorizontalAlignment.CENTER; cells.SetFont(font1); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; IRow row = sheet0.CreateRow(i + 3); row.CreateCell(0).SetCellValue(i + 1); row.CreateCell(1).SetCellValue(dr["PCON_CUSTMNAME"].ToString()); row.CreateCell(2).SetCellValue(dr["PCON_BCODE"].ToString()); row.CreateCell(3).SetCellValue(dr["TSA_ID"].ToString()); row.CreateCell(4).SetCellValue(dr["PCON_YZHTH"].ToString()); row.CreateCell(5).SetCellValue(dr["CM_PROJ"].ToString()); row.CreateCell(6).SetCellValue(dr["CM_ENGNAME"].ToString()); row.CreateCell(7).SetCellValue(dr["CM_MAP"].ToString()); row.CreateCell(8).SetCellValue(dr["CM_MATERIAL"].ToString()); row.CreateCell(9).SetCellValue(dr["CM_PRICE"].ToString()); row.CreateCell(10).SetCellValue(dr["CM_COUNT"].ToString()); row.CreateCell(11).SetCellValue(dr["CM_NUMBER"].ToString()); row.CreateCell(12).SetCellValue(dr["CM_UNIT"].ToString()); row.CreateCell(13).SetCellValue(dr["CM_WEIGHT"].ToString()); row.CreateCell(14).SetCellValue(dr["CM_ALL"].ToString()); row.CreateCell(15).SetCellValue(dr["CM_SIGN"].ToString()); row.CreateCell(16).SetCellValue(dr["CM_JIAO"].ToString()); row.CreateCell(17).SetCellValue(dr["CM_DUTY"].ToString()); row.CreateCell(18).SetCellValue(dr["CM_NOTE"].ToString()); row.CreateCell(19).SetCellValue(dr["CM_DTSJ"].ToString()); row.CreateCell(20).SetCellValue(dr["CM_CPRK"].ToString()); row.CreateCell(21).SetCellValue(dr["CM_TZFH"].ToString()); row.CreateCell(22).SetCellValue(dr["CM_CKSJ"].ToString()); row.CreateCell(23).SetCellValue(dr["CM_DFSM"].ToString()); if (!list.Contains(dr["PCON_BCODE"].ToString())) { row.CreateCell(24).SetCellValue(string.Format("{0:N2}", Convert.ToDecimal(dr["PCON_YFK"].ToString()) / Convert.ToDecimal(Convert.ToDecimal(dr["PCON_HTZJ"].ToString()) == 0 ? "1" : dr["PCON_HTZJ"].ToString()) * 100) + "%"); row.CreateCell(25).SetCellValue(dr["PCON_YFK"].ToString()); row.CreateCell(26).SetCellValue(string.Format("{0:N2}", (1 - Convert.ToDecimal(dr["PCON_YFK"].ToString()) / Convert.ToDecimal(Convert.ToDecimal(dr["PCON_HTZJ"].ToString()) == 0 ? "1" : dr["PCON_HTZJ"].ToString())) * 100) + "%"); row.CreateCell(27).SetCellValue((Convert.ToDecimal(dr["PCON_JINE"].ToString()) - Convert.ToDecimal(dr["PCON_YFK"].ToString())).ToString()); row.CreateCell(28).SetCellValue(dr["BR_KPJE"].ToString()); list.Add(dr["PCON_BCODE"].ToString()); } else { row.CreateCell(24).SetCellValue(""); row.CreateCell(25).SetCellValue(""); row.CreateCell(26).SetCellValue(""); row.CreateCell(27).SetCellValue(""); row.CreateCell(28).SetCellValue(""); } for (int j = 0; j < 29; j++) { row.Cells[j].CellStyle = cells; } } #endregion sheet0.ForceFormulaRecalculation = true; MemoryStream file = new MemoryStream(); wk.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); } }
//Datatable导出Excel public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName) { HSSFWorkbook workbook = new HSSFWorkbook(); try { ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { //ILog log = LogManager.GetLogger("Exception Log"); //log.Error(ex.Message + Environment.NewLine + ex.StackTrace); //记录AuditTrail // CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex); // MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { workbook = null; } }
/// <summary> /// 导出合并单元格的Excel /// </summary> /// <param name="dt"></param> /// <param name="savePath"></param> /// <param name="dicMerge">合并数据集的格式</param> /// <returns></returns> public static bool ExportDataTable2MergeCellsInExcelByNPOI(DataTable dt, string savePath, Dictionary <string, Dictionary <string, int> > dicMerge) { System.IO.FileStream file; try { file = new System.IO.FileStream(savePath, FileMode.Create); } catch { return(false); } try { HSSFWorkbook workbook = CreateWorkBook(); ISheet sheetNew; if (dt.TableName == "") { sheetNew = workbook.CreateSheet(); } else { sheetNew = workbook.CreateSheet(dt.TableName); } //列名行 NPOI.SS.UserModel.IRow row = sheetNew.CreateRow(0); for (int j = 0; j < dt.Columns.Count; j++) { row.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName); } //数据行 for (int i = 1; i <= dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow pRow = sheetNew.CreateRow(i); for (int k = 0; k < dt.Columns.Count; k++) { string cellValue = dt.Rows[i - 1].ItemArray[k].ToString(); ICell curCell = pRow.CreateCell(k); curCell.SetCellValue(cellValue); } } int startRow = 1; //合并路名列 foreach (KeyValuePair <string, Dictionary <string, int> > item in dicMerge) { string roadName = item.Key; int sameRoadRowsCount = GetRowsFromDic(item.Value, startRow, ref sheetNew); CellRangeAddress range = new CellRangeAddress(startRow, startRow + sameRoadRowsCount - 1, 0, 0); sheetNew.AddMergedRegion(range); //((HSSFSheet)sheetNew).SetEnclosedBorderOfRegion(range,NPOI.SS.UserModel.BorderStyle.Dotted, NPOI.HSSF.Util.HSSFColor.Red.Index); startRow += sameRoadRowsCount; } ICellStyle style = workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; workbook.Write(file);//如果存在同名文件夹,那么旧文件夹自动被覆盖! return(true); } finally { file.Close(); } }
/// <summary> /// 导出Excel格式的充值码信息 /// </summary> /// <param name="path">导出文件的路径(服务器端)</param> /// <param name="orgid">机构id</param> /// <param name="rsid">充值码设置项的id</param> /// <returns></returns> public string RechargeCode4Excel(string path, int orgid, int rsid) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //xml配置文件 XmlDocument xmldoc = new XmlDocument(); string confing = WeiSha.Common.App.Get["ExcelInputConfig"].VirtualPath + "充值码.xml"; xmldoc.Load(WeiSha.Common.Server.MapPath(confing)); XmlNodeList nodes = xmldoc.GetElementsByTagName("item"); //创建工作簿对象 Song.Entities.RechargeSet rs = Gateway.Default.From <RechargeSet>().Where(RechargeSet._.Rs_ID == rsid).ToFirst <RechargeSet>(); ISheet sheet = hssfworkbook.CreateSheet(rs.Rs_Theme); //sheet.DefaultColumnWidth = 30; //创建数据行对象 IRow rowHead = sheet.CreateRow(0); for (int i = 0; i < nodes.Count; i++) { rowHead.CreateCell(i).SetCellValue(nodes[i].Attributes["Column"].Value); } //生成数据行 ICellStyle style_size = hssfworkbook.CreateCellStyle(); style_size.WrapText = true; WhereClip wc = RechargeCode._.Org_ID == orgid; if (rsid >= 0) { wc.And(RechargeCode._.Rs_ID == rsid); } RechargeCode[] rcodes = Gateway.Default.From <RechargeCode>().Where(wc).OrderBy(RechargeCode._.Rc_CrtTime.Desc).ToArray <RechargeCode>(); for (int i = 0; i < rcodes.Length; i++) { IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < nodes.Count; j++) { Type type = rcodes[i].GetType(); System.Reflection.PropertyInfo propertyInfo = type.GetProperty(nodes[j].Attributes["Field"].Value); //获取指定名称的属性 object obj = propertyInfo.GetValue(rcodes[i], null); if (obj != null) { string format = nodes[j].Attributes["Format"] != null ? nodes[j].Attributes["Format"].Value : ""; string datatype = nodes[j].Attributes["DataType"] != null ? nodes[j].Attributes["DataType"].Value : ""; string defvalue = nodes[j].Attributes["DefautValue"] != null ? nodes[j].Attributes["DefautValue"].Value : ""; string value = ""; switch (datatype) { case "date": DateTime tm = Convert.ToDateTime(obj); value = tm > DateTime.Now.AddYears(-100) ? tm.ToString(format) : ""; break; default: value = obj.ToString(); break; } if (defvalue.Trim() != "") { foreach (string s in defvalue.Split('|')) { string h = s.Substring(0, s.IndexOf("=")); string f = s.Substring(s.LastIndexOf("=") + 1); if (value.ToLower() == h.ToLower()) { value = f; } } } row.CreateCell(j).SetCellValue(value); } } } FileStream file = new FileStream(path, FileMode.Create); hssfworkbook.Write(file); file.Close(); return(path); }
static void Main(string[] args) { Console.WriteLine(DateTime.Now + " Start"); //查詢的值 var strEnter = Console.ReadLine(); if (strEnter == "" && args[0] == "") { Console.WriteLine("請輸入值"); return; } if (strEnter == "") { strEnter = args[0]; } //var strEnter = "T0001"; //連線字串 SqlConnection conn = new SqlConnection(); string strconn = ConfigurationManager.ConnectionStrings["PracticeConnectionString"].ConnectionString; conn.ConnectionString = strconn; //設定EXCEL IWorkbook workbook = new HSSFWorkbook(); try { using (conn) { //SP名稱 var procedure = "spSearch"; //SP參數 var values = new { Account = strEnter }; //執行結果 var results = conn.Query <Member>(procedure, values, commandType: CommandType.StoredProcedure).ToList(); if (results.Count == 0) { Console.WriteLine("資料不存在"); return; } //EXCEL sheet名稱 ISheet sheet = workbook.CreateSheet("Member"); //設定欄位名稱的cell style var style = workbook.CreateCellStyle(); var font = workbook.CreateFont(); font.Underline = FontUnderlineType.Single; font.Color = HSSFColor.White.Index; style.SetFont(font); //style.FillForegroundColor = HSSFColor.BlueGrey.Index; style.FillPattern = FillPattern.SolidForeground; var i = 0; //跑表頭,根據欄位屬性來設定 foreach (var item in results) { var j = 0; var row = sheet.CreateRow(i); if (i == 0) { foreach (var property in item.GetType().GetProperties()) { var cell = row.CreateCell(j); cell.SetCellValue(property.Name); cell.CellStyle = style; sheet.AutoSizeColumn(j); j++; } i++; j = 0; row = sheet.CreateRow(i); } //跑內容 foreach (var property in item.GetType().GetProperties()) { row.CreateCell(j).SetCellValue(property.GetValue(item, null)?.ToString()); j++; } i++; } //若有檔案刪除定並重寫 File.Delete("D:/tmp/test.xlsx"); FileStream file = new FileStream("D:/tmp/test.xls", FileMode.Create); workbook.Write(file); file.Close(); } } catch (Exception) { throw; } Console.WriteLine(DateTime.Now + " 成功"); Console.ReadKey(); }
/// <summary> /// 导出Excel内部功能实现,提供给前面三个方法进行调用 /// </summary> /// <typeparam name="T">导出Excel的实体集合</typeparam> /// <param name="list">需要导出Excel的List集合</param> /// <param name="excelheader">Excel表头</param> /// <param name="listGather">将list集合进行汇总后的信息</param> /// <param name="excludeNoUsePropety">排除T实体中不需要的属性</param> /// <returns></returns> private static IWorkbook ExportToWorkBookExcel <T>(IList <T> list, List <string> excelheader, IList <T> listGather, List <string> excludeNoUsePropety) where T : class, new() { IWorkbook iWorkbook = new HSSFWorkbook(); ISheet isSheet = iWorkbook.CreateSheet(); var typeOf = typeof(T); var getProperties = typeOf.GetProperties(); //第一步:默认首先设置表头信息 IRow headRow = isSheet.CreateRow(0); for (int i = 0; i < excelheader.Count; i++) { headRow.CreateCell(i, CellType.STRING).SetCellValue(excelheader[i]); } //第二步:遍历所有的读取出来的数据添加到Excel中,添加集合 var cellStyle = (HSSFCellStyle)iWorkbook.CreateCellStyle(); var format = (HSSFDataFormat)iWorkbook.CreateDataFormat(); for (int i = 0; i < list.Count; i++) { IRow temRow = isSheet.CreateRow(i + 1); //遍历一行的每一个单元格,填写内容到单元格中 int num = 0; //控制在第几行显示第几个数据 //得到显示或者不显示的字段(整个实体类型的集合),然后循环去显示 for (int j = 0; j < getProperties.Length; j++) { var proName = getProperties[j].Name; if (excludeNoUsePropety.Count != 0) { if (excludeNoUsePropety.Contains(proName)) { continue; } } try { var newCell = temRow.CreateCell(num++); var vName = typeOf.GetProperty(proName).GetValue(list[i], null); if (vName != null) { Type type = vName.GetType(); //增加数据类型转换,int和decimal转换后在Excel里面显示正常 switch (type.FullName) { case "System.Int32": newCell.SetCellValue(Convert.ToInt32(vName.ToString())); cellStyle.DataFormat = format.GetFormat("0"); newCell.CellStyle = cellStyle; break; case "System.Decimal": newCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); newCell.SetCellValue(Convert.ToDouble(vName.ToString())); break; default: newCell.SetCellValue(vName.ToString()); break; } } else { newCell.SetCellValue(""); } } catch (Exception exception) { throw new Exception("导出Excel出现错误了,错误原因:" + exception.Message); } } } //第三步:判断是否含有汇总信息,如果含有汇总信息,则显示在Excel的最下面,如果没有汇总信息,则直接返回 if (listGather.Count != 0) { IRow footRow = isSheet.CreateRow(list.Count + 1); //创建最后一行用来存放汇总信息 int num = 0; //控制在第几行显示第几个数据 for (int j = 0; j < getProperties.Length; j++) { var properName = getProperties[j].Name; if (excludeNoUsePropety.Count != 0) //证明需要除掉传递进来不需要的属性 { if (excludeNoUsePropety.Contains(properName)) { continue; } } var footCell = footRow.CreateCell(num++); var vName = typeOf.GetProperty(properName).GetValue(listGather[0], null); if (vName != null) { Type type = vName.GetType(); //增加数据类型转换 switch (type.FullName) { case "System.Int32": footCell.SetCellValue(Convert.ToInt32(vName.ToString())); cellStyle.DataFormat = format.GetFormat("0"); footCell.CellStyle = cellStyle; break; case "System.Decimal": footCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); footCell.SetCellValue(Convert.ToDouble(vName.ToString())); break; default: footCell.SetCellValue(vName.ToString()); break; } } else { footCell.SetCellValue(""); } } } return(iWorkbook); }
public void setSheet(HSSFWorkbook newExcel, HSSFSheet sheet, List <LL_Table> dt, string title) { #region 设置行宽,列高 sheet.SetColumnWidth(0, 30 * 256); sheet.SetColumnWidth(1, 30 * 256); sheet.SetColumnWidth(2, 30 * 256); sheet.SetColumnWidth(3, 30 * 256); sheet.SetColumnWidth(4, 30 * 256); sheet.SetColumnWidth(5, 30 * 256); sheet.SetColumnWidth(6, 30 * 256); sheet.SetColumnWidth(7, 30 * 256); sheet.SetColumnWidth(8, 30 * 256); sheet.SetColumnWidth(9, 30 * 256); sheet.SetColumnWidth(10, 30 * 256); sheet.SetColumnWidth(11, 30 * 256); sheet.SetColumnWidth(12, 30 * 256); sheet.DefaultRowHeight = 15 * 20; #endregion #region 设置字体 HSSFFont font_title = (HSSFFont)newExcel.CreateFont(); font_title.FontHeightInPoints = 10; HSSFFont font_name = (HSSFFont)newExcel.CreateFont(); font_name.FontHeightInPoints = 7; font_name.IsBold = true; HSSFFont font_data = (HSSFFont)newExcel.CreateFont(); font_data.FontHeightInPoints = 7; #endregion #region 设置样式 //1、标题的样式 HSSFCellStyle style_title = (HSSFCellStyle)newExcel.CreateCellStyle(); style_title.Alignment = HorizontalAlignment.Center; style_title.VerticalAlignment = VerticalAlignment.Center; style_title.SetFont(font_title); //2、字段名的样式 HSSFCellStyle style_name = (HSSFCellStyle)newExcel.CreateCellStyle(); style_name.Alignment = HorizontalAlignment.Center; style_name.VerticalAlignment = VerticalAlignment.Center; style_name.SetFont(font_name); style_name.BorderTop = BorderStyle.Thin; style_name.BorderBottom = BorderStyle.Thin; style_name.BorderLeft = BorderStyle.Thin; style_name.BorderRight = BorderStyle.Thin; //3、批次的样式 HSSFCellStyle style_batch = (HSSFCellStyle)newExcel.CreateCellStyle(); style_batch.Alignment = HorizontalAlignment.Center; style_batch.VerticalAlignment = VerticalAlignment.Center; style_batch.FillPattern = FillPattern.SolidForeground; style_batch.FillForegroundColor = HSSFColor.Grey40Percent.Index; style_batch.SetFont(font_data); style_batch.BorderTop = BorderStyle.Thin; style_batch.BorderBottom = BorderStyle.Thin; style_batch.BorderLeft = BorderStyle.Thin; style_batch.BorderRight = BorderStyle.Thin; //4、数据的样式 HSSFCellStyle style_data = (HSSFCellStyle)newExcel.CreateCellStyle(); style_data.Alignment = HorizontalAlignment.Center; style_data.VerticalAlignment = VerticalAlignment.Center; style_data.SetFont(font_data); style_data.BorderTop = BorderStyle.Thin; style_data.BorderBottom = BorderStyle.Thin; style_data.BorderLeft = BorderStyle.Thin; style_data.BorderRight = BorderStyle.Thin; #endregion #region 设置内容 //第一行 标题 HSSFRow row_title = (HSSFRow)sheet.CreateRow(0); HSSFCell cell_title = (HSSFCell)row_title.CreateCell(0); cell_title.SetCellValue(title); cell_title.CellStyle = style_title; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 12)); //合并单元格(起始行,结束行,起始列,结束列) //第二行 字段名 HSSFRow row_name = (HSSFRow)sheet.CreateRow(1); HSSFCell cell_name_1 = (HSSFCell)row_name.CreateCell(0); cell_name_1.SetCellValue("序号"); cell_name_1.CellStyle = style_name; HSSFCell cell_name_2 = (HSSFCell)row_name.CreateCell(1); cell_name_2.SetCellValue("学号"); cell_name_2.CellStyle = style_name; HSSFCell cell_name_3 = (HSSFCell)row_name.CreateCell(2); cell_name_3.SetCellValue("班级"); cell_name_3.CellStyle = style_name; HSSFCell cell_name_4 = (HSSFCell)row_name.CreateCell(3); cell_name_4.SetCellValue("姓名"); cell_name_4.CellStyle = style_name; HSSFCell cell_name_5 = (HSSFCell)row_name.CreateCell(4); cell_name_5.SetCellValue("节假日去向"); cell_name_5.CellStyle = style_name; HSSFCell cell_name_6 = (HSSFCell)row_name.CreateCell(5); cell_name_6.SetCellValue("离校时间"); cell_name_6.CellStyle = style_name; HSSFCell cell_name_7 = (HSSFCell)row_name.CreateCell(6); cell_name_7.SetCellValue("返校时间"); cell_name_7.CellStyle = style_name; HSSFCell cell_name_8 = (HSSFCell)row_name.CreateCell(7); cell_name_8.SetCellValue("离校方式"); cell_name_8.CellStyle = style_name; HSSFCell cell_name_9 = (HSSFCell)row_name.CreateCell(8); cell_name_9.SetCellValue("返校方式"); cell_name_9.CellStyle = style_name; HSSFCell cell_name_10 = (HSSFCell)row_name.CreateCell(9); cell_name_10.SetCellValue("离校去向地址"); cell_name_10.CellStyle = style_name; HSSFCell cell_name_11 = (HSSFCell)row_name.CreateCell(10); cell_name_11.SetCellValue("联系人"); cell_name_11.CellStyle = style_name; HSSFCell cell_name_12 = (HSSFCell)row_name.CreateCell(11); cell_name_12.SetCellValue("联系方式"); cell_name_12.CellStyle = style_name; HSSFCell cell_name_13 = (HSSFCell)row_name.CreateCell(12); cell_name_13.SetCellValue("签名确认"); cell_name_13.CellStyle = style_name; //数据 int n = 2; int i = 1; foreach (LL_Table item in dt) { HSSFRow row = (HSSFRow)sheet.CreateRow(n++);//写入行 row.CreateCell(0).SetCellValue(i++); row.CreateCell(1).SetCellValue(item.ST_Num); row.CreateCell(2).SetCellValue(item.ST_Class); row.CreateCell(3).SetCellValue(item.ST_Name); row.CreateCell(4).SetCellValue(item.ST_Go); row.CreateCell(5).SetCellValue(item.TimeLeave); row.CreateCell(6).SetCellValue(item.TimeBack); row.CreateCell(7).SetCellValue(item.LeaveWay); row.CreateCell(8).SetCellValue(item.BackWay); row.CreateCell(9).SetCellValue(item.Address); row.CreateCell(10).SetCellValue(item.Contact); row.CreateCell(11).SetCellValue(item.Tel); row.CreateCell(12).SetCellValue(""); foreach (ICell cell in row) { if (cell.ColumnIndex == 0) { cell.CellStyle = style_batch; } else { cell.CellStyle = style_data; } } } #endregion }
public ActionResult ExportStats(Schools school, UserGroup ugroup, int year) { var stats = new ClassStatistics { school = school, usergroup = ugroup, year = year }; stats.CalculateStats(repository); var ms = new MemoryStream(); using (var fs = new FileStream( AppDomain.CurrentDomain.BaseDirectory + "/Content/templates/NPOITemplate.xls", FileMode.Open, FileAccess.Read, FileShare.Read)) { var templateWorkbook = new HSSFWorkbook(fs, true); var sheet = templateWorkbook.CreateSheet(school.ToString()); // create fonts var boldStyle = templateWorkbook.CreateCellStyle(); var boldFont = templateWorkbook.CreateFont(); boldFont.Boldweight = (short)FontBoldWeight.BOLD; boldStyle.SetFont(boldFont); var rowcount = 0; var row = sheet.CreateRow(rowcount++); var colcount = 0; // show general stats first row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Malaysian")); colcount += 2; row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Foreigners")); colcount += 2; row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("SubTotal")); colcount += 2; row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Total")); row = sheet.CreateRow(rowcount++); for (int i = 0; i < 3; i++) { row.CreateCell(i * 2).SetCellValue(SecurityElement.Escape("M")); row.CreateCell(i * 2 + 1).SetCellValue(SecurityElement.Escape("F")); } row = sheet.CreateRow(rowcount++); row.CreateCell(0, CellType.NUMERIC).SetCellValue(stats.msian_male); row.CreateCell(1, CellType.NUMERIC).SetCellValue(stats.msian_female); row.CreateCell(2, CellType.NUMERIC).SetCellValue(stats.foreign_male); row.CreateCell(3, CellType.NUMERIC).SetCellValue(stats.foreign_female); row.CreateCell(4, CellType.NUMERIC).SetCellValue(stats.msian_male + stats.foreign_male); row.CreateCell(5, CellType.NUMERIC).SetCellValue(stats.msian_female + stats.foreign_female); row.CreateCell(6, CellType.NUMERIC).SetCellValue(stats.msian_male + stats.foreign_male + stats.msian_female + stats.foreign_female); foreach (var entry in stats.collections) { // class row row = sheet.CreateRow(rowcount++); row.CreateCell(0).SetCellValue(SecurityElement.Escape(entry.name)); row.GetCell(0).CellStyle = boldStyle; // header row1 row = sheet.CreateRow(rowcount++); colcount = 0; foreach (var race in entry.GetList()) { row.CreateCell(colcount).SetCellValue(SecurityElement.Escape(race.name)); colcount += 2; } row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Total")); // header row2 row = sheet.CreateRow(rowcount++); for (int i = 0; i < entry.GetList().Count(); i++) { row.CreateCell(i * 2).SetCellValue(SecurityElement.Escape("M")); row.CreateCell(i * 2 + 1).SetCellValue(SecurityElement.Escape("F")); } // stats row row = sheet.CreateRow(rowcount++); colcount = 0; foreach (var race in entry.GetList()) { row.CreateCell(colcount++).SetCellValue(race.male); row.CreateCell(colcount++).SetCellValue(race.female); } row.CreateCell(colcount).SetCellValue(entry.GetList().Sum(x => x.male + x.female)); } // delete first sheet templateWorkbook.RemoveSheetAt(0); templateWorkbook.Write(ms); } // return created file path); return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Statistics_{0}_{1}.xls", ugroup, school))); }
public void PrintMnExcelFile() { string name = Request.QueryString["name"]; string sex = Request.QueryString["sex"]; string sf = Request.QueryString["sf"]; string sfz = Request.QueryString["sfz"]; string dept = Request.QueryString["dept"]; string ids = Request.QueryString["ids"]; string sum = Request.QueryString["sum"]; string result = ""; try { int beginRow = 4; int beginCol = 0; int endRow = 46; int endCol = 7; //有数据开始生成表格 string save_path = "", tick = ""; string temp_path = Server.MapPath("~/UploadFile/xls_files");//生成的文件存放路径 if (!Directory.Exists(temp_path)) { Directory.CreateDirectory(temp_path); } FileStream fs = new FileStream(Server.MapPath("~/Excel/美年体检最新报价.xls"), FileMode.OpenOrCreate, FileAccess.ReadWrite); var hssfworkbook = new HSSFWorkbook(fs); var dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; var si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; var style_border = hssfworkbook.CreateCellStyle(); var font = hssfworkbook.CreateFont(); font.FontHeight = 16 * 16; style_border.VerticalAlignment = VerticalAlignment.CENTER; style_border.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; style_border.BorderBottom = CellBorderType.THIN; style_border.BottomBorderColor = HSSFColor.BLACK.index; style_border.BorderLeft = CellBorderType.THIN; style_border.LeftBorderColor = HSSFColor.BLACK.index; style_border.BorderRight = CellBorderType.THIN; style_border.RightBorderColor = HSSFColor.BLACK.index; style_border.BorderTop = CellBorderType.THIN; style_border.TopBorderColor = HSSFColor.BLACK.index; style_border.SetFont(font); ISheet sheet1 = hssfworkbook.GetSheet("Sheet1"); IRow row1 = sheet1.GetRow(1); ICell cellName = row1.GetCell(0); string biaotou = "姓名:" + name + " 身份:" + sf + " 身份证:" + sfz + " 单位:" + dept + " 性别:" + sex; cellName.SetCellValue(biaotou); IRow rowend = sheet1.GetRow(endRow); ICell cellend2 = rowend.GetCell(4); string hhjjh = cellend2.ToString(); ICell cellend = rowend.GetCell(5); cellend.SetCellValue(sum); string[] idArray = ids.Split(','); foreach (var item in idArray) { int id = int.Parse(item); IRow rowID = sheet1.GetRow(id); rowID.ZeroHeight = true;//行的高度为0,隐藏 等同于删除行 } sheet1.ForceFormulaRecalculation = true; tick = "美年体检模板表" + DateTime.Now.ToString("yyMMddhhmmss");; string vname = ".xls"; save_path = temp_path + "\\" + tick + vname; var file = new FileStream(save_path, FileMode.Create); hssfworkbook.Write(file); file.Dispose(); file.Close(); result = "{\"state\":\"yes\",\"msg\":\"~/UploadFile/xls_files/" + tick + vname + "\"}"; // result = "{\"state\":\"yes\",\"msg\":\"~/Excel/" + tick + vname + "\"}"; } catch (Exception ex) { result = "{\"state\":\"no\",\"msg\":\"生成报表失败错误信息:" + ex.Message + "!\"}"; throw (ex); } Response.Write(result); }
public void Report(string FileName) { DivideByFactory(); int pos = 0; //Export to excel HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ////create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; #region Create fonts and styles HSSFFont HeaderF1 = hssfworkbook.CreateFont(); HeaderF1.FontHeightInPoints = 11; HeaderF1.Boldweight = 11 * 256; HeaderF1.FontName = "Calibri"; HSSFFont HeaderF2 = hssfworkbook.CreateFont(); HeaderF2.FontHeightInPoints = 10; HeaderF2.Boldweight = 10 * 256; HeaderF2.FontName = "Calibri"; HSSFFont HeaderF3 = hssfworkbook.CreateFont(); HeaderF3.FontHeightInPoints = 9; HeaderF3.Boldweight = 9 * 256; HeaderF3.FontName = "Calibri"; HSSFFont SimpleF = hssfworkbook.CreateFont(); SimpleF.FontHeightInPoints = 10; SimpleF.FontName = "Calibri"; HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle(); SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN; SimpleCS.BottomBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN; SimpleCS.LeftBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN; SimpleCS.RightBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN; SimpleCS.TopBorderColor = HSSFColor.BLACK.index; SimpleCS.SetFont(SimpleF); HSSFCellStyle CountDecCS = hssfworkbook.CreateCellStyle(); CountDecCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.000"); CountDecCS.BorderBottom = HSSFCellStyle.BORDER_THIN; CountDecCS.BottomBorderColor = HSSFColor.BLACK.index; CountDecCS.BorderLeft = HSSFCellStyle.BORDER_THIN; CountDecCS.LeftBorderColor = HSSFColor.BLACK.index; CountDecCS.BorderRight = HSSFCellStyle.BORDER_THIN; CountDecCS.RightBorderColor = HSSFColor.BLACK.index; CountDecCS.BorderTop = HSSFCellStyle.BORDER_THIN; CountDecCS.TopBorderColor = HSSFColor.BLACK.index; CountDecCS.SetFont(SimpleF); HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle(); CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0"); CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN; CountCS.BottomBorderColor = HSSFColor.BLACK.index; CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN; CountCS.LeftBorderColor = HSSFColor.BLACK.index; CountCS.BorderRight = HSSFCellStyle.BORDER_THIN; CountCS.RightBorderColor = HSSFColor.BLACK.index; CountCS.BorderTop = HSSFCellStyle.BORDER_THIN; CountCS.TopBorderColor = HSSFColor.BLACK.index; CountCS.SetFont(SimpleF); HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle(); SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index; //SimpleHeaderCS.WrapText = true; SimpleHeaderCS.SetFont(HeaderF3); #endregion Create fonts and styles HSSFCell Cell1; if (FrontsProfilDT.Rows.Count > 0) { HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, Профиль"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); pos += 2; int ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент ЗОВ"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Дата создания"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Бухг.наим."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Инв.номер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Фасад"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Вставка"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет наполнителя"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Вставка-2"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет наполнителя-2"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Квадратура"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ед.изм."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("ID упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ упаковки"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < FrontsProfilDT.Rows.Count; i++) { ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ZOVClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["OrderNumber"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["DocDateTime"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["AccountingName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["InvNumber"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["TechStoreName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr35"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr36"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr37"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr38"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr1"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["Count"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToDouble(FrontsProfilDT.Rows[i]["Square"])); Cell1.CellStyle = CountDecCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Measure"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackageID"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackNumber"])); Cell1.CellStyle = CountCS; pos++; } } pos = 0; if (FrontsTPSDT.Rows.Count > 0) { HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, ТПС"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); pos += 2; int ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент ЗОВ"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Дата создания"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Бухг.наим."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Инв.номер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Фасад"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Вставка"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет наполнителя"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Вставка-2"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет наполнителя-2"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Квадратура"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ед.изм."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("ID упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ упаковки"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < FrontsTPSDT.Rows.Count; i++) { ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ZOVClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["OrderNumber"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["DocDateTime"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["AccountingName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["InvNumber"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["TechStoreName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr35"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr36"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr37"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr38"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr1"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["Count"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToDouble(FrontsTPSDT.Rows[i]["Square"])); Cell1.CellStyle = CountDecCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Measure"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackageID"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackNumber"])); Cell1.CellStyle = CountCS; pos++; } } pos = 0; if (DecorProfilDT.Rows.Count > 0) { HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, Профиль"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); pos += 2; int ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент ЗОВ"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Дата создания"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Бухг.наим."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Инв.номер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Артикул"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Длина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Высота"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ширина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ед.изм."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("ID упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ упаковки"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < DecorProfilDT.Rows.Count; i++) { ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["ClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["ZOVClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["OrderNumber"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["DocDateTime"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["AccountingName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["InvNumber"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["TechStoreName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["Expr35"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Length"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Height"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Width"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Count"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["Measure"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackageID"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackNumber"])); Cell1.CellStyle = CountCS; pos++; } } pos = 0; if (DecorTPSDT.Rows.Count > 0) { HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, ТПС"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); pos += 2; int ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент ЗОВ"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Дата создания"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Бухг.наим."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Инв.номер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Артикул"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Длина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Высота"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ширина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ед.изм."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("ID упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ упаковки"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < DecorTPSDT.Rows.Count; i++) { ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["ClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["ZOVClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["OrderNumber"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["DocDateTime"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["AccountingName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["InvNumber"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["TechStoreName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["Expr35"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Length"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Height"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Width"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Count"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["Measure"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackageID"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackNumber"])); Cell1.CellStyle = CountCS; pos++; } } string tempFolder = System.Environment.GetEnvironmentVariable("TEMP"); FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls"); int j = 1; while (file.Exists == true) { file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls"); } FileStream NewFile = new FileStream(file.FullName, FileMode.Create); hssfworkbook.Write(NewFile); NewFile.Close(); System.Diagnostics.Process.Start(file.FullName); ClearReport(); }
/// <summary> /// 表头及内容样式 /// </summary> /// <param name="sheet"></param> static void SheetHeadStyle(ISheet ws, HSSFWorkbook workBook, int rowCount, int colCount) { // ws.AutoSizeColumn(0); //标题行自适应宽度 ICellStyle styleHeadNull = workBook.CreateCellStyle(); //为了.... IFont fontHeadNull = workBook.CreateFont(); /// ICellStyle styleHead = workBook.CreateCellStyle(); IFont fontHead = workBook.CreateFont(); //新建一个字体样式对象 for (int i = 0; i < rowCount; i++) { IRow rowHead = ws.GetRow(i); //在工作表中:建立行,参数为行号,从0计 rowHead.Height = Convert.ToInt16(rowHead.Height + (2 * 20)); for (int j = 0; j < colCount; j++) { if (i == 0) { styleHead.Alignment = HorizontalAlignment.Center; //设置单元格的样式:水平对齐居中 styleHead.VerticalAlignment = VerticalAlignment.Top; fontHead.Boldweight = short.MaxValue; //设置字体加粗样式 fontHead.Color = NPOI.HSSF.Util.HSSFColor.White.Index; // styleHead.BorderBottom = BorderStyle.Thin; //边框 styleHead.BorderLeft = BorderStyle.Thin; styleHead.BorderRight = BorderStyle.Thin; styleHead.BorderTop = BorderStyle.Thin; // styleHead.FillPattern = FillPattern.SolidForeground; styleHead.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index; } else { styleHead = styleHeadNull; fontHead = fontHeadNull; //新建一个字体样式对象 styleHead.Alignment = HorizontalAlignment.Left; //设置单元格的样式:水平对齐居中 styleHead.VerticalAlignment = VerticalAlignment.Top; fontHead.Color = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index; // styleHead.BorderBottom = BorderStyle.Thin; //边框 styleHead.BorderLeft = BorderStyle.Thin; styleHead.BorderRight = BorderStyle.Thin; styleHead.BorderTop = BorderStyle.Thin; // } styleHead.SetFont(fontHead); //使用SetFont方法将字体样式添加到单元格样式中 ICell cellHead = rowHead.GetCell(j); //在行中:建立单元格,参数为列号,从0计 cellHead.CellStyle = styleHead; //将新的样式赋给单元格 int length = Encoding.Default.GetBytes(cellHead.ToString()).Length; if (length > 250) { length = 200; } if (ws.GetColumnWidth(j) / 256 < length + 1) { ws.SetColumnWidth(j, (length + 2) * 256); } } } // }
//Datatable导出Excel public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); try { ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //标题头 int icolIndex = 0; CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0); sheet.AddMergedRegion(region); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 20; ICell celltitle = headerRow.CreateCell(0); celltitle.SetCellValue(title); celltitle.CellStyle = HeadercellStyle; //用column name 作为列名 IRow headerRow1 = sheet.CreateRow(1); foreach (DataColumn item in dt.Columns) { ICell cell1 = headerRow1.CreateCell(icolIndex); cell1.SetCellValue(item.ColumnName); cell1.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 2; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { string s = ex.Message; } finally { workbook = null; } }
/// <summary> /// DataTable导出到Excel文件(无表头)另外的是有表头的 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">保存位置</param> /// <param name="strSheetName">工作表名称</param> /// <Author>CallmeYhz 2015-11-26 10:13:09</Author> public static void MyExport(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames) { if (strSheetName == "") { strSheetName = "Sheet"; } MemoryStream getms = new MemoryStream(); #region 为getms赋值 if (oldColumnNames.Length != newColumnNames.Length) { getms = new MemoryStream(); } HSSFWorkbook workbook = new HSSFWorkbook(); //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet(); ISheet sheet = workbook.CreateSheet(strSheetName); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "http://....../"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); if (HttpContext.Current.Session["realname"] != null) { si.Author = HttpContext.Current.Session["realname"].ToString(); } else { if (HttpContext.Current.Session["username"] != null) { si.Author = HttpContext.Current.Session["username"].ToString(); } } //填加xls文件作者信息 si.ApplicationName = "NPOI"; //填加xls文件创建程序信息 si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息 si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息 si.Title = strHeaderText; //填加xls文件标题信息 si.Subject = strHeaderText; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); #region 取得列宽 int[] arrColWidth = new int[oldColumnNames.Length]; for (int i = 0; i < oldColumnNames.Length; i++) { arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length; } /* * 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 < oldColumnNames.Length; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } /* * for (int j = 0; j < dtSource.Columns.Count; j++) * { * int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; * if (intTemp > arrColWidth[j]) * { * arrColWidth[j] = intTemp; * } * } * */ } #endregion int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString()); } #region 列头及样式 { //HSSFRow headerRow = sheet.CreateRow(1); IRow headerRow = sheet.CreateRow(0); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); for (int i = 0; i < oldColumnNames.Length; i++) { headerRow.CreateCell(i).SetCellValue(newColumnNames[i]); headerRow.GetCell(i).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256); } /* * foreach (DataColumn column in dtSource.Columns) * { * headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); * headerRow.GetCell(column.Ordinal).CellStyle = headStyle; * * //设置列宽 * sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); * } * */ } #endregion rowIndex = 1; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); //foreach (DataColumn column in dtSource.Columns) for (int i = 0; i < oldColumnNames.Length; i++) { ICell newCell = dataRow.CreateCell(i); string drValue = row[oldColumnNames[i]].ToString(); switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); sheet = null; workbook = null; //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet getms = ms; } #endregion using (MemoryStream ms = getms) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } }
//npoi中单个工作薄最多可以创建4000个单元格样式,当时我也是卡在单元格样式这块卡了半天 //npoi中会默认创建一个单元格样式,也就是我们在新建excel中看到的那种样式 //每当我们创建一个单元格时,单元格样式默认为上述的单元格样式 //为什么要强调这个默认的单元格样式呢?因为我被他坑过(泪奔。。。) //如果你要设置某个单元格的单元格样式,一定要先创建新的单元格样式,切勿直接修改,因为你会发现影响的可不只是这一个单元格 //当时我傻傻的认为每个单元格都有自己的样式,结果他们的样式都指向系统创建的默认的样式,而不是自己new的 //具体的使用后面呈现 #endregion #region 四、单元格边框问题 //我们在excel中设置边框非常方便,鼠标拖一下,右击设置单元格样式,设置边框即可,但是在npoi可没这么简单,这个过程我们要用代码写出来 //主观上边框分为外边框和内边框 //但实际上投射到单元格就是16种单元格样式的组合!! private ICellStyle GetCellBorderStyle(int style, NPOI.SS.UserModel.BorderStyle outStyle, NPOI.SS.UserModel.BorderStyle inStyle) { ICellStyle cellStyle = _hssfWorkbook.CreateCellStyle(); cellStyle.BorderTop = inStyle; cellStyle.BorderLeft = inStyle; cellStyle.BorderRight = inStyle; cellStyle.BorderBottom = inStyle; switch (style) { //九宫格中间的样式 case 0: break; //九宫格左上角样式 case 1: cellStyle.BorderTop = outStyle; cellStyle.BorderLeft = outStyle; break; //九宫格上方样式 case 2: cellStyle.BorderTop = outStyle; break; //九宫格右上角样式 case 3: cellStyle.BorderTop = outStyle; cellStyle.BorderRight = outStyle; break; //九宫格右边样式 case 4: cellStyle.BorderRight = outStyle; break; //九宫格右下角样式 case 5: cellStyle.BorderBottom = outStyle; cellStyle.BorderRight = outStyle; break; //九宫格下方样式 case 6: cellStyle.BorderBottom = outStyle; break; //九宫格左下角样式 case 7: cellStyle.BorderBottom = outStyle; cellStyle.BorderLeft = outStyle; break; //九宫格左边样式 case 8: cellStyle.BorderLeft = outStyle; break; //单行单列样式 case 9: cellStyle.BorderTop = outStyle; cellStyle.BorderLeft = outStyle; cellStyle.BorderRight = outStyle; cellStyle.BorderBottom = outStyle; break; //单列多行上方样式 case 10: cellStyle.BorderTop = outStyle; cellStyle.BorderLeft = outStyle; cellStyle.BorderRight = outStyle; break; //单列多行中间样式 case 11: cellStyle.BorderLeft = outStyle; cellStyle.BorderRight = outStyle; break; //单列多行下方样式 case 12: cellStyle.BorderLeft = outStyle; cellStyle.BorderRight = outStyle; cellStyle.BorderBottom = outStyle; break; //单行多列右边样式 case 13: cellStyle.BorderTop = outStyle; cellStyle.BorderRight = outStyle; cellStyle.BorderBottom = outStyle; break; //单行多列中间样式 case 14: cellStyle.BorderTop = outStyle; cellStyle.BorderBottom = outStyle; break; //单行多列下方样式 case 15: cellStyle.BorderTop = outStyle; cellStyle.BorderLeft = outStyle; cellStyle.BorderBottom = outStyle; break; default: break; } return(cellStyle); }
/// <summary> /// 把指定的DataTable导出Excel /// Yakecan /// </summary> /// <param name="dt">数据源</param> /// <param name="fileName">导出的文件名称</param> /// <param name="sheetTittle">Sheet的名称</param> public void Export(DataTable dt, string fileName, string sheetTittle) { HSSFWorkbook workbook = CreateNewExcel(); ISheet sheet1 = workbook.CreateSheet(sheetTittle); IRow titleRow = sheet1.CreateRow(0); titleRow.Height = (short)20 * 25; ICellStyle titleStyle = workbook.CreateCellStyle(); titleStyle.Alignment = HorizontalAlignment.Center; titleStyle.VerticalAlignment = VerticalAlignment.Center; IFont font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = (short)16; titleStyle.SetFont(font); NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count); sheet1.AddMergedRegion(region); // 添加合并区域 ICell titleCell = titleRow.CreateCell(0); titleCell.CellStyle = titleStyle; titleCell.SetCellValue(sheetTittle); IRow headerRow = sheet1.CreateRow(1); ICellStyle headerStyle = workbook.CreateCellStyle(); headerStyle.Alignment = HorizontalAlignment.Center; headerStyle.VerticalAlignment = VerticalAlignment.Center; headerStyle.BorderBottom = BorderStyle.Thin; headerStyle.BorderLeft = BorderStyle.Thin; headerStyle.BorderRight = BorderStyle.Thin; headerStyle.BorderTop = BorderStyle.Thin; IFont titleFont = workbook.CreateFont(); titleFont.FontHeightInPoints = (short)11; titleFont.FontName = "宋体"; headerStyle.SetFont(titleFont); headerRow.CreateCell(0).SetCellValue("序号"); headerRow.GetCell(0).CellStyle = headerStyle; for (int i = 0; i < dt.Columns.Count; i++) { headerRow.CreateCell(i + 1).SetCellValue(dt.Columns[i].ColumnName); headerRow.GetCell(i + 1).CellStyle = headerStyle; sheet1.SetColumnWidth(i, 256 * 18); } ICellStyle bodyStyle = workbook.CreateCellStyle(); bodyStyle.BorderBottom = BorderStyle.Thin; bodyStyle.BorderLeft = BorderStyle.Thin; bodyStyle.BorderRight = BorderStyle.Thin; bodyStyle.BorderTop = BorderStyle.Thin; for (int r = 0; r < dt.Rows.Count; r++) { IRow bodyRow = sheet1.CreateRow(r + 2); bodyRow.CreateCell(0).SetCellValue(r + 1); bodyRow.GetCell(0).CellStyle = bodyStyle; bodyRow.GetCell(0).CellStyle.Alignment = HorizontalAlignment.Center; for (int c = 0; c < dt.Columns.Count; c++) { bodyRow.CreateCell(c + 1).SetCellValue(dt.Rows[r][c].ToString()); bodyRow.GetCell(c + 1).CellStyle = bodyStyle; } } sheet1.CreateFreezePane(1, 2); //FileStream fs = new FileStream(path, FileMode.Create); MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet1 = null; headerRow = null; workbook = null; OutPutExcelStreamOnClient(ms, fileName); ms.Dispose(); }
/// <summary> /// 根据数据实体和配置生成相应的Excel文件 /// </summary> /// <param name="sourceData">数据源</param> /// <param name="fileName">文件名</param> /// <returns></returns> public string CreateExcelFile(IList sourceData, string fileName) { //获取列表第一个元素的数据类型 Type type = null; if (sourceData.Count > 0) { type = sourceData[0].GetType(); } //创建2007的Excel HSSFWorkbook workbook = null; //如果文件存在,则直接获取采用追加的方式追加进去 string filePath = GetDownloadPath(fileName); string absFilePath = FileUtils.GetPhysicalPath(filePath); ISheet sheet = null; IRow row = null; //如果已经存在该文件,则说明已经产生过了,那么采用追加的方式 if (File.Exists(absFilePath)) { using (var fs = new FileStream(absFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { POIFSFileSystem ps = new POIFSFileSystem(fs); workbook = new HSSFWorkbook(ps); sheet = workbook.GetSheetAt(0); fs.Close(); fs.Dispose(); //row = sheet.CreateRow(sheet.LastRowNum + 1); }//读取流 } else { // (absFilePath); workbook = new HSSFWorkbook(); sheet = workbook.CreateSheet(); //设置标题列样式 ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.VerticalAlignment = VerticalAlignment.Center; headStyle.Alignment = HorizontalAlignment.Center; //设置字体-加粗字体 IFont headFont = workbook.CreateFont(); headFont.IsBold = true; row = sheet.CreateRow(0); //创建标题列 for (int i = 0; i < this.Properties.Count; i++) { var cell = row.CreateCell(i); cell.SetCellValue(this.Properties[i].Caption); cell.CellStyle = headStyle; cell.CellStyle.SetFont(headFont); } } //创建内容列 for (int i = 0; i < sourceData.Count; i++) { var entity = sourceData[i]; //创建新的内容行 row = sheet.CreateRow(i + sheet.LastRowNum + 1); //根据配置的属性列创建对应单元格 for (int j = 0; j < this.Properties.Count; j++) { var prop = this.Properties[j].EntityProp; //储存内容值 object value = null; //检查是否存在,如果存在则表示是拼接数据 if (prop.IndexOf("+", StringComparison.Ordinal) > -1) { var joinProps = prop.Split('+'); for (int jp = 0; jp < joinProps.Length; jp++) { var jprop = joinProps[jp]; var newValue = this.GetPropValue(jprop, type, entity); if (value != null) { if (newValue != null) { //这里只智齿字符串的累加,主要解决地址合并省市区的情况 value = value.ToString() + this.Properties[j].JoinPropChar + newValue.ToString(); } } else { value = newValue; } } } else { value = this.GetPropValue(prop, type, entity); } //根据数据类型填写对应的单元格 if (value == null) { row.CreateCell(j).SetCellValue(""); } else if (value is int) { row.CreateCell(j).SetCellValue((int)value); } else if (value is long) { row.CreateCell(j).SetCellValue((long)value); } else if (value is decimal) { row.CreateCell(j).SetCellValue(Convert.ToDouble(value)); } else if (value is double) { row.CreateCell(j).SetCellValue((double)value); } else if (value is DateTime) { row.CreateCell(j).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss.fff")); } else if (value is bool) { row.CreateCell(j).SetCellValue((bool)value); } else { row.CreateCell(j).SetCellValue(value.ToString()); } } } using (FileStream fs = File.OpenWrite(absFilePath)) { workbook.Write(fs); fs.Close(); fs.Dispose(); } workbook = null; return(filePath); }
public static void ExportExcel(DataGridView dgv, string fileName = "", string fontname = "微软雅黑", short fontsize = 10) { //检测是否有数据 if (dgv.RowCount <= 0) { return; } //创建主要对象 HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight"); //设置字体,大小,对齐方式 HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontName = fontname; font.FontHeightInPoints = fontsize; style.SetFont(font); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐 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; //首行填充黄色 ICellStyle headerStyle = workbook.CreateCellStyle(); headerStyle.FillForegroundColor = IndexedColors.Yellow.Index; headerStyle.FillPattern = FillPattern.SolidForeground; headerStyle.SetFont(font); headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐 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; //添加表头 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i < dgv.Columns.Count; i++) { dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText); dataRow.GetCell(i).CellStyle = headerStyle; } //注释的这行是设置筛选的 //sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count)); //添加列及内容 for (int i = 0; i < dgv.Rows.Count; i++) { sheet.AutoSizeColumn(i);//先来个常规自适应 dataRow = (HSSFRow)sheet.CreateRow(i + 1); for (int j = 0; j < dgv.Columns.Count; j++) { string ValueType = dgv.Rows[i].Cells[j].Value.GetType().ToString(); string Value = dgv.Rows[i].Cells[j].Value.ToString(); switch (ValueType) { case "System.String": //字符串类型 dataRow.CreateCell(j).SetCellValue(Value); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.DateTime.TryParse(Value, out dateV); dataRow.CreateCell(j).SetCellValue(dateV); break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(Value, out boolV); dataRow.CreateCell(j).SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(Value, out intV); dataRow.CreateCell(j).SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(Value, out doubV); dataRow.CreateCell(j).SetCellValue(doubV); break; case "System.DBNull": //空值处理 dataRow.CreateCell(j).SetCellValue(""); break; default: dataRow.CreateCell(j).SetCellValue(""); break; } dataRow.GetCell(j).CellStyle = style; //设置宽度 sheet.SetColumnWidth(j, (Value.Length + 10) * 300); //首行冻结 sheet.CreateFreezePane(dgv.Columns.Count, 1); } } //保存文件 string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; MemoryStream ms = new MemoryStream(); if (saveDialog.ShowDialog() == DialogResult.OK) { saveFileName = saveDialog.FileName; if (!CheckFiles(saveFileName)) { MessageBox.Show("文件占用,请关闭文件后再继续操作! " + saveFileName); workbook = null; ms.Close(); ms.Dispose(); return; } workbook.Write(ms); FileStream file = new FileStream(saveFileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); MessageBox.Show("导出完成!", "", MessageBoxButtons.OK); } else { workbook = null; ms.Close(); ms.Dispose(); } }
//styles public void createStyle(string name) { AddStyle(name, (HSSFCellStyle)hssfworkbook.CreateCellStyle()); }
/// <summary> /// List导出到Excel的MemoryStream /// </summary> /// <param name="list">数据源</param> /// <param name="sHeaderText">表头文本</param> /// <param name="columns">需要导出的属性</param> private MemoryStream CreateExportMemoryStream(List <T> list, string sHeaderText, string[] columns) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); Type type = typeof(T); PropertyInfo[] properties = GetProperties(type, columns); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); #region 取得每列的列宽(最大宽度) int[] arrColWidth = new int[properties.Length]; for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { //GBK对应的code page是CP936 arrColWidth[columnIndex] = properties[columnIndex].Name.Length; } #endregion for (int rowIndex = 0; rowIndex < list.Count; rowIndex++) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(sHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1)); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { // 类属性如果有Description就用Description当做列名 DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(properties[columnIndex], typeof(DescriptionAttribute)); string description = properties[columnIndex].Name; if (customAttribute != null) { description = customAttribute.Description; } headerRow.CreateCell(columnIndex).SetCellValue(description); headerRow.GetCell(columnIndex).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256); } } #endregion } #endregion #region 填充内容 ICellStyle contentStyle = workbook.CreateCellStyle(); contentStyle.Alignment = HorizontalAlignment.Left; IRow dataRow = sheet.CreateRow(rowIndex + 2); // 前面2行已被占用 for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { ICell newCell = dataRow.CreateCell(columnIndex); newCell.CellStyle = contentStyle; string drValue = properties[columnIndex].GetValue(list[rowIndex], null).ParseToString(); switch (properties[columnIndex].PropertyType.ToString()) { case "System.String": newCell.SetCellValue(drValue); break; case "System.DateTime": case "System.Nullable`1[System.DateTime]": newCell.SetCellValue(drValue.ParseToDateTime()); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": case "System.Nullable`1[System.Boolean]": newCell.SetCellValue(drValue.ParseToBool()); break; case "System.Byte": case "System.Nullable`1[System.Byte]": case "System.Int16": case "System.Nullable`1[System.Int16]": case "System.Int32": case "System.Nullable`1[System.Int32]": newCell.SetCellValue(drValue.ParseToInt()); break; case "System.Int64": case "System.Nullable`1[System.Int64]": newCell.SetCellValue(drValue.ParseToString()); break; case "System.Double": case "System.Nullable`1[System.Double]": newCell.SetCellValue(drValue.ParseToDouble()); break; case "System.Decimal": case "System.Nullable`1[System.Decimal]": newCell.SetCellValue(drValue.ParseToDouble()); break; case "System.DBNull": newCell.SetCellValue(string.Empty); break; default: newCell.SetCellValue(string.Empty); break; } } #endregion } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); workbook.Close(); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="headerTextList">表头摘要信息</param> public MemoryStream Export(DataTable dtSource, List<String> headerTextList) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("sheet1"); //设置Excel文件属性信息 SetFileProperty(workbook); HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //计算列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } //获取每一列的最大列宽 for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 for (int i = 0; i < headerTextList.Count; i++) { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i); headerRow.HeightInPoints = 18; headerRow.CreateCell(0).SetCellValue(headerTextList[i]); HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headerStyle.Alignment = HorizontalAlignment.Left; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 14; //font.Boldweight = 700; headerStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headerStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = headerTextList.Count + 1; } #endregion #region 填充表格内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } }
/// <summary> /// 导出报表证书信息查询数据到excle /// </summary> /// <param name="fields">字段</param> /// <param name="query">数据集</param> /// <param name="path">excle模版的位置</param> /// <param name="from">显示的标题默认行数为1</param> /// <returns></returns> public string WriteExcleVJianDingRenWu(string[] fields, dynamic[] query, string path = @"~/up/VJianDingRenWu.xls", int from = 1) { HSSFWorkbook _book = new HSSFWorkbook(); string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path); //物理路径 FileStream file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read); //打开文件 IWorkbook hssfworkbook = new HSSFWorkbook(file); //数据流进行编辑 ISheet sheet = hssfworkbook.GetSheet("检定任务"); //读取工作表 string guid = Guid.NewGuid().ToString(); //生产唯一标识 string saveFileName = xlsPath.Path(@"JianDingRenWu/" + guid + "VJianDingRenWu"); Dictionary <string, string> propertyName; PropertyInfo[] properties; //标题行 var titles = "委托单号,报告编号,是否可以领取,器具名称,型号,出厂编号,证书单位,客户特殊要求,所在位置,器具状态,送检时间,超期原因,上传状态,报告状态,审核审批不通过原因,送检单位".Split(','); var dd = sheet.GetRow(0).GetCell(1).CellStyle; HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow; ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.ShrinkToFit = true; for (int i = 0; i < titles.Length; i++) { if (!string.IsNullOrWhiteSpace(titles[i])) { var cell = dataRow.CreateCell(i); cell.CellStyle = dd; cell.SetCellValue(titles[i]); //列值 } } //内容行 for (int i = 0; i < query.Length; i++) { propertyName = new Dictionary <string, string>(); if (query[i] == null) { continue; } Type type = query[i].GetType(); properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (PropertyInfo property in properties) { object o = property.GetValue(query[i], null); if (!string.IsNullOrEmpty(property.Name) && o != null) { propertyName.Add(property.Name, o.ToString()); } } int j = 0; dataRow = sheet.CreateRow(i + from) as HSSFRow; fields.All(a => { if (propertyName.ContainsKey(a)) //列名 { var cell = dataRow.CreateCell(j); cell.SetCellValue(propertyName[a]); //列值 } j++; return(true); }); } sheet.ForceFormulaRecalculation = true; using (FileStream fileWrite = new FileStream(saveFileName, FileMode.Create)) { hssfworkbook.Write(fileWrite); } //一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(string.Format("../../up/JianDingRenWu/{0}.xls", guid + "VJianDingRenWu")); //记录日志 }