本文整理汇总了C#中NPOI.XSSF.UserModel.XSSFWorkbook.CreateDataFormat方法的典型用法代码示例。如果您正苦于以下问题：C# XSSFWorkbook.CreateDataFormat方法的具体用法？C# XSSFWorkbook.CreateDataFormat怎么用？C# XSSFWorkbook.CreateDataFormat使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类NPOI.XSSF.UserModel.XSSFWorkbook的用法示例。
在下文中一共展示了XSSFWorkbook.CreateDataFormat方法的11个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: ExportDTI
/// <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;
//.........这里部分代码省略.........
示例2: Main
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();
}示例3: TestCloneStyleDiffWB
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));
}示例4: TestSetColor
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();
}示例5: DataTableToExcel
/// <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();
}示例6: GenerateTemplate
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();
//.........这里部分代码省略.........
示例7: CustomerStateInfoToExcel
/// <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");
//.........这里部分代码省略.........
示例8: CustomerToExcel
/// <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:");
//.........这里部分代码省略.........
示例9: ExportDTI
/// <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);
//.........这里部分代码省略.........
示例10: DownloadAsExcel
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())
//.........这里部分代码省略.........
示例11: GenerateExcel
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;
}