/// <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 wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
// Aqua background
ICellStyle style = wb.CreateCellStyle();
style.FillBackgroundColor = IndexedColors.Aqua.Index;
style.FillPattern = FillPattern.BigSpots;
IRow row = ws.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.SetCellValue("X");
cell.CellStyle = style;
// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.CreateCellStyle();
style.FillBackgroundColor = IndexedColors.Orange.Index;
style.FillPattern = FillPattern.SolidForeground;
cell = row.CreateCell(2);
cell.SetCellValue("X");
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet s1 = workbook.CreateSheet("Sheet1");
ICellStyle rowstyle = workbook.CreateCellStyle();
rowstyle.FillForegroundColor = IndexedColors.Red.Index;
rowstyle.FillPattern = FillPattern.SolidForeground;
ICellStyle c1Style = workbook.CreateCellStyle();
c1Style.FillForegroundColor = IndexedColors.Yellow.Index;
c1Style.FillPattern = FillPattern.SolidForeground;
IRow r1 = s1.CreateRow(1);
IRow r2= s1.CreateRow(2);
r1.RowStyle = rowstyle;
r2.RowStyle = rowstyle;
ICell c1 = r2.CreateCell(2);
c1.CellStyle = c1Style;
c1.SetCellValue("Test");
ICell c4 = r2.CreateCell(4);
c4.CellStyle = c1Style;
using(var fs=File.Create("test.xlsx"))
{
workbook.Write(fs);
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
int x = 1;
for (int i = 0; i < 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
ICell cell = row.CreateCell(j);
if (x % 2 == 0)
{
//fill background with blue
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
cell.CellStyle = style1;
}
else
{
//fill background with yellow
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
cell.CellStyle = style1;
}
x++;
}
}
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = IndexedColors.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = IndexedColors.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// 写入DataTable到Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="excelFile"></param>
public virtual void writeDataTableToExcel(DataTable dt, string excelFile)
{
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
//写入基本数据
writeSheet(workbook, cellStyleA, cellStyleB, dt);
#region 输出文件
//输出到流
workbook.Write(memoryStream);
//写Excel文件
File.WriteAllBytes(excelFile, memoryStream.ToArray());
#endregion
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
ICellStyle style = wb.CreateCellStyle();
//Setting the line of the top border
style.BorderTop = BorderStyle.Thick;
style.TopBorderColor = 256;
style.BorderLeft = BorderStyle.Thick;
style.LeftBorderColor = 256;
style.BorderRight = BorderStyle.Thick;
style.RightBorderColor = 256;
style.BorderBottom = BorderStyle.Thick;
style.BottomBorderColor = 256;
IRow row = ws.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
// Create a new font and alter it
IFont font = wb.CreateFont();
font.FontHeightInPoints = 24;
font.FontName = "Courier New";
font.IsItalic = true;
font.IsStrikeout = true;
// Fonts are set into a style so create a new one to use.
ICellStyle style = wb.CreateCellStyle();
style.SetFont(font);
IRow row = ws.CreateRow(0);
// Create a cell and put a value in it.
ICell cell = row.CreateCell(1);
cell.SetCellValue("Thisi s a test of fonts");
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.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();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
hlink_font.Color = HSSFColor.BLUE.index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.URL);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.FILE);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a place in this workbook
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// 读取DataTable的数据,并把数据写入excel
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel里第一个sheet的名称</param>
/// <param name="DataTable1">要写入的DataTable</param>
/// <param name="freezePaneRow">冻结拆分窗口的行数</param>
/// <returns></returns>
public string createExcel(string filePath, string fileName, string sheetName, DataTable DataTable1, int freezePaneRow)
{
string result = null;
try
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet(sheetName);
//设置居中
ICellStyle styleCell = xssfworkbook.CreateCellStyle();
styleCell.VerticalAlignment = VerticalAlignment.Center;
styleCell.Alignment = HorizontalAlignment.Center;
for (int i = 0; i < DataTable1.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i);
for (int j = 0; j < DataTable1.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(DataTable1.Rows[i][j].ToString());
sheet.GetRow(i).GetCell(j).CellStyle = styleCell;
}
}
//自适应列宽
for (int i = 0; i < DataTable1.Columns.Count; i++) {
sheet.AutoSizeColumn(i);
}
//冻结拆分窗口
sheet.CreateFreezePane(1, freezePaneRow);
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
stream.Close();
stream.Dispose();
if (!Directory.Exists(filePath)) {
Directory.CreateDirectory(filePath);
}
using (FileStream fs = new FileStream(filePath + fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
fs.Close();
fs.Dispose();
}
}
catch (Exception e) {
result = e.Message;
}
return result;
}
public void TestBug52348()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("52348.xlsx");
StylesTable st = workbook.GetStylesSource();
Assert.AreEqual(0, st.StyleXfsSize);
XSSFCellStyle style = workbook.CreateCellStyle() as XSSFCellStyle; // no exception at this point
Assert.IsNull(style.GetStyleXf());
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(workbook));
}
public void TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont());
Assert.AreEqual(18, orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont());
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
clone.Alignment = HorizontalAlignment.Left;
clone.DataFormat = 17;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(18, orig.DataFormat);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestGetFillForegroundColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(1, wb.NumCellStyles);
Assert.AreEqual(2, styles.GetFills().Count);
XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
Assert.AreEqual(FillPatternType.NoFill, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPatternType.SolidForeground);
Assert.AreEqual(FillPatternType.SolidForeground, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPatternType.SolidForeground);
Assert.AreEqual(FillPatternType.SolidForeground, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet A1");
IRow row = sheet.CreateRow(1);
// Create a cell and put a value in it.
ICell cell = row.CreateCell(1);
cell.SetCellValue(4);
// Style the cell with borders all around.
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = IndexedColors.Black.Index;
style.BorderLeft = BorderStyle.DashDotDot;
style.LeftBorderColor = IndexedColors.Green.Index;
style.BorderRight = BorderStyle.Hair;
style.RightBorderColor = IndexedColors.Blue.Index;
style.BorderTop = BorderStyle.MediumDashed;
style.TopBorderColor = IndexedColors.Orange.Index;
//create border diagonal
style.BorderDiagonalLineStyle = BorderStyle.Medium; //this property must be set before BorderDiagonal and BorderDiagonalColor
style.BorderDiagonal = BorderDiagonal.Forward;
style.BorderDiagonalColor = IndexedColors.Gold.Index;
cell.CellStyle = style;
// Create a cell and put a value in it.
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue(5);
ICellStyle style2 = workbook.CreateCellStyle();
style2.BorderDiagonalLineStyle = BorderStyle.Medium;
style2.BorderDiagonal = BorderDiagonal.Backward;
style2.BorderDiagonalColor = IndexedColors.Red.Index;
cell2.CellStyle = style2;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
protected void btn_click(object sender, EventArgs e)
{
//FileStream fs = new FileStream(Server.MapPath(@"\Content\Sample.xlsx"), FileMode.Open, FileAccess.Read);
//XSSFWorkbook temWorkBook = new XSSFWorkbook(fs);
//ISheet nsheet = temWorkBook.GetSheet("Sheet1");
//IRow datarow = nsheet.GetRow(4);
//datarow.GetCell(0).SetCellValue(77);
//nsheet.ForceFormulaRecalculation = true;
//using (var ms = new MemoryStream())
//{
// temWorkBook.Write(ms);
// Response.Clear();
// Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// Response.AppendHeader("Content-Disposition","inline;filename=Sample"+DateTime.Now.ToString("yyyyMMMdd")+".xlsx");
// Response.BinaryWrite(ms.ToArray());
// Response.End();
//}
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("New Sheet");
ISheet sheet2 = workbook.CreateSheet("Second Sheet");
ICell cell1 = sheet1.CreateRow(0).CreateCell(0);
IFont fontBold = workbook.CreateFont();
fontBold.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(fontBold);
cell1.CellStyle = style1;
cell1.SetCellValue("sample value");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for(int j = 0;j < 15;j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
Response.Clear();
Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AppendHeader("Content-Disposition", "inline;filename=Sample" + DateTime.Now.ToString("yyyyMMMdd") + ".xlsx");
Response.BinaryWrite(ms.ToArray());
Response.End();
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet A1");
IRow row = sheet.CreateRow(1);
// Create a cell and put a value in it.
ICell cell = row.CreateCell(1);
cell.SetCellValue(4);
// Style the cell with borders all around.
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.THIN;
style.BottomBorderColor = HSSFColor.BLACK.index;
style.BorderLeft = BorderStyle.DASH_DOT_DOT;
style.LeftBorderColor = HSSFColor.GREEN.index;
style.BorderRight = BorderStyle.HAIR;
style.RightBorderColor = HSSFColor.BLUE.index;
style.BorderTop = BorderStyle.MEDIUM_DASHED;
style.TopBorderColor = HSSFColor.ORANGE.index;
style.BorderDiagonalLineStyle = BorderStyle.MEDIUM; //this property must be set before BorderDiagonal and BorderDiagonalColor
style.BorderDiagonal = BorderDiagonal.FORWARD;
style.BorderDiagonalColor = HSSFColor.GOLD.index;
cell.CellStyle = style;
// Create a cell and put a value in it.
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue(5);
ICellStyle style2 = workbook.CreateCellStyle();
style2.BorderDiagonalLineStyle = BorderStyle.MEDIUM;
style2.BorderDiagonal = BorderDiagonal.BACKWARD;
style2.BorderDiagonalColor = HSSFColor.RED.index;
cell2.CellStyle = style2;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void TestUpdateAnyBorderSideNotAffectsOthersSameXSSFCellBorder()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
var cell1 = row.CreateCell(0);
var style1 = wb.CreateCellStyle();
style1.BorderBottom = BorderStyle.Double;
style1.BottomBorderColor = global::NPOI.SS.UserModel.IndexedColors.Black.Index;
cell1.CellStyle = style1;
Assert.AreEqual(BorderStyle.None, style1.BorderTop);
var cell2 = row.CreateCell(1);
var style2 = wb.CreateCellStyle();
style2.BorderBottom = BorderStyle.Double;
style2.BottomBorderColor = global::NPOI.SS.UserModel.IndexedColors.Black.Index;
style2.BorderTop = BorderStyle.Thin;
style2.TopBorderColor = global::NPOI.SS.UserModel.IndexedColors.Black.Index;
cell2.CellStyle = style2;
Assert.AreEqual(BorderStyle.None, style1.BorderTop);
wb.Close();
}
public void TestGetFillForegroundColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(1, wb.NumCellStyles);
Assert.AreEqual(2, styles.GetFills().Count);
XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
Assert.AreEqual(IndexedColors.AUTOMATIC.Index, defaultStyle.FillForegroundColor);
Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
Assert.AreEqual(FillPatternType.NO_FILL, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
}
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 = FillPatternType.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(FillPatternType.SolidForeground, styleBack.FillPattern);
wbBack.Close();
wb.Close();
}
private static ICellStyle SetCellStyle(NPOI.XSSF.UserModel.XSSFWorkbook workbook, string color)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.WrapText = true;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.FillPattern = FillPattern.SolidForeground;
switch (color)
{
case "rose":
cellStyle.FillForegroundColor = IndexedColors.Violet.Index;
break;
case "white":
cellStyle.FillForegroundColor = IndexedColors.White.Index;
break;
case "red":
cellStyle.FillForegroundColor = IndexedColors.Red.Index;
break;
case "yellow":
cellStyle.FillForegroundColor = IndexedColors.Yellow.Index;
break;
case "lime":
cellStyle.FillForegroundColor = IndexedColors.BrightGreen.Index;
break;
case "gray":
cellStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
break;
default:
cellStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
break;
}
//CellsStyle.FillBackgroundColor = IndexedColors.BrightGreen.Index;
return(cellStyle);
}
private void setUp_testCopyCellFrom_CellCopyPolicy()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFRow row = wb.CreateSheet().CreateRow(0) as XSSFRow;
srcCell = row.CreateCell(0) as XSSFCell;
destCell = row.CreateCell(1) as XSSFCell;
srcCell.CellFormula = ("2+3");
ICellStyle style = wb.CreateCellStyle();
style.BorderTop = BorderStyle.Thick;
style.FillBackgroundColor = ((short)5);
srcCell.CellStyle = (style);
destCell.SetCellValue(true);
}
public void Bug58996_UsedToWorkIn3_11_ButNotIn3_13()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCellStyle cellStyle = workbook.CreateCellStyle() as XSSFCellStyle;
cellStyle.FillForegroundColorColor = (null);
Assert.IsNull(cellStyle.FillForegroundColorColor);
cellStyle.FillBackgroundColorColor = (null);
Assert.IsNull(cellStyle.FillBackgroundColorColor);
cellStyle.FillPattern = FillPatternType.NoFill;;
Assert.AreEqual(FillPatternType.NoFill, cellStyle.FillPattern);
cellStyle.SetBottomBorderColor(null);
Assert.IsNull(cellStyle.BottomBorderXSSFColor);
cellStyle.SetTopBorderColor(null);
Assert.IsNull(cellStyle.TopBorderXSSFColor);
cellStyle.SetLeftBorderColor(null);
Assert.IsNull(cellStyle.LeftBorderXSSFColor);
cellStyle.SetRightBorderColor(null);
Assert.IsNull(cellStyle.RightBorderXSSFColor);
}
public void TestBug49702()
{
// First try with a new file
XSSFWorkbook wb = new XSSFWorkbook();
// Should have one style
Assert.AreEqual(1, wb.NumCellStyles);
wb.GetCellStyleAt((short)0);
try
{
wb.GetCellStyleAt((short)1);
Assert.Fail("Shouldn't be able to get style at 1 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Add another one
ICellStyle cs = wb.CreateCellStyle();
cs.DataFormat = ((short)11);
// Re-check
Assert.AreEqual(2, wb.NumCellStyles);
wb.GetCellStyleAt((short)0);
wb.GetCellStyleAt((short)1);
try
{
wb.GetCellStyleAt((short)2);
Assert.Fail("Shouldn't be able to get style at 2 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Save and reload
XSSFWorkbook nwb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(2, nwb.NumCellStyles);
nwb.GetCellStyleAt((short)0);
nwb.GetCellStyleAt((short)1);
try
{
nwb.GetCellStyleAt((short)2);
Assert.Fail("Shouldn't be able to Get style at 2 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Now with an existing file
wb = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx");
Assert.AreEqual(3, wb.NumCellStyles);
wb.GetCellStyleAt((short)0);
wb.GetCellStyleAt((short)1);
wb.GetCellStyleAt((short)2);
try
{
wb.GetCellStyleAt((short)3);
Assert.Fail("Shouldn't be able to Get style at 3 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
}
public static void ExportHandleNumExcel(Dictionary<string, Model.DTO.HKHandleNumDetail> hkHandleNumDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号6 香港新马寄出个数量化单（5.5之后）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("香港新马");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.GetRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚网申3");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue(hkHandleNumDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.HKHandleNumDetail item in hkHandleNumDetails.Values)
{
row = sheet.CreateRow(4 + i);
row.HeightInPoints = 25;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//申请学历
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.Education);
//学校申请
cell = row.CreateCell(4);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Application);
//新加坡公立硕士
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.HK.SingaporeMaster);
//香港博士
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.HK.Doctor);
//签证申请
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Visa);
//资深文案
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//寄出日期
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd"));
//备注
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(4 + hkHandleNumDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(7);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
row = sheet.CreateRow(5 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1、新马申请学历：幼稚园、小学、O Level、A Level、中学、预科、专科、本科、硕士、博士、语言");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2、香港申请学历：本科、硕士和博士");
cell.CellStyle = style;
row = sheet.CreateRow(7 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3、量化手写无效；需要机打。");
cell.CellStyle = style;
row = sheet.CreateRow(8 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、寄出后2个工作日内填报量化。");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("5、新加坡公立硕士以及香港博士申请一个合同号算2个寄出个数。");
cell.CellStyle = style;
row = sheet.CreateRow(10 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("6、未精益，只填报制作文案即可。");
cell.CellStyle = style;
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public void TestShrinkToFit()
{
// Existing file
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("ShrinkToFit.xlsx");
ISheet s = wb.GetSheetAt(0);
IRow r = s.GetRow(0);
ICellStyle cs = r.GetCell(0).CellStyle;
Assert.AreEqual(true, cs.ShrinkToFit);
// New file
XSSFWorkbook wbOrig = new XSSFWorkbook();
s = wbOrig.CreateSheet();
r = s.CreateRow(0);
cs = wbOrig.CreateCellStyle();
cs.ShrinkToFit = (/*setter*/false);
r.CreateCell(0).CellStyle = (/*setter*/cs);
cs = wbOrig.CreateCellStyle();
cs.ShrinkToFit = (/*setter*/true);
r.CreateCell(1).CellStyle = (/*setter*/cs);
// Write out1, Read, and check
wb = XSSFTestDataSamples.WriteOutAndReadBack(wbOrig) as XSSFWorkbook;
s = wb.GetSheetAt(0);
r = s.GetRow(0);
Assert.AreEqual(false, r.GetCell(0).CellStyle.ShrinkToFit);
Assert.AreEqual(true, r.GetCell(1).CellStyle.ShrinkToFit);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
}
public void TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont());
Assert.AreEqual(18, orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont());
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
clone.Alignment = HorizontalAlignment.Left;
clone.DataFormat = 17;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(18, orig.DataFormat);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestDefaultStyles()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFCellStyle style1 = (XSSFCellStyle)wb1.CreateCellStyle();
Assert.AreEqual(IndexedColors.Automatic.Index, style1.FillBackgroundColor);
Assert.IsNull(style1.FillBackgroundColorColor);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb1));
//compatibility with HSSF
HSSFWorkbook wb2 = new HSSFWorkbook();
HSSFCellStyle style2 = (HSSFCellStyle)wb2.CreateCellStyle();
Assert.AreEqual(style2.FillBackgroundColor, style1.FillBackgroundColor);
Assert.AreEqual(style2.FillForegroundColor, style1.FillForegroundColor);
Assert.AreEqual(style2.FillPattern, style1.FillPattern);
Assert.AreEqual(style2.LeftBorderColor, style1.LeftBorderColor);
Assert.AreEqual(style2.TopBorderColor, style1.TopBorderColor);
Assert.AreEqual(style2.RightBorderColor, style1.RightBorderColor);
Assert.AreEqual(style2.BottomBorderColor, style1.BottomBorderColor);
Assert.AreEqual(style2.BorderBottom, style1.BorderBottom);
Assert.AreEqual(style2.BorderLeft, style1.BorderLeft);
Assert.AreEqual(style2.BorderRight, style1.BorderRight);
Assert.AreEqual(style2.BorderTop, style1.BorderTop);
}
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="xssfworkbook">Excel操作类</param>
/// <param name="font">单元格字体</param>
/// <param name="fillForegroundColor">图案的颜色</param>
/// <param name="fillPattern">图案样式</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">垂直对齐方式</param>
/// <returns></returns>
public static ICellStyle GetCellStyle(XSSFWorkbook xssfworkbook, IFont font, XSSFColor fillForegroundColor, FillPattern fillPattern, XSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
{
XSSFCellStyle cellstyle = xssfworkbook.CreateCellStyle() as XSSFCellStyle;
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.SetFillForegroundColor(fillForegroundColor);
}
if (fillBackgroundColor != null)
{
cellstyle.SetFillBackgroundColor(fillBackgroundColor);
}
if (font != null)
{
cellstyle.SetFont(font);
}
return cellstyle;
}
public void Test48877()
{
String text = "Use \n with word wrap on to create a new line.\n" +
"This line finishes with two trailing spaces. ";
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IFont font1 = wb.CreateFont();
font1.Color=((short)20);
IFont font2 = wb.CreateFont();
font2.Color = (short)(FontColor.RED);
IFont font3 = wb.GetFontAt((short)0);
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(2) as XSSFCell;
XSSFRichTextString richTextString =
wb.GetCreationHelper().CreateRichTextString(text) as XSSFRichTextString;
// Check the text has the newline
Assert.AreEqual(text, richTextString.String);
// Apply the font
richTextString.ApplyFont(font3);
richTextString.ApplyFont(0, 3, font1);
cell.SetCellValue(richTextString);
// To enable newlines you need Set a cell styles with wrap=true
ICellStyle cs = wb.CreateCellStyle();
cs.WrapText=(true);
cell.CellStyle=(cs);
// Check the text has the
Assert.AreEqual(text, cell.StringCellValue);
// Save the file and re-read it
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
cell = row.GetCell(2) as XSSFCell;
Assert.AreEqual(text, cell.StringCellValue);
// Now add a 2nd, and check again
int fontAt = text.IndexOf("\n", 6);
cell.RichStringCellValue.ApplyFont(10, fontAt + 1, font2);
Assert.AreEqual(text, cell.StringCellValue);
Assert.AreEqual(4, (cell.RichStringCellValue as XSSFRichTextString).NumFormattingRuns);
Assert.AreEqual("Use", (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[0].t);
String r3 = (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[2].t;
Assert.AreEqual("line.\n", r3.Substring(r3.Length - 6));
// Save and re-check
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
cell = row.GetCell(2) as XSSFCell;
Assert.AreEqual(text, cell.StringCellValue);
// FileOutputStream out = new FileOutputStream("/tmp/test48877.xlsx");
// wb.Write(out);
// out.Close();
}
public static System.IO.MemoryStream createExcelReport(DataTable dt)
{
using (System.IO.MemoryStream mem = new System.IO.MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.Center;
headerLabelCellStyle.BorderBottom = BorderStyle.Thin;
headerLabelCellStyle.BorderRight = BorderStyle.Thin;
headerLabelCellStyle.BorderTop = BorderStyle.Thin;
headerLabelCellStyle.BorderLeft = BorderStyle.Thin;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.Bold;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.Right;
rightAlignedCellStyle.BorderBottom = BorderStyle.Thin;
rightAlignedCellStyle.BorderRight = BorderStyle.Thin;
rightAlignedCellStyle.BorderTop = BorderStyle.Thin;
rightAlignedCellStyle.BorderLeft = BorderStyle.Thin;
#endregion
#region Currency Cell Style
var CellStyleRight = workbook.CreateCellStyle();
CellStyleRight.Alignment = HorizontalAlignment.Right;
CellStyleRight.BorderBottom = BorderStyle.Thin;
CellStyleRight.BorderRight = BorderStyle.Thin;
CellStyleRight.BorderTop = BorderStyle.Thin;
CellStyleRight.BorderLeft = BorderStyle.Thin;
#endregion
#region Detail Currency Subtotal Style
var CellStyleNormal = workbook.CreateCellStyle();
CellStyleNormal.BorderBottom = BorderStyle.Thin;
CellStyleNormal.BorderRight = BorderStyle.Thin;
CellStyleNormal.BorderTop = BorderStyle.Thin;
CellStyleNormal.BorderLeft = BorderStyle.Thin;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.Normal;
CellStyleNormal.SetFont(detailCurrencySubtotalFont);
#endregion
#endregion
var sheet = workbook.CreateSheet(dt.TableName);
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
List <String> columns = new List <string>();
for (int i = 0; i < dt.Columns.Count; i++)
{
columns.Add(dt.Columns[i].ColumnName);
var cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
cell.CellStyle = headerLabelCellStyle;
}
rowIndex++;
//// Add data rows
foreach (System.Data.DataRow item in dt.Rows)
{
row = sheet.CreateRow(rowIndex);
for (int ii = 0; ii < dt.Columns.Count; ii++)
{
var cell = row.CreateCell(ii);
cell.SetCellValue(dt.Columns[ii].ColumnName);
if (dt.Columns[ii].DataType == typeof(System.Decimal))
{
cell.CellStyle = CellStyleRight;
}
else
{
cell.CellStyle = CellStyleNormal;
}
cell.SetCellValue(item[dt.Columns[ii].ColumnName].ToString());
}
rowIndex++;
}
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(mem);
return(mem);
}
}
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 static void ExportUKExcel(Dictionary<string, Model.DTO.UKHandleNumDetail> ukHandleNumDetails, Dictionary<string, Model.DTO.UKHandleQuanDetail> ukHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\英国学校申请量化激励日报单（2015年1月1日后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("个数单");
//下面开始导出个数
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
IRow row = sheet.GetRow(3);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(12);
cell.SetCellValue(ukHandleNumDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleNumDetail item in ukHandleNumDetails.Values)
{
row = sheet.CreateRow(6 + i);
row.HeightInPoints = 16.5F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//申请学历
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.Education);
//前四
cell = row.CreateCell(4);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.FirstFour);
//博士
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.Doctor);
//国内合作
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.Cooperation);
//申请个数
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.ApplyNum);
//网申个数
cell = row.CreateCell(8);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.OnlineNum);
//文书个数
cell = row.CreateCell(9);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.PSNum);
//资深文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//高级文书文案
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.OldSenior);
//文书文案
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.OldAuthor);
//转案区间
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(15);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(6 + ukHandleNumDetails.Count);
row.HeightInPoints = 12;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
row = sheet.CreateRow(7+ ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1、申请学历填写标准的申请类别");
cell.CellStyle = style;
row = sheet.CreateRow(8 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2、量化手写无效；需要机打。");
cell.CellStyle = style;
row = sheet.CreateRow(9 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3、寄出后2个工作日内填报量化。");
cell.CellStyle = style;
row = sheet.CreateRow(10 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、博士申请及英国前四所本硕申请一个合同号算2个寄出个数，国内合作一个合同号算0.5个寄出个数。");
cell.CellStyle = style;
//下面开始导出量化
sheet = workbook.GetSheet("量化单");
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.GetRow(3);
cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(11);
cell.SetCellValue(ukHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleQuanDetail item in ukHandleQuanDetails.Values)
{
row = sheet.CreateRow(6 + i);
row.HeightInPoints = 16.7F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//套磁
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Magnetic);
//文书
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.PS);
//录取
cell = row.CreateCell(8);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//资深文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//文书文案
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//转案区间
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(6 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(7 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
row = sheet.CreateRow(8 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
row = sheet.CreateRow(9 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("3、申请学历填写标准的申请类别");
cell.CellStyle = style;
//下面开始保存
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.HKHandleQuanDetail> hkHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号12 香港新马激励量化单（5.5日之后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("香港新马");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.GetRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚网申3");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue(hkHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.HKHandleQuanDetail item in hkHandleQuanDetails.Values)
{
row = sheet.CreateRow(4 + i);
row.HeightInPoints = 25;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//翻译
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Translation);
//签证寄出
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Visa);
//寄出日期
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd"));
//资深文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//新马文案
cell = row.CreateCell(11);
cell.CellStyle = style;
//录取
cell = row.CreateCell(12);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//获签
cell = row.CreateCell(13);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Sign);
//文书文案
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(15);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//备注
cell = row.CreateCell(16);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(4 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(9);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
//style.BorderTop = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
////设置合并单元格区域
//CellRangeAddress cellRangeAddress = new CellRangeAddress(5 + hkHandleQuanDetails.Count, 5 + hkHandleQuanDetails.Count, 0, 15);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(5 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
////设置合并单元格区域
//cellRangeAddress = new CellRangeAddress(6 + hkHandleQuanDetails.Count, 6 + hkHandleQuanDetails.Count, 0, 15);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(6 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
cell.CellStyle.Alignment = HorizontalAlignment.Left;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
//设置合并单元格区域
//cellRangeAddress = new CellRangeAddress(7 + hkHandleQuanDetails.Count, 7 + hkHandleQuanDetails.Count, 0, 1);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(7 + hkHandleQuanDetails.Count);
//row.HeightInPoints = 37.5F;
cell = row.CreateCell(0);
cell.SetCellValue("3、新加坡量化类别：");
cell.CellStyle = style;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
row = sheet.CreateRow(8 + hkHandleQuanDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、香港量化类别：");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hkHandleQuanDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("5、马来西亚量化类别：");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.GetRow(7 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("公立硕士");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("幼儿园，小学，公立大学，大专、本科、语言/预科/私立大专，本科、研究生");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("NEXUS/BC幼儿园");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("visa only");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.CellStyle = style;
row = sheet.GetRow(8 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("博士/研究生");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("PS（1封）+推荐信（2封）+CV（1封）");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("个人陈述PS（1封）");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("推荐信（2封）");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("CV（1封）");
cell.CellStyle = style;
row = sheet.GetRow(9 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("公立本科");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("公立硕士");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("语言/预科/私立本科、研究生");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("家长旅游签");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.CellStyle = style;
sheet.AutoSizeColumn(4);
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.UKHandleQuanDetail> ukHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\英国学校申请量化激励日报单（2015年1月1日后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("量化单");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
IRow row = sheet.GetRow(3);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(11);
cell.SetCellValue(ukHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleQuanDetail item in ukHandleQuanDetails.Values)
{
row = sheet.CreateRow(5 + i);
row.HeightInPoints = 16.7F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//套磁
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Magnetic);
//文书
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.PS);
//录取
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//资深文案
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//文书文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//转案区间
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(5 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//设置合并单元格区域
CellRangeAddress cellRangeAddress = new CellRangeAddress(7 + ukHandleQuanDetails.Count, 7 + ukHandleQuanDetails.Count, 0, 12);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(7 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
//设置合并单元格区域
cellRangeAddress = new CellRangeAddress(8 + ukHandleQuanDetails.Count, 8 + ukHandleQuanDetails.Count, 0, 12);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(8 + ukHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
cell.CellStyle.Alignment = HorizontalAlignment.Left;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
//设置合并单元格区域
cellRangeAddress = new CellRangeAddress(9 + ukHandleQuanDetails.Count, 9 + ukHandleQuanDetails.Count, 0, 1);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(9 + ukHandleQuanDetails.Count);
row.HeightInPoints = 37.5F;
cell = row.CreateCell(0);
cell.SetCellValue("3、申请学历填写标准的申请类别");
cell.CellStyle = style;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
////设置单元格格式
//style = workbook.CreateCellStyle();
//style.Alignment = HorizontalAlignment.Center;
//style.VerticalAlignment = VerticalAlignment.Center;
//style.BorderTop = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderBottom = BorderStyle.Thin;
//font = workbook.CreateFont();
//font.FontName = "宋体";
//font.FontHeightInPoints = 11;
//style.SetFont(font);
//cell = row.CreateCell(2);
//cell.SetCellValue("高中及以下（无文书）");
//style.WrapText = true;
//cell.CellStyle = style;
//cell = row.CreateCell(3);
//cell.SetCellValue("高端本科");
//cell.CellStyle = style;
//cell = row.CreateCell(4);
//cell.SetCellValue("本科预科/硕士预科（无文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(5);
//cell.SetCellValue("非高端本科院校");
//cell.CellStyle = style;
//cell = row.CreateCell(6);
//cell.SetCellValue("高端硕士院校");
//cell.CellStyle = style;
//cell = row.CreateCell(7);
//cell.SetCellValue("非高端研究生");
//cell.CellStyle = style;
//cell = row.CreateCell(8);
//cell.SetCellValue("博士");
//cell.CellStyle = style;
//cell = row.CreateCell(9);
//cell.SetCellValue("只申请语言学校");
//cell.CellStyle = style;
//cell = row.CreateCell(10);
//cell.SetCellValue("高中及以下（有文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(11);
//cell.SetCellValue("本科预科/硕士预科（有文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(12);
//cell.CellStyle = style;
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public void CreateExcelFile()
{
string FileName = @"d:\5.5.5.101_5555_Log_18.txt";
string[] filelist = File.ReadAllLines(FileName, Encoding.Default);
List <string> StringLists = new List <string>();
int gg = 0;
for (int linenum = filelist.Length - 1; linenum >= 0; linenum--)
{
if (filelist[linenum].IndexOf("ANR") > -1)
{
int first = filelist[linenum].IndexOf("ANR in ") + "ANR in ".Length;
int last = filelist[linenum].LastIndexOf(" (");
string str2 = filelist[linenum].Substring(first, last - first);
StringLists.Add(str2);
}
}
////建立Excel 2007檔案
IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//合併區
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 6)); //合併Summary行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 5)); //合併Error List行
//背景色（藍色）
ICellStyle cellStyle0 = workbook.CreateCellStyle();
cellStyle0.FillPattern = FillPattern.SolidForeground;
cellStyle0.FillForegroundColor = IndexedColors.PaleBlue.Index;
cellStyle0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（綠色）
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = IndexedColors.Lime.Index;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（粉色）
ICellStyle cellStyle2 = workbook.CreateCellStyle();
cellStyle2.FillPattern = FillPattern.SolidForeground;
cellStyle2.FillForegroundColor = IndexedColors.Tan.Index;
cellStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（灰色）
ICellStyle cellStyle3 = workbook.CreateCellStyle();
cellStyle3.FillPattern = FillPattern.SolidForeground;
cellStyle3.FillForegroundColor = IndexedColors.Grey25Percent.Index;
//背景色（白色）
ICellStyle cellStyle4 = workbook.CreateCellStyle();
cellStyle4.FillPattern = FillPattern.SolidForeground;
cellStyle4.FillForegroundColor = IndexedColors.White.Index;
//Summary儲存格格式
ICellStyle summaryStyle = workbook.CreateCellStyle();
IFont summaryFont = workbook.CreateFont();
summaryFont.FontHeightInPoints = 18;
summaryStyle.SetFont(summaryFont);
summaryStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
summaryStyle.FillPattern = FillPattern.SolidForeground;
summaryStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;
//A列
sheet.CreateRow(0).CreateCell(0).SetCellValue("Project Name");
sheet.CreateRow(1).CreateCell(0).SetCellValue("Model Name");
sheet.CreateRow(2).CreateCell(0).SetCellValue("Start Time");
sheet.CreateRow(3).CreateCell(0).SetCellValue("Renew Time");
sheet.CreateRow(4).CreateCell(0).SetCellValue("SW Build Time");
sheet.CreateRow(5).CreateCell(0).SetCellValue("Project No.");
sheet.CreateRow(6).CreateCell(0).SetCellValue("Test Device");
sheet.CreateRow(7).CreateCell(0).SetCellValue("Tester");
for (int A = 0; A < 8; A++)
{
sheet.GetRow(A).GetCell(0).CellStyle = cellStyle0;
}
//E列
sheet.GetRow(0).CreateCell(4).SetCellValue("Date");
sheet.GetRow(1).CreateCell(4).SetCellValue("Period (H)");
sheet.GetRow(2).CreateCell(4).SetCellValue("SW ISSUES");
sheet.GetRow(3).CreateCell(4).SetCellValue("System Crash");
sheet.GetRow(4).CreateCell(4).SetCellValue("Result");
sheet.GetRow(5).CreateCell(4).SetCellValue("MTBF_SW");
sheet.GetRow(6).CreateCell(4).SetCellValue("MTBF_Crash");
for (int E = 0; E < 7; E++)
{
sheet.GetRow(E).GetCell(4).CellStyle = cellStyle0;
}
sheet.GetRow(4).GetCell(4).CellStyle = cellStyle4;
//F列
sheet.GetRow(0).CreateCell(5).SetCellValue("-----");
sheet.GetRow(1).CreateCell(5).SetCellValue("-----");
sheet.GetRow(2).CreateCell(5).SetCellValue("-----");
sheet.GetRow(3).CreateCell(5).SetCellValue("-----");
sheet.GetRow(4).CreateCell(5).SetCellValue("");
sheet.GetRow(5).CreateCell(5).SetCellValue("-----");
sheet.GetRow(6).CreateCell(5).SetCellValue("-----");
for (int F = 0; F < 7; F++)
{
sheet.GetRow(F).GetCell(5).CellStyle = cellStyle2;
}
sheet.GetRow(4).GetCell(5).CellStyle = cellStyle4;
//Summary
sheet.CreateRow(9).CreateCell(0).SetCellValue("Summary");
sheet.GetRow(9).GetCell(0).CellStyle = summaryStyle;
//Error List
sheet.CreateRow(10).CreateCell(0).SetCellValue("Error List");
sheet.GetRow(10).GetCell(0).CellStyle = cellStyle3;
//Total
sheet.GetRow(10).CreateCell(6).SetCellValue("Total");
sheet.GetRow(10).GetCell(6).CellStyle = cellStyle3;
//搜尋相同字串並記次
Dictionary <string, int> dict = new Dictionary <string, int>();
foreach (string myStringList in StringLists)
{
if (dict.ContainsKey(myStringList))
{
//如果Dictionary中存在这个关键词元素，则把这个Dictionary的key+1
dict[myStringList]++;
}
else
{
//如果Dictionary中不存在这个关键词元素，则把它添加进Dictionary
dict.Add(myStringList, 1);
}
}
int rowcnt = dict.Count;
while (rowcnt != 0)
{
foreach (KeyValuePair <string, int> item in dict)
{
Console.WriteLine(item.Key);
Console.WriteLine(item.Value);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10 + rowcnt, 10 + rowcnt, 0, 5)); //合併Error List行
sheet.CreateRow(10 + rowcnt).CreateCell(0).SetCellValue(item.Key);
sheet.GetRow(10 + rowcnt).CreateCell(6).SetCellValue(item.Value);
rowcnt--;
}
}
for (int c = 0; c <= 25; c++)
{
sheet.AutoSizeColumn(c);
}
FileStream file = new FileStream(@"d:\npoi.xlsx", FileMode.Create);//產生檔案
workbook.Write(file);
file.Close();
}
public void TestGetFillForegroundColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(1, wb.NumCellStyles);
Assert.AreEqual(2, styles.GetFills().Count);
XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
Assert.AreEqual(IndexedColors.AUTOMATIC.Index, defaultStyle.FillForegroundColor);
Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
Assert.AreEqual(FillPatternType.NO_FILL, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
}
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 TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.RIGHT);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.RIGHT,orig.Alignment);
Assert.AreEqual(fnt,orig.GetFont());
Assert.AreEqual(18,orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreNotEqual(fnt,clone.GetFont());
Assert.AreNotEqual(18,clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
}
public void Bug49702()
{
// First try with a new file
XSSFWorkbook wb1 = new XSSFWorkbook();
// Should have one style
Assert.AreEqual(1, wb1.NumCellStyles);
wb1.GetCellStyleAt((short)0);
try
{
wb1.GetCellStyleAt((short)1);
Assert.Fail("Shouldn't be able to get style at 1 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Add another one
ICellStyle cs = wb1.CreateCellStyle();
cs.DataFormat = ((short)11);
// Re-check
Assert.AreEqual(2, wb1.NumCellStyles);
wb1.GetCellStyleAt((short)0);
wb1.GetCellStyleAt((short)1);
try
{
wb1.GetCellStyleAt((short)2);
Assert.Fail("Shouldn't be able to get style at 2 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Save and reload
XSSFWorkbook nwb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb1);
Assert.AreEqual(2, nwb.NumCellStyles);
nwb.GetCellStyleAt((short)0);
nwb.GetCellStyleAt((short)1);
try
{
nwb.GetCellStyleAt((short)2);
Assert.Fail("Shouldn't be able to Get style at 2 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Now with an existing file
XSSFWorkbook wb2 = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx");
Assert.AreEqual(3, wb2.NumCellStyles);
wb2.GetCellStyleAt((short)0);
wb2.GetCellStyleAt((short)1);
wb2.GetCellStyleAt((short)2);
try
{
wb2.GetCellStyleAt((short)3);
Assert.Fail("Shouldn't be able to Get style at 3 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
wb2.Close();
wb1.Close();
nwb.Close();
}
public void TestBug55752()
{
IWorkbook wb = new XSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet("test");
for (int i = 0; i < 4; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < 2; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = (wb.CreateCellStyle());
}
}
// set content
IRow row1 = sheet.GetRow(0);
row1.GetCell(0).SetCellValue("AAA");
IRow row2 = sheet.GetRow(1);
row2.GetCell(0).SetCellValue("BBB");
IRow row3 = sheet.GetRow(2);
row3.GetCell(0).SetCellValue("CCC");
IRow row4 = sheet.GetRow(3);
row4.GetCell(0).SetCellValue("DDD");
// merge cells
CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1);
sheet.AddMergedRegion(range1);
CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1);
sheet.AddMergedRegion(range2);
CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1);
sheet.AddMergedRegion(range3);
Assert.AreEqual(0, range3.FirstColumn);
Assert.AreEqual(1, range3.LastColumn);
Assert.AreEqual(2, range3.LastRow);
CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1);
sheet.AddMergedRegion(range4);
// set border
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range1, sheet, wb);
row2.GetCell(0).CellStyle.BorderBottom = BorderStyle.Thin;
row2.GetCell(1).CellStyle.BorderBottom = BorderStyle.Thin;
ICell cell0 = CellUtil.GetCell(row3, 0);
CellUtil.SetCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
ICell cell1 = CellUtil.GetCell(row3, 1);
CellUtil.SetCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range4, sheet, wb);
// write to file
Stream stream = new FileStream("C:/temp/55752.xlsx", FileMode.Create, FileAccess.ReadWrite);
try
{
wb.Write(stream);
}
finally
{
stream.Close();
}
}
finally
{
wb.Close();
}
}
public void TestGetFillForegroundColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(1, wb.NumCellStyles);
Assert.AreEqual(2, styles.GetFills().Count);
XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
Assert.AreEqual(FillPattern.NoFill, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void Test51037()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s = wb.CreateSheet() as XSSFSheet;
ICellStyle defaultStyle = wb.GetCellStyleAt((short)0);
Assert.AreEqual(0, defaultStyle.Index);
ICellStyle blueStyle = wb.CreateCellStyle();
blueStyle.FillForegroundColor=(IndexedColors.AQUA.Index);
blueStyle.FillPattern=(FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(1, blueStyle.Index);
ICellStyle pinkStyle = wb.CreateCellStyle();
pinkStyle.FillForegroundColor=(IndexedColors.PINK.Index);
pinkStyle.FillPattern=(FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(2, pinkStyle.Index);
// Starts empty
Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray());
CT_Cols cols = s.GetCTWorksheet().GetColsArray(0);
Assert.AreEqual(0, cols.sizeOfColArray());
// Add some rows and columns
XSSFRow r1 = s.CreateRow(0) as XSSFRow;
XSSFRow r2 = s.CreateRow(1) as XSSFRow;
r1.CreateCell(0);
r1.CreateCell(2);
r2.CreateCell(0);
r2.CreateCell(3);
// Check no style is there
Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray());
Assert.AreEqual(0, cols.sizeOfColArray());
Assert.AreEqual(defaultStyle, s.GetColumnStyle(0));
Assert.AreEqual(defaultStyle, s.GetColumnStyle(2));
Assert.AreEqual(defaultStyle, s.GetColumnStyle(3));
// Apply the styles
s.SetDefaultColumnStyle(0, pinkStyle);
s.SetDefaultColumnStyle(3, blueStyle);
// Check
Assert.AreEqual(pinkStyle, s.GetColumnStyle(0));
Assert.AreEqual(defaultStyle, s.GetColumnStyle(2));
Assert.AreEqual(blueStyle, s.GetColumnStyle(3));
Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray());
Assert.AreEqual(2, cols.sizeOfColArray());
Assert.AreEqual(1, cols.GetColArray(0).min);
Assert.AreEqual(1, cols.GetColArray(0).max);
Assert.AreEqual(pinkStyle.Index, cols.GetColArray(0).style);
Assert.AreEqual(4, cols.GetColArray(1).min);
Assert.AreEqual(4, cols.GetColArray(1).max);
Assert.AreEqual(blueStyle.Index, cols.GetColArray(1).style);
// Save, re-load and re-check
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
s = wb.GetSheetAt(0) as XSSFSheet;
defaultStyle = wb.GetCellStyleAt(defaultStyle.Index);
blueStyle = wb.GetCellStyleAt(blueStyle.Index);
pinkStyle = wb.GetCellStyleAt(pinkStyle.Index);
Assert.AreEqual(pinkStyle, s.GetColumnStyle(0));
Assert.AreEqual(defaultStyle, s.GetColumnStyle(2));
Assert.AreEqual(blueStyle, s.GetColumnStyle(3));
}
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 IHttpActionResult ExportAllToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
if (filter.GroupBy <= 0)
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] columnname = filter.DynamicFilters.Where(a => a.IsVisible == true).Select(a => a.ColumnName).ToArray();
string[] headers, columns = null;
headers = columns = columnname;
columns = columns.Select(x => x.Replace("SalesTeam", "RepGroup")).ToArray();
columns = columns.Select(x => x.Replace("CollectedDate", "CollectionDate")).ToArray();
//byte[] rgb = new byte[3] { 22, 183, 223 };
//XSSFCellStyle HeaderCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//HeaderCellStyle.SetFillBackgroundColor(new XSSFColor(rgb));
var headerRow = sheet.CreateRow(0);
//headerRow.RowStyle.FillBackgroundColor= (short)ColorTranslator.ToWin32(Color.Aqua);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <EntitySales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell = row.CreateCell(j);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font);
string value = null;
var objSalesItem = salesList[i];
var objStaticItem = objSalesItem.GetType().GetTypeInfo().GetProperty(columns[j]);
if (objStaticItem != null)
{
var property = salesList[i].GetType().GetRuntimeProperties().FirstOrDefault(p => string.Equals(p.Name, objStaticItem.Name, StringComparison.OrdinalIgnoreCase));
if (property != null)
{
value = Convert.ToString(property.GetValue(salesList[i], null));
}
}
else
{
var objDynamicItem = objSalesItem.ReportColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
bool isFinanceDataCount = salesList[i].FinanceDataRecordCount > 1;
if (isFinanceDataCount)
{
for (int k = 0; k < salesList[i].FinanceDataRecordCount; k++)
{
rowIndex++;
var financeRow = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var cell = financeRow.CreateCell(j);
string value = null;
var objSalesItem = salesList[i].FinanceDataList.ToList()[k];
var objStaticItem = objSalesItem.GetType().GetProperty(columns[j]);
if (objStaticItem != null)
{
value = Convert.ToString(objStaticItem.GetValue(objSalesItem, null));
}
else
{
var objDynamicItem = objSalesItem.FinanceColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
}
}
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
#region Sales Grouped Report
return(ExportGroupedSalesToExcel(filter));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
public IHttpActionResult ExportGroupedSalesToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] headers = new string[] { filter.GroupBy == 1 ? "Practice Name" : filter.GroupBy == 2 ? "Rep Name" : "Sales Team", "Sales", "Last Activity On" };
var headerRow = sheet.CreateRow(0);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <GroupedSales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
var objSalesItem = salesList[i];
if (objSalesItem == null)
{
continue;
}
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell1 = row.CreateCell(0);
cell1.CellStyle = workbook.CreateCellStyle();
cell1.CellStyle.SetFont(font);
cell1.SetCellValue(string.IsNullOrEmpty(objSalesItem.KeyName) ? "Missing Information" : objSalesItem.KeyName);
var cell2 = row.CreateCell(1);
cell2.CellStyle = workbook.CreateCellStyle();
cell2.CellStyle.SetFont(font);
cell2.SetCellValue(objSalesItem.Count);
var cell3 = row.CreateCell(2);
cell3.CellStyle = workbook.CreateCellStyle();
cell3.CellStyle.SetFont(font);
cell3.SetCellValue(Convert.ToString(objSalesItem.LastActivityOn));
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
private IWorkbook GenerateHeader()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheetReport = workbook.CreateSheet("Report");
IFont fontBold = workbook.CreateFont();
fontBold.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle styleFontBold = workbook.CreateCellStyle();
styleFontBold.SetFont(fontBold);
ICell cell1 = sheetReport.CreateRow(0).CreateCell(0);
cell1.CellStyle = styleFontBold;
cell1.SetCellValue("From " + txtBoxFrom.Text + " to " + txtBoxTo.Text);
sheetReport.CreateRow(1).CreateCell(0).SetCellValue("Labor Cost Report");
sheetReport.GetRow(1).GetCell(0).CellStyle = styleFontBold;
return workbook;
}
