public byte[] Export(DataTable dt, IDictionary<string, string> columns) { //HttpResponse rsp = HttpContext.Current.Response; HSSFWorkbook wb = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); ISheet sheet = wb.CreateSheet("Sheet1"); //填充表头 IRow dataRow = sheet.CreateRow(0); bool isReColumn = !(columns == null || columns.Count == 0); int tmpInt = 0; if (isReColumn) { foreach (string columnName in columns.Keys) { dataRow.CreateCell(tmpInt++).SetCellValue(columns[columnName]); } } else { foreach (DataColumn column in dt.Columns) { dataRow.CreateCell(tmpInt++).SetCellValue(column.ColumnName); } } //填充内容 for (int i = 0; i < dt.Rows.Count; i++) { dataRow = sheet.CreateRow(i + 1); if (isReColumn) { tmpInt = 0; foreach (string columnName in columns.Keys) { dataRow.CreateCell(tmpInt++).SetCellValue(dt.Rows[i][columnName].ToString()); } } else { for (int j = 0; j < dt.Columns.Count; j++) { dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } } wb.Write(ms); //rsp.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls")); //rsp.BinaryWrite(ms.ToArray()); wb = null; ms.Close(); byte[] bytes = ms.ToArray(); ms.Dispose(); return bytes; }
/// <summary> /// 导出 /// </summary> public override byte[] Export(List <SearchWordConvertMapDb> list, Dictionary <string, object> extra = null) { //创建Excel文件的对象 var wb = new HSSFWorkbook(); //添加一个sheet var sheet = wb.CreateSheet("二级车型配置表"); //给sheet1添加第一行的头部标题 var row = sheet.CreateRow(0); // 设置样式 var style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; // 设置字体 var font = wb.CreateFont(); font.FontHeightInPoints = 9; font.FontName = "微软雅黑"; style.SetFont(font); string[] title = { "关键词", "同义词", "是否删除", "二级车型ID", "排序", "规格", "特殊规格", "二级车型名称" }; for (var i = 0; i < title.Length; i++) { var cell = row.CreateCell(i); cell.SetCellValue(title[i]); } for (var i = 0; i < list.Count; i++) { var cell = sheet.CreateRow(i + 1); cell.CreateCell(0).SetCellValue(list[i].TargetWord); cell.CreateCell(1).SetCellValue(list[i].SourceWord); cell.CreateCell(2).SetCellValue("1"); cell.CreateCell(3).SetCellValue(list[i].VehicleID); cell.CreateCell(4).SetCellValue(list[i].Sort); cell.CreateCell(5).SetCellValue(list[i].TireSize); cell.CreateCell(6).SetCellValue(list[i].SpecialTireSize); cell.CreateCell(7).SetCellValue(list[i].VehicleName); } // 写入到客户端 byte[] file; using (var ms = new MemoryStream()) { wb.Write(ms); ms.Seek(0, SeekOrigin.Begin); file = ms.ToArray(); } return(file); }
public ActionResult Export_ToExcel() { var getPersonCities = ItemManager.Instance.GetPersonCities(); var workbook = new HSSFWorkbook(); //Create new Excel Sheet var sheet = workbook.CreateSheet(); HSSFFont myFont = (HSSFFont)workbook.CreateFont(); myFont.FontHeightInPoints = (short)12; myFont.FontName = "Times New Roman"; HSSFCellStyle borderedCellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); borderedCellStyle.SetFont(myFont); borderedCellStyle.WrapText = true; borderedCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; borderedCellStyle.FillPattern = FillPattern.SolidForeground; borderedCellStyle.VerticalAlignment = VerticalAlignment.Center; HSSFCellStyle borderedCellStyleContent = (HSSFCellStyle)workbook.CreateCellStyle(); borderedCellStyleContent.SetFont(myFont); borderedCellStyleContent.WrapText = true; //borderedCellStyleContent.Alignment = HorizontalAlignment.Center; //chieu ngang borderedCellStyleContent.VerticalAlignment = VerticalAlignment.Center; //(Optional) set the width of the columns sheet.SetColumnWidth(0, 20 * 200); sheet.SetColumnWidth(1, 20 * 200); //sheet.SetColumnWidth(2, 20 * 200); //sheet.SetDefaultColumnStyle(0, borderedCellStyle); //sheet.SetDefaultColumnStyle(1, borderedCellStyle); //sheet.SetDefaultColumnStyle(2, borderedCellStyle); CreateCell(sheet.CreateRow(0), 0, "Column Value", borderedCellStyle); IRow HeaderRow = sheet.CreateRow(0); CreateCell(HeaderRow, 0, "CityName", borderedCellStyle); CreateCell(HeaderRow, 1, "Gender", borderedCellStyle); CreateCell(HeaderRow, 2, "Amount", borderedCellStyle); //headerRow.CreateCell(0).SetCellValue("CityName"); //headerRow.CreateCell(1).SetCellValue("Gender"); //headerRow.CreateCell(2).SetCellValue("Amount"); //for (int c = 0; c < headerRow.Cells.Count; c++) //{ // headerRow.Cells[c].CellStyle = borderedCellStyle; //} sheet.CreateFreezePane(0, 1, 0, 1); var result = from cities in getPersonCities group cities by cities.IdCity into c select c.ToList(); int RowIndex = 1; foreach (var objOrg in result) { int Number = objOrg.Count; if (Number > 1) { int MergeIndex = (Number - 1) + RowIndex; CellRangeAddress Merged = new CellRangeAddress(RowIndex, MergeIndex, 0, 0); sheet.AddMergedRegion(Merged); } foreach (var objOrg1 in objOrg) { IRow row = sheet.CreateRow(RowIndex); CreateCell(row, 0, objOrg1.CityName, borderedCellStyleContent); CreateCell(row, 1, objOrg1.Gender, borderedCellStyleContent); CreateCell(row, 2, objOrg1.Amount.ToString(), borderedCellStyleContent); //row.CreateCell(0).SetCellValue(objOrg1.CityName); //row.CreateCell(1).SetCellValue(objOrg1.Gender); //row.CreateCell(2).SetCellValue(objOrg1.Amount); RowIndex++; //for (int c = 0; c < row.Cells.Count; c++) //{ // row.Cells[c].CellStyle = borderedCellStyleContent; //} } } //Write the Workbook to a memory stream MemoryStream output = new MemoryStream(); workbook.Write(output); //Return the result to the end user return(File(output.ToArray(), //The binary data of the XLS file "application/vnd.ms-excel", //MIME type of Excel files "OrganizationList.xls")); }
/// <summary> /// DataTable 导出到 Excel 的 MemoryStream /// </summary> /// <param name="workbook">源 workbook</param> /// <param name="dtSource">源 DataTable</param> /// <param name="strHeaderText">表头文本 空值未不要表头标题(多个表对应多个表头以英文逗号(,)分开,个数应与表相同)</param> /// <returns></returns> public static void ExportFromDSExcel(HSSFWorkbook workbook, DataTable dtSource, string strHeaderText) { ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); ISheet sheet = workbook.CreateSheet(strHeaderText); int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").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("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; int intTop = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表、填充表头、填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } intTop = 0; #region 表头及样式 { if (strHeaderText.Length > 0) { IRow headerRow = sheet.CreateRow(intTop); intTop += 1; 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(intTop); intTop += 1; ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); 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); // 设置设置列宽 太长会报错 修改2014 年9月22日 int dd = (arrColWidth[column.Ordinal] + 1) * 256; if (dd > 200 * 256) { dd = 100 * 256; } sheet.SetColumnWidth(column.Ordinal, dd); } } #endregion rowIndex = intTop; } #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": //日期类型 if (drValue.Length > 0) { DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 } else { newCell.SetCellValue(drValue); } 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++; } }
/// <summary> /// 导出到excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="data"></param> /// <param name="sheetName"></param> /// <returns></returns> public static MemoryStream ListToExcel <T>(List <T> data, string sheetName = "Sheet1") where T : class { var count = 0; var columnIndex = 0; var type = data.GetType(); var properties = type.GetGenericArguments()[0].GetProperties(); //获取T类型的所有属性 IWorkbook workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(sheetName); try { var rowHeader = sheet.CreateRow(count); for (var n = 0; n < properties.Length; n++) { //sheet.AutoSizeColumn(n);//自动列宽 var ignore = ((ExcelExportIgnoreAttribute[])properties[n].GetCustomAttributes(typeof(ExcelExportIgnoreAttribute), false)).FirstOrDefault(); if (ignore != null) { //sheet.SetColumnHidden(n, true); continue; } var displayName = ((DisplayNameAttribute[])properties[n].GetCustomAttributes(typeof(DisplayNameAttribute), false)).FirstOrDefault(); rowHeader.CreateCell(columnIndex).SetCellValue(displayName == null ? "无列名" : displayName.DisplayName); columnIndex++; } count++; for (var i = 0; i < data.Count; ++i) { var obj = data[i]; var row = sheet.CreateRow(count); columnIndex = 0; for (var j = 0; j < properties.Length; ++j) { var ignore = ((ExcelExportIgnoreAttribute[])properties[j].GetCustomAttributes(typeof(ExcelExportIgnoreAttribute), false)).FirstOrDefault(); if (ignore != null) { //sheet.SetColumnHidden(j, true); continue; } var pValue = properties[j].GetValue(obj, null); var cell = row.CreateCell(columnIndex); cell.SetCellValue(pValue == null ? "" : pValue.ToString()); columnIndex++; } ++count; } using (var ms = new MemoryStream()) { workbook.Write(ms); //写入到内存流 ms.Flush(); ms.Position = 0; return(ms); } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// List导出到Excel的MemoryStream /// </summary> /// <param name="list">需要导出的泛型List</param> /// <param name="strHeaderText">第一行标题头</param> /// <param name="titleDictionaries">列名称字典映射</param> /// <param name="title">todo: describe title parameter on ListToExcel</param> /// <param name="titleDic">todo: describe titleDic parameter on ListToExcel</param> /// <returns>内存流</returns> private static MemoryStream ListToExcel <T>(List <T> list, string strHeaderText = null, Dictionary <string, string> titleDic = null) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); //设置文件属性 SetFileSummary(strHeaderText, workbook); //获取列宽 int[] arrColWidth = GetColumnWidth(titleDic); //日期风格 ICellStyle dateStyle = GetDateStyle(workbook); //Excel标题风格 HSSFCellStyle headStyle = GetHeadStyle(workbook); /*在第一行创建标题行*/ CreateHeadRow(titleDic, sheet, arrColWidth, headStyle); //通过反射得到对象的属性集合 Type type = null; if (list != null && list.Count > 0) { type = list.First().GetType(); for (int row = 0; row < list.Count; row++) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(row + 1); int cellIndex = 0; foreach (var dicItem in titleDic) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(cellIndex); string drValue = string.Empty; PropertyInfo propInfo = type.GetProperty(dicItem.Key); var propValue = type.GetProperty(dicItem.Key).GetValue(list[row]); if (propValue != null) { drValue = propValue.ToString(); } SetCellValueByType(newCell, drValue, propInfo, dateStyle); cellIndex = cellIndex + 1; } } } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook.Close(); return(ms); } }
public async Task <ActionResult> ExportToExcel(LineStoreMaterialDetailQueryViewModel model) { IList <LineStoreMaterialDetail> lst = new List <LineStoreMaterialDetail>(); using (LineStoreMaterialServiceClient client = new LineStoreMaterialServiceClient()) { await Task.Run(() => { PagingConfig cfg = new PagingConfig() { IsPaging = false, OrderBy = "CreateTime Desc", Where = GetDetailWhereCondition(model) }; MethodReturnResult <IList <LineStoreMaterialDetail> > result = client.GetDetail(ref cfg); if (result.Code == 0) { lst = 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); ICell cell = null; IRow row = null; ISheet ws = null; for (int j = 0; j < lst.Count; j++) { if (j % 65535 == 0) { ws = wb.CreateSheet(); row = ws.CreateRow(0); #region //列名 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(StringResource.ItemNo); //项目号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_LineStoreName); //线边仓 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("工单号"); //工单号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_MaterialCode); //物料编码 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("物料名称"); //物料名称 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_MaterialLot); //物料批号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_ReceiveQty); //领料数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_LoadingQty); //上料数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_UnloadingQty); //下料数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_CurrentQty); //当前数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_SupplierMaterialLot); //供应商物料批次号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(LSMResources.StringResource.LineStoreMaterialDetailViewModel_SupplierCode); //供应商编码 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; } LineStoreMaterialDetail obj = lst[j]; Material m = model.GetMaterial(obj.Key.MaterialCode); Supplier s = model.GetSupplier(obj.SupplierCode); row = ws.CreateRow(j + 1); #region //数据 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(j + 1); //项目号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Key.LineStoreName); //线边仓 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Key.OrderNumber); //工单号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Key.MaterialCode); //物料编码 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(m == null ? string.Empty : m.Name); //物料名称 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Key.MaterialLot); //物料批号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.ReceiveQty); //领料数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.LoadingQty); //上料数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.UnloadingQty); //下料数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.CurrentQty); //当前数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.SupplierMaterialLot); //供应商物料批次号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.SupplierCode); //供应商编码 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(s == null ? string.Empty : s.Name); //供应商名称 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Attr1); //效率档 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Description); //描述 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Editor); //编辑人 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //编辑时间 #endregion } MemoryStream ms = new MemoryStream(); wb.Write(ms); ms.Flush(); ms.Position = 0; return(File(ms, "application/vnd.ms-excel", "LineStoreMaterialData.xls")); }
private static byte[] SaveToExcelFile(List <Tuple <int, int, object> > content, string sheetName) { HSSFWorkbook hssfwb = new HSSFWorkbook(); var sheet = hssfwb.CreateSheet(sheetName); Dictionary <int, IRow> rows = new Dictionary <int, IRow>(); double output; DateTime dt; var numberStyle = hssfwb.CreateCellStyle(); var formatId = HSSFDataFormat.GetBuiltinFormat("0"); if (formatId == -1) { var newDataFormat = hssfwb.CreateDataFormat(); numberStyle.DataFormat = newDataFormat.GetFormat("0"); } else { numberStyle.DataFormat = formatId; } var dateStyle = hssfwb.CreateCellStyle(); formatId = HSSFDataFormat.GetBuiltinFormat("dd/MM/yyyy"); if (formatId == -1) { var newDataFormat = hssfwb.CreateDataFormat(); dateStyle.DataFormat = newDataFormat.GetFormat("dd/MM/yyyy"); } else { dateStyle.DataFormat = formatId; } var invalidStyle = (HSSFCellStyle)hssfwb.CreateCellStyle(); invalidStyle.FillForegroundColor = IndexedColors.Yellow.Index; invalidStyle.FillPattern = FillPattern.SolidForeground; foreach (var cell in content) { if (string.IsNullOrEmpty(cell.Item3?.ToString())) { continue; } var rowId = cell.Item1; var columnId = cell.Item2; IRow row = sheet.GetRow(rowId); if (row == null) { row = sheet.CreateRow(rowId); } var excelCell = row.GetCell(columnId); if (excelCell == null) { excelCell = row.CreateCell(columnId); } if (cell.Item3 is DateTime) { excelCell.SetCellValue((DateTime)cell.Item3); excelCell.CellStyle = dateStyle; } else if (cell.Item3 is bool) { excelCell.SetCellValue((bool)cell.Item3); } else if (cell.Item3 is string) { excelCell.SetCellValue((string)cell.Item3); } else if (double.TryParse(cell.Item3.ToString(), out output)) { excelCell.SetCellValue(output); excelCell.CellStyle = numberStyle; } //else if (cell.Item3.StartsWith(INVALID_CELL)) //{ // excelCell.CellStyle = invalidStyle; // if (cell.Item3 != INVALID_CELL) // excelCell.SetCellValue(cell.Item3.Replace(INVALID_CELL, "")); //} else { excelCell.SetCellValue(cell.Item3.ToString()); } } var ms = new MemoryStream(); hssfwb.Write(ms); return(ms.ToArray()); }
/// <summary> /// 转换内存表为EXCEL文件流 /// </summary> /// <param name="SourceTable">源数据</param> /// <param name="DateTimeFormat">时间列格式化</param> /// <returns>EXCEL文件流</returns> public static Stream RenderDataTableToExcelStream(DataTable SourceTable, string DateTimeFormat = "yyyy-MM-dd HH:mm:ss") { HSSFWorkbook workbook = new HSSFWorkbook(); IFont font1 = workbook.CreateFont(); font1.IsBold = true; font1.Color = HSSFColor.White.Index; MemoryStream ms = new MemoryStream(); ISheet sheet = workbook.CreateSheet("本地信息列表"); sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, SourceTable.Columns.Count - 1)); //首行筛选 sheet.DefaultColumnWidth = 20; //sheet.DefaultRowHeight = 20; IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 35;//行高 ICellStyle headstyle = workbook.CreateCellStyle(); headstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 方法1 headstyle.Alignment = HorizontalAlignment.Center; //设置居中 方法3 headstyle.FillPattern = FillPattern.SolidForeground; headstyle.FillForegroundColor = HSSFColor.BlueGrey.Index; headstyle.SetFont(font1); foreach (DataColumn column in SourceTable.Columns) { ICell cell = headerRow.CreateCell(column.Ordinal); cell.SetCellValue(column.ColumnName); cell.CellStyle = headstyle; } int rowIndex = 1; ICellStyle style = workbook.CreateCellStyle(); style.VerticalAlignment = VerticalAlignment.Center; //垂直居中 方法1 style.Alignment = HorizontalAlignment.Center; //设置居中 方法3 style.WrapText = true; foreach (DataRow row in SourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); //dataRow.HeightInPoints = 20;//行高 IDataFormat dataformat = workbook.CreateDataFormat(); foreach (DataColumn column in SourceTable.Columns) { if (row[column] is DBNull) { dataRow.CreateCell(column.Ordinal).SetCellValue(string.Empty); continue; } if (column.DataType == typeof(int)) { dataRow.CreateCell(column.Ordinal).SetCellValue((int)row[column]); dataRow.GetCell(column.Ordinal).CellStyle = style; } else if (column.DataType == typeof(float)) { dataRow.CreateCell(column.Ordinal).SetCellValue((float)row[column]); dataRow.GetCell(column.Ordinal).CellStyle = style; } else if (column.DataType == typeof(double)) { dataRow.CreateCell(column.Ordinal).SetCellValue((double)row[column]); dataRow.GetCell(column.Ordinal).CellStyle = style; } else if (column.DataType == typeof(Byte)) { dataRow.CreateCell(column.Ordinal).SetCellValue((byte)row[column]); dataRow.GetCell(column.Ordinal).CellStyle = style; } else if (column.DataType == typeof(UInt16)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt16)row[column]); dataRow.GetCell(column.Ordinal).CellStyle = style; } else if (column.DataType == typeof(UInt32)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt32)row[column]); dataRow.GetCell(column.Ordinal).CellStyle = style; } else if (column.DataType == typeof(UInt64)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt64)row[column]); dataRow.GetCell(column.Ordinal).CellStyle = style; } else if (column.DataType == typeof(DateTime)) { dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)row[column]); style.DataFormat = dataformat.GetFormat(DateTimeFormat); dataRow.GetCell(column.Ordinal).CellStyle = style; } else { dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column])); dataRow.GetCell(column.Ordinal).CellStyle = style; } } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return(ms); }
bool saveAndWriteContent() { string path = ""; string[] _path = this.richTextBox2.Text.Split('\n')[0].Split('\\'); string[] _newPath = new string[_path.Length - 1]; Array.Copy(_path, 0, _newPath, 0, _path.Length - 1); foreach (string i in _newPath) { path += i + "\\"; } path += "new.xls"; if (File.Exists(path)) { File.Delete(path); } try { fs_write = new FileStream(path, FileMode.Create, FileAccess.Write); workbook = new HSSFWorkbook(); } catch (Exception e) { MessageBox.Show("创建文件失败" + path); fs_write.Close(); return(false); } //content:二维数组,存储每个sheet的所有A145_FCT对象 #region ~添加原始数据 sheet = workbook.CreateSheet("原始数据"); for (int j = 0; j < Head.Length; j++) { string[] head_temp = Head[j].Split(','); for (int k = 0; k < head_temp.Length; k++) { if (k == 0) { sheet.CreateRow(j).CreateCell(0).SetCellValue(head_temp[k]); } else { sheet.GetRow(j).CreateCell(k).SetCellValue(head_temp[k]); } } } for (int i = 0; i < A145s.Length; i++) { sheet.CreateRow(i + 5).CreateCell(0).SetCellValue(A145s[i].SN); sheet.GetRow(i + 5).CreateCell(1).SetCellValue(A145s[i].StationID); sheet.GetRow(i + 5).CreateCell(2).SetCellValue(A145s[i].PASS_FAIL_Status); sheet.GetRow(i + 5).CreateCell(3).SetCellValue(A145s[i].SiteID); sheet.GetRow(i + 5).CreateCell(4).SetCellValue(A145s[i].FailItem); sheet.GetRow(i + 5).CreateCell(5).SetCellValue(A145s[i].StartTime); sheet.GetRow(i + 5).CreateCell(6).SetCellValue(A145s[i].TestTime); sheet.GetRow(i + 5).CreateCell(7).SetCellValue(A145s[i].USB_DP1_TO_GND); sheet.GetRow(i + 5).CreateCell(8).SetCellValue(A145s[i].USB_DN1_TO_GND); sheet.GetRow(i + 5).CreateCell(9).SetCellValue(A145s[i].USBC_VBUS_TO_GND); sheet.GetRow(i + 5).CreateCell(10).SetCellValue(A145s[i].USBC_VCONN_TO_GND); sheet.GetRow(i + 5).CreateCell(11).SetCellValue(A145s[i].USBC_CC1_TO_GND); sheet.GetRow(i + 5).CreateCell(12).SetCellValue(A145s[i].USBC_VBUS_TO_USBC_CC1); sheet.GetRow(i + 5).CreateCell(13).SetCellValue(A145s[i].USBC_VBUS_TO_USBC_VCONN); sheet.GetRow(i + 5).CreateCell(14).SetCellValue(A145s[i].USBC_CC1_TO_USB_DP1); sheet.GetRow(i + 5).CreateCell(15).SetCellValue(A145s[i].USB_DP1_TO_USB_DN1); sheet.GetRow(i + 5).CreateCell(16).SetCellValue(A145s[i].USBC_VBUS_TO_USB_DN1); sheet.GetRow(i + 5).CreateCell(17).SetCellValue(A145s[i].PP_USBC_VBUS); sheet.GetRow(i + 5).CreateCell(18).SetCellValue(A145s[i].TP0304); sheet.GetRow(i + 5).CreateCell(19).SetCellValue(A145s[i].USBC_CC1); sheet.GetRow(i + 5).CreateCell(20).SetCellValue(A145s[i].USB_DP1); sheet.GetRow(i + 5).CreateCell(21).SetCellValue(A145s[i].USB_DN1); sheet.GetRow(i + 5).CreateCell(22).SetCellValue(A145s[i].CURRENT_CC1_475uA); sheet.GetRow(i + 5).CreateCell(23).SetCellValue(A145s[i].CURRENT_VCONN_475uA); sheet.GetRow(i + 5).CreateCell(24).SetCellValue(A145s[i].CURRENT_CC1_550uA); sheet.GetRow(i + 5).CreateCell(25).SetCellValue(A145s[i].CURRENT_VCONN_550uA); } #endregion for (int i = 0; i < content.Length; i++) { #region ~添加每个sheet的数据 sheet = workbook.CreateSheet("#" + content[i][0].SiteID); for (int j = 0; j < Head.Length; j++) { string[] head_temp = Head[j].Split(','); for (int k = 0; k < head_temp.Length; k++) { if (k == 0) { sheet.CreateRow(j).CreateCell(0).SetCellValue(head_temp[k]); } else { sheet.GetRow(j).CreateCell(k).SetCellValue(head_temp[k]); } } } for (int j = 0; j < content[i].Length; j++) { sheet.CreateRow(j + 5).CreateCell(0).SetCellValue(content[i][j].SN); sheet.GetRow(j + 5).CreateCell(1).SetCellValue(content[i][j].StationID); sheet.GetRow(j + 5).CreateCell(2).SetCellValue(content[i][j].PASS_FAIL_Status); sheet.GetRow(j + 5).CreateCell(3).SetCellValue(content[i][j].SiteID); sheet.GetRow(j + 5).CreateCell(4).SetCellValue(content[i][j].FailItem); sheet.GetRow(j + 5).CreateCell(5).SetCellValue(content[i][j].StartTime); sheet.GetRow(j + 5).CreateCell(6).SetCellValue(content[i][j].TestTime); sheet.GetRow(j + 5).CreateCell(7).SetCellValue(content[i][j].USB_DP1_TO_GND); sheet.GetRow(j + 5).CreateCell(8).SetCellValue(content[i][j].USB_DN1_TO_GND); sheet.GetRow(j + 5).CreateCell(9).SetCellValue(content[i][j].USBC_VBUS_TO_GND); sheet.GetRow(j + 5).CreateCell(10).SetCellValue(content[i][j].USBC_VCONN_TO_GND); sheet.GetRow(j + 5).CreateCell(11).SetCellValue(content[i][j].USBC_CC1_TO_GND); sheet.GetRow(j + 5).CreateCell(12).SetCellValue(content[i][j].USBC_VBUS_TO_USBC_CC1); sheet.GetRow(j + 5).CreateCell(13).SetCellValue(content[i][j].USBC_VBUS_TO_USBC_VCONN); sheet.GetRow(j + 5).CreateCell(14).SetCellValue(content[i][j].USBC_CC1_TO_USB_DP1); sheet.GetRow(j + 5).CreateCell(15).SetCellValue(content[i][j].USB_DP1_TO_USB_DN1); sheet.GetRow(j + 5).CreateCell(16).SetCellValue(content[i][j].USBC_VBUS_TO_USB_DN1); sheet.GetRow(j + 5).CreateCell(17).SetCellValue(content[i][j].PP_USBC_VBUS); sheet.GetRow(j + 5).CreateCell(18).SetCellValue(content[i][j].TP0304); sheet.GetRow(j + 5).CreateCell(19).SetCellValue(content[i][j].USBC_CC1); sheet.GetRow(j + 5).CreateCell(20).SetCellValue(content[i][j].USB_DP1); sheet.GetRow(j + 5).CreateCell(21).SetCellValue(content[i][j].USB_DN1); sheet.GetRow(j + 5).CreateCell(22).SetCellValue(content[i][j].CURRENT_CC1_475uA); sheet.GetRow(j + 5).CreateCell(23).SetCellValue(content[i][j].CURRENT_VCONN_475uA); sheet.GetRow(j + 5).CreateCell(24).SetCellValue(content[i][j].CURRENT_CC1_550uA); sheet.GetRow(j + 5).CreateCell(25).SetCellValue(content[i][j].CURRENT_VCONN_550uA); } #endregion } try { workbook.Write(fs_write); } catch (Exception e) { MessageBox.Show(e.Message); return(false); } finally { if (fs_write != null) { fs_write.Close(); } } return(true); }
//private static WriteLog wl = new WriteLog(); #region 从datatable中将数据导出到excel /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> static MemoryStream ExportDT(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet; #region 右击文件 属性信息 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://www.yongfa365.com/"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "柳永法"; //填加xls文件作者信息 // si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息 // si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息 // si.Comments = "说明信息"; //填加xls文件作者信息 // si.Title = "NPOI测试"; //填加xls文件标题信息 // si.Subject = "NPOI测试Demo"; //填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} #endregion HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } #region 表头及样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet; //workbook.Dispose(); return(ms); } }
/// <summary> /// 导出 /// </summary> /// <param name="id"></param> /// <returns></returns> public FileResult OutPutExcel(string statedate, string EndDate) { string sql = string.Format(@"select (select MatchBorrowNumber from T_Payment where ID=p.ReunId) as MatchBorrowNumber,(select Reun_Code from T_Payment where ID=p.ReunId) as Reun_Code,(select CrateDate from T_Payment where ID=p.ReunId) as CrateDate, (select PostUser from T_Payment where ID=p.ReunId) as PostUser, (select Reun_Reason from T_Payment where ID=p.ReunId) as Reun_Reason, StoreName,Abstract,Price,Num, (select top 1 ApproveDate from T_PaymentApprove where Reunbursement_id =p.ReunId and ApproveName='" + UserModel.Nickname + "' and ApproveDate<>'' order by ID desc) as ApproveDate,(select top 1 Remark from T_PaymentApprove where Reunbursement_id =p.ReunId and ApproveName='" + UserModel.Nickname + "' and ApproveDate<>'' order by ID desc) as Remark from T_PaymentProduct p where ReunId in(select Reunbursement_id from T_PaymentApprove where ApproveDate>='" + statedate + "' and ApproveDate<='" + EndDate + " 23:59:59' AND ApproveName='" + UserModel.Nickname + "')"); List <OutDetail> list = db.Database.SqlQuery <OutDetail>(sql).ToList(); //创建Excel文件的对象 HSSFWorkbook book = new HSSFWorkbook(); //添加一个sheet ISheet sheet1 = book.CreateSheet("Sheet1"); IRow row1 = sheet1.CreateRow(0); row1.Height = 3 * 265; IFont cfont = book.CreateFont(); cfont.FontName = "宋体"; cfont.FontHeight = 1 * 256; row1.CreateCell(0).SetCellValue("申请日期"); row1.CreateCell(1).SetCellValue("报销人"); row1.CreateCell(2).SetCellValue("报销原因"); row1.CreateCell(3).SetCellValue("店铺名称"); row1.CreateCell(4).SetCellValue("产品名称"); row1.CreateCell(5).SetCellValue("单价"); row1.CreateCell(6).SetCellValue("数量"); row1.CreateCell(7).SetCellValue("应付金额"); row1.CreateCell(8).SetCellValue("审核日期"); row1.CreateCell(9).SetCellValue("报销单号"); row1.CreateCell(10).SetCellValue("审核备注"); row1.CreateCell(11).SetCellValue("冲抵借支批号"); //Remark sheet1.SetColumnWidth(0, 20 * 256); sheet1.SetColumnWidth(1, 30 * 256); sheet1.SetColumnWidth(2, 15 * 256); sheet1.SetColumnWidth(3, 15 * 256); sheet1.SetColumnWidth(4, 20 * 256); sheet1.SetColumnWidth(5, 20 * 256); sheet1.SetColumnWidth(6, 20 * 256); sheet1.SetColumnWidth(7, 20 * 256); sheet1.SetColumnWidth(8, 20 * 256); sheet1.SetColumnWidth(9, 20 * 256); sheet1.SetColumnWidth(10, 20 * 256); sheet1.SetColumnWidth(11, 20 * 256); double cost = 0; for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.Height = 3 * 265; rowtemp.CreateCell(0).SetCellValue(list[i].CrateDate.ToString()); rowtemp.Cells[0].CellStyle.Alignment = HorizontalAlignment.Center; rowtemp.Cells[0].CellStyle.VerticalAlignment = VerticalAlignment.Center; rowtemp.Cells[0].CellStyle.WrapText = true; rowtemp.Cells[0].CellStyle.GetFont(book).FontName = "宋体"; rowtemp.Cells[0].CellStyle.GetFont(book).FontHeight = 1 * 256; rowtemp.CreateCell(1).SetCellValue(string.IsNullOrWhiteSpace(list[i].PostUser) ? "" : list[i].PostUser); rowtemp.CreateCell(2).SetCellValue(string.IsNullOrWhiteSpace(list[i].Reun_Reason) ? "" : list[i].Reun_Reason); rowtemp.CreateCell(3).SetCellValue(string.IsNullOrWhiteSpace(list[i].StoreName) ? "" : list[i].StoreName); rowtemp.CreateCell(4).SetCellValue(string.IsNullOrWhiteSpace(list[i].Abstract) ? "" : list[i].Abstract); rowtemp.CreateCell(5).SetCellValue(Convert.ToDouble(list[i].Price).ToString("0.00")); rowtemp.CreateCell(6).SetCellValue(list[i].Num.ToString()); rowtemp.CreateCell(7).SetCellValue(Convert.ToDouble(Convert.ToDouble(list[i].Price) * Convert.ToDouble(list[i].Num)).ToString("0.00")); rowtemp.CreateCell(8).SetCellValue(list[i].approveDate.ToString()); rowtemp.CreateCell(9).SetCellValue(list[i].Reun_Code); rowtemp.CreateCell(10).SetCellValue(list[i].Remark); rowtemp.CreateCell(11).SetCellValue(list[i].MatchBorrowNumber); cost += Convert.ToDouble(Convert.ToDouble(list[i].Price) * Convert.ToDouble(list[i].Num)); } IRow heji = sheet1.CreateRow(list.Count() + 1); ICell heji1 = heji.CreateCell(7); heji1.SetCellValue(cost.ToString("0.00")); Response.ContentType = "application/vnd.ms-excel;charset=UTF-8"; // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); ms.Flush(); ms.Position = 0; return(File(ms, "application/vnd.ms-excel", "报销数据.xls")); }
public ActionResult ExportExcel(BaoYangPriceSelectModel selectModel, string pageType) { PagerModel pager = new PagerModel(1, 100000); var manager = new BaoYangPriceGuideManager(); var list = manager.SelectBaoYangPriceGuide(selectModel, pager); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row = sheet1.CreateRow(0); var fileName = $"保养价格指导{ThreadIdentity.Operator.Name.Split('@')[0]}.xls"; var rowNum = 0; row.CreateCell(rowNum).SetCellValue("品牌"); row.CreateCell(rowNum++).SetCellValue("PID"); row.CreateCell(rowNum++).SetCellValue("二级类目"); row.CreateCell(rowNum++).SetCellValue("三级类目"); row.CreateCell(rowNum++).SetCellValue("产品名称"); row.CreateCell(rowNum++).SetCellValue("库存"); row.CreateCell(rowNum++).SetCellValue("工场店库存"); row.CreateCell(rowNum++).SetCellValue("近7天销量"); row.CreateCell(rowNum++).SetCellValue("近30天销量"); row.CreateCell(rowNum++).SetCellValue("周转天数"); if (!pageType.Equals("see")) { row.CreateCell(rowNum++).SetCellValue("进货价"); row.CreateCell(rowNum++).SetCellValue("最近一次采购价"); } if (string.IsNullOrWhiteSpace(pageType)) { row.CreateCell(rowNum++).SetCellValue("理论指导价"); row.CreateCell(rowNum++).SetCellValue("实际指导价"); } row.CreateCell(rowNum++).SetCellValue("官网价格"); if (string.IsNullOrWhiteSpace(pageType)) { row.CreateCell(rowNum++).SetCellValue("活动价"); } if (!pageType.Equals("see")) { row.CreateCell(rowNum++).SetCellValue("途虎毛利率"); row.CreateCell(rowNum++).SetCellValue("途虎毛利额"); } row.CreateCell(rowNum++).SetCellValue("汽配龙"); if (!pageType.Equals("see")) { row.CreateCell(rowNum++).SetCellValue("汽配龙毛利率"); row.CreateCell(rowNum++).SetCellValue("汽配龙毛利额"); row.CreateCell(rowNum++).SetCellValue("工场店毛利率"); row.CreateCell(rowNum++).SetCellValue("工场店毛利额"); } row.CreateCell(rowNum++).SetCellValue("京东自营"); row.CreateCell(rowNum++).SetCellValue("特维轮天猫"); row.CreateCell(rowNum++).SetCellValue("养车无忧官网"); row.CreateCell(rowNum++).SetCellValue("汽车超人零售"); row.CreateCell(rowNum++).SetCellValue("康众官网"); row.CreateCell(rowNum++).SetCellValue("汽车超人批发"); row.CreateCell(rowNum++).SetCellValue("途虎淘宝"); row.CreateCell(rowNum++).SetCellValue("途虎淘宝2"); row.CreateCell(rowNum++).SetCellValue("途虎天猫1"); row.CreateCell(rowNum++).SetCellValue("途虎天猫2"); row.CreateCell(rowNum++).SetCellValue("途虎天猫3"); row.CreateCell(rowNum++).SetCellValue("途虎天猫4"); row.CreateCell(rowNum++).SetCellValue("途虎京东"); row.CreateCell(rowNum).SetCellValue("途虎京东旗舰"); if (list.Item1 && list.Item2.Any()) { var i = 0; foreach (var item in list.Item2) { var rowtempNum = 0; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(++i); rowtemp.CreateCell(rowtempNum).SetCellValue(item.Brand); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.PID); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.SecondType); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ThirdType); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ProductName); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.totalstock == null ? "" : item.totalstock.Value.ToString()); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ShopStock); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_week == null ? "" : item.num_week.Value.ToString()); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_month == null ? "" : item.num_month.Value.ToString()); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_month == null || item.totalstock == null || item.num_month <= 0 || item.totalstock <= 0 ? "" : Math.Ceiling((decimal)item.totalstock / item.num_month.Value * 30).ToString(CultureInfo.InvariantCulture)); if (!pageType.Equals("see")) { rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.cost == null ? "" : item.cost.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.PurchasePrice == null ? "" : item.PurchasePrice.Value.ToString("0.00")); } if (string.IsNullOrWhiteSpace(pageType)) { rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.TheoryGuidePrice == null ? "" : item.TheoryGuidePrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.ActualGuidePrice == null ? "" : item.ActualGuidePrice.Value.ToString("0.00")); } rowtemp.CreateCell(rowtempNum++).SetCellValue(item.Price.ToString("0.00")); if (string.IsNullOrWhiteSpace(pageType)) { rowtemp.CreateCell(rowtempNum++).SetCellValue(item.FlashSalePrice != null ? item.FlashSalePrice.Value.ToString("0.00") : "-"); } if (!pageType.Equals("see")) { rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0 ? ((item.Price - item.cost.Value) / item.Price).ToString("0.00%") : ""); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0 ? (item.Price - item.cost.Value).ToString("0.00") : ""); } rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QPLPrice == null ? "" : item.QPLPrice.Value.ToString("0.00")); if (!pageType.Equals("see")) { rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.QPLPrice > 0 && item.cost > 0 ? (((decimal)item.QPLPrice - item.cost.Value) / (decimal)item.QPLPrice).ToString("0.00%") : ""); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.QPLPrice > 0 && item.cost > 0 ? ((decimal)item.QPLPrice - item.cost.Value).ToString("0.00") : ""); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.QPLPrice > 0 && item.Price > 0 ? ((item.Price - (decimal)item.QPLPrice) / item.Price).ToString("0.00%") : ""); rowtemp.CreateCell(rowtempNum++) .SetCellValue(item.QPLPrice > 0 ? (item.Price - (decimal)item.QPLPrice).ToString("0.00") : ""); } rowtemp.CreateCell(rowtempNum++).SetCellValue(item.JDSelfPrice == null ? "" : item.JDSelfPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TWLTMPrice == null ? "" : item.TWLTMPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QccrlPrice == null ? "" : item.QccrlPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.YcwyPrice == null ? "" : item.YcwyPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.KzPrice == null ? "" : item.KzPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QccrpPrice == null ? "" : item.QccrpPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TBPrice == null ? "" : item.TBPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TB2Price == null ? "" : item.TB2Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM1Price == null ? "" : item.TM1Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM2Price == null ? "" : item.TM2Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM3Price == null ? "" : item.TM3Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM4Price == null ? "" : item.TM4Price.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum++).SetCellValue(item.JDPrice == null ? "" : item.JDPrice.Value.ToString("0.00")); rowtemp.CreateCell(rowtempNum).SetCellValue(item.JDFlagShipPrice == null ? "" : item.JDFlagShipPrice.Value.ToString("0.00")); } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", fileName)); }
//需要引用DLL组件 /// <summary> /// DataGridView导出至Excel,解决问题:打开Excel文件格式与扩展名指定格式不一致 /// </summary> /// <param name="dataGridView">数据源表格</param> /// <param name="isShowExcle">导出时是否显示excel界面</param> /// <returns></returns> //public static bool DcExcel(DataGridView dataGridView, bool isShowExcle = true) //{ // int FormatNum;//保存excel文件的格式 // Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); // string excelVersion = excel.Version;//获取你使用的excel 的版本号 // //声明保存对话框 // SaveFileDialog saveFileDialog = new SaveFileDialog(); // //默然文件后缀 // saveFileDialog.DefaultExt = "xls"; // if (Convert.ToDouble(excelVersion) < 12)//You use Excel 97-2003 // { // FormatNum = -4143; // //文件后缀列表 // saveFileDialog.Filter = "Excel(*.xls)|*.xls"; // } // else//you use excel 2007 or later // { // FormatNum = 56; // //文件后缀列表 // saveFileDialog.Filter = "Excel(*.xls)|*.xls|Excel(2007-2016)(*.xlsx)|*.xlsx"; // } // Form fr = dataGridView.Parent as Form; // if (fr != null)//默认文件名 // { // saveFileDialog.FileName = fr.Text; // } // //默然路径是系统当前路径 // saveFileDialog.InitialDirectory = Directory.GetCurrentDirectory(); // //打开保存对话框 // if (saveFileDialog.ShowDialog() == DialogResult.Cancel) // return false; // //返回文件路径 // string fileName = saveFileDialog.FileName; // if (string.IsNullOrEmpty(fileName.Trim())) // { return false; } // if (dataGridView.Rows.Count == 0) // return false; // //建立Excel对象 // var objWorkbook = excel.Application.Workbooks.Add(true); // excel.Visible = isShowExcle; // //生成字段名称 // for (int i = 0; i < dataGridView.ColumnCount; i++) // { // excel.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText; // excel.Cells[1, i + 1].Font.Bold = true; // } // //填充数据 // for (int i = 0; i < dataGridView.RowCount - 1; i++) // { // for (int j = 0; j < dataGridView.ColumnCount; j++) // { // if (dataGridView[j, i].ValueType == typeof(string)) // { // excel.Cells[i + 2, j + 1] = "'" + dataGridView[j, i].Value.ToString(); // } // else // { // excel.Cells[i + 2, j + 1] = dataGridView[j, i].Value.ToString(); // } // } // } // //Excel.XlFileFormat.xlOpenXMLWorkbook(.xlsx) // //Excel.XlFileFormat.xlExcel8(Excel97 - 2003, .xls) // //判断excel文件的保存格式是xls还是xlsx // var format = fileName.EndsWith(".xls") ? Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8 : Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook; // objWorkbook.SaveAs(fileName, format, Missing.Value, Missing.Value, Missing.Value, // Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, // Missing.Value, Missing.Value); // return true; //} #region 导出到Excel 2003:xls文件 private void DgvToXls(DataGridView dgv) { if (dgv.Rows.Count == 0) { return; } SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Execl files (*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = "导出数据到本地计算机"; saveFileDialog.ShowDialog(); if (saveFileDialog.FileName == "") { return; } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(); HSSFRow headRow = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i < dgv.Columns.Count; i++) { HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String); headCell.SetCellValue(dgv.Columns[i].HeaderText); } for (int i = 0; i < dgv.Rows.Count; i++) { HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1); for (int j = 0; j < dgv.Columns.Count; j++) { HSSFCell cell = (HSSFCell)row.CreateCell(j); if (dgv.Rows[i].Cells[j].Value == null) { cell.SetCellType(CellType.Blank); } else { if (dgv.Columns[j].HeaderText == "日期" && dgv.Rows[i].Cells[j].Value != DBNull.Value) { cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd")); } else { cell.SetCellValue((dgv.Rows[i].Cells[j].Value).ToString()); } //if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32")) //{ // cell.SetCellValue(Convert.ToInt32(dgv.Rows[i].Cells[j].Value)); //} //else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.String")) //{ // cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString()); //} //else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single")) //{ // cell.SetCellValue(Convert.ToSingle(dgv.Rows[i].Cells[j].Value)); //} //else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double")) //{ // cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value)); //} //else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal")) //{ // cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value)); //} //else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime")) //{ // cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd")); //} //else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Time")) //{ // cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("HH-mm-ss")); //} } } } for (int i = 0; i < dgv.Columns.Count; i++) { sheet.AutoSizeColumn(i); } #region 保存到Excel using (FileStream fs = new FileStream(saveFileDialog.FileName, FileMode.Create)) { wb.Write(fs); } #endregion MessageBox.Show("恭喜,导出成功"); }
/// <summary> /// 生成Excel /// </summary> /// <returns>生成的Excel文件</returns> public virtual byte[] GenerateExcel() { NeedPage = false; if (GridHeaders == null) { GetHeaders(); } if (IsSearched == false) { DoSearch(); } HSSFWorkbook workbook = new HSSFWorkbook(); List <HSSFSheet> sheets = new List <NPOI.HSSF.UserModel.HSSFSheet>(); //准备好sheet,每6万行生成一个sheet var sheetno = ((EntityList.Count - 1) / 60000) + 1; var headerStyle = workbook.CreateCellStyle(); //设定单元格边框 headerStyle.BorderBottom = BorderStyle.Thin; headerStyle.BorderLeft = BorderStyle.Thin; headerStyle.BorderRight = BorderStyle.Thin; headerStyle.BorderTop = BorderStyle.Thin; //用灰色填充背景 var headerbg = HSSFColor.Grey25Percent.Index; headerStyle.FillForegroundColor = headerbg; headerStyle.FillPattern = FillPattern.SolidForeground; headerStyle.FillBackgroundColor = headerbg; //去掉 Id 列和动作列 RemoveActionAndIdColumn(); //循环生成所有sheet,并为每个sheet添加表头 var headerrows = 0; for (int i = 1; i <= sheetno; i++) { HSSFSheet sheet = workbook.CreateSheet("Sheet" + i) as HSSFSheet; //生成表头 headerrows = MakeExcelHeader(sheet, GridHeaders, 0, 0, headerStyle); sheets.Add(sheet); } var rowIndex = headerrows; var colIndex = 0; //Excel中用到的style,每种前景色和背景色的组合为一个style。Nopi值支持4000个style,所以同样的style要重复使用 Dictionary <string, ICellStyle> styles = new Dictionary <string, ICellStyle>(); //Excel中用到的font,主要是为了更改字体颜色 Dictionary <string, IFont> fonts = new Dictionary <string, IFont>(); //循环数据 foreach (var row in EntityList) { var sheetindex = ((rowIndex - headerrows) / 60000); colIndex = 0; string bgColor = ""; string fColor = ""; //获取设定的行背景色 bgColor = SetFullRowBgColor(row); //获取设定的行前景色 fColor = SetFullRowColor(row); var dr = sheets[sheetindex].CreateRow(rowIndex - sheetindex * 60000) as HSSFRow; foreach (var baseCol in GridHeaders) { //处理枚举变量的多语言 bool IsEmunBoolParp = false; var proType = baseCol.FieldType; if (proType.IsEnumOrNullableEnum()) { IsEmunBoolParp = true; } foreach (var col in baseCol.BottomChildren) { //获取数据,并过滤特殊字符 string text = Regex.Replace(col.GetText(row).ToString(), @"<[^>]*>", String.Empty); //处理枚举变量的多语言 if (IsEmunBoolParp) { if (int.TryParse(text, out int enumvalue)) { text = PropertyHelper.GetEnumDisplayName(proType, enumvalue); } } //建立excel单元格 var cell = dr.CreateCell(colIndex); ICellStyle style = null; IFont font = null; var styleKey = string.Empty; //获取设定的单元格背景色 string backColor = col.GetBackGroundColor(row); //获取设定的单元格前景色 string foreColor = col.GetForeGroundColor(row); //如果行背景色或单元格背景色有值,则用颜色的ARGB的值作为style的key if (bgColor != "" || backColor != "") { styleKey = backColor == "" ? bgColor : backColor; } //如果行前景色或单元格前景色有值,则用颜色的ARGB加上背景色的ARGB作为style的key if (fColor != "" || foreColor != "") { styleKey += foreColor == "" ? foreColor : fColor; } //如果已经有符合条件的style,则使用 if (styles.ContainsKey(styleKey)) { style = styles[styleKey]; } //如果没有,则新建一个style else { var newKey = ""; var newFontKey = ""; //新建style style = workbook.CreateCellStyle(); //设定单元格边框 style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; //如果行前景色或单元格前景色有值,则设定单元格的填充颜色 if (bgColor != "" || backColor != "") { newKey = backColor == "" ? bgColor : backColor; var ci = Utils.GetExcelColor(backColor == "" ? bgColor : backColor); style.FillForegroundColor = ci; style.FillPattern = FillPattern.SolidForeground; style.FillBackgroundColor = ci; } //如果行前景色或单元格前景色有值,则设定单元格的字体的颜色 if (fColor != "" || foreColor != "") { newFontKey = foreColor == "" ? foreColor : fColor; newKey += foreColor == "" ? foreColor : fColor; //如果已经有符合条件的字体,则使用 if (fonts.ContainsKey(newFontKey)) { font = fonts[newFontKey]; } //如果没有,则新建 else { //新建字体 font = workbook.CreateFont(); //设定字体颜色 font.Color = Utils.GetExcelColor(foreColor == "" ? fColor : foreColor); //向集合中添加新字体 fonts.Add(newFontKey, font); } //设定style中的字体 style.SetFont(font); } //将新建的style添加到集合中 styles.Add(newKey, style); } cell.CellStyle = style; cell.SetCellValue(text); colIndex++; } } rowIndex++; } //获取Excel文件的二进制数据 byte[] rv = new byte[] { }; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; rv = ms.ToArray(); } return(rv); }
/// <summary> /// 转换内存表为EXCEL文件流(行数超过65535,sheet分页) /// </summary> /// <param name="SourceTable">源数据</param> /// <param name="sheetSize">sheet最大行数,不大于65535</param> /// <param name="DateTimeFormat">时间列格式化</param> /// <returns>EXCEL文件流</returns> public static Stream RenderDataTableToPagingExcelStream(DataTable SourceTable, int sheetSize = 65535, string DateTimeFormat = "yyyy-MM-dd HH:mm:ss") { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); IDataFormat dataformat = workbook.CreateDataFormat(); ICellStyle style = workbook.CreateCellStyle(); int count = SourceTable.Rows.Count; int total = count / sheetSize + (count % sheetSize > 0 ? 1 : 0); for ( int sheetIndex = 0; sheetIndex < total; sheetIndex++) { ISheet sheet = workbook.CreateSheet(); IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in SourceTable.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } // handling value. int rowIndex = 1; for (int i = sheetIndex * sheetSize; i < (total.Equals(sheetIndex + 1) ? count : (sheetIndex + 1) * sheetSize); i++) { DataRow row = SourceTable.Rows[i]; IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns) { if (row[column] is DBNull) { dataRow.CreateCell(column.Ordinal).SetCellValue(string.Empty); continue; } if (column.DataType == typeof(int)) { dataRow.CreateCell(column.Ordinal).SetCellValue((int)row[column]); } else if (column.DataType == typeof(float)) { dataRow.CreateCell(column.Ordinal).SetCellValue((float)row[column]); } else if (column.DataType == typeof(double)) { dataRow.CreateCell(column.Ordinal).SetCellValue((double)row[column]); } else if (column.DataType == typeof(Byte)) { dataRow.CreateCell(column.Ordinal).SetCellValue((byte)row[column]); } else if (column.DataType == typeof(UInt16)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt16)row[column]); } else if (column.DataType == typeof(UInt32)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt32)row[column]); } else if (column.DataType == typeof(UInt64)) { dataRow.CreateCell(column.Ordinal).SetCellValue((UInt64)row[column]); } else if (column.DataType == typeof(DateTime)) { dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)row[column]); style.DataFormat = dataformat.GetFormat(DateTimeFormat); dataRow.GetCell(column.Ordinal).CellStyle = style; } else { dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column])); } } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; } workbook = null; return(ms); }
/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param> public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "刘晓雷"; //填加xls文件作者信息 si.ApplicationName = "恒泰信息"; //填加xls文件创建程序信息 si.LastAuthor = "刘晓雷"; //填加xls文件最后保存者信息 si.Comments = "刘晓雷"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion #region 设置标题样式 ICellStyle headStyle = workbook.CreateCellStyle(); int[] arrColWidth = new int[properties.Length]; string[] arrColName = new string[properties.Length]; //列名 ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表 headStyle.Alignment = HorizontalAlignment.Center; // ------------------ if (excelConfig.Background != new Color()) { if (excelConfig.Background != new Color()) { headStyle.FillPattern = FillPattern.SolidForeground; headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background); } } IFont font = workbook.CreateFont(); font.FontHeightInPoints = excelConfig.TitlePoint; if (excelConfig.ForeColor != new Color()) { font.Color = GetXLColour(workbook, excelConfig.ForeColor); } font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 列头及样式 ICellStyle cHeadStyle = workbook.CreateCellStyle(); cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont cfont = workbook.CreateFont(); cfont.FontHeightInPoints = excelConfig.HeadPoint; cHeadStyle.SetFont(cfont); #endregion #region 设置内容单元格样式 int i = 0; foreach (PropertyInfo column in properties) { ICellStyle columnStyle = workbook.CreateCellStyle(); columnStyle.Alignment = HorizontalAlignment.Center; arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length; arrColName[i] = column.Name; if (excelConfig.ColumnEntity != null) { ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name); if (columnentity != null) { arrColName[i] = columnentity.ExcelColumn; if (columnentity.Width != 0) { arrColWidth[i] = columnentity.Width; } if (columnentity.Background != new Color()) { if (columnentity.Background != new Color()) { columnStyle.FillPattern = FillPattern.SolidForeground; columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background); } } if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color()) { IFont columnFont = workbook.CreateFont(); columnFont.FontHeightInPoints = 10; if (columnentity.Font != null) { columnFont.FontName = columnentity.Font; } if (columnentity.Point != 0) { columnFont.FontHeightInPoints = columnentity.Point; } if (columnentity.ForeColor != new Color()) { columnFont.Color = GetXLColour(workbook, columnentity.ForeColor); } columnStyle.SetFont(font); } } } arryColumStyle[i] = columnStyle; i++; } #endregion #region 填充数据 #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 0; foreach (T item in lists) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(0); if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------ } } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出 int headIndex = 0; foreach (PropertyInfo column in properties) { headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]); headerRow.GetCell(headIndex).CellStyle = cHeadStyle; //设置列宽 sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256); headIndex++; } #endregion } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); int ordinal = 0; foreach (PropertyInfo column in properties) { ICell newCell = dataRow.CreateCell(ordinal); newCell.CellStyle = arryColumStyle[ordinal]; string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString(); SetCell(newCell, dateStyle, column.PropertyType, drValue); ordinal++; } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dtSource"> 源DataTable </param> /// <param name="strHeaderText"> 表头文本 </param> public static MemoryStream ExportMemoryStream(DataTable dtSource, string strHeaderText) { var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { var dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; var si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion 右击文件 属性信息 var dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); var format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 var arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length; } for (var i = 0; i < dtSource.Rows.Count; i++) { for (var j = 0; j < dtSource.Columns.Count; j++) { var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } var rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if ((rowIndex == 65535) || (rowIndex == 0)) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 表头及样式 { var headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); var headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; var font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion 表头及样式 #region 列头及样式 { var headerRow = (HSSFRow)sheet.CreateRow(1); var headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); var 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] + 3) * 256); } //headerRow.Dispose(); } #endregion 列头及样式 rowIndex = 2; } #endregion 新建表,填充表头,填充列头,样式 #region 填充内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { var newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); var drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 var boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": var 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++; } var ms = new MemoryStream(); //using (var ms = new MemoryStream()) //{ workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); //} }
/// <summary> /// 將DataTable轉成Stream輸出. /// </summary> /// <param name="SourceTable">The source table.</param> /// <returns></returns> public Stream RenderDataTableToExcel(DataTable SourceTable, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); InitializeWorkbook(workbook); MemoryStream ms = new MemoryStream(); ISheet sheet = workbook.CreateSheet(); //合并单元格 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8)); //设置标题的样式 IRow titlerow = sheet.CreateRow(0); ICellStyle style = workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 25; style.SetFont(font); titlerow.CreateCell(0).SetCellValue(title); titlerow.GetCell(0).CellStyle = style; //设置时间格式 ICellStyle datetimestyle = workbook.CreateCellStyle(); datetimestyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss"); IRow headerRow = sheet.CreateRow(1); // handling header. foreach (DataColumn column in SourceTable.Columns) { if (column.DataType == typeof(DateTime)) { sheet.SetColumnWidth(column.Ordinal, 5120); } headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } // handling value. int rowIndex = 2; object obj; foreach (DataRow row in SourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns) { obj = row[column]; if (column.DataType == typeof(double)) { if (obj == DBNull.Value) { obj = 0.0; } dataRow.CreateCell(column.Ordinal).SetCellValue((double)obj); } else if (column.DataType == typeof(DateTime)) { dataRow.CreateCell(column.Ordinal).SetCellValue((DateTime)obj); dataRow.GetCell(column.Ordinal).CellStyle = datetimestyle; } else { dataRow.CreateCell(column.Ordinal).SetCellValue(obj.ToString()); } } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return(ms); }
/// <summary> /// 输出Excel内存流 /// </summary> /// <param name="dts">数据表集合</param> /// <returns>内存流</returns> public static MemoryStream ToExcelStream(this DataTable[] dts) { if (dts.IsNullOrLength0()) { return(new MemoryStream()); } MemoryStream ms = null; IWorkbook workbook = null; try { //创建一个工作簿 workbook = new HSSFWorkbook(); for (int k = 0; k < dts.Length; k++) { DataTable dt = dts[k]; string sheetName = string.IsNullOrWhiteSpace(dt.TableName) ? $"sheet{k + 1}" : dt.TableName; //创建一个 sheet 表 ISheet sheet = workbook.CreateSheet(sheetName); //创建一行 IRow rowH = sheet.CreateRow(0); //创建一个单元格 ICell cell = null; //创建列标题单元格样式 ICellStyle cellTitleStyle = workbook.CreateCellStyle(); cellTitleStyle.Alignment = HorizontalAlignment.Center; cellTitleStyle.VerticalAlignment = VerticalAlignment.Center; IFont f = workbook.CreateFont(); //f.Boldweight = (short)FontBoldWeight.Bold; cellTitleStyle.SetFont(f); //创建格式 IDataFormat dataFormat = workbook.CreateDataFormat(); //设置为文本格式,也可以为 text,即 dataFormat.GetFormat("text"); //cellTitleStyle.DataFormat = dataFormat.GetFormat("@"); //设置列名 foreach (DataColumn col in dt.Columns) { //创建单元格并设置单元格内容 rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption); //设置单元格格式 rowH.Cells[col.Ordinal].CellStyle = cellTitleStyle; } //写入数据 for (int i = 0; i < dt.Rows.Count; i++) { //跳过第一行,第一行为列名 IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { cell = row.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } } //创建一个 IO 流 ms = new MemoryStream(); //写入到流 workbook.Write(ms); return(ms); } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { if (workbook != null) { workbook.Close(); } } }
/// <summary> /// DataTable 导出到 Excel 的 MemoryStream /// </summary> /// <param name="dtSource">源 DataTable</param> /// <param name="strHeaderText">表头文本 空值未不要表头标题</param> /// <returns></returns> public static MemoryStream ExportExcel(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 文件属性 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "517best.com"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "517best.com"; si.ApplicationName = "517best.com"; si.LastAuthor = "517best.com"; si.Comments = ""; si.Title = ""; si.Subject = ""; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding("gb2312").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("gb2312").GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; int intTop = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表、填充表头、填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } intTop = 0; #region 表头及样式 { if (strHeaderText.Length > 0) { IRow headerRow = sheet.CreateRow(intTop); intTop += 1; 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(intTop); intTop += 1; ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); 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 = intTop; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// 导出 /// </summary> /// <param name="colipuSouceModels"></param> private void Export(List <ColipuSouceModel> colipuSouceModels) { //输出Excel HSSFWorkbook workbook = new HSSFWorkbook(); ISheet tb = workbook.CreateSheet("核销结果"); #region 表头 IRow row = tb.CreateRow(0); var cellValList = new List <string>() { "出库单号", "商品编号", "金额", "数量", "签收日期", "是否已核销", }; for (int i = 0; i < cellValList.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(cellValList[i]); } #endregion #region 组装数据 var table = new DataTable("匹配结果"); DataColumn column; column = new DataColumn { ColumnName = "Dorder", Caption = "出库单号" }; table.Columns.Add(column); column = new DataColumn { ColumnName = "ProductCode", Caption = "商品编码" }; table.Columns.Add(column); column = new DataColumn { ColumnName = "Price", Caption = "价格" }; table.Columns.Add(column); column = new DataColumn { ColumnName = "Quantity", Caption = "价格" }; table.Columns.Add(column); column = new DataColumn { ColumnName = "Time", Caption = "签收时间" }; table.Columns.Add(column); column = new DataColumn { ColumnName = "IsHeXiao", Caption = "是否核销" }; table.Columns.Add(column); #endregion //colipuSouceModels foreach (var item in colipuSouceModels.OrderBy(x => x.ProductCode)) { if (item != null) { var tableRow = table.NewRow(); tableRow["Dorder"] = item.DOrder; tableRow["ProductCode"] = item.ProductCode; tableRow["Price"] = item.Price; tableRow["Quantity"] = item.Quantity; tableRow["Time"] = item.Time; tableRow["IsHeXiao"] = item.IsHeXiao; table.Rows.Add(tableRow); } } #region 数据 for (int i = 0; i < table.Rows.Count; i++) { IRow row1 = tb.CreateRow(i + 1); for (int j = 0; j < table.Columns.Count; j++) { ICell cell = row1.CreateCell(j); cell.SetCellValue(table.Rows[i][j].ToString()); } } using (FileStream fs = File.OpenWrite($"{textBox2.Text}/核销结果.xls")) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件! { workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。 if (MessageBox.Show("操作成功!", "", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK) { Environment.Exit(0); Close(); } } #endregion }
protected void btnExcel_Click(object sender, EventArgs e) { _cusName = DTRequest.GetFormString("txtCusName"); _cid = DTRequest.GetFormString("hCusId"); _check1 = DTRequest.GetFormString("ddlcheck1"); _check2 = DTRequest.GetFormString("ddlcheck2"); _check3 = DTRequest.GetFormString("ddlcheck3"); _isconfirm = DTRequest.GetFormString("ddlisConfirm"); _oid = DTRequest.GetFormString("txtOid"); _sign = DTRequest.GetFormString("ddlsign"); _money = DTRequest.GetFormString("txtMoney"); _sdate = DTRequest.GetFormString("txtsDate"); _edate = DTRequest.GetFormString("txteDate"); _farea = DTRequest.GetFormString("ddlfarea"); _darea = DTRequest.GetFormString("ddldarea"); _invType = DTRequest.GetFormString("ddlinvType"); _name = DTRequest.GetFormString("txtName"); _unit = DTRequest.GetFormString("txtUnit"); _purchaserName = DTRequest.GetFormString("txtpurchaserName"); _self = DTRequest.GetFormString("self");//self=1表示个人页面 BLL.invoices bll = new BLL.invoices(); DataTable dt = bll.GetList(this.pageSize, this.page, "inv_id>0" + CombSqlTxt(), "inv_addDate desc,inv_id desc", manager, out this.totalCount, out _tmoney, false).Tables[0]; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=发票列表.xlsx"); //HttpUtility.UrlEncode(fileName)); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("明细"); IFont font = hssfworkbook.CreateFont(); font.Boldweight = short.MaxValue; font.FontHeightInPoints = 11; #region 表格样式 //设置单元格的样式:水平垂直对齐居中 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BottomBorderColor = HSSFColor.Black.Index; cellStyle.LeftBorderColor = HSSFColor.Black.Index; cellStyle.RightBorderColor = HSSFColor.Black.Index; cellStyle.TopBorderColor = HSSFColor.Black.Index; cellStyle.WrapText = true;//自动换行 //设置表头的样式:水平垂直对齐居中,加粗 ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle(); titleCellStyle.Alignment = HorizontalAlignment.Center; titleCellStyle.VerticalAlignment = VerticalAlignment.Center; titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色 titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式 titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色 //设置边框 titleCellStyle.BorderBottom = BorderStyle.Thin; titleCellStyle.BorderLeft = BorderStyle.Thin; titleCellStyle.BorderRight = BorderStyle.Thin; titleCellStyle.BorderTop = BorderStyle.Thin; titleCellStyle.BottomBorderColor = HSSFColor.Black.Index; titleCellStyle.LeftBorderColor = HSSFColor.Black.Index; titleCellStyle.RightBorderColor = HSSFColor.Black.Index; titleCellStyle.TopBorderColor = HSSFColor.Black.Index; //设置字体 titleCellStyle.SetFont(font); #endregion //表头 IRow headRow = sheet.CreateRow(0); headRow.HeightInPoints = 25; headRow.CreateCell(0).SetCellValue("客户"); headRow.CreateCell(1).SetCellValue("订单号"); headRow.CreateCell(2).SetCellValue("开票项目"); headRow.CreateCell(3).SetCellValue("开票金额"); headRow.CreateCell(4).SetCellValue("超开"); headRow.CreateCell(5).SetCellValue("送票方式"); headRow.CreateCell(6).SetCellValue("开票区域"); headRow.CreateCell(7).SetCellValue("申请人"); headRow.CreateCell(8).SetCellValue("申请区域审批"); headRow.CreateCell(9).SetCellValue("开票区域审批"); headRow.CreateCell(10).SetCellValue("财务审批"); headRow.CreateCell(11).SetCellValue("开票状态"); headRow.CreateCell(12).SetCellValue("开票日期"); headRow.CreateCell(13).SetCellValue("专普票"); headRow.CreateCell(14).SetCellValue("开票单位"); headRow.CreateCell(15).SetCellValue("购买方名称"); headRow.GetCell(0).CellStyle = titleCellStyle; headRow.GetCell(1).CellStyle = titleCellStyle; headRow.GetCell(2).CellStyle = titleCellStyle; headRow.GetCell(3).CellStyle = titleCellStyle; headRow.GetCell(4).CellStyle = titleCellStyle; headRow.GetCell(5).CellStyle = titleCellStyle; headRow.GetCell(6).CellStyle = titleCellStyle; headRow.GetCell(7).CellStyle = titleCellStyle; headRow.GetCell(8).CellStyle = titleCellStyle; headRow.GetCell(9).CellStyle = titleCellStyle; headRow.GetCell(10).CellStyle = titleCellStyle; headRow.GetCell(11).CellStyle = titleCellStyle; headRow.GetCell(12).CellStyle = titleCellStyle; headRow.GetCell(13).CellStyle = titleCellStyle; headRow.GetCell(14).CellStyle = titleCellStyle; headRow.GetCell(15).CellStyle = titleCellStyle; sheet.SetColumnWidth(0, 15 * 256); sheet.SetColumnWidth(1, 20 * 256); sheet.SetColumnWidth(2, 20 * 256); sheet.SetColumnWidth(3, 20 * 256); sheet.SetColumnWidth(4, 20 * 256); sheet.SetColumnWidth(5, 15 * 256); sheet.SetColumnWidth(6, 20 * 256); sheet.SetColumnWidth(7, 20 * 256); sheet.SetColumnWidth(8, 20 * 256); sheet.SetColumnWidth(9, 20 * 256); sheet.SetColumnWidth(10, 20 * 256); sheet.SetColumnWidth(11, 20 * 256); sheet.SetColumnWidth(12, 20 * 256); sheet.SetColumnWidth(13, 20 * 256); sheet.SetColumnWidth(14, 20 * 256); sheet.SetColumnWidth(15, 20 * 256); if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); row.HeightInPoints = 22; row.CreateCell(0).SetCellValue(dt.Rows[i]["c_name"].ToString()); row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_oid"])); row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_serviceType"]) + "/" + Utils.ObjectToStr(dt.Rows[i]["inv_serviceName"])); row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_money"])); row.CreateCell(4).SetCellValue("0"); row.CreateCell(5).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_sentWay"])); row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["de_subname"])); row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_personName"])); row.CreateCell(8).SetCellValue(dt.Rows[i]["inv_flag1"].ToString() == "0" ? "待审批" : dt.Rows[i]["inv_flag1"].ToString() == "1" ? "审批未通过" : "审批通过"); row.CreateCell(9).SetCellValue(dt.Rows[i]["inv_flag2"].ToString() == "0" ? "待审批" : dt.Rows[i]["inv_flag2"].ToString() == "1" ? "审批未通过" : "审批通过"); row.CreateCell(10).SetCellValue(dt.Rows[i]["inv_flag3"].ToString() == "0" ? "待审批" : dt.Rows[i]["inv_flag3"].ToString() == "1" ? "审批未通过" : "审批通过"); row.CreateCell(11).SetCellValue(Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["inv_isConfirm"]), false) ? "已开票" : "未开票"); row.CreateCell(12).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["inv_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["inv_date"]).Value.ToString("yyyy-MM-dd")); row.CreateCell(13).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_type"])); row.CreateCell(14).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["invU_name"])); row.CreateCell(15).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["inv_purchaserName"])); row.GetCell(0).CellStyle = cellStyle; row.GetCell(1).CellStyle = cellStyle; row.GetCell(2).CellStyle = cellStyle; row.GetCell(3).CellStyle = cellStyle; row.GetCell(4).CellStyle = cellStyle; row.GetCell(5).CellStyle = cellStyle; row.GetCell(6).CellStyle = cellStyle; row.GetCell(7).CellStyle = cellStyle; row.GetCell(8).CellStyle = cellStyle; row.GetCell(9).CellStyle = cellStyle; row.GetCell(10).CellStyle = cellStyle; row.GetCell(11).CellStyle = cellStyle; row.GetCell(12).CellStyle = cellStyle; row.GetCell(13).CellStyle = cellStyle; row.GetCell(14).CellStyle = cellStyle; row.GetCell(15).CellStyle = cellStyle; } } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); }
public FileResult ExamRoomExport() { #region 生成xls string[] temArr = { "考场", "科目时间", "监考老师", "座位号", "姓名", "准考证", "学生班级" }; List <string> lstTitle = new List <string>(temArr); //var lstTitle = new List { "考场", "科目时间", "监考老师", "座位号", "姓名", "准考证", "学生班级" };//, IWorkbook book = new HSSFWorkbook(); ISheet sheet = book.CreateSheet("Sheet1"); IRow rowTitle = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中 for (int i = 0; i < lstTitle.Count; i++) { rowTitle.CreateCell(i).SetCellValue(lstTitle[i]); } //var list = null; ////var list = data.GetListResultStudent(examId); //if (list != null) //{ // list.OrderBy(o => o.RoomId); // int start = 0;//记录同组开始行号 // int end = 0;//记录同组结束行号 // string temp = "";//记录上一行的值 // for (int i = 0; i < list.Count; i++) // { // IRow row = sheet.CreateRow(i + 1); // row.CreateCell(0).SetCellValue(list[i].RoomName); // row.CreateCell(1).SetCellValue(list[i].ExamTime.ToLongDateString() + list[i].ExamTime.ToLongTimeString()); // row.CreateCell(2).SetCellValue(list[i].TeacherName); // row.CreateCell(3).SetCellValue(list[i].ZuoWeiNumber); // row.CreateCell(4).SetCellValue(list[i].StudentName); // row.CreateCell(5).SetCellValue(list[i].ZhunNumber); // row.CreateCell(6).SetCellValue(list[i].ClassName); // row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellType(CellType.String); // var cellText = row.Cells[0].StringCellValue;//获取当前行 第1列的单元格的值 // if (cellText == temp)//上下行相等,记录要合并的最后一行 // { // end = i; // } // else//上下行不等,记录 // { // if (start != end) // { // //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域 // //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列 // CellRangeAddress region = new CellRangeAddress(start + 1, end + 1, 0, 0); // sheet.AddMergedRegion(region); // } // start = i; // end = i; // temp = cellText; // } // } //} #endregion for (int i = 0; i < 7; i++) { sheet.AutoSizeColumn(i);//i:根据标题的个数设置自动列宽 } MemoryStream ms = new MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "考场总表导出.xls")); }
public void ExportApproval(string HTCode, string ApplierMUDID, string BUHeadMUDID, string Category, string Type) { #region 抓取数据 int total = 0; var list = PreApprovalService.QueryLoad(HTCode, ApplierMUDID, BUHeadMUDID, Category, Type, int.MaxValue, 1, out total ).Select(a => new { c1 = FormatterNull(a.c1), c2 = FormatterNull(a.c2), c3 = FormatterNull(a.c3), c4 = FormatterNull(a.c4), c5 = FormatterNull(a.c5), c6 = FormatterNull(a.c6), c7 = FormatterNull(a.c7), c8 = FormatterNull(a.c8), c9 = FormatterNull(a.c9), c10 = FormatterNull(a.c10), c11 = FormatterNull(a.c11), c12 = FormatterNull(a.c12), c13 = FormatterNull(a.c13) }).ToArray();; #endregion #region 构建Excel HSSFWorkbook wk = new HSSFWorkbook(); ISheet sheet = wk.CreateSheet("Cater"); IRow row = sheet.CreateRow(0); //ICellStyle style = wk.CreateCellStyle(); //style.WrapText = true; //style.Alignment = HorizontalAlignment.Center; //IFont font = wk.CreateFont(); //font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; //font.FontHeightInPoints = 10; //style.SetFont(font); ICellStyle style = wk.CreateCellStyle(); style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightOrange.Index; style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightOrange.Index; style.Alignment = HorizontalAlignment.Center; IFont font = wk.CreateFont(); font.Color = NPOI.HSSF.Util.HSSFColor.White.Index; font.Boldweight = short.MaxValue; font.FontHeightInPoints = 10; style.SetFont(font); #endregion #region 生成表头 var title = new string[] { "HT编号", "申请人姓名", "申请人MUDID", "流程类别", "流程状态", "提交日期", "提交时间", "审批人MUDID", "审批人姓名", "审批动作", "审批理由", "审批日期", "审批时间" }; sheet.DefaultRowHeight = 200 * 2; for (var i = 0; i < title.Length; i++) { sheet.SetColumnWidth(i, 15 * 256); } for (var i = 0; i < title.Length; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(title[i]); cell.SetCellType(CellType.String); cell.CellStyle = style; } #endregion #region 制作表体 for (var i = 1; i <= list.Length; i++) { var item = list[i - 1]; row = sheet.CreateRow(i); ICell cell = null; //2018-1-12 史强 注释掉序号列 //cell = row.CreateCell(0); //cell.SetCellValue(i); var j = 0; if (item != null) { cell = row.CreateCell(j); cell.SetCellValue(item.c1); cell = row.CreateCell(++j); cell.SetCellValue(item.c2); cell = row.CreateCell(++j); cell.SetCellValue(item.c3); cell = row.CreateCell(++j); cell.SetCellValue(item.c4); cell = row.CreateCell(++j); cell.SetCellValue(item.c5); cell = row.CreateCell(++j); cell.SetCellValue(item.c6); cell = row.CreateCell(++j); cell.SetCellValue(item.c7); cell = row.CreateCell(++j); cell.SetCellValue(item.c8); cell = row.CreateCell(++j); cell.SetCellValue(item.c9); cell = row.CreateCell(++j); cell.SetCellValue(item.c10); cell = row.CreateCell(++j); cell.SetCellValue(item.c11); cell = row.CreateCell(++j); cell.SetCellValue(item.c12); cell = row.CreateCell(++j); cell.SetCellValue(item.c13); cell = row.CreateCell(++j); } } #endregion #region 写入到客户端 using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { wk.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); Response.ContentType = "application/vnd.ms-excel"; Response.BinaryWrite(ms.ToArray()); } #endregion }
public static HSSFWorkbook GetExcel() { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); //increase the width of Column A sheet.SetColumnWidth(0, 5000); //create the format instance IDataFormat format = workbook.CreateDataFormat(); // Create a row and put some cells in it. Rows are 0 based. ICell cell = sheet.CreateRow(0).CreateCell(0); //number format with 2 digits after the decimal point - "1.20" SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00")); //RMB currency format with comma - "¥20,000" ICell cell2 = sheet.CreateRow(1).CreateCell(0); SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0")); //scentific number format - "3.15E+00" ICell cell3 = sheet.CreateRow(2).CreateCell(0); SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00")); //percent format, 2 digits after the decimal point - "99.33%" ICell cell4 = sheet.CreateRow(3).CreateCell(0); SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%")); //phone number format - "021-65881234" ICell cell5 = sheet.CreateRow(4).CreateCell(0); SetValueAndFormat(workbook, cell5, 02165881234, format.GetFormat("000-00000000")); //Chinese capitalized character number - 壹贰叁 元 ICell cell6 = sheet.CreateRow(5).CreateCell(0); SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元")); //Chinese date string ICell cell7 = sheet.CreateRow(6).CreateCell(0); SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日")); cell7.SetCellValue(new DateTime(2004, 5, 6)); //Chinese date string ICell cell8 = sheet.CreateRow(7).CreateCell(0); SetValueAndFormat(workbook, cell8, new DateTime(2005, 11, 6), format.GetFormat("yyyy年m月d日")); //formula value with datetime style ICell cell9 = sheet.CreateRow(8).CreateCell(0); cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")"; ICellStyle cellStyle9 = workbook.CreateCellStyle(); cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); cell9.CellStyle = cellStyle9; //display current time ICell cell10 = sheet.CreateRow(9).CreateCell(0); SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@")); ISheet sheet2 = workbook.CreateSheet("Sheet2"); //increase the width of Column A sheet2.SetColumnWidth(0, 5000); // Create a row and put some cells in it. Rows are 0 based. ICell cell21 = sheet2.CreateRow(0).CreateCell(0); //number format with 2 digits after the decimal point - "1.20" SetValueAndFormat(workbook, cell21, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00")); return(workbook); }
/// <summary> /// 实体类集合导出到EXCLE2003 /// </summary> /// <param name="cellHeard">单元头的Key和Value:{ { "UserName", "姓名" }, { "Age", "年龄" } };</param> /// <param name="enList">数据源</param> /// <param name="sheetName">工作表名称</param> /// <returns>文件的下载地址</returns> public static string EntityListToExcel2003(Dictionary <string, string> cellHeard, IList enList, string filePath, string sheetName) { try { //string fileName = sheetName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称 //string urlPath = "UpFiles/ExcelFiles/" + fileName; // 文件下载的URL地址,供给前台下载 //string filePath = sheetName; // 文件路径 // 1.检测是否存在文件夹,若不存在就建立个文件夹 string directoryName = Path.GetDirectoryName(filePath); if (!Directory.Exists(directoryName)) { Directory.CreateDirectory(directoryName); } // 2.解析单元格头部,设置单元头的中文名称 HSSFWorkbook workbook = new HSSFWorkbook(); // 工作簿 ISheet sheet = workbook.CreateSheet(sheetName); // 工作表 IRow row = sheet.CreateRow(0); List <string> keys = cellHeard.Keys.ToList(); for (int i = 0; i < keys.Count; i++) { row.CreateCell(i).SetCellValue(cellHeard[keys[i]]); // 列名为Key的值 } // 3.List对象的值赋值到Excel的单元格里 int rowIndex = 1; // 从第二行开始赋值(第一行已设置为单元头) foreach (var en in enList) { IRow rowTmp = sheet.CreateRow(rowIndex); for (int i = 0; i < keys.Count; i++) // 根据指定的属性名称,获取对象指定属性的值 { string cellValue = ""; // 单元格的值 object properotyValue = null; // 属性的值 System.Reflection.PropertyInfo properotyInfo = null; // 属性的信息 // 3.1 若属性头的名称包含'.',就表示是子类里的属性,那么就要遍历子类,eg:UserEn.UserName if (keys[i].IndexOf(".") >= 0) { // 3.1.1 解析子类属性(这里只解析1层子类,多层子类未处理) string[] properotyArray = keys[i].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries); string subClassName = properotyArray[0]; // '.'前面的为子类的名称 string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称 System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型 if (subClassInfo != null) { // 3.1.2 获取子类的实例 var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null); // 3.1.3 根据属性名称获取子类里的属性类型 properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName); if (properotyInfo != null) { properotyValue = properotyInfo.GetValue(subClassEn, null); // 获取子类属性的值 } } } else { // 3.2 若不是子类的属性,直接根据属性名称获取对象对应的属性 properotyInfo = en.GetType().GetProperty(keys[i]); if (properotyInfo != null) { properotyValue = properotyInfo.GetValue(en, null); } } // 3.3 属性值经过转换赋值给单元格值 if (properotyValue != null) { cellValue = properotyValue.ToString(); // 3.3.1 对时间初始值赋值为空 if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59") { cellValue = ""; } } // 3.4 填充到Excel的单元格里 rowTmp.CreateCell(i).SetCellValue(cellValue); } rowIndex++; } // 4.生成文件 FileStream file = new FileStream(filePath, FileMode.Create); workbook.Write(file); file.Close(); // 5.返回下载路径 return(filePath); } catch (Exception ex) { throw ex; } }
private void SaveExcelFile(string FilePath) { Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet("DataFromReportProgram"); // dgv에서 column 참조해서 나중에 재설정할것 var columns = new[] { "ID", "Name" }; var headers = new[] { "ID", "Name" }; var headerRow = sheet.CreateRow(0); var font = workbook.CreateFont(); var headerFont = workbook.CreateFont(); headerFont.FontHeightInPoints = 11; headerFont.FontName = "맑은 고딕"; headerFont.IsBold = true; headerFont.Color = IndexedColors.White.Index; var dataFont = workbook.CreateFont(); dataFont.FontHeightInPoints = 11; dataFont.FontName = "맑은 고딕"; dataFont.IsBold = false; dataFont.Color = IndexedColors.Black.Index; ICellStyle HeaderStyle = workbook.CreateCellStyle(); HeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double; HeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HeaderStyle.FillForegroundColor = IndexedColors.DarkBlue.Index; HeaderStyle.FillPattern = FillPattern.SolidForeground; HeaderStyle.SetFont(headerFont); ICellStyle DefaultStyle_Odd = workbook.CreateCellStyle(); DefaultStyle_Odd.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; DefaultStyle_Odd.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; DefaultStyle_Odd.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; DefaultStyle_Odd.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; DefaultStyle_Odd.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; DefaultStyle_Odd.FillForegroundColor = IndexedColors.White.Index; DefaultStyle_Odd.FillPattern = FillPattern.SolidForeground; DefaultStyle_Odd.SetFont(dataFont); ICellStyle DefaultStyle_Even = workbook.CreateCellStyle(); DefaultStyle_Even.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; DefaultStyle_Even.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; DefaultStyle_Even.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; DefaultStyle_Even.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; DefaultStyle_Even.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; DefaultStyle_Even.FillForegroundColor = IndexedColors.Grey25Percent.Index; DefaultStyle_Even.FillPattern = FillPattern.SolidForeground; DefaultStyle_Even.SetFont(dataFont); /* * ICellStyle cellStyle1 = workbook.CreateCellStyle(); * cellStyle1.FillForegroundColor = IndexedColors.White.Index; * cellStyle1.FillPattern = FillPattern.SolidForeground; * cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; * cellStyle1.SetFont(dataFont); * * ICellStyle cellStyle2 = workbook.CreateCellStyle(); * cellStyle2.FillForegroundColor = IndexedColors.Grey25Percent.Index; * cellStyle2.FillPattern = FillPattern.SolidForeground; * cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; * cellStyle2.SetFont(dataFont); */ //Below loop is create header for (int i = 0; i < selectedDataView.ColumnCount; i++) { var cell = headerRow.CreateCell(i); cell.SetCellValue(selectedDataView.Columns[i].HeaderText); cell.CellStyle = HeaderStyle; } //Below loop is fill content for (int i = 0; i < selectedDataView.RowCount; i++) { var row = sheet.CreateRow(i + 1); for (int j = 0; j < selectedDataView.ColumnCount; j++) { var cell = row.CreateCell(j); cell.SetCellValue(selectedDataView.Rows[i].Cells[j].Value.ToString()); if (i % 2 == 0) { cell.CellStyle = DefaultStyle_Even; } else { cell.CellStyle = DefaultStyle_Odd; } } } // 셀 크기 조정 for (int i = 0; i < selectedDataView.ColumnCount; i++) { // 너비 자동조정 sheet.AutoSizeColumn(i); // 자동 조정하면 너무 빡빡해서 여백을 약간 줌 sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 512); } // 셀 높이 조정 (기본 Height 255) /*for(int i=0; i<dgv.RowCount; i++) * { * sheet.GetRow(i).Height = 300; * }*/ // Declare one MemoryStream variable for write file in stream var stream = new MemoryStream(); workbook.Write(stream); //Write to file using file stream FileStream file = new FileStream(FilePath, FileMode.Create, FileAccess.Write); stream.WriteTo(file); file.Close(); stream.Close(); stopwatch.Stop(); MessageBox.Show("time : " + stopwatch.ElapsedMilliseconds + "ms"); #region 어플리케이션에 셀 하나씩 직접 대입 /* string filePath = "D:\\SavedExcelFolder\\"+string.Format("Test {0}.xls", DateTime.Now.ToString("yyyy-MM-dd")); * * Excel._Application app = new Excel.Application(); * Excel.Workbook workbook; * Excel.Worksheet worksheet; * * workbook = app.Workbooks.Add(Type.Missing); * * app.Visible = false; * * worksheet = workbook.Sheets["Sheet1"]; * worksheet = workbook.ActiveSheet; * * worksheet.Name = sheetName; * int colIndex = 0; * * //// storing header part in Excel * //for (int i = 1; i < dgv.Columns.Count + 1; i++) * //{ * // if (dgv.Columns[i - 1].Visible == true) * // { * // colIndex += 1; * // worksheet.Cells[1, colIndex] = dgv.Columns[i - 1].HeaderText; * // } * //} * //// storing Each row and column value to excel sheet * //for (int i = 0; i < dgv.Rows.Count - 1; i++) * //{ * // colIndex = 0; * // for (int j = 0; j < dgv.Columns.Count; j++) * // { * // if (dgv.Columns[j].Visible == true) * // { * // colIndex += 1; * // worksheet.Cells[i + 2, colIndex] = dgv.Rows[i].Cells[j].Value == null ? "" : dgv.Rows[i].Cells[j].Value.ToString() ?? ""; * // } * // } * //} * * // storing header part in Excel * for (int i = 0; i < dgv.ColumnCount; i++) * { * if (dgv.Columns[i].Visible == true) * { * colIndex += 1; * worksheet.Cells[1, colIndex] = dgv.Columns[i].HeaderText; * } * } * // storing Each row and column value to excel sheet * for (int i = 0; i < dgv.Rows.Count; i++) * { * colIndex = 0; * for (int j = 0; j < dgv.ColumnCount; j++) * { * if (dgv.Columns[j].Visible == true) * { * colIndex += 1; * worksheet.Cells[i + 2, colIndex] = dgv.Rows[i].Cells[j].Value == null ? "" : dgv.Rows[i].Cells[j].Value.ToString() ?? ""; * * * } * } * } * * Excel.Range usedRange; * usedRange = worksheet.UsedRange; * * Excel.Range rangeStart = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[usedRange.Row, usedRange.Column]; * Excel.Range rangeEnd = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[usedRange.Row + usedRange.Rows.Count, usedRange.Column + usedRange.Columns.Count]; * * string usedStartPos = rangeStart.Address; * string usedEndPos = rangeEnd.Address; * * //// BackColor 설정 * //((Excel.Range)excelWorksheet.get_Range("A1", "J1")).Interior.Color = ColorTranslator.ToOle(Color.Navy); * //((Excel.Range)excelWodrksheet.get_Range("A2", "J2")).Interior.Color = ColorTranslator.ToOle(Color.RoyalBlue); * * //// Font Color 설정 * //((Excel.Range)excelWorksheet.get_Range("A1", "J1")).Font.Color = ColorTranslator.ToOle(Color.White); * //((Excel.Range)excelWorksheet.get_Range("A2", "J2")).Font.Color = ColorTranslator.ToOle(Color.White); * * // 상단 첫번째 Row 타이틀 볼드 * SetHeaderBold(worksheet, 1); * * // 자동 넓이 지정 * for (int i = 0; i < usedRange.Columns.Count; i++) * { * AutoFitColumn(worksheet, i+1); * } * * // 타이틀 색상 * for (int i = 0; i < usedRange.Columns.Count; i++) * { * //worksheet.Cells[1, i+1].Interior.Color = Excel.XlRgbColor.rgbGrey; * worksheet.Cells[1, i + 1].Interior.ColorIndex = 16; * } * // 행마다 배경색 번갈아가면서 넣기 * for (int i= 1; i< usedRange.Rows.Count; i++) * { *//*if (i % 2 == 0) * worksheet.Cells.Rows[i + 1].Interior.ColorIndex = 15; * else * worksheet.Cells.Rows[i + 1].Interior.ColorIndex = 0;*//* * for (int j = 1; j < usedRange.Columns.Count; j++) * { * if (i % 2 == 0) * worksheet.Cells[i + 1, j].Interior.ColorIndex = 15; * else * worksheet.Cells[i + 1, j].Interior.ColorIndex = 0; * } * * * } * * // 선그리기 * usedRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; * usedRange.Borders.ColorIndex = 1; * * // 정렬 * usedRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; * workbook.SaveAs(filePath, XlFileFormat.xlWorkbookNormal); * workbook.Close(); * app.Quit();*/ #endregion #region 엑셀에 차트 넣기 /*//차트화할 셀의영역 * Excel.Range chartArea = worksheet.get_Range("A1", "B30"); * * //차트생성 * Excel.ChartObjects chart = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing); * * //차트위치 * Excel.ChartObject mychart = (Excel.ChartObject)chart.Add(100, 40, 800, 400); * * //차트 할당 * Excel.Chart chartPage = mychart.Chart; * * //차트의 데이터 셋팅 * chartPage.SetSourceData(chartArea, Excel.XlRowCol.xlColumns); * * //차트의 형태 * //chartPage.ChartType = Excel.XlChartType.xlCylinderColClustered; * chartPage.ChartType = Excel.XlChartType.xlColumnClustered; * app.DisplayAlerts = false; * * // 작업관리자 프로세스 해제 * System.Runtime.InteropServices.Marshal.ReleaseComObject(app); * System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);*/ #endregion }
/// <summary> /// 将对象组绑定到Excel(当T为Datarow时必须传入ColumnHeader) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="collection"></param> /// <param name="strSheetName"></param> /// <returns></returns> public static IWorkbook FillExcelSheet <T>(IEnumerable <T> collection, string strSheetName, List <string> ColumnHeader = null, Boolean IsExcel2003 = true) { IWorkbook workbook = null; ISheet worksheet = null; if (IsExcel2003) { HSSFWorkbook workbookTemp = new HSSFWorkbook(); worksheet = workbookTemp.CreateSheet(strSheetName); workbook = workbookTemp; } else { XSSFWorkbook workbookTemp = new XSSFWorkbook(); worksheet = workbookTemp.CreateSheet(strSheetName); workbook = workbookTemp; } var type = typeof(T); var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance); int sheetCount = collection.Count() % 50000 == 0 ? collection.Count() / 50000 : collection.Count() / 50000 + 1; for (int i = 0; i < sheetCount; i++) { bool hasWriteHeader = false; int rowPos = 0; IEnumerable <T> outputData = null; if (i != 0) { worksheet = workbook.CreateSheet(strSheetName + "-" + i); } if (collection.Count() >= (i + 1) * 50000) { outputData = collection.Skip(i * 50000).Take(50000); } else { outputData = collection.Skip(i * 50000).Take(collection.Count() - i * 50000); } foreach (var item in outputData) { int colPos = 0; if (!hasWriteHeader) { if (type == typeof(DataRow) || (ColumnHeader != null && ColumnHeader.Count > 0)) { if (ColumnHeader != null) { foreach (string info in ColumnHeader) { if (worksheet.GetRow(rowPos) != null) { worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(info); } else { worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(info); } colPos++; } hasWriteHeader = true; colPos = 0; rowPos++; } else { hasWriteHeader = true; } } else { foreach (var propertyInfo in properties) { var value = propertyInfo.GetValue(item, null); if (propertyInfo != null && (propertyInfo.PropertyType == typeof(String) || propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?) || propertyInfo.PropertyType.IsPrimitive)) { var customAttributes = propertyInfo.GetCustomAttributes(typeof(DescriptionAttribute), true); if (customAttributes.Any()) { var attr = customAttributes.FirstOrDefault() as DescriptionAttribute; var desc = attr.Description; if (worksheet.GetRow(rowPos) != null) { worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(desc); } else { worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(desc); } } else { var desc = propertyInfo.Name; if (worksheet.GetRow(rowPos) != null) { worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(desc); } else { worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(desc); } } colPos++; } else if (propertyInfo.PropertyType.BaseType == typeof(Enum)) { var customAttributes = propertyInfo.GetCustomAttributes(typeof(DescriptionAttribute), true); if (customAttributes.Any()) { var attr = customAttributes.FirstOrDefault() as DescriptionAttribute; var desc = attr.Description; if (worksheet.GetRow(rowPos) != null) { worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(desc); } else { worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(desc); } } else { var desc = propertyInfo.Name; if (worksheet.GetRow(rowPos) != null) { worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(desc); } else { worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(desc); } } colPos++; } } hasWriteHeader = true; colPos = 0; rowPos++; } } if (type == typeof(DataRow) && ColumnHeader != null) { DataRow dr = item as DataRow; foreach (string column in ColumnHeader) { var value = dr[column]; if (value != null && value.GetType() == typeof(DateTime)) { value = ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"); } if (worksheet.GetRow(rowPos) != null) { worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(value.ToString()); } else { worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(value.ToString()); } colPos++; } rowPos++; } else { foreach (var propertyInfo in properties) { var value = propertyInfo.GetValue(item, null); if (propertyInfo != null && (propertyInfo.PropertyType == typeof(String) || propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?) || propertyInfo.PropertyType.IsPrimitive)) { if (value != null) { if (propertyInfo.DeclaringType == typeof(DateTime) || (value != null && value.GetType() == typeof(DateTime)) || propertyInfo.DeclaringType == typeof(DateTime?) || (value != null && value.GetType() == typeof(DateTime?))) { value = ((DateTime)value).ToString("yyyy-M-d H:mm:ss"); } if (worksheet.GetRow(rowPos) != null) { worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(value.ToString()); } else { worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(value.ToString()); } } colPos++; } else if (propertyInfo.PropertyType.BaseType == typeof(Enum)) { string tempValue = value.ToString() + ":" + (int)value; if (value != null) { if (worksheet.GetRow(rowPos) != null) { worksheet.GetRow(rowPos).CreateCell(colPos).SetCellValue(tempValue); } else { worksheet.CreateRow(rowPos).CreateCell(colPos).SetCellValue(tempValue); } } colPos++; } } rowPos++; } } } return(workbook); }
protected void btnExcel_Click(object sender, EventArgs e) { _cusName = DTRequest.GetString("txtCusName"); _cid = DTRequest.GetString("hCusId"); _check1 = DTRequest.GetFormString("ddlcheck1"); _check2 = DTRequest.GetFormString("ddlcheck2"); _check3 = DTRequest.GetFormString("ddlcheck3"); _foresdate = DTRequest.GetFormString("txtforesdate"); _foreedate = DTRequest.GetFormString("txtforeedate"); _collect = DTRequest.GetFormString("ddlcollect"); _self = DTRequest.GetFormString("self"); _person = DTRequest.GetFormString("txtPerson"); _sign = DTRequest.GetFormString("ddlsign"); _money = DTRequest.GetFormString("txtMoney"); _oID = DTRequest.GetFormString("txtorderid"); _area = DTRequest.GetFormString("ddlarea"); _person1 = DTRequest.GetFormString("txtPerson1"); _sdate = DTRequest.GetFormString("txtsdate"); _edate = DTRequest.GetFormString("txtedate"); _num = DTRequest.GetFormString("txtnum"); _method1 = DTRequest.GetFormString("ddlmethod1"); BLL.ReceiptPayDetail bll = new BLL.ReceiptPayDetail(); DataTable dt = bll.GetList(this.pageSize, this.page, "rpd_type=0" + CombSqlTxt(), "rpd_adddate desc,rpd_id desc", manager, out this.totalCount, out decimal _tmoney, true, false).Tables[0]; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=付款明细列表.xlsx"); //HttpUtility.UrlEncode(fileName)); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("明细"); IFont font = hssfworkbook.CreateFont(); font.Boldweight = short.MaxValue; font.FontHeightInPoints = 11; #region 表格样式 //设置单元格的样式:水平垂直对齐居中 ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BottomBorderColor = HSSFColor.Black.Index; cellStyle.LeftBorderColor = HSSFColor.Black.Index; cellStyle.RightBorderColor = HSSFColor.Black.Index; cellStyle.TopBorderColor = HSSFColor.Black.Index; cellStyle.WrapText = true;//自动换行 //设置表头的样式:水平垂直对齐居中,加粗 ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle(); titleCellStyle.Alignment = HorizontalAlignment.Center; titleCellStyle.VerticalAlignment = VerticalAlignment.Center; titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色 titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式 titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色 //设置边框 titleCellStyle.BorderBottom = BorderStyle.Thin; titleCellStyle.BorderLeft = BorderStyle.Thin; titleCellStyle.BorderRight = BorderStyle.Thin; titleCellStyle.BorderTop = BorderStyle.Thin; titleCellStyle.BottomBorderColor = HSSFColor.Black.Index; titleCellStyle.LeftBorderColor = HSSFColor.Black.Index; titleCellStyle.RightBorderColor = HSSFColor.Black.Index; titleCellStyle.TopBorderColor = HSSFColor.Black.Index; //设置字体 titleCellStyle.SetFont(font); #endregion //表头 IRow headRow = sheet.CreateRow(0); headRow.HeightInPoints = 25; headRow.CreateCell(0).SetCellValue("订单号"); headRow.CreateCell(1).SetCellValue("付款对象"); headRow.CreateCell(2).SetCellValue("客户银行账号"); headRow.CreateCell(3).SetCellValue("付款内容"); headRow.CreateCell(4).SetCellValue("付款金额"); headRow.CreateCell(5).SetCellValue("预付日期"); headRow.CreateCell(6).SetCellValue("付款方式"); headRow.CreateCell(7).SetCellValue("申请人"); headRow.CreateCell(8).SetCellValue("区域"); headRow.CreateCell(9).SetCellValue("部门审批"); headRow.CreateCell(10).SetCellValue("财务审批"); headRow.CreateCell(11).SetCellValue("总经理审批"); headRow.CreateCell(12).SetCellValue("付款人"); headRow.CreateCell(13).SetCellValue("实付日期"); headRow.CreateCell(14).SetCellValue("对账标识"); headRow.GetCell(0).CellStyle = titleCellStyle; headRow.GetCell(1).CellStyle = titleCellStyle; headRow.GetCell(2).CellStyle = titleCellStyle; headRow.GetCell(3).CellStyle = titleCellStyle; headRow.GetCell(4).CellStyle = titleCellStyle; headRow.GetCell(5).CellStyle = titleCellStyle; headRow.GetCell(6).CellStyle = titleCellStyle; headRow.GetCell(7).CellStyle = titleCellStyle; headRow.GetCell(8).CellStyle = titleCellStyle; headRow.GetCell(9).CellStyle = titleCellStyle; headRow.GetCell(10).CellStyle = titleCellStyle; headRow.GetCell(11).CellStyle = titleCellStyle; headRow.GetCell(12).CellStyle = titleCellStyle; headRow.GetCell(13).CellStyle = titleCellStyle; headRow.GetCell(14).CellStyle = titleCellStyle; sheet.SetColumnWidth(0, 15 * 256); sheet.SetColumnWidth(1, 20 * 256); sheet.SetColumnWidth(2, 20 * 256); sheet.SetColumnWidth(3, 20 * 256); sheet.SetColumnWidth(4, 20 * 256); sheet.SetColumnWidth(5, 15 * 256); sheet.SetColumnWidth(6, 20 * 256); sheet.SetColumnWidth(7, 20 * 256); sheet.SetColumnWidth(8, 20 * 256); sheet.SetColumnWidth(9, 20 * 256); sheet.SetColumnWidth(10, 20 * 256); sheet.SetColumnWidth(11, 20 * 256); sheet.SetColumnWidth(12, 20 * 256); sheet.SetColumnWidth(13, 20 * 256); sheet.SetColumnWidth(14, 20 * 256); if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); row.HeightInPoints = 22; row.CreateCell(0).SetCellValue(dt.Rows[i]["rpd_oid"].ToString()); row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"])); row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["cb_bankName"]) + "\r\n" + Utils.ObjectToStr(dt.Rows[i]["cb_bankNum"]) + "\r\n" + Utils.ObjectToStr(dt.Rows[i]["cb_bank"])); row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_content"])); row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_money"])); row.CreateCell(5).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rpd_foredate"]).Value.ToString("yyyy-MM-dd")); row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["pm_name"])); row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_personNum"]) + "-" + Utils.ObjectToStr(dt.Rows[i]["rpd_personName"])); row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_area"])); row.CreateCell(9).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(dt.Rows[i]["rpd_flag1"].ToString())]); row.CreateCell(10).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(dt.Rows[i]["rpd_flag2"].ToString())]); row.CreateCell(11).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(dt.Rows[i]["rpd_flag3"].ToString())]); row.CreateCell(12).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_confirmerName"])); row.CreateCell(13).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["rp_date"]).Value.ToString("yyyy-MM-dd")); row.CreateCell(14).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rpd_num"])); row.GetCell(0).CellStyle = cellStyle; row.GetCell(1).CellStyle = cellStyle; row.GetCell(2).CellStyle = cellStyle; row.GetCell(3).CellStyle = cellStyle; row.GetCell(4).CellStyle = cellStyle; row.GetCell(5).CellStyle = cellStyle; row.GetCell(6).CellStyle = cellStyle; row.GetCell(7).CellStyle = cellStyle; row.GetCell(8).CellStyle = cellStyle; row.GetCell(9).CellStyle = cellStyle; row.GetCell(10).CellStyle = cellStyle; row.GetCell(11).CellStyle = cellStyle; row.GetCell(12).CellStyle = cellStyle; row.GetCell(13).CellStyle = cellStyle; row.GetCell(14).CellStyle = cellStyle; } } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); }
/// <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> /// DataTable转换成Excel文档流并且添加超链接以及限制列宽 /// </summary> /// <param name="table"></param> /// <returns></returns> public static MemoryStream CreateExcel(DataTable table) { MemoryStream ms = new MemoryStream(); using (table) { // using (IWorkbook workbook = new HSSFWorkbook()) // { // using (ISheet sheet = workbook.CreateSheet()) // { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in table.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption); //If Caption not set, returns the ColumnName value } // handling value. int rowIndex = 1; foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) { if (column.Caption == "URL地址" || column.Caption == "页面地址" || column.Caption == "url") { #region 设置超链接 ICell cell = dataRow.CreateCell(column.Ordinal); //创建单元格 cell.SetCellValue(row[column].ToString()); //设置显示文本 HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url); //建一个HSSFHyperlink实体,指明链接类型为URL(这里是枚举,可以根据需求自行更改) link.Address = row[column].ToString(); //给HSSFHyperlink的地址赋值 cell.Hyperlink = link; //将链接方式赋值给单元格的Hyperlink即可将链接附加到单元格上 #endregion #region 设置字体 IFont font = workbook.CreateFont(); //创建字体样式 font.Color = HSSFColor.Blue.Index; //设置字体颜色 ICellStyle style = workbook.CreateCellStyle(); //创建单元格样式 style.SetFont(font); //设置单元格样式中的字体样式 cell.CellStyle = style; //为单元格设置显示样式 #endregion } else { if (row[column].ToString().Length > 32767) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString().Substring(0, 32766)); } else { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } } } rowIndex++; } SetSizeColumns(sheet); //设置列宽 workbook.Write(ms); ms.Flush(); ms.Position = 0; } // } // } return(ms); }