/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() 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 == 0) { #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; // XSSFFont font = workbook.CreateFont() as XSSFFont; // 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 = 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(); } #endregion 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": //字符串类型 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(); }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); 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;@")); FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
public void TestSetColor() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); //CreationHelper ch = wb.GetCreationHelper(); IDataFormat format = wb.CreateDataFormat(); ICell cell = row.CreateCell(1); cell.SetCellValue("somEvalue"); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/format.GetFormat("###0")); cellStyle.FillBackgroundColor = (/*setter*/IndexedColors.DarkBlue.Index); cellStyle.FillForegroundColor = (/*setter*/IndexedColors.DarkBlue.Index); cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.Alignment = HorizontalAlignment.Right; cellStyle.VerticalAlignment = VerticalAlignment.Top; cell.CellStyle = (/*setter*/cellStyle); /*OutputStream stream = new FileOutputStream("C:\\temp\\CellColor.xlsx"); try { wb.Write(stream); } finally { stream.Close(); }*/ IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb); ICell cellBack = wbBack.GetSheetAt(0).GetRow(0).GetCell(1); Assert.IsNotNull(cellBack); ICellStyle styleBack = cellBack.CellStyle; Assert.AreEqual(IndexedColors.DarkBlue.Index, styleBack.FillBackgroundColor); Assert.AreEqual(IndexedColors.DarkBlue.Index, styleBack.FillForegroundColor); Assert.AreEqual(HorizontalAlignment.Right, styleBack.Alignment); Assert.AreEqual(VerticalAlignment.Top, styleBack.VerticalAlignment); Assert.AreEqual(FillPattern.SolidForeground, styleBack.FillPattern); wbBack.Close(); wb.Close(); }
public override void Test49928() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("49928.xlsx"); doTest49928Core(wb); // an attempt to register an existing format returns its index int poundFmtIdx = wb.GetSheetAt(0).GetRow(0).GetCell(0).CellStyle.DataFormat; Assert.AreEqual(poundFmtIdx, wb.GetStylesSource().PutNumberFormat(poundFmt)); // now create a custom format with Pound (\u00a3) IDataFormat dataFormat = wb.CreateDataFormat(); short customFmtIdx = dataFormat.GetFormat("\u00a3##.00[Yellow]"); Assert.IsTrue(customFmtIdx > BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX); Assert.AreEqual("\u00a3##.00[Yellow]", dataFormat.GetFormat(customFmtIdx)); }
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)); }
public IDataFormat CreateDataFormat() { return(workbook.CreateDataFormat()); }
/// <summary> /// DataTable导出到Excel【暂无用】 /// </summary> /// <param name="source"></param> /// <param name="saveFileName"></param> public static void DataTableToExcel(DataTable source, string saveFileName) { //创建工作簿 IWorkbook workbook = new XSSFWorkbook(); //创建Sheet ISheet sheet = workbook.CreateSheet("Sheet1"); //删除sheet //workbook.RemoveSheetAt(0); //获取Sheet //ISheet sheet = workbook.GetSheet("Sheet1"); //ISheet sheet = workbook.GetSheetAt(0); //创建表头 int r = 0; //行下标 IRow rowTitle = sheet.CreateRow(r); rowTitle.HeightInPoints = 20; //设置表头高为20点 for (int i = 0; i < source.Columns.Count; i++) { //设置列宽 sheet.SetColumnWidth(i, 5120); //20*256 //赋值 string title = source.Columns[i].ColumnName; ICell cell = rowTitle.CreateCell(i); cell.SetCellValue(title); //设置样式 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.FillBackgroundColor = HSSFColor.LightOrange.Index; cellStyle.FillForegroundColor = HSSFColor.LightOrange.Index; cellStyle.FillPattern = FillPattern.SolidForeground; cell.CellStyle = cellStyle; } //创建表单 r++; for (; r < source.Rows.Count; r++) { IRow row = sheet.CreateRow(r); for (int i = 0; i < source.Columns.Count; i++) { var value = source.Rows[r][i]; ICell cell = row.CreateCell(i); switch (Type.GetTypeCode(value.GetType())) { //datetime 格式特殊处理 case TypeCode.DateTime: { cell.SetCellValue(Convert.ToDateTime(value)); //set date format ICellStyle cellStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy/mm/dd"); cell.CellStyle = cellStyle; } break; default: cell.SetCellValue(value.ToString()); break; } } } //创建excel文件 FileStream sw = File.Create(saveFileName); workbook.Write(sw); sw.Close(); }
public static byte[] GenerateTemplate(List<Business.Entities.company> listCompany,List<Business.Entities.contractor> listContractor,List<Business.Entities.project> listProject) { //culture Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //supaya file tidak corrupt int parseRecordNumber = 100; // number of rows that has style or validation int startRowIndex = 3; XSSFCellStyle styleCurrency; XSSFCellStyle styleDate; XSSFCellStyle styleNumeric; XSSFCellStyle styleDecimal; //kamus XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet; XSSFRow row; XSSFCell cell; XSSFCellStyle style; XSSFFont font; CellRangeAddressList addressList; XSSFDataValidationHelper dvHelper; XSSFDataValidationConstraint dvConstraint; XSSFDataValidation validation; List<string> listCompanyString = new List<string>(); foreach(var data in listCompany) { listCompanyString.Add(data.name); } List<string> listContractorString = new List<string>(); foreach(var data in listContractor) { listContractorString.Add(data.name); } List<string> listProjectString = new List<string>(); foreach(var data in listProject) { listProjectString.Add(data.name); } styleCurrency = (XSSFCellStyle)workbook.CreateCellStyle(); styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)"); styleNumeric = (XSSFCellStyle)workbook.CreateCellStyle(); styleNumeric.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0"); styleDate = (XSSFCellStyle)workbook.CreateCellStyle(); styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("mm/dd/yyyy"); styleDecimal = (XSSFCellStyle)workbook.CreateCellStyle(); styleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.00"); List<string> columnList = new List<string>(); columnList.Add("Name"); int ContractorStringLocation = 1; columnList.Add("Contractor"); columnList.Add("Photo"); columnList.Add("Description"); columnList.Add("Start Date"); columnList.Add("Finish Date"); columnList.Add("Highlight"); columnList.Add("Project Stage"); columnList.Add("Status"); columnList.Add("Budget"); columnList.Add("Currency"); columnList.Add("Num"); int PmcStringLocation = 12; columnList.Add("Pmc"); columnList.Add("Summary"); int CompanyStringLocation = 14; columnList.Add("Company"); columnList.Add("Status Non Technical"); columnList.Add("Is Completed"); columnList.Add("Completed Date"); int ProjectStringLocation = 18; columnList.Add("Project"); columnList.Add("Submit For Approval Time"); columnList.Add("Approval Status"); columnList.Add("Approval Time"); columnList.Add("Deleted"); columnList.Add("Approval Message"); columnList.Add("Status Technical"); columnList.Add("Scurve Data"); sheet = (XSSFSheet)workbook.CreateSheet("Data"); int col = 0; int rowNumber = 0; //create row (header) row = (XSSFRow)sheet.CreateRow((short)rowNumber); dvHelper = new XSSFDataValidationHelper(sheet); //header data style = (XSSFCellStyle)workbook.CreateCellStyle(); cell = (XSSFCell)row.CreateCell(col); cell.SetCellValue("M Project"); font = (XSSFFont)workbook.CreateFont(); font.FontHeight = 24; style.SetFont(font); cell.CellStyle = style; rowNumber++; row = (XSSFRow)sheet.CreateRow((short)rowNumber); style = (XSSFCellStyle)workbook.CreateCellStyle(); font = (XSSFFont)workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.Bold; style.SetFont(font); rowNumber++; row = (XSSFRow)sheet.CreateRow((short)rowNumber); //header data foreach (string data in columnList) { cell = (XSSFCell)row.CreateCell(col); cell.SetCellValue(data); cell.CellStyle = style; //cell.CellStyle.IsLocked = true; //column width sheet.SetColumnWidth(col, (30 * 256)); ++col; } //sheet.CreateFreezePane(0, 4); //dropdownlist Company if(listCompanyString.Count > 0) { XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterCompany"); int i=0; foreach(string a in listCompanyString) { row = (XSSFRow)hidden.CreateRow(i); cell = (XSSFCell)row.CreateCell(0); cell.SetCellValue(a); i++; } validation = null; dvConstraint = null; dvHelper = null; dvHelper=new XSSFDataValidationHelper(sheet); addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,CompanyStringLocation,CompanyStringLocation); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterCompany!$A$1:$A$" + listCompanyString.Count); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.SuppressDropDownArrow = true; validation.ShowErrorBox = true; workbook.SetSheetHidden(1, true); sheet.AddValidationData(validation); } //dropdownlist Contractor if(listContractorString.Count > 0) { XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterContractor"); int i=0; foreach(string a in listContractorString) { row = (XSSFRow)hidden.CreateRow(i); cell = (XSSFCell)row.CreateCell(0); cell.SetCellValue(a); i++; } validation = null; dvConstraint = null; dvHelper = null; dvHelper=new XSSFDataValidationHelper(sheet); addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,ContractorStringLocation,ContractorStringLocation); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterContractor!$A$1:$A$" + listContractorString.Count); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.SuppressDropDownArrow = true; validation.ShowErrorBox = true; workbook.SetSheetHidden(2, true); sheet.AddValidationData(validation); } //dropdownlist Project if(listProjectString.Count > 0) { XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterProject"); int i=0; foreach(string a in listProjectString) { row = (XSSFRow)hidden.CreateRow(i); cell = (XSSFCell)row.CreateCell(0); cell.SetCellValue(a); i++; } validation = null; dvConstraint = null; dvHelper = null; dvHelper=new XSSFDataValidationHelper(sheet); addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,ProjectStringLocation,ProjectStringLocation); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterProject!$A$1:$A$" + listProjectString.Count); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.SuppressDropDownArrow = true; validation.ShowErrorBox = true; workbook.SetSheetHidden(3, true); sheet.AddValidationData(validation); } /*Cell formatting*/ for (int i = startRowIndex; i <= parseRecordNumber; i++) { rowNumber++; row = (XSSFRow)sheet.CreateRow((short)rowNumber); //start_date col = 4; cell = (XSSFCell)row.CreateCell((short)col); cell.CellStyle = styleDate; if(i==startRowIndex) { addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 4, 4); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy"); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000"); sheet.AddValidationData(validation); } //finish_date col = 5; cell = (XSSFCell)row.CreateCell((short)col); cell.CellStyle = styleDate; if(i==startRowIndex) { addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 5, 5); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy"); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000"); sheet.AddValidationData(validation); } //budget col = 9; cell = (XSSFCell)row.CreateCell((short)col); cell.CellStyle = styleDecimal; if(i==startRowIndex) { } //num col = 11; cell = (XSSFCell)row.CreateCell((short)col); cell.CellStyle = styleNumeric; if(i==startRowIndex) { addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 11, 11); dvHelper = new XSSFDataValidationHelper(sheet); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "0", "1000000000000000000"); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("Input Error", "Value must be a number, maximum 1.000.000.000.000.000.000"); sheet.AddValidationData(validation); } //completed_date col = 17; cell = (XSSFCell)row.CreateCell((short)col); cell.CellStyle = styleDate; if(i==startRowIndex) { addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 17, 17); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy"); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000"); sheet.AddValidationData(validation); } //submit_for_approval_time col = 19; cell = (XSSFCell)row.CreateCell((short)col); cell.CellStyle = styleDate; if(i==startRowIndex) { addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 19, 19); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy"); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000"); sheet.AddValidationData(validation); } //approval_time col = 21; cell = (XSSFCell)row.CreateCell((short)col); cell.CellStyle = styleDate; if(i==startRowIndex) { addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 21, 21); dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy"); validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000"); sheet.AddValidationData(validation); } } //write to byte[] MemoryStream ms = new MemoryStream(); workbook.Write(ms); return ms.ToArray(); }
/// <summary> /// Customer State Info /// </summary> /// <returns></returns> public static void CustomerStateInfoToExcel(DataTable dt, Stream stream, string Category) { XSSFWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Cisco Hub Management"); ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); font.Boldweight = short.MaxValue; style.SetFont(font); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); if (Category == "Amount") { dateStyle.DataFormat = format.GetFormat("$#,##0"); } else { dateStyle.DataFormat = format.GetFormat("0"); } IRow dataRow0 = sheet.CreateRow(0); IRow dataRow1 = sheet.CreateRow(1); ICell c = dataRow1.CreateCell(0); c.SetCellValue("Customer"); c.CellStyle = style; c = dataRow1.CreateCell(1); c.SetCellValue("P/N"); c.CellStyle = style; c = dataRow1.CreateCell(2); c.SetCellValue("Project"); c.CellStyle = style; c = dataRow1.CreateCell(3); c.SetCellValue("Split"); c.CellStyle = style; c = dataRow1.CreateCell(4); c.SetCellValue("Over 90 days"); c.CellStyle = style; c = dataRow0.CreateCell(4); c.SetCellValue("Hub Inventory Aging(A)"); c.CellStyle = style; c = dataRow1.CreateCell(5); c.SetCellValue("Over 60 days"); c.CellStyle = style; c = dataRow1.CreateCell(6); c.SetCellValue("Over 30 days"); c.CellStyle = style; c = dataRow1.CreateCell(7); c.SetCellValue("30 days or less"); c.CellStyle = style; c = dataRow1.CreateCell(8); c.SetCellValue("Total Aging"); c.CellStyle = style; c = dataRow0.CreateCell(9); c.SetCellValue("Backlog(B)"); c.CellStyle = style; c = dataRow1.CreateCell(9); c.SetCellValue("Current Period"); c.CellStyle = style; c = dataRow1.CreateCell(10); c.SetCellValue("Next Period(+1)"); c.CellStyle = style; c = dataRow1.CreateCell(11); c.SetCellValue("Next Next Period(+2)"); c.CellStyle = style; c = dataRow1.CreateCell(12); c.SetCellValue("GHub"); c.CellStyle = style; c = dataRow1.CreateCell(13); c.SetCellValue("Site Inventory"); c.CellStyle = style; c = dataRow0.CreateCell(14); c.SetCellValue("Demand(C)"); c.CellStyle = style; c = dataRow1.CreateCell(14); c.SetCellValue("Current Period"); c.CellStyle = style; c = dataRow1.CreateCell(15); c.SetCellValue("Next Period(+1)"); c.CellStyle = style; c = dataRow1.CreateCell(16); c.SetCellValue("Thirth Period"); c.CellStyle = style; c = dataRow1.CreateCell(17); c.SetCellValue("Fourth Period"); c.CellStyle = style; c = dataRow1.CreateCell(18); c.SetCellValue("Fifth Period"); c.CellStyle = style; c = dataRow1.CreateCell(19); c.SetCellValue("Sixth Period"); c.CellStyle = style; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 4, 8)); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 9, 11)); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 14, 19)); for (int i = 0; i < 11; i++) { sheet.SetColumnWidth(i, 3840); } int dcIndex = 2; foreach (DataRow dr in dt.Rows) { IRow dataRow = sheet.CreateRow(dcIndex); c = dataRow.CreateCell(0); c.SetCellValue(Convert.ToString(dr["site"])); c = dataRow.CreateCell(1); c.SetCellValue(Convert.ToString(dr["cPartNo"])); c = dataRow.CreateCell(2); c.SetCellValue(Convert.ToString(dr["mitem"])); c = dataRow.CreateCell(3); c.SetCellValue(Convert.ToString(dr["Split"])); c = dataRow.CreateCell(4); if (!string.IsNullOrEmpty(Convert.ToString(dr["Hub90days"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["Hub90days"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(5); if (!string.IsNullOrEmpty(Convert.ToString(dr["Hub60days"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["Hub60days"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(6); if (!string.IsNullOrEmpty(Convert.ToString(dr["hub30days"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["hub30days"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(7); if (!string.IsNullOrEmpty(Convert.ToString(dr["Hub0days"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["Hub0days"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(8); if (!string.IsNullOrEmpty(Convert.ToString(dr["TotalAgent"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["TotalAgent"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(9); if (!string.IsNullOrEmpty(Convert.ToString(dr["BacklogCurrentP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["BacklogCurrentP"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(10); if (!string.IsNullOrEmpty(Convert.ToString(dr["BacklogNextP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["BacklogNextP"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(11); if (!string.IsNullOrEmpty(Convert.ToString(dr["BacklogThirdP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["BacklogThirdP"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(12); if (!string.IsNullOrEmpty(Convert.ToString(dr["GHub"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["GHub"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(13); if (!string.IsNullOrEmpty(Convert.ToString(dr["SiteInventory"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["SiteInventory"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(14); if (!string.IsNullOrEmpty(Convert.ToString(dr["DemandCurrentP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["DemandCurrentP"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(15); if (!string.IsNullOrEmpty(Convert.ToString(dr["DemandNextP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["DemandNextP"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(16); if (!string.IsNullOrEmpty(Convert.ToString(dr["DemandThirthP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["DemandThirthP"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(17); if (!string.IsNullOrEmpty(Convert.ToString(dr["DemandFourthP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["DemandFourthP"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(18); if (!string.IsNullOrEmpty(Convert.ToString(dr["DemandFifthP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["DemandFifthP"])); c.CellStyle = dateStyle; } c = dataRow.CreateCell(19); if (!string.IsNullOrEmpty(Convert.ToString(dr["DemandSixthP"]))) { c.SetCellValue(ParseHelper.Parse<int>(dr["DemandSixthP"])); c.CellStyle = dateStyle; } dcIndex++; } workbook.Write(stream); }
/// <summary> /// Customer /// </summary> /// <returns></returns> public static void CustomerToExcel(DataTable dt, Stream stream) { //记录条数 double doubV = 0; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet1 = (XSSFSheet)workbook.CreateSheet("Sheet1"); sheet1.SetColumnWidth(0, 8000); sheet1.SetColumnWidth(1, 8000); sheet1.SetColumnWidth(2, 8000); sheet1.SetColumnWidth(3, 8000); sheet1.SetColumnWidth(4, 8000); sheet1.SetColumnWidth(5, 8000); //----------样式----------- //Titel XSSFFont fontTitle = (XSSFFont)workbook.CreateFont(); fontTitle.Boldweight = (short)FontBoldWeight.Bold; XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; headStyle.SetFont(fontTitle); headStyle.VerticalAlignment = VerticalAlignment.Center; //Lable XSSFFont fontLable = (XSSFFont)workbook.CreateFont(); XSSFCellStyle styleLable = (XSSFCellStyle)workbook.CreateCellStyle(); styleLable.Alignment = HorizontalAlignment.Right; styleLable.SetFont(fontLable); styleLable.VerticalAlignment = VerticalAlignment.Center; //Content XSSFFont fontContent = (XSSFFont)workbook.CreateFont(); fontContent.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index; XSSFCellStyle styleContent = (XSSFCellStyle)workbook.CreateCellStyle(); styleContent.Alignment = HorizontalAlignment.Center; styleContent.SetFont(fontContent); styleContent.VerticalAlignment = VerticalAlignment.Center; //Content2 XSSFFont fontContent2 = (XSSFFont)workbook.CreateFont(); fontContent2.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index; XSSFCellStyle styleContent2 = (XSSFCellStyle)workbook.CreateCellStyle(); styleContent2.Alignment = HorizontalAlignment.Left; styleContent2.SetFont(fontContent); styleContent2.VerticalAlignment = VerticalAlignment.Center; //Content3 美元 XSSFFont fontContentUSD = (XSSFFont)workbook.CreateFont(); fontContentUSD.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index; XSSFCellStyle styleContentUSD = (XSSFCellStyle)workbook.CreateCellStyle(); styleContentUSD.Alignment = HorizontalAlignment.Right; styleContentUSD.SetFont(fontContentUSD); styleContentUSD.VerticalAlignment = VerticalAlignment.Center; XSSFDataFormat format1 = (XSSFDataFormat)workbook.CreateDataFormat(); styleContentUSD.DataFormat = format1.GetFormat("$#,##0"); //-------------------------- foreach (DataRow dr in dt.Rows) { XSSFRow row0 = (XSSFRow)sheet1.CreateRow(0); row0.HeightInPoints = 30; row0.CreateCell(0).SetCellValue("Customer Profile"); sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5)); row0.GetCell(0).CellStyle = headStyle; XSSFRow row1 = (XSSFRow)sheet1.CreateRow(1); row1.CreateCell(0).SetCellValue("Customer:"); row1.CreateCell(1).SetCellValue(Convert.ToString(dr["Customer"])); row1.CreateCell(2).SetCellValue("Market:"); row1.CreateCell(3).SetCellValue(Convert.ToString(dr["Market"])); row1.CreateCell(4).SetCellValue("MULTEK Team"); row1.CreateCell(5).SetCellValue(""); row1.GetCell(0).CellStyle = styleLable; row1.GetCell(2).CellStyle = styleLable; row1.GetCell(4).CellStyle = styleLable; row1.GetCell(1).CellStyle = styleContent; row1.GetCell(3).CellStyle = styleContent; row1.GetCell(5).CellStyle = styleContent; XSSFRow row2 = (XSSFRow)sheet1.CreateRow(2); row2.CreateCell(0).SetCellValue("Location:"); row2.CreateCell(1).SetCellValue(Convert.ToString(dr["Location"])); row2.CreateCell(2).SetCellValue("Flextronics Business Segment:"); row2.CreateCell(3).SetCellValue(Convert.ToString(dr["FlextronicsBusinessSegment"])); row2.CreateCell(4).SetCellValue("Sub-Segment:"); row2.CreateCell(5).SetCellValue(Convert.ToString(dr["SubSegment"])); row2.GetCell(0).CellStyle = styleLable; row2.GetCell(2).CellStyle = styleLable; row2.GetCell(4).CellStyle = styleLable; row2.GetCell(1).CellStyle = styleContent; row2.GetCell(3).CellStyle = styleContent; row2.GetCell(5).CellStyle = styleContent; XSSFRow row3 = (XSSFRow)sheet1.CreateRow(3); row3.CreateCell(0).SetCellValue("Ticker Symbol:"); row3.CreateCell(1).SetCellValue(Convert.ToString(dr["TickerSymbol"])); row3.CreateCell(2).SetCellValue("Customer's Annual Revenue:"); if (double.TryParse(Convert.ToString(dr["CustomersAnnualRevenue"]), out doubV)) { row3.CreateCell(3).SetCellValue(doubV); row3.GetCell(3).CellStyle = styleContentUSD;//格式化显示 } else { row3.CreateCell(3).SetCellValue(""); } row3.CreateCell(4).SetCellValue("Multek GAM/BDM:"); row3.CreateCell(5).SetCellValue(Convert.ToString(dr["MultekGAM"])); row3.GetCell(0).CellStyle = styleLable; row3.GetCell(2).CellStyle = styleLable; row3.GetCell(4).CellStyle = styleLable; row3.GetCell(1).CellStyle = styleContent; row3.GetCell(5).CellStyle = styleContent; XSSFRow row4 = (XSSFRow)sheet1.CreateRow(4); row4.CreateCell(0).SetCellValue("Customer's Web Site:"); row4.CreateCell(1).SetCellValue(Convert.ToString(dr["CustomersWebSite"])); row4.CreateCell(2).SetCellValue("Revenue Currency:"); row4.CreateCell(3).SetCellValue(Convert.ToString(dr["RevenueCurrency"])); row4.CreateCell(4).SetCellValue("Inside Sales Lead:"); row4.CreateCell(5).SetCellValue(Convert.ToString(dr["MultekSalesPerson"])); row4.GetCell(0).CellStyle = styleLable; row4.GetCell(2).CellStyle = styleLable; row4.GetCell(4).CellStyle = styleLable; row4.GetCell(1).CellStyle = styleContent; row4.GetCell(3).CellStyle = styleContent; row4.GetCell(5).CellStyle = styleContent; XSSFRow row5 = (XSSFRow)sheet1.CreateRow(5); row5.CreateCell(0).SetCellValue("TAM:"); if (double.TryParse(Convert.ToString(dr["TAM"]), out doubV)) { row5.CreateCell(1).SetCellValue(doubV); row5.GetCell(1).CellStyle = styleContentUSD;//格式化显示 } else { row5.CreateCell(1).SetCellValue(""); } row5.CreateCell(2).SetCellValue("Revenue Year:"); row5.CreateCell(3).SetCellValue(Convert.ToString(dr["RevenueYear"])); row5.CreateCell(4).SetCellValue("FAE:"); row5.CreateCell(5).SetCellValue(Convert.ToString(dr["FAE"])); row5.GetCell(0).CellStyle = styleLable; row5.GetCell(2).CellStyle = styleLable; row5.GetCell(4).CellStyle = styleLable; row5.GetCell(3).CellStyle = styleContent; row5.GetCell(5).CellStyle = styleContent; XSSFRow row6 = (XSSFRow)sheet1.CreateRow(6); row6.CreateCell(0).SetCellValue("Multek SAM:"); if (double.TryParse(Convert.ToString(dr["MultekSAM"]), out doubV)) { row6.CreateCell(1).SetCellValue(doubV); row6.GetCell(1).CellStyle = styleContentUSD;//格式化显示 } else { row6.CreateCell(1).SetCellValue(""); } row6.CreateCell(2).SetCellValue("Customer's Profitability:"); row6.CreateCell(3).SetCellValue(Convert.ToString(dr["CustomersProfitability"])); row6.CreateCell(4).SetCellValue("Customer Service:"); row6.CreateCell(5).SetCellValue(Convert.ToString(dr["CustomerService"])); row6.GetCell(0).CellStyle = styleLable; row6.GetCell(2).CellStyle = styleLable; row6.GetCell(4).CellStyle = styleLable; row6.GetCell(3).CellStyle = styleContent; row6.GetCell(5).CellStyle = styleContent; XSSFRow row7 = (XSSFRow)sheet1.CreateRow(7); row7.CreateCell(0).SetCellValue("Multek Market Share of SAM:"); row7.CreateCell(1).SetCellValue(Convert.ToString(dr["MultekMarketShareOfSAM"])); row7.CreateCell(2).SetCellValue("Key Purchasing Contact:"); row7.CreateCell(3).SetCellValue(Convert.ToString(dr["KeyPurchasingContact"])); row7.CreateCell(4).SetCellValue("Flex GAM:"); row7.CreateCell(5).SetCellValue(Convert.ToString(dr["FlextronicsBDMGAM"])); row7.GetCell(0).CellStyle = styleLable; row7.GetCell(2).CellStyle = styleLable; row7.GetCell(4).CellStyle = styleLable; row7.GetCell(1).CellStyle = styleContent; row7.GetCell(3).CellStyle = styleContent; row7.GetCell(5).CellStyle = styleContent; XSSFRow row8 = (XSSFRow)sheet1.CreateRow(8); row8.CreateCell(0).SetCellValue("FY'14 Revenue:"); if (double.TryParse(Convert.ToString(dr["FY14Revenue"]), out doubV)) { row8.CreateCell(1).SetCellValue(doubV); row8.GetCell(1).CellStyle = styleContentUSD;//格式化显示 } else { row8.CreateCell(1).SetCellValue(""); } row8.CreateCell(2).SetCellValue("Purchasing Manager:"); row8.CreateCell(3).SetCellValue(Convert.ToString(dr["PurchasingManager"])); row8.CreateCell(4).SetCellValue(""); row8.CreateCell(5).SetCellValue(""); row8.GetCell(0).CellStyle = styleLable; row8.GetCell(2).CellStyle = styleLable; row8.GetCell(3).CellStyle = styleContent; XSSFRow row9 = (XSSFRow)sheet1.CreateRow(9); row9.CreateCell(0).SetCellValue("FY'15 Forecast:"); if (double.TryParse(Convert.ToString(dr["FY15Forecast"]), out doubV)) { row9.CreateCell(1).SetCellValue(doubV); row9.GetCell(1).CellStyle = styleContentUSD;//格式化显示 } else { row9.CreateCell(1).SetCellValue(""); } row9.CreateCell(2).SetCellValue("Supplier Quality Engineer:"); row9.CreateCell(3).SetCellValue(Convert.ToString(dr["SupplierQualityEngineer"])); row9.CreateCell(4).SetCellValue(""); row9.CreateCell(5).SetCellValue(""); row9.GetCell(0).CellStyle = styleLable; row9.GetCell(2).CellStyle = styleLable; row9.GetCell(3).CellStyle = styleContent; XSSFRow row10 = (XSSFRow)sheet1.CreateRow(10); row10.CreateCell(0).SetCellValue("FY'16 Forecast:"); if (double.TryParse(Convert.ToString(dr["FY16Forecast"]), out doubV)) { row10.CreateCell(1).SetCellValue(doubV); row10.GetCell(1).CellStyle = styleContentUSD;//格式化显示 } else { row10.CreateCell(1).SetCellValue(""); } row10.CreateCell(2).SetCellValue("VP of Supply Chain / Purchasing:"); row10.CreateCell(3).SetCellValue(Convert.ToString(dr["VPofSupplyChainPurchasing"])); row10.CreateCell(4).SetCellValue(""); row10.CreateCell(5).SetCellValue(""); row10.GetCell(0).CellStyle = styleLable; row10.GetCell(2).CellStyle = styleLable; row10.GetCell(3).CellStyle = styleContent; XSSFRow row11 = (XSSFRow)sheet1.CreateRow(11); row11.CreateCell(0).SetCellValue("FY'17 Forecast:"); if (double.TryParse(Convert.ToString(dr["FY17Forecast"]), out doubV)) { row11.CreateCell(1).SetCellValue(doubV); row11.GetCell(1).CellStyle = styleContentUSD;//格式化显示 } else { row11.CreateCell(1).SetCellValue(""); } row11.CreateCell(2).SetCellValue("President:"); row11.CreateCell(3).SetCellValue(Convert.ToString(dr["President"])); row11.CreateCell(4).SetCellValue(""); row11.CreateCell(5).SetCellValue(""); row11.GetCell(0).CellStyle = styleLable; row11.GetCell(2).CellStyle = styleLable; row11.GetCell(3).CellStyle = styleContent; XSSFRow row13 = (XSSFRow)sheet1.CreateRow(12); row13.CreateCell(0).SetCellValue("Programs Supported:"); row13.CreateCell(1).SetCellValue(Convert.ToString(dr["ProgramsSupported"])); row13.CreateCell(2).SetCellValue(""); row13.CreateCell(3).SetCellValue(""); row13.CreateCell(4).SetCellValue(""); row13.CreateCell(5).SetCellValue(""); sheet1.AddMergedRegion(new CellRangeAddress(12, 12, 1, 5)); row13.GetCell(0).CellStyle = styleLable; row13.GetCell(2).CellStyle = styleLable; row13.GetCell(1).CellStyle = styleContent2; row13.GetCell(3).CellStyle = styleContent; XSSFRow row14 = (XSSFRow)sheet1.CreateRow(13); row14.CreateCell(0).SetCellValue("Multek Approved Facilities:"); row14.CreateCell(1).SetCellValue(Convert.ToString(dr["MultekApprovedFacilities"])); row14.CreateCell(2).SetCellValue(""); row14.CreateCell(3).SetCellValue(""); row14.CreateCell(4).SetCellValue(""); row14.CreateCell(5).SetCellValue(""); row14.GetCell(0).CellStyle = styleLable; row14.GetCell(1).CellStyle = styleContent; XSSFRow row15 = (XSSFRow)sheet1.CreateRow(14); row15.CreateCell(0).SetCellValue("Multek Competitors:"); row15.CreateCell(1).SetCellValue(Convert.ToString(dr["MultekCompetitors"])); row15.CreateCell(2).SetCellValue(""); row15.CreateCell(3).SetCellValue(""); row15.CreateCell(4).SetCellValue(""); row15.CreateCell(5).SetCellValue(""); sheet1.AddMergedRegion(new CellRangeAddress(14, 14, 1, 5)); row15.GetCell(0).CellStyle = styleLable; row15.GetCell(1).CellStyle = styleContent2; XSSFRow row17 = (XSSFRow)sheet1.CreateRow(15); row17.CreateCell(0).SetCellValue("Strategy for Growth:"); row17.CreateCell(1).SetCellValue(Convert.ToString(dr["StrategyforGrowth"])); row17.CreateCell(2).SetCellValue(""); row17.CreateCell(3).SetCellValue(""); row17.CreateCell(4).SetCellValue(""); row17.CreateCell(5).SetCellValue(""); row17.HeightInPoints = 50; sheet1.AddMergedRegion(new CellRangeAddress(15, 15, 1, 5)); row17.GetCell(0).CellStyle = styleLable; row17.GetCell(1).CellStyle = styleContent2; } //MemoryStream ms = new MemoryStream(); workbook.Write(stream); //ms.Seek(0, SeekOrigin.Begin); //return ms; }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> 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) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 0) { #region 列头及样式 { 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(); } #endregion 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": //字符串类型 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(); }
public ActionResult DownloadAsExcel(string id) { if (string.IsNullOrWhiteSpace(id)) { TempData["Message"] = "Unable to download file. No file was selected. Please select a file and try again."; return RedirectToAction("Index"); } try { var file = GetNamedFile(id); var created = file.TimeStamp; var filePathAndFilename = file.FilePath; var filename = file.FileNameLessExtension; var streamReader = new StreamReader(filePathAndFilename); var engine = new FileHelperEngine<FeederSystemFixedLengthRecord>(); var result = engine.ReadStream(streamReader); var transactions = result.ToList(); // Opening the Excel template... var templateFileStream = new FileStream(Server.MapPath(@"~\Files\RevisedScrubberWithoutData.xlsx"), FileMode.Open, FileAccess.Read); // Getting the complete workbook... var templateWorkbook = new XSSFWorkbook(templateFileStream); // Getting the worksheet by its name... var sheet = templateWorkbook.GetSheet("Sheet1"); // We need this so the date will be formatted correctly; otherwise, the date format gets all messed up. var dateCellStyle = templateWorkbook.CreateCellStyle(); var format = templateWorkbook.CreateDataFormat(); dateCellStyle.DataFormat = format.GetFormat("[$-809]m/d/yyyy;@"); // Here's another to ensure we get a number with 2 decimal places: var twoDecimalPlacesCellStyle = templateWorkbook.CreateCellStyle(); format = templateWorkbook.CreateDataFormat(); twoDecimalPlacesCellStyle.DataFormat = format.GetFormat("#0.00"); var boldFont = templateWorkbook.CreateFont(); boldFont.FontHeightInPoints = 11; boldFont.FontName = "Calibri"; boldFont.Boldweight = (short)FontBoldWeight.Bold; var boldCellStyle = templateWorkbook.CreateCellStyle(); boldCellStyle.SetFont(boldFont); var boldTotalAmountStyle = templateWorkbook.CreateCellStyle(); boldTotalAmountStyle.DataFormat = twoDecimalPlacesCellStyle.DataFormat; boldTotalAmountStyle.SetFont(boldFont); var grandTotal = 0.0; var i = 0; foreach (var transaction in transactions) { i++; // Getting the row... 0 is the first row. var dataRow = sheet.GetRow(i); dataRow.CreateCell(0).SetCellValue(transaction.FiscalYear); dataRow.CreateCell(1).SetCellValue(transaction.ChartNum); dataRow.CreateCell(2).SetCellValue(transaction.Account); dataRow.CreateCell(3).SetCellValue(transaction.SubAccount); dataRow.CreateCell(4).SetCellValue(transaction.ObjectCode); dataRow.CreateCell(5).SetCellValue(transaction.SubObjectCode); dataRow.CreateCell(6).SetCellValue(transaction.BalanceType); dataRow.CreateCell(7).SetCellValue(transaction.ObjectType.Trim()); dataRow.CreateCell(8).SetCellValue(transaction.FiscalPeriod); dataRow.CreateCell(9).SetCellValue(transaction.DocumentType); dataRow.CreateCell(10).SetCellValue(transaction.OriginCode); dataRow.CreateCell(11).SetCellValue(transaction.DocumentNumber); dataRow.CreateCell(12).SetCellValue(transaction.LineSequenceNumber); dataRow.CreateCell(13).SetCellValue(transaction.TransactionDescription); var transactionAmount = Convert.ToDouble(transaction.Amount.Trim()); grandTotal += transactionAmount; var cell = dataRow.CreateCell(14); cell.CellStyle = twoDecimalPlacesCellStyle; cell.SetCellValue(transactionAmount); dataRow.CreateCell(15).SetCellValue(transaction.DebitCreditCode.Trim()); cell = dataRow.CreateCell(16); cell.CellStyle = dateCellStyle; cell.SetCellValue(Convert.ToDateTime(transaction.TransactionDate)); dataRow.CreateCell(17).SetCellValue(transaction.OrganizationTrackingNumber); dataRow.CreateCell(18).SetCellValue(transaction.ProjectCode); dataRow.CreateCell(19).SetCellValue(transaction.OrganizationReferenceId.Trim()); dataRow.CreateCell(20).SetCellValue(transaction.ReferenceTypeCode.Trim()); dataRow.CreateCell(21).SetCellValue(transaction.ReferenceOriginCode.Trim()); dataRow.CreateCell(22).SetCellValue(transaction.ReferenceNumber.Trim()); dataRow.CreateCell(23).SetCellValue(transaction.ReversalDate.Trim()); dataRow.CreateCell(24).SetCellValue(transaction.TransactionEncumbranceUpdateCode.Trim()); } if (transactions.Any()) { var totalsRow = sheet.GetRow(i + 1); var totalsCell = totalsRow.CreateCell(13); totalsCell.CellStyle = boldCellStyle; totalsCell.SetCellValue(" Grand Total"); totalsCell = totalsRow.CreateCell(14); totalsCell.CellStyle = boldTotalAmountStyle; totalsCell.SetCellValue(grandTotal); } // Forcing formula recalculation... sheet.ForceFormulaRecalculation = true; var ms = new MemoryStream(); // Writing the workbook content to the FileStream... templateWorkbook.Write(ms); TempData["Message"] = "Excel report created successfully!"; // Sending the server processed data back to the user computer... return File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename + ".xlsx"); } catch (Exception ex) { TempData["Message"] = String.Format("Opps! Something went wrong: {0}", ex.Message); return RedirectToAction("Index"); } }
public MemoryStream GenerateExcel(List<LogPresentationStub> items) { //kamus lokal int rowIndex = 0, colIndex; XSSFCellStyle styleHeader, styleDate; XSSFFont font; XSSFRow row; XSSFCell cell; //algoritma Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("activity log"); //create row (header) row = (XSSFRow)sheet.CreateRow((short)rowIndex++); //header style styleHeader = (XSSFCellStyle)workbook.CreateCellStyle(); font = (XSSFFont)workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.Bold; ; styleHeader.SetFont(font); //header data List<string> colNames = new List<string> { "id", "timestamp", "application", "ip", "user", "action", "data" }; colIndex = 0; foreach (string single in colNames) { cell = (XSSFCell)row.CreateCell(colIndex++); cell.SetCellValue(single); cell.CellStyle = styleHeader; } //body styleDate = (XSSFCellStyle)workbook.CreateCellStyle(); styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd HH:mm"); foreach (LogPresentationStub single in items) { row = (XSSFRow)sheet.CreateRow((short)rowIndex++); colIndex = 0; cell = (XSSFCell)row.CreateCell(colIndex++); cell.SetCellValue(single.Id); cell = (XSSFCell)row.CreateCell(colIndex++); cell.SetCellValue(single.Timestamp); cell.CellStyle = styleDate; cell = (XSSFCell)row.CreateCell(colIndex++); cell.SetCellValue(single.Application); cell = (XSSFCell)row.CreateCell(colIndex++); cell.SetCellValue(single.Ip); cell = (XSSFCell)row.CreateCell(colIndex++); cell.SetCellValue(single.User); cell = (XSSFCell)row.CreateCell(colIndex++); cell.SetCellValue(single.Action); cell = (XSSFCell)row.CreateCell(colIndex++); cell.SetCellValue(single.Data); } //write to file MemoryStream ms = new MemoryStream(); workbook.Write(ms); return ms; }