/// <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());
}
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 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();
}
/// <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;
}
