public void OutExcelData() { string ExportFileName = "SystemData.xlsx"; string ExportFileTitle = "Data"; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; XSSFWorkbook NpoiWB = new XSSFWorkbook(); XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle(); XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat(); xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0")); XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle(); XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true; cellStyleFontColor.SetFont(font1); ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle); List <string> listColumn = new List <string>() { "SystemClass", "systemValue", "SystemTitle", "SystemNotation", "SystemRemark", "SystemStatus" }; IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40; for (int i = 0; i < listColumn.Count; i++) { ICell xCellT = xRowT.CreateCell(i); xCellT.SetCellValue(listColumn[i]); } List <listSystemDetail> systemDetailList = new List <listSystemDetail>(); systemDetailList = sdModels.reListSystemDetail(); for (int i = 0; i < systemDetailList.Count; i++) { listSystemDetail dr = systemDetailList[i]; List <string> listData = new List <string>(); listData.Add(dr.lSystemClass.ToString()); listData.Add(dr.lSystemValue.ToString()); listData.Add(dr.lSystemTitle.ToString()); listData.Add(dr.lSystemNotation.ToString()); listData.Add(dr.lSystemRemark.ToString()); listData.Add(dr.lSystemStatus.ToString()); IRow xRowD = xSheet.CreateRow(i + 1); xRowD.HeightInPoints = 40; for (int b = 0; b < listData.Count; b++) { ICell xCellData = xRowD.CreateCell(b); xCellData.SetCellValue(listData[b]); } } MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS); Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + ""); Response.BinaryWrite(MS.ToArray()); NpoiWB = null; MS.Close(); MS.Dispose(); Response.Flush(); Response.End(); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> static void ExportDT(DataTable dtSource, string strHeaderText, string sheetName, FileStream fs) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet; if (string.IsNullOrEmpty(sheetName)) { sheet = workbook.CreateSheet() as XSSFSheet; } else { sheet = workbook.CreateSheet(sheetName) as XSSFSheet; } #region 右击文件 属性信息 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://www.yongfa365.com/"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "柳永法"; //填加xls文件作者信息 // si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息 // si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息 // si.Comments = "说明信息"; //填加xls文件作者信息 // si.Title = "NPOI测试"; //填加xls文件标题信息 // si.Subject = "NPOI测试Demo"; //填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} #endregion XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.UTF8.GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.UTF8.GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 0) { #region 表头及样式 if (!string.IsNullOrEmpty(strHeaderText)) { XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow; XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion } #endregion #region 填充内容 XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } workbook.Write(fs); fs.Close(); }
/// <summary> /// 导出期初模板 /// </summary> /// <param name="dtSource"></param> /// <param name="exportTemplateFilePath"></param> /// <param name="fillRow"></param> /// <param name="replaceCells"></param> /// <returns></returns> private static MemoryStream ExportOpeningTemplate(DataTable dtSource, string[] dropDowndtSource, string exportTemplateFilePath, int fillRow, int dropDownFillStartCell, int dropDownFillEndCell) { try { //打开Excle模板文件 IWorkbook workbook = null; using (FileStream fileOne = new FileStream(exportTemplateFilePath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(fileOne); //获取第一个工作表 } XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); //获取第一个sheet XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper .CreateExplicitListConstraint(dropDowndtSource); CellRangeAddressList addressList = new CellRangeAddressList(1, dtSource.Rows.Count, dropDownFillStartCell, dropDownFillEndCell); XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); sheet.AddValidationData(validation); //格式日期 XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //格式数字 XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat; decimelStyle.DataFormat = decimelformat.GetFormat("0.00####"); //单元格样式 ICellStyle style = workbook.CreateCellStyle(); //style.BorderBottom = BorderStyle.Thin; //style.BorderLeft = BorderStyle.Thin; //style.BorderRight = BorderStyle.Thin; //style.BorderTop = BorderStyle.Thin; int rowIndex = fillRow; foreach (DataRow row in dtSource.Rows) { #region 填充内容 //sheet.ShiftRows(rowIndex, sheet.LastRowNum, 1, true, false); XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 if (drValue.Length > 0) { DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": Double doubV = 0; Double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); newCell.CellStyle = decimelStyle; break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } newCell.CellStyle = style; } #endregion 填充内容 rowIndex++; } NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; workbook.Write(ms); ms.Flush(); ms.Position = 0; ms.Seek(0, SeekOrigin.Begin); ms.AllowClose = true; return(ms); } catch (Exception ex) { throw ex; } }
private static MemoryStream ExportExcel2007(DataTable dtSource, string strHeaderText) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; //格式日期 XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //格式数字 XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat; decimelStyle.DataFormat = decimelformat.GetFormat("0.00####"); // 取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { if (rowIndex == 1048576 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as XSSFSheet; } #region 表头及样式 { XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion 表头及样式 #region 列头及样式 { XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow; XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.IsLocked = true; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] > 255 ? 254 : arrColWidth[column.Ordinal] + 1) * 256); } //sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1); } rowIndex = 2; #endregion 列头及样式 rowIndex = 2; } #region 填充内容 XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 if (drValue.Length > 0) { DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": Double doubV = 0; Double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); newCell.CellStyle = decimelStyle; break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion 填充内容 rowIndex++; } NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; workbook.Write(ms); ms.Flush(); ms.Position = 0; ms.Seek(0, SeekOrigin.Begin); ms.AllowClose = true; return(ms); }
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { if (rowIndex == 0) { { XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } rowIndex = 1; } XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } rowIndex++; } workbook.Write(fs); fs.Close(); }
public static MemoryStream GetDataTableToMemory(DataTable rowData, IEnumerable <string> colNames, bool v) { XSSFWorkbook workbook = new XSSFWorkbook(); string TableName = rowData.TableName; if (string.IsNullOrEmpty(TableName)) { TableName = "Sheet1"; } XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(TableName); XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //dateStyle.FillBackgroundColor = IndexedColors.Blue.Index; //dateStyle.FillPattern = FillPattern.Bricks; XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //设置样式--- //设置单元格上下左右边框线 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //文字水平和垂直对齐方式 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //cellStyle.DataFormat = 194; //cellStyle.FillForegroundColor = IndexedColors.Pink.Index; //cellStyle.FillPattern = FillPattern.SolidForeground; int rowIndex = 0; XSSFRow eheaderRow = (XSSFRow)sheet.CreateRow(0); List <string> lcolName = new List <string>(); foreach (var item in colNames) { lcolName.Add(item); } for (int i = 0; i < lcolName.Count; i++) { sheet.SetColumnWidth(i, 14 * 256); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; XSSFFont font = (XSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //文字水平和垂直对齐方式 headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; eheaderRow.CreateCell(i).SetCellValue(lcolName[i]); eheaderRow.GetCell(i).CellStyle = headStyle; } rowIndex = 1; foreach (DataRow row in rowData.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); #region 填充内容 foreach (DataColumn column in rowData.Columns) { if (column.ColumnName == "CARTON_GROSS_WEIGTH") { string ssw = row[column].ToString(); } string ss = row[column].ToString(); XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); string type = row[column].GetType().FullName.ToString(); newCell.SetCellValue(GetValue(row[column].ToString(), type)); newCell.CellStyle = cellStyle; } #endregion rowIndex++; } NpoiMemoryStream ms = new NpoiMemoryStream { AllowClose = false }; workbook.Write(ms); ms.Flush(); ms.Seek(0, SeekOrigin.Begin); ms.AllowClose = true; return(ms); //throw new NotImplementedException(); }
/// /// DataTable转换成Excel文档流 /// /// /// static MemoryStream DatagridviewToExcel(DataGridView myDgv, CE_SystemFileType fileType) { MemoryStream ms = new MemoryStream(); IWorkbook workbook = null; ISheet sheet = null; IRow headerRow = null; try { if (fileType == CE_SystemFileType.Excel) { #region xls workbook = new HSSFWorkbook(); sheet = workbook.CreateSheet(); headerRow = sheet.CreateRow(0); // handling header. int columnIndex = 0; foreach (DataGridViewColumn column in myDgv.Columns) { if (!column.Visible) { continue; } headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value columnIndex++; } // handling value. HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataGridViewRow row in myDgv.Rows) { IRow dataRow = sheet.CreateRow(row.Index + 1); columnIndex = 0; foreach (DataGridViewColumn column in myDgv.Columns) { if (!column.Visible) { continue; } string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" : myDgv.Rows[row.Index].Cells[column.Index].Value.ToString(); HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex); if (column.ValueType == null) { column.ValueType = typeof(string); } switch (column.ValueType.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; } columnIndex++; } } #endregion } else if (fileType == CE_SystemFileType.Excel2010) { #region xlsx workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet(); headerRow = sheet.CreateRow(0); // handling header. int columnIndex = 0; foreach (DataGridViewColumn column in myDgv.Columns) { if (!column.Visible) { continue; } headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value columnIndex++; } // handling value. XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataGridViewRow row in myDgv.Rows) { IRow dataRow = sheet.CreateRow(row.Index + 1); columnIndex = 0; foreach (DataGridViewColumn column in myDgv.Columns) { if (!column.Visible) { continue; } string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" : myDgv.Rows[row.Index].Cells[column.Index].Value.ToString(); XSSFCell newCell = (XSSFCell)dataRow.CreateCell(columnIndex); if (column.ValueType == null) { column.ValueType = typeof(string); } switch (column.ValueType.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; } columnIndex++; } } #endregion } AutoSizeColumns(sheet); workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } catch (Exception ex) { throw new Exception(ex.Message); } }
public void OutExcelData() { string ExportFileName = "EmpData.xlsx"; string ExportFileTitle = "Data"; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; XSSFWorkbook NpoiWB = new XSSFWorkbook(); XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle(); XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat(); xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0")); XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle(); XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true; cellStyleFontColor.SetFont(font1); /// 進行產生Excel檔案流程 ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle); List <string> listColumn = edModel.listEmployeeColumn; /// 建立標題列 IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40; for (int i = 0; i < listColumn.Count; i++) { ICell xCellT = xRowT.CreateCell(i); xCellT.SetCellValue(listColumn[i]); } /// 讀取資料庫資料 List <listEmployeeDetail> ListEmpData = new List <listEmployeeDetail>(); ListEmpData = edModel.ReListEmployeeDetail(); if (ListEmpData != null && ListEmpData.Count > 0) { for (int i = 0; i < ListEmpData.Count; i++) { listEmployeeDetail item = ListEmpData[i]; List <string> list = new List <string>(); list.Add(item.lEmpIndex.ToString()); list.Add(item.lEmpName.ToString()); list.Add(item.lEmpSex.ToString()); list.Add(item.lEmpEmail.ToString()); list.Add(item.lEmpAddress.ToString()); list.Add(item.lEmpMobile.ToString()); list.Add(item.lEmpPhone.ToString()); list.Add(item.lEmpNotation.ToString()); list.Add(item.lEmpRemark.ToString()); list.Add(item.lEmpStatus.ToString()); list.Add(item.lEmpJoinDate.ToString()); list.Add(item.lEmpLeaveDate.ToString()); IRow xRowD = xSheet.CreateRow(i + 1); xRowD.HeightInPoints = 30; for (int b = 0; b < list.Count; b++) { ICell xCellData = xRowD.CreateCell(b); xCellData.SetCellValue(list[b]); } } } MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS); Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + ""); Response.BinaryWrite(MS.ToArray()); // ---------------------------------------------------------------------------------------------- // 釋放記憶體參數 NpoiWB = null; MS.Close(); MS.Dispose(); Response.Flush(); Response.End(); }
/// <summary> /// 由DataTable导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <returns>Excel工作表</returns> public static MemoryStream ExportToExcel(DataTable sourceTable, string sheetName = "对账单明细表") { if (sourceTable.Rows.Count <= 0) { return(null); } IWorkbook workbook = new XSSFWorkbook(); // ICellStyle cellStyle = GetCellStyle(workbook); ISheet sheet = workbook.CreateSheet(sheetName); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; XSSFFont font = (XSSFFont)workbook.CreateFont(); //font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //取得列宽 int[] arrColWidth = new int[sourceTable.Columns.Count]; foreach (DataColumn item in sourceTable.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < sourceTable.Rows.Count; i++) { for (int j = 0; j < sourceTable.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(sourceTable.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { if (intTemp > 30) { break; } arrColWidth[j] = intTemp; } } } IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in sourceTable.Columns) { ICell headerCell = headerRow.CreateCell(column.Ordinal); headerCell.SetCellValue(column.ColumnName); headerCell.CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } // handling value. int rowIndex = 1; XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataRow row in sourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { string drValue = row[column].ToString(); XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); switch (column.DataType.ToString()) { case "System.String": //字符串类型 //newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal,CellType.String); newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); //ms.Flush(); //ms.Position = 0; return(ms); } }
/// <summary> /// 设置单元格的值 /// </summary> /// <param name="cell">单元格</param> /// <param name="filed">字段</param> /// <param name="dataRowIndex">行号 用于设置公式</param> /// <param name="cellVal">单元格值</param> private void SetCellValue(ICell cell, PowerExcelConfigCell filed, int dataRowIndex, string cellVal) { //设置单元格类型 //如果没有公式,且指定了单元格类型,直接设置类型 var cellType = filed.GetCellType(); if (string.IsNullOrEmpty(filed.Formula) && cellType != CellType.Unknown) { cell.SetCellType(cellType); } else { cell.SetCellType(CellType.Formula); } //数值列,尝试将数据转换为数值 if (cell.CellType == CellType.Numeric) { //获取格式信息 string formatCode = _df.GetFormat(cell.CellStyle.DataFormat); //是否是日期列 if (DateUtil.IsCellDateFormatted(cell)) { var val = cellVal.AsDateTime(); if (val != DateTime.MinValue) { cell.SetCellValue(val); } } else { // 否则就是其他数值列类型 double num = cellVal.AsDouble(double.MinValue); if (num == double.MinValue) { //转换失败,直接赋值 cell.SetCellValue(cellVal); } else { //检测是否是百分比 是的话,手动除以100,因为程序里通常存的是50,表示50% if (formatCode.EndsWith("%")) { cell.SetCellValue(num / 100); } else { cell.SetCellValue(num); } } } } else if (cell.CellType == CellType.Formula) { //公式列信息, {i} 标示当前行 cell.CellFormula = filed.Formula.Replace("{i}", (dataRowIndex + 1).ToString()); } else { cell.SetCellValue(cellVal); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public MemoryStream ExportDT(DataTable dtSource, string strHeaderText) { //XSSFWorkbook workbook = new XSSFWorkbook();(.xls用该格式限制列为256列) //(.xlsx 16384列) string sheetname = dtSource.TableName; XSSFSheet sheet = workbook.CreateSheet(sheetname) as XSSFSheet; #region 右击文件 属性信息 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://www.yongfa365.com/"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "柳永法"; //填加xls文件作者信息 // si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息 // si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息 // si.Comments = "说明信息"; //填加xls文件作者信息 // si.Title = "NPOI测试"; //填加xls文件标题信息 // si.Subject = "NPOI测试Demo"; //填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} #endregion XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0)//超过6355就新建一个表 { sheet = workbook.CreateSheet(sheetname) as XSSFSheet; } #region 表头及样式 //{ // XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; // headerRow.HeightInPoints = 25; // headerRow.CreateCell(0).SetCellValue(strHeaderText); // XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; // headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // HSSFFont font = workbook.CreateFont() as HSSFFont; // font.FontHeightInPoints = 20; // font.Boldweight = 700; // headStyle.SetFont(font); // headerRow.GetCell(0).CellStyle = headStyle; // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); // //headerRow.Dispose(); //} #endregion #region 列头及样式 { XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = HorizontalAlignment.Center; headStyle.SetFillForegroundColor(new XSSFColor(new byte[] { 153, 204, 204 })); headStyle.FillPattern = FillPattern.SolidForeground; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontName = "宋体"; font.FontHeightInPoints = 11; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 320); } //headerRow.Dispose(); } #endregion rowIndex = 1; } #endregion #region 填充内容 XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; XSSFCellStyle rowStyle = workbook.CreateCellStyle() as XSSFCellStyle; rowStyle.Alignment = HorizontalAlignment.Center; XSSFFont cellfont = workbook.CreateFont() as XSSFFont; cellfont.FontName = "宋体"; cellfont.FontHeightInPoints = 11; rowStyle.SetFont(cellfont); foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; newCell.CellStyle = rowStyle; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); // ms.Flush(); //ms.Position = 0; // workbook.Dispose(); return(ms); } }
/// <summary> /// 导出Xlsx /// </summary> /// <param name="localFilePath">文件保存路径</param> /// <param name="dtSource">数据源</param> public static void ExportXlsx(string localFilePath, System.Data.DataTable dtSource) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (XSSFSheet)workbook.CreateSheet(); } #region 列头及样式 { XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; XSSFFont font = (XSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = 1; } #endregion #region 填充内容 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } }
/// <summary> /// /// </summary> /// <param name="error"></param> /// <param name="strFileName"></param> /// <param name="dsSource"></param> /// <param name="pwd"></param> /// <param name="rowIndex"></param> /// <param name="wait"></param> /// <param name="isAppend"></param> /// <param name="dateFormat"></param> public static void DataTableToExcel(ref string error, string strFileName, DataSet dsSource, string pwd, ref int rowIndex, WaitDialogForm wait = null, bool isAppend = false, string dateFormat = "yyyy-MM-dd") { try { error = string.Empty; FileStream fs = null; XSSFWorkbook workbook = null; if (!isAppend) { fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write); workbook = new XSSFWorkbook(); } else { fs = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); workbook = new XSSFWorkbook(fs);//将文件读到内存,在内存中操作excel } fs.Close(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Xw"; // workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "xw"; //填加xls文件作者信息 si.ApplicationName = "xw"; //填加xls文件创建程序信息 si.LastAuthor = "xw"; //填加xls文件最后保存者信息 si.Comments = "xw"; //填加xls文件作者信息 si.Title = "xw"; //填加xls文件标题信息 si.Subject = "xw"; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; // workbook.SummaryInformation = si; } #endregion XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat(dateFormat); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFCellStyle rowStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFFont fontRow = (XSSFFont)workbook.CreateFont(); XSSFFont font = (XSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 9; font.FontName = "微软雅黑"; //font.Boldweight = 700; headStyle.SetFont(font); fontRow.FontHeightInPoints = 9; fontRow.FontName = "微软雅黑"; rowStyle.SetFont(fontRow); for (int k = 0; k < dsSource.Tables.Count; k++) { var dtSource = dsSource.Tables[k]; XSSFSheet sheet = null; if (!isAppend) { rowIndex = 0; sheet = (XSSFSheet)workbook.CreateSheet(); if (!string.IsNullOrEmpty(pwd)) { sheet.ProtectSheet(pwd); } workbook.SetSheetName(k, dtSource.TableName); } else { sheet = (XSSFSheet)workbook.GetSheet(dtSource.TableName); if (sheet == null) { isAppend = false; rowIndex = 0; sheet = (XSSFSheet)workbook.CreateSheet(); if (!string.IsNullOrEmpty(pwd)) { sheet.ProtectSheet(pwd); } workbook.SetSheetName(k, dtSource.TableName); } } if (wait != null) { wait.SetCaption(string.Format("正在创建第 {0} / {1} Excel的Sheet中", (k + 1), dsSource.Tables.Count));//"(" + (k + 1) + " /" + dsSource.Tables.Count + ")Excel的Sheet中..."); } //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (!isAppend) { //if (rowIndex != 0) { //sheet = (XSSFSheet)workbook.CreateSheet(); //if (!string.IsNullOrEmpty(pwd)) { // sheet.ProtectSheet(pwd); //} //} #region 表头及样式 // if (!string.IsNullOrEmpty(strHeaderText)) { // rowIndex++; // XSSFRow headerRow = (XSSFRow)sheet.CreateRow(rowIndex); // headerRow.HeightInPoints = 25; // headerRow.CreateCell(0).SetCellValue(strHeaderText); // XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); // // headStyle.Alignment = CellHorizontalAlignment.CENTER; // XSSFFont font = (XSSFFont)workbook.CreateFont(); // font.FontHeightInPoints = 20; // font.Boldweight = 700; // headStyle.SetFont(font); // headerRow.GetCell(0).CellStyle = headStyle; // // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); // //headerRow.Dispose(); // } #endregion #region 列头及样式 XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //headerRow.GetCell(column.Ordinal).CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //headerRow.GetCell(column.Ordinal).CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //headerRow.GetCell(column.Ordinal).CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //headerRow.GetCell(column.Ordinal).CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //设置列宽 // sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } #endregion isAppend = true; rowIndex = 1; } #endregion if (wait != null) { wait.SetCaption(string.Format("正在将数据写入Excel第 {0} / {1} 行中...", rowIndex, dtSource.Rows.Count));//"正在写入第(" + (k + 1) + "/" + dsSource.Tables.Count + "个ExcelSheet表格中的第 " + rowIndex + "/" + dtSource.Rows.Count + " 行数据..."); } #region 填充内容 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); newCell.CellStyle = rowStyle; //newCell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //newCell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //newCell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //newCell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; var obj = row[column]; if (obj == null) { continue; } string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double tempVal = 0; var isflg = double.TryParse(drValue, out tempVal); if (isflg) { newCell.SetCellValue(tempVal); newCell.SetCellType(CellType.Numeric); } else { newCell.SetCellValue(drValue); newCell.SetCellType(CellType.String); } break; case "System.DateTime": //日期类型 System.DateTime dateV; isflg = System.DateTime.TryParse(drValue, out dateV); if (isflg) { newCell.SetCellValue(drValue); } else { newCell.SetCellValue(string.Empty); } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.UInt16": //整型 case "System.UInt32": case "System.UInt64": int intV = 0; isflg = int.TryParse(drValue, out intV); if (isflg) { newCell.SetCellValue(intV); newCell.SetCellType(CellType.Numeric); } break; case "System.Single": //浮点型 case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; isflg = double.TryParse(drValue, out doubV); if (isflg) { newCell.SetCellValue(doubV); newCell.SetCellType(CellType.Numeric); } break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); newCell.SetCellType(CellType.String); break; default: tempVal = 0; isflg = double.TryParse(drValue, out tempVal); if (isflg) { newCell.SetCellValue(tempVal); newCell.SetCellType(CellType.Numeric); } else { newCell.SetCellValue(drValue); newCell.SetCellType(CellType.String); } break; } } #endregion rowIndex++; } if (sheet != null) { sheet.ForceFormulaRecalculation = true; } } //将内存数据写到文件 using (FileStream fs1 = File.OpenWrite(strFileName)) { workbook.Write(fs1); workbook.Close(); } } catch (Exception ex) { error = $"导出数据出错{ex.Message}"; } }
private XSSFCellStyle PhaseCellStyle(CellStyle paramCellStyle) { XSSFCellStyle style = null; //try //{ style = (XSSFCellStyle)_workbook.CreateCellStyle(); XSSFFont font = (XSSFFont)_workbook.CreateFont(); font.FontName = paramCellStyle.FontName.ToString(); font.FontHeightInPoints = paramCellStyle.FontHeightInPoints; if (paramCellStyle.FontBoldFlag) { font.Boldweight = 700; } style.SetFont(font); //设置水平位置 switch (paramCellStyle.HorizontalAlignment.ToString()) { case "Center": style.Alignment = HorizontalAlignment.Center; break; case "Left": style.Alignment = HorizontalAlignment.Left; break; case "Right": style.Alignment = HorizontalAlignment.Right; break; default: style.Alignment = HorizontalAlignment.Center; break; } //设置垂直位置 switch (paramCellStyle.VerticalAlignment.ToString()) { case "Center": style.VerticalAlignment = VerticalAlignment.Center; break; case "Top": style.VerticalAlignment = VerticalAlignment.Top; break; case "Botton": style.VerticalAlignment = VerticalAlignment.Bottom; break; default: style.VerticalAlignment = VerticalAlignment.Center; break; } //设置表格线样式宽度 switch (paramCellStyle.BorderStyle.ToString()) { case "Thin": style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; break; default: break; } //设置表格线颜色 switch (paramCellStyle.BorderStyle.ToString()) { case "Black": style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; break; default: break; } //设置单元格是否换行 #region 设置为文本 style.WrapText = paramCellStyle.WrapText; XSSFDataFormat format = (XSSFDataFormat)_workbook.GetCreationHelper().CreateDataFormat(); short index = format.GetFormat("@"); style.DataFormat = index; #endregion //} //catch(Exception ex) //{ // string ss = ex.Message; // return null; //} return(style); }
public void TestCloneStyleDiffWB() { XSSFWorkbook wbOrig = new XSSFWorkbook(); Assert.AreEqual(1, wbOrig.NumberOfFonts); Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count); XSSFFont fnt = (XSSFFont)wbOrig.CreateFont(); fnt.FontName = ("TestingFont"); Assert.AreEqual(2, wbOrig.NumberOfFonts); Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count); XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat(); fmt.GetFormat("MadeUpOne"); fmt.GetFormat("MadeUpTwo"); XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle(); orig.Alignment = (HorizontalAlignment.Right); orig.SetFont(fnt); orig.DataFormat = (fmt.GetFormat("Test##")); Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment); Assert.IsTrue(fnt == orig.GetFont()); Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat); Assert.AreEqual(2, wbOrig.NumberOfFonts); Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count); // Now a style on another workbook XSSFWorkbook wbClone = new XSSFWorkbook(); Assert.AreEqual(1, wbClone.NumberOfFonts); Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count); Assert.AreEqual(1, wbClone.NumCellStyles); XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat(); XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle(); Assert.AreEqual(1, wbClone.NumberOfFonts); Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count); Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment); Assert.IsFalse("TestingFont" == clone.GetFont().FontName); clone.CloneStyleFrom(orig); Assert.AreEqual(2, wbClone.NumberOfFonts); Assert.AreEqual(2, wbClone.NumCellStyles); Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count); Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment); Assert.AreEqual("TestingFont", clone.GetFont().FontName); Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat); Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##")); // Save it and re-check XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone); Assert.AreEqual(2, wbReload.NumberOfFonts); Assert.AreEqual(2, wbReload.NumCellStyles); Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count); XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1); Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment); Assert.AreEqual("TestingFont", reload.GetFont().FontName); Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat); Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##")); Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig)); Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone)); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; //workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; //workbook.SummaryInformation = si; } #endregion XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (XSSFSheet)workbook.CreateSheet(); } #region 表头及样式 { XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); // headStyle.Alignment = CellHorizontalAlignment.CENTER; XSSFFont font = (XSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { XSSFRow headerRow = (XSSFRow)sheet.CreateRow(1); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; XSSFFont font = (XSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); }
public static void ExportToFile(GridView gv, string excelName)//匯出Excel { try { //建立WorkBook及試算表 XSSFWorkbook workbook = new XSSFWorkbook(); MemoryStream ms = new MemoryStream(); XSSFSheet mySheet1 = (XSSFSheet)workbook.CreateSheet(excelName); //建立標題列Header XSSFRow rowHeader = (XSSFRow)mySheet1.CreateRow(0); for (int i = 0; i < gv.HeaderRow.Cells.Count; i++) { string strValue = gv.HeaderRow.Cells[i].Text; XSSFCell cell = (XSSFCell)rowHeader.CreateCell(i); cell.SetCellValue(HttpUtility.HtmlDecode(strValue).Trim()); //建立新的CellStyle ICellStyle CellsStyle = workbook.CreateCellStyle(); //建立字型 IFont StyleFont = workbook.CreateFont(); //設定文字字型 StyleFont.FontName = "微軟正黑體"; //設定文字大小 StyleFont.FontHeightInPoints = 12; //設定文字大小為10pt CellsStyle.SetFont(StyleFont); cell.CellStyle = CellsStyle; } //建立內容列 DataRow for (int i = 0; i < gv.Rows.Count; i++) { XSSFRow rowItem = (XSSFRow)mySheet1.CreateRow(i + 1); for (int j = 0; j < gv.HeaderRow.Cells.Count; j++) { Label lb = null; // 因為GridView中有TemplateField,所以要將Label.Text讀出來 if (gv.Rows[i].Cells[j].Controls.Count > 1) { lb = gv.Rows[i].Cells[j].Controls[1] as Label; } string value1 = (lb != null) ? HttpUtility.HtmlDecode(lb.Text) : HttpUtility.HtmlDecode(gv.Rows[i].Cells[j].Text).Trim(); int intry = 0; bool isNumeric = !value1.StartsWith("0") && int.TryParse(value1, out intry); XSSFCell cell = (XSSFCell)rowItem.CreateCell(j); if (string.IsNullOrEmpty(value1.Trim())) { //空白 cell.SetCellValue(Convert.ToString("")); } else if (!isNumeric) { if (value1.Length > 10) { //文字格式 mySheet1.SetColumnWidth(j, 50 * 256); //欄位寬度設為50 } else if (value1.Length > 3) { //文字格式 mySheet1.SetColumnWidth(j, 30 * 256); //欄位寬度設為30 } else { //文字格式 mySheet1.SetColumnWidth(j, 15 * 256); //欄位寬度設為15 } XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); // 給cell style XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("@"); // 文字格式 //建立字型 IFont StyleFont = workbook.CreateFont(); //設定文字字型 StyleFont.FontName = "微軟正黑體"; //設定文字大小 StyleFont.FontHeightInPoints = 12; //設定文字大小為12pt cellStyle.SetFont(StyleFont); //cellStyle.WrapText = true; //文字自動換列 cell.CellStyle = cellStyle; cell.SetCellValue(value1); } { cell.SetCellValue(value1); } } } //匯出 workbook.Write(ms); //此為匯出副檔名xlsx //方法一 /* * HttpContext.Current.Response.Clear(); * HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; * HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "AAA.xlsx")); * HttpContext.Current.Response.BinaryWrite(ms.ToArray()); * HttpContext.Current.Response.Flush(); * HttpContext.Current.Response.End();*/ //方法二 using (FileStream fs = new FileStream(@"C:\Users\TPE-Intern001\Desktop\20210205.xls", FileMode.Create, FileAccess.Write, FileShare.None, 4096, true))//写入指定的文件 { byte[] b = ms.ToArray(); fs.Write(b, 0, b.Length); ms.Close(); fs.Flush(); fs.Close(); } //釋放資源 workbook = null; ms.Close(); ms.Dispose(); } catch (Exception) { } }
/// /// DataTable转换成Excel文档流 /// /// /// static MemoryStream RenderToExcel(DataTable table, CE_SystemFileType fileType) { MemoryStream ms = new MemoryStream(); using (table) { IWorkbook workbook = null; ISheet sheet = null; IRow headerRow = null; if (fileType == CE_SystemFileType.Excel) { #region xls workbook = new HSSFWorkbook(); sheet = workbook.CreateSheet(); 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; HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) { string drValue = row[column] == null ? "" : row[column].ToString(); HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); 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; } } rowIndex++; } #endregion } else if (fileType == CE_SystemFileType.Excel2010) { #region xlsx workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet(); 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; XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) { string drValue = row[column] == null ? "" : row[column].ToString(); XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); 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; } } rowIndex++; } #endregion } AutoSizeColumns(sheet); workbook.Write(ms); ms.Flush(); ms.Position = 0; } return ms; }
/// <summary> /// DataTable导出到Excel的MemoryStream,2007格式 /// </summary> /// <param name="dtSource">源DataTable</param> public static MemoryStream ExportDT2007(DataTable dtSource) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; #region 右击文件 属性信息 { //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); //dsi.Company = "http://www.huobanplus.com"; //workbook.DocumentSummaryInformation = dsi; //SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); //si.Author = "杭州火图科技"; //填加xls文件作者信息 //si.ApplicationName = "伙伴商城"; //填加xls文件创建程序信息 //si.LastAuthor = "voidarea"; //填加xls文件最后保存者信息 //si.Comments = ""; //填加xls文件作者信息 //si.Title = ""; //填加xls文件标题信息 //si.Subject = ""; //填加文件主题信息 //si.CreateDateTime = DateTime.Now; //workbook.SummaryInformation = si; } #endregion XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as XSSFSheet; } #region 表头及样式 #endregion #region 列头及样式 { //HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 if (arrColWidth[column.Ordinal] > 255) { arrColWidth[column.Ordinal] = 254; } else { sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } } #endregion //rowIndex = 2; rowIndex = 1; } #endregion #region 填充内容 XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet; //workbook.Dispose(); return(ms); } }
public static bool DataSetToExcel(DataSet ds, string Path) { bool result = false; FileStream fs = null; XSSFWorkbook workbook = new XSSFWorkbook(); for (int i = 0; i < ds.Tables.Count; i++) { XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName); XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 0; #region 新建表,填充表头,填充列头,样式 if (rowIndex == 0) { #region 列头及样式 { XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; XSSFFont font = (XSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); ////自定义表头 string name = ds.Tables[i].TableName; //for (var j = 0; j < dataList[name].ToArray().Count(); j++) //{ // headerRow.CreateCell(j).SetCellValue(dataList[name].ToArray()[j]); // headerRow.GetCell(j).CellStyle = headStyle; //} } #endregion rowIndex = 1; } #endregion foreach (DataRow row in ds.Tables[i].Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); #region 填充内容 foreach (DataColumn column in ds.Tables[i].Columns) { XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); string type = row[column].GetType().FullName.ToString(); newCell.SetCellValue(GetValue(row[column].ToString(), type)); } #endregion rowIndex++; } } using (fs = File.OpenWrite(Path)) { workbook.Write(fs);//向打开的这个xls文件中写入数据 result = true; } return(result); }
public static bool DataSetToExcel(DataSet ds, string Path, string strTitle) { try { string[] title = strTitle.Split(','); bool result = false; FileStream fs = null; XSSFWorkbook workbook = new XSSFWorkbook(); for (int i = 0; i < ds.Tables.Count; i++) { string TableName = ds.Tables[i].TableName; if (string.IsNullOrEmpty(TableName)) { TableName = "Sheet1"; } XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(TableName); XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //dateStyle.FillBackgroundColor = IndexedColors.Blue.Index; //dateStyle.FillPattern = FillPattern.Bricks; XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //设置样式--- //设置单元格上下左右边框线 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //文字水平和垂直对齐方式 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //cellStyle.DataFormat = 194; //cellStyle.FillForegroundColor = IndexedColors.Pink.Index; //cellStyle.FillPattern = FillPattern.SolidForeground; int rowIndex = 0; #region 新建表,填充表头,填充列头,样式 if (rowIndex == 0) { #region 列头及样式 XSSFRow eheaderRow = (XSSFRow)sheet.CreateRow(0); //headerRow.Height = 1 * 256; //for (int i = 0; i < columnNum; i++) //写入字段 // datas[0, i] = title[j]; for (int j = 0; j < title.Length; j++) { sheet.SetColumnWidth(j, 14 * 256); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; XSSFFont font = (XSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //文字水平和垂直对齐方式 headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; eheaderRow.CreateCell(j).SetCellValue(title[j]); eheaderRow.GetCell(j).CellStyle = headStyle; } #endregion rowIndex = 1; } #endregion foreach (DataRow row in ds.Tables[i].Rows) { //string ss = row[0].ToString(); XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); #region 填充内容 foreach (DataColumn column in ds.Tables[i].Columns) { if (column.ColumnName == "CARTON_GROSS_WEIGTH") { string ssw = row[column].ToString(); } string ss = row[column].ToString(); XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); string type = row[column].GetType().FullName.ToString(); //if (type == "System.Decimal") //{ // newCell.SetCellValue(double.Parse(row[column].ToString())); //} //else //{ newCell.SetCellValue(GetValue(row[column].ToString(), type)); //} newCell.CellStyle = cellStyle; } #endregion rowIndex++; } } using (fs = File.OpenWrite(Path)) { workbook.Write(fs);//向打开的这个xls文件中写入数据 result = true; } return(result); } catch (Exception) { throw; } }