public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue("Date Range");
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
public static StyleContainer CreateStyles(HSSFWorkbook workbook)
{
var styles = new StyleContainer();
var h1Font = workbook.CreateFont();
h1Font.FontHeightInPoints = (short)24;
h1Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
var h2Font = workbook.CreateFont();
h2Font.FontHeightInPoints = (short)16;
h2Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
var h3Font = workbook.CreateFont();
h3Font.FontHeightInPoints = (short)12;
h3Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
styles.RightAligned = workbook.CreateCellStyle();
styles.RightAligned.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
styles.Date = workbook.CreateCellStyle();
styles.Date.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
styles.Date.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
styles.Currency = workbook.CreateCellStyle();
styles.Currency.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
//styles.Currency.DataFormat = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
styles.Currency.DataFormat = (short)7;
styles.LeftAligned = workbook.CreateCellStyle();
styles.LeftAligned.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
styles.TitleStyle = workbook.CreateCellStyle();
styles.TitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
styles.TitleStyle.SetFont(titleFont);
// Fonts are set into a style so create a new one to use.
styles.HeaderStyle = workbook.CreateCellStyle();
styles.HeaderStyle.SetFont(h1Font);
styles.HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Fonts are set into a style so create a new one to use.
styles.Header2Style = workbook.CreateCellStyle();
styles.Header2Style.SetFont(h2Font);
// Fonts are set into a style so create a new one to use.
styles.Header3Style = workbook.CreateCellStyle();
styles.Header3Style.SetFont(h3Font);
return styles;
}
public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
//ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color=(NPOI.HSSF.Util.HSSFColor.RED.index);
fnt.Boldweight=(short)FontBoldWeight.BOLD;
cs.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle=(cs);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
/// <summary>
/// Creates the excel workbook.
/// </summary>
/// <param name="subject">The subject.</param>
public void CreateWorkbook(string subject)
{
//Creating the excel workbook
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
//Creating summary information to the document
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Thoris";
//Applying summary information to the document
wb.DocumentSummaryInformation = dsi;
//Creating summary information for the data
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
//Applying summary information to the data
wb.SummaryInformation = si;
_workbook = wb;
//Creating the current styles
Font font = _workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
cellStyle.SetFont(font);
_headerStyle = CreateHeaderStyle(_workbook);
_dataStyle = CreateStyle(_workbook, false);
}
public void AddListToExcelSheet(HSSFWorkbook workbook, ISheet sheet, string Title, Dictionary<string, bool> list)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue(Title);
foreach (var org in list)
{
if (org.Value == true)
{
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
cell.SetCellValue(org.Key);
}
}
}
/// <summary>
/// 加边框
/// </summary>
/// <param Name="rowindex">1开始</param>
/// <param Name="cellIndex">1开始</param>
public void AddBorder( ISheet sheet, HSSFWorkbook workbook)
{
ICellStyle styel = workbook.CreateCellStyle();
styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 11;
font1.Boldweight = 600;
font1.FontName = "宋体";
styel.SetFont(font1);
for (int rowindex=1;rowindex<sheet.LastRowNum+1;rowindex++)
{
for (int cellIndex =0; cellIndex < dcs.Count;cellIndex++ )
{
sheet.GetRow(rowindex).RowStyle = styel;
ICell cell = sheet.GetRow(rowindex ).GetCell(cellIndex );
HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle;
Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
Style.VerticalAlignment = VerticalAlignment.Center;
Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
Style.DataFormat = 0;
Style.SetFont(font1);
cell.CellStyle = Style;
}
}
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="response"></param>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetname"></param>
public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
response.ContentType = "application/vnd.ms-excel";
//response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public WorkbookExcelRender()
{
_workbook = new HSSFWorkbook();
_isDisposed = false;
OddCellStyle = _workbook.CreateCellStyle();
EvenCellStyle = _workbook.CreateCellStyle();
OddCellStyleCenterAligned = _workbook.CreateCellStyle();
EvenCellStyleCenterAligned = _workbook.CreateCellStyle();
_nextCellIndex = -1;
RTFHelper = new RTFHelper()
{
NegFont = _workbook.CreateFont(),
NormalFont = _workbook.CreateFont(),
PosFont = _workbook.CreateFont(),
NegFontCrossed = _workbook.CreateFont(),
NormalFontCrossed = _workbook.CreateFont(),
PosFontCrossed = _workbook.CreateFont(),
Pallete = _workbook.GetCustomPalette(),
RTFRenderer = new RtfTextRender()
};
InitDefaultRTFHelper();
InitDefaultCellStyles();
}
/// <summary>
/// 标题单元格样式
/// </summary>
/// <param name="book"></param>
/// <returns>参见模型</returns>
public ICellStyle getCellStyleTitle(NPOI.HSSF.UserModel.HSSFWorkbook book)
{
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
IFont titlefont = book.CreateFont();
titlefont.FontHeightInPoints = 14;
titlefont.Boldweight = (short)FontBoldWeight.Bold; //字体加粗
cellstyle.SetFont(titlefont);
return(cellstyle);
}
/// <summary>
/// 普通单元格样式 居中
/// </summary>
/// <param name="book"></param>
/// <returns>参见模型</returns>
public ICellStyle getCellStyleCenter(NPOI.HSSF.UserModel.HSSFWorkbook book)
{
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center; //水平居中
cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cellstyle.WrapText = true; //自动换行
IFont cellfont = book.CreateFont();
cellfont.FontHeightInPoints = 11; //11号字体
cellstyle.SetFont(cellfont);
return(cellstyle);
}
public byte[] WriteExcel(IEnumerable data, string[] columns)
{
MemoryStream output = new MemoryStream();
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.SetFont(headerFont);
headerStyle.Alignment = HorizontalAlignment.Center;
//(Optional) freeze the header row so it is not scrolled
sheet.CreateFreezePane(0, 1, 0, 1);
IEnumerator foo = data.GetEnumerator();
foo.MoveNext();
Type t = foo.Current.GetType();
IRow header = sheet.CreateRow(0);
PropertyInfo[] properties = t.GetProperties();
int colIndex = 0;
for (int i = 0; i < properties.Length; i++)
{
if (columns.Contains(properties[i].Name))
{
ICell cell = header.CreateCell(colIndex);
cell.CellStyle = headerStyle;
cell.SetCellValue(properties[i].Name);
colIndex++;
}
}
int rowIndex = 0;
foreach (object o in data)
{
colIndex = 0;
IRow row = sheet.CreateRow(rowIndex + 1);
for (int i = 0; i < properties.Length; i++)
{
if (columns.Contains(properties[i].Name))
{
row.CreateCell(colIndex).SetCellValue(properties[i].GetValue(o, null).ToString());
colIndex++;
}
}
rowIndex++;
}
workbook.Write(output);
return output.ToArray();
}
/// <summary>
/// 头部单元格样式
/// </summary>
/// <param name="book"></param>
/// <returns>参见模型</returns>
public ICellStyle getCellStyleHead(NPOI.HSSF.UserModel.HSSFWorkbook book)
{
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.WrapText = true;
IFont cellheadfont = book.CreateFont();
cellheadfont.FontHeightInPoints = 11;
cellheadfont.Boldweight = (short)FontBoldWeight.Bold; //字体加粗
cellstyle.SetFont(cellheadfont);
return(cellstyle);
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public bool ToExcel(DataTable table)
{
FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
IWorkbook workBook = new HSSFWorkbook();
this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName;
ISheet sheet = workBook.CreateSheet(this._sheetName);
//处理表格标题
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(this._title);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
row.Height = 500;
ICellStyle cellStyle = workBook.CreateCellStyle();
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 17;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
row.Cells[0].CellStyle = cellStyle;
//处理表格列头
row = sheet.CreateRow(1);
for (int i = 0; i < table.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
row.Height = 350;
sheet.AutoSizeColumn(i);
}
//处理数据内容
for (int i = 0; i < table.Rows.Count; i++)
{
row = sheet.CreateRow(2 + i);
row.Height = 250;
for (int j = 0; j < table.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
sheet.SetColumnWidth(j, 256 * 15);
}
}
//写入数据流
workBook.Write(fs);
fs.Flush();
fs.Close();
return true;
}
public void TestColorStyleCopy()
{
HSSFWorkbook bookA = new HSSFWorkbook();
HSSFWorkbook bookB = new HSSFWorkbook();
bookA.Workbook.CustomPalette.ClearColors();
bookA.Workbook.CustomPalette.SetColor(0x8, 12, 15, 255); //0x8 is blueish
bookA.Workbook.CustomPalette.SetColor(0x9, 200, 200, 200); //0x9 is light gray
bookB.Workbook.CustomPalette.ClearColors();
bookB.Workbook.CustomPalette.SetColor(0x8, 192, 168, 0); //Throw a color into the destination book so we can see color merge working.
HSSFSheet sheetA = bookA.CreateSheet("Sheet A") as HSSFSheet;
ICell cell = sheetA.CreateRow(0).CreateCell(0);
cell.SetCellValue("I'm a stylish cell!");
IFont myFont = bookA.CreateFont();
myFont.FontName = "Times New Roman";
myFont.IsItalic = true;
myFont.FontHeightInPoints = 12;
myFont.Color = 0x8;
ICellStyle myStyle = bookA.CreateCellStyle();
myStyle.SetFont(myFont);
myStyle.FillForegroundColor = 0x9;
cell.CellStyle = myStyle;
HSSFSheet sheetB = bookB.CreateSheet("BookB Sheet") as HSSFSheet;
ICell beeCell = sheetB.CreateRow(0).CreateCell(0);
ICellStyle styleB = bookB.CreateCellStyle();
styleB.FillForegroundColor = 0x8;
beeCell.CellStyle = styleB;
beeCell.SetCellValue("Hello NPOI");
//Copy the sheet, and make sure the color, style, and font is correct
sheetA.CopyTo(bookB, "Copied Sheet A", true, true);
HSSFSheet theCopy = bookB.GetSheetAt(1) as HSSFSheet;
ICell copiedCell = theCopy.GetRow(0).GetCell(0);
//Check that the fill color got copied
byte[] srcColor = bookA.Workbook.CustomPalette.GetColor(0x9);
byte[] destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.FillForegroundColor);
Assert.IsTrue(srcColor[0]==destColor[0] && srcColor[1]==destColor[1] && srcColor[2]==destColor[2]);
//Check that the font color got copied
srcColor = bookA.Workbook.CustomPalette.GetColor(0x8);
destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.GetFont(bookB).Color);
Assert.IsTrue(srcColor[0] == destColor[0] && srcColor[1] == destColor[1] && srcColor[2] == destColor[2]);
//Check that the fill color of the cell originally in the destination book is still intact
srcColor = bookB.Workbook.CustomPalette.GetColor(0x8);
Assert.IsTrue(srcColor[0] == 192 && srcColor[1] == 168 && srcColor[2] == 0);
//Check that the font made it over okay
Assert.AreEqual(copiedCell.CellStyle.GetFont(bookB).FontName, myFont.FontName);
}
public byte[] Create(List<string> columns, List<List<string>> rows, HttpServerUtilityBase server)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("Sheet1");
// create header style
var headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.BOLD;
var headerStyle = workbook.CreateCellStyle();
headerStyle.FillBackgroundColor = HSSFColor.GREY_40_PERCENT.index;
headerStyle.FillForegroundColor = HSSFColor.GREY_40_PERCENT.index;
headerStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headerStyle.SetFont(headerFont);
// Getting the row... 0 is the first row.
var dataRow = sheet.CreateRow(0);
for (int i = 0; i < columns.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(columns.ElementAt(i));
dataRow.GetCell(i).CellStyle = headerStyle;
}
var rowCount = 0;
foreach (var a in rows) // gets the array that represents a row
{
rowCount++;
dataRow = sheet.CreateRow(rowCount);
for (var i = 0; i < a.Count; i++) // gets the individual cell
{
dataRow.CreateCell(i).SetCellValue(a[i]);
}
}
// Forcing formula recalculation...
sheet.ForceFormulaRecalculation = true;
// adjust column widths
for (int i = 0; i < columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
return ms.ToArray();
}
}
public static ICellStyle CreateHeaderStyle(HSSFWorkbook wb, short foreColor, short backgroundColor)
{
var style = wb.CreateCellStyle();
style.FillForegroundColor = (short)17;
var font = wb.CreateFont();
font.Color = foreColor;
font.FontHeightInPoints = 12;
style.SetFont(font);
style.FillBackgroundColor = 16;
style.FillForegroundColor = backgroundColor;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
return style;
}
/// <summary>
/// 创建Excel标题行
/// </summary>
/// <param name="workbook">工作簿对象</param>
/// <param name="sheet">Excel单页</param>
/// <param name="strHeaderText">标题行文本</param>
/// <param name="dtSource">DataTable数据源</param>
public void CreateRowHeader(HSSFWorkbook workbook, Sheet sheet, int startRowIndex, string strHeaderText, DataTable dtSource)
{
#region 表头及样式
{
Row headerRow = sheet.CreateRow(startRowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headStyle.WrapText = true;
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
int[] arrColWidth = GetColumnWidth(dtSource);
Row headerRow = sheet.CreateRow(startRowIndex+1);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
}
/// <summary>
/// 為資料建立成NPOI 的Workbook和Sheet
/// </summary>
/// <param name="sourceTable">要匯出的內容</param>
/// <param name="workbook">The workbook.</param>
/// <param name="sheet">The sheet.</param>
/// <param name="headerRowIndex">標題欄的index起始位置</param>
/// <param name="rowIndexStart">第一筆實際資料的起始位置</param>
private void CreatWorksheetForSingleDataTable(DataTable sourceTable, ref HSSFWorkbook workbook,
ref ISheet sheet, int headerRowIndex = 0, int rowIndexStart = 1)
{
IRow headerRow = sheet.CreateRow(headerRowIndex);
// Set up the Header Coloumn cell style
var headerCellStyle = workbook.CreateCellStyle();
headerCellStyle.BorderTop = BorderStyle.Thin;
headerCellStyle.BorderBottom = BorderStyle.Thin;
headerCellStyle.Alignment = HorizontalAlignment.Center;
// Set up the Header Coloumn cell font style
var headerCellFontStyle = workbook.CreateFont();
headerCellFontStyle.Boldweight = (short)FontBoldWeight.Bold;
headerCellStyle.SetFont(headerCellFontStyle);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
var headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerCellStyle;
}
// handling value.
int rowIndex = rowIndexStart;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
public void TestWriteSheetFont()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
".xls");
FileStream out1 = new FileStream(filepath,FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color=(NPOI.HSSF.Util.HSSFColor.Red.Index);
fnt.Boldweight= (short)FontBoldWeight.Bold;
cs.SetFont(fnt);
for (short rownum = ( short ) 0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
public XlsModel(string sheetName)
{
SheetName = sheetName;
// Create a new workbook and a sheet named "User Accounts"
_workbook = new HSSFWorkbook();
_sheet = _workbook.CreateSheet(SheetName);
var boldFont = _workbook.CreateFont();
boldFont.FontHeightInPoints = 10;
boldFont.FontName = "Arial";
boldFont.Boldweight = (short) FontBoldWeight.Bold;
_dateStyle = _workbook.CreateCellStyle();
_dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd/mm/yyyy");
_nullStyle = _workbook.CreateCellStyle();
_nullStyle.FillForegroundColor = HSSFColor.Grey40Percent.Index;
_nullStyle.FillPattern = FillPattern.SolidForeground;
_headStyle = _workbook.CreateCellStyle();
_headStyle.SetFont(boldFont);
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
// New files start with 4 built in fonts, and 21 built in styles
Assert.AreEqual(4, wb.NumberOfFonts);
Assert.AreEqual(21, wb.NumCellStyles);
// Create a test font and style, and use them
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.CreateCell(0).CellStyle = (s);
// Should have one more than the default of each
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
public void RenderRTF_WhenCalled_RemoveHtmlTag()
{
var betTypeName = "Baccarat";
_ticketHelper.GetBetTypeNameById(Arg.Any<int>()).Returns(betTypeName);
var workbook = new HSSFWorkbook();
var rtfHelper = new RTFHelper()
{
NegFont = workbook.CreateFont(),
NormalFont = workbook.CreateFont(),
PosFont = workbook.CreateFont(),
NegFontCrossed = workbook.CreateFont(),
NormalFontCrossed = workbook.CreateFont(),
PosFontCrossed = workbook.CreateFont(),
RTFRenderer = new RtfTextRender()
};
_choice.RenderRTF(_ticket, _ticketHelper, null, false, rtfHelper);
Assert.AreEqual(_choice.Template.League.LeagueName.leagueName, betTypeName);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="listColumn"> </param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="defaultColumnWidth">默认列宽度 </param>
public static MemoryStream Export(DataTable dtSource, List<Column> listColumn, string strHeaderText, int defaultColumnWidth)
{
#region 门卫代码
if (listColumn == null) listColumn = new List<Column>();
#endregion
var workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "云学时代";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "云学时代"; //填加xls文件作者信息
si.ApplicationName = strHeaderText; //填加xls文件创建程序信息
si.LastAuthor = "云学时代"; //填加xls文件最后保存者信息
si.Comments = "云学时代"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = "云学时代" + strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 标题样式
ICellStyle titleCellStyle = workbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.BlueGrey.Index;
titleCellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SparseDots;
//titleCellStyle.FillBackgroundColor = HSSFColor.BLUE_GREY.index;
titleCellStyle.FillBackgroundColor = HSSFColor.BlueGrey.Index;
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = 18;
titleFont.Boldweight = 700;
titleFont.FontName = "黑体";
titleCellStyle.SetFont(titleFont);
#endregion
#region 表头样式
ICellStyle headCellStyle = workbook.CreateCellStyle();
headCellStyle.Alignment = HorizontalAlignment.Center;
//headCellStyle.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
headCellStyle.FillForegroundColor = HSSFColor.LightYellow.Index;
//headCellStyle.FillPattern = FillPatternType.SPARSE_DOTS;
headCellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SparseDots;
//headCellStyle.FillBackgroundColor = HSSFColor.LIGHT_YELLOW.index;
headCellStyle.FillBackgroundColor = HSSFColor.LightYellow.Index;
IFont headfont = workbook.CreateFont();
headfont.FontHeightInPoints = 10;
headfont.Boldweight = 700;
headfont.FontName = "宋体";
headCellStyle.SetFont(headfont);
#endregion
#region 单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
IFont cellfont = workbook.CreateFont();
cellfont.FontHeightInPoints = 10;
cellfont.Boldweight = 400;
cellfont.FontName = "宋体";
cellStyle.SetFont(cellfont);
#endregion
#region 日期样式
ICellStyle dateStyle = workbook.CreateCellStyle();
//dateStyle.Alignment=HorizontalAlignment.LEFT;
dateStyle.Alignment = HorizontalAlignment.Left;
IFont dateFont = workbook.CreateFont();
dateFont.FontHeightInPoints = 10;
dateFont.Boldweight = 400;
dateFont.FontName = "宋体";
dateStyle.SetFont(dateFont);
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#endregion
#region 取得列宽
//var arrColWidth = new int[dtSource.Columns.Count];
//foreach (DataColumn item in dtSource.Columns)
//{
// arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).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;
// }
// }
//}
for (int i = 0; i < dtSource.Columns.Count; i++)
{
var firstOrDefault = listColumn.FirstOrDefault(a => a.Key == dtSource.Columns[i].ColumnName);
if (firstOrDefault != null)
{
sheet.SetColumnWidth(i, firstOrDefault.Width);
}
else
{
sheet.SetColumnWidth(i, defaultColumnWidth);
}
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
IRow titleRow = sheet.CreateRow(0);
titleRow.HeightInPoints = 22;
titleRow.CreateCell(0).SetCellValue(strHeaderText);
titleRow.GetCell(0).CellStyle = titleCellStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
#endregion
#region 列头及样式
IRow headerRow = sheet.CreateRow(1);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headCellStyle;
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
newCell.CellStyle = cellStyle; //格式化显示
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
//using (var ms = new MemoryStream())
//{
// workbook.Write(ms);
// ms.Flush();
// ms.Position = 0;
// return ms;
//}
var ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
public Boolean Format()
{
Boolean ok = false;
try
{
Writer.WriteLine("Start Format");
// Open Workbook
FileStream stream = new FileStream(FileName, FileMode.Open, FileAccess.ReadWrite);
IWorkbook wb = new HSSFWorkbook(stream);
stream.Close();
// Get Sheet
ISheet ws;
if (SheetName == null)
{
ws = wb.GetSheetAt(0);
}
else
{
ws = wb.GetSheet(SheetName);
}
// Style Header Row
Writer.WriteLine(" Style Header Row");
IFont headerFont = wb.CreateFont();
headerFont.FontName = "Arial";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerFont.FontHeightInPoints = 10;
ICellStyle headerStyle = wb.CreateCellStyle();
headerStyle.SetFont(headerFont);
IRow headerRow = ws.GetRow(0);
foreach (ICell cell in headerRow)
{
cell.CellStyle = headerStyle;
}
// Style Data Rows
Writer.WriteLine(" Style Data Rows");
IFont dataFont = wb.CreateFont();
dataFont.FontName = "Arial";
dataFont.Boldweight = (short)FontBoldWeight.Normal;
dataFont.FontHeightInPoints = 10;
//ICellStyle dataStyle = wb.CreateCellStyle();
//dataStyle.SetFont(dataFont);
ICellStyle dataStyle;
IRow dataRow;
for (Int32 i = 1; i < RowCount; i++)
{
dataRow = ws.GetRow(i);
foreach (ICell dataCell in dataRow)
{
dataStyle = dataCell.CellStyle;
dataStyle.SetFont(dataFont);
dataCell.CellStyle = dataStyle;
}
}
Writer.WriteLine(" AutoSize Columns");
// AutoSize Columns
for (Int32 i = 0; i < ColumnCount; i++)
{
ws.AutoSizeColumn(i);
ws.SetColumnWidth(i, ws.GetColumnWidth(i) + 384); // 256 = 1 char width
}
Writer.WriteLine(" Save and Close Workbook");
// Save and Close Workbook
stream = new FileStream(FileName, FileMode.OpenOrCreate);
wb.Write(stream);
stream.Close();
ok = true;
Writer.WriteLine("End Format");
}
catch (Exception xcp)
{
Writer.WriteLine(this.GetType().ToString());
Writer.WriteLine(xcp);
throw xcp;
}
return ok;
}
public void RenderRTF_SubTicket_RemoveHtmlTag()
{
//// Arrange
_ticket.BetTypeId = 1801;
_ticket.TransDesc = "round=DSP*tableCode=va21*game=BAC*type=1*val=";
var workbook = new HSSFWorkbook();
var rtfHelper = new RTFHelper()
{
NegFont = workbook.CreateFont(),
NormalFont = workbook.CreateFont(),
PosFont = workbook.CreateFont(),
NegFontCrossed = workbook.CreateFont(),
NormalFontCrossed = workbook.CreateFont(),
PosFontCrossed = workbook.CreateFont(),
RTFRenderer = new RtfTextRender()
};
//// Act
_choice.RenderRTF(_ticket, _ticketHelper, null, false, rtfHelper);
//// Assert
Assert.IsTrue(!_choice.Template.League.LeagueName.leagueName.Contains("<span class='ag-casino-bet-type'>"));
}
private void exportarDataExecl(eInterfaceContableVC e)
{
bInterfaceContableVC bc = new bInterfaceContableVC();
DataTable dtlist = bc.GetSelectContableExport(e);
String filename = "Registro Contable " + ddl_contrato_i.SelectedItem.Text + "-"+ddl_tip_operacion_i.SelectedItem.Text +System.DateTime.Today.ToShortDateString()+".xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
var output = new MemoryStream();
var writer = new StreamWriter(output);
HSSFWorkbook xssfworkbook = new HSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("INTERFACE "+ddl_tip_operacion_i.SelectedItem.Text.ToUpper());
IFont titleFont = xssfworkbook.CreateFont();
titleFont.FontName = "Calibri";
titleFont.Boldweight = (short)FontBoldWeight.Bold;
titleFont.Color = (IndexedColors.Black.Index);
titleFont.FontHeightInPoints = 11;
ICellStyle styleCabecera = xssfworkbook.CreateCellStyle();
styleCabecera.Alignment = HorizontalAlignment.Center;
styleCabecera.VerticalAlignment = VerticalAlignment.Center;
styleCabecera.SetFont(titleFont);
styleCabecera.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleCabecera.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleCabecera.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleCabecera.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
for (int cl1 = 0; cl1 < 11; cl1++)
{
sheet.SetColumnWidth(cl1, 150 * 25);
}
for (int cl = 11; cl <= 21; cl++)
{
sheet.SetColumnWidth(cl, 300 * 25);
}
IRow dataHeader = sheet.CreateRow(0);
ICell dataCellH;
for (int h = 0; h < dtlist.Columns.Count; h++)
{
dataCellH = dataHeader.CreateCell(h);
dataCellH.SetCellValue(dtlist.Columns[h].ToString());
dataCellH.CellStyle = styleCabecera;
dataCellH.CellStyle.WrapText = true;
}
for (int r = 0; r < dtlist.Rows.Count; r++)
{
IRow dataBody = sheet.CreateRow(1 + r);
for (int c = 0; c < dtlist.Columns.Count; c++)
{
dataBody.CreateCell(c, CellType.String).SetCellValue(dtlist.Rows[r][c].ToString());
}
}
xssfworkbook.Write(output);
writer.Flush();
dtlist.Rows.Clear();
dtlist.Columns.Clear();
dtlist.Clear();
Response.BinaryWrite(output.GetBuffer());
Response.End();
}
/// <summary>
/// 利用office组件导出Excel表格文件
/// </summary>
//public static void ExportExcelFile(DataTable ExcelTable)
//{
// try
// {
// string SaveExcelName = string.Empty;//保存的Excel文件名称
// SaveFileDialog SFDialog = new SaveFileDialog();
// SFDialog.DefaultExt = "xls";
// SFDialog.Filter = "Excel文件(*.xls)|*.xls";
// SFDialog.ShowDialog();
// SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
// if (SaveExcelName.IndexOf(":") < 0) return;
// Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序
// object missing = System.Reflection.Missing.Value;
// if (XlsApp == null)
// {
// MessageBoxEx.Show("无法创建Excel表格文件，您的电脑可能未安装Excel软件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
// }
// else
// {
// Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像
// Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板
// Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1;
// Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格
// int TotalCount = ExcelTable.Rows.Count;
// //int rowRead = 0;//读取行数
// //float PercentRead = 0;//导入百分比
// //写入字段名
// for (int i = 0; i < ExcelTable.Columns.Count; i++)
// {
// WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称
// Ran = (Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1, i + 1];//列名称写入单元格
// Ran.Interior.ColorIndex = 15;
// Ran.Font.Bold = true;//标题加粗
// }
// //ProgressBarMsg ProgBarMsg = new ProgressBarMsg();
// //ProgBarMsg.MaxNum = TotalCount;//获取总记录行项
// //ProgBarMsg.ShowDialog();//显示进度条
// //写字段值
// for (int j = 0; j < ExcelTable.Rows.Count; j++)
// {
// for (int k = 0; k < ExcelTable.Columns.Count; k++)
// {
// WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值
// }
// //rowRead++;
// //PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比
// //ProgressBarMsg.PercentMsg = rowRead;
// //Thread.Sleep(200);
// Application.DoEvents();//处理当前在消息队列中所有windows消息
// }
// WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing, missing);
// Ran = WkSheet.get_Range((object)WkSheet.Cells[2, 1], (object)WkSheet.Cells[ExcelTable.Rows.Count + 1, ExcelTable.Columns.Count]);//给工作表指定区域
// //设置Excel表格边框样式
// Ran.BorderAround2(missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
// Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, missing, missing);
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度
// if (ExcelTable.Columns.Count > 1)
// {//设置垂直表格颜色索引
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
// }
// WkBk.Save();//保存Excel表数据
// //关闭表格对像，并退出应用程序域
// WkBk.Close(missing, missing, missing);
// XlsApp.Quit();
// XlsApp = null;
// GC.Collect();//强制关闭
// MessageBoxEx.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// }
// }
// catch (Exception ex)
// {
// MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
// }
//}
/// <summary>
/// 设置导出Excel标题样式
/// </summary>
/// <param name="HeaderRow">标题行</param>
/// <param name="WkBk">工作表</param>
/// <param name="ColNum">列序号</param>
private static void SetXlsHeaderStyle(HSSFRow HeaderRow, HSSFWorkbook WkBk, int ColNum)
{
try
{
HSSFCellStyle HeaderStyle = (HSSFCellStyle)WkBk.CreateCellStyle();//创建Excel表格标题样式
HSSFFont fontStyle = (HSSFFont)WkBk.CreateFont();//创建Excel表格字体样式
HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//设置居中
HeaderStyle.FillForegroundColor = HSSFColor.Lime.Index;//设置背景色
HeaderStyle.FillPattern = FillPattern.SolidForeground;//设置填充样式
HeaderStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0");//设置数据格式
fontStyle.FontHeightInPoints = 11;
fontStyle.Boldweight = 700;
HeaderStyle.SetFont(fontStyle);
HeaderRow.GetCell(ColNum).CellStyle = (ICellStyle)HeaderStyle;//设置列样式
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
public void TestOptimiseFonts()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Add 6 fonts, some duplicates
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
IFont f3 = wb.CreateFont();
f3.FontHeight = ((short)33);
f3.FontName = ("Unique");
IFont f4 = wb.CreateFont();
f4.FontHeight = ((short)11);
f4.FontName = ("Testing");
IFont f5 = wb.CreateFont();
f5.FontHeight = ((short)22);
f5.FontName = ("Also Testing");
IFont f6 = wb.CreateFont();
f6.FontHeight = ((short)66);
f6.FontName = ("Also Unique");
// Use all three of the four in cell styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
Assert.AreEqual(5, cs1.FontIndex);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f4);
Assert.AreEqual(8, cs2.FontIndex);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f5);
Assert.AreEqual(9, cs3.FontIndex);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f6);
Assert.AreEqual(10, cs4.FontIndex);
Assert.AreEqual(25, wb.NumCellStyles);
// And three in rich text
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
HSSFRichTextString rtr1 = new HSSFRichTextString("Test");
rtr1.ApplyFont(0, 2, f1);
rtr1.ApplyFont(3, 4, f2);
r.CreateCell(0).SetCellValue(rtr1);
HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");
rtr2.ApplyFont(0, 2, f3);
rtr2.ApplyFont(3, 5, f5);
rtr2.ApplyFont(6, 8, f6);
r.CreateCell(1).SetCellValue(rtr2);
// Check what we have now
Assert.AreEqual(10, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Optimise
HSSFOptimiser.OptimiseFonts(wb);
// Check font count
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Check font use in cell styles
Assert.AreEqual(5, cs1.FontIndex);
Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
Assert.AreEqual(8, cs4.FontIndex); // two have gone
// And in rich text
// RTR 1 had f1 and f2, unchanged
Assert.AreEqual(5, r.GetCell(0).RichStringCellValue.GetFontAtIndex(0));
Assert.AreEqual(5, r.GetCell(0).RichStringCellValue.GetFontAtIndex(1));
Assert.AreEqual(6, r.GetCell(0).RichStringCellValue.GetFontAtIndex(3));
Assert.AreEqual(6, r.GetCell(0).RichStringCellValue.GetFontAtIndex(4));
// RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
Assert.AreEqual(7, r.GetCell(1).RichStringCellValue.GetFontAtIndex(0));
Assert.AreEqual(7, r.GetCell(1).RichStringCellValue.GetFontAtIndex(1));
Assert.AreEqual(6, r.GetCell(1).RichStringCellValue.GetFontAtIndex(3));
Assert.AreEqual(6, r.GetCell(1).RichStringCellValue.GetFontAtIndex(4));
Assert.AreEqual(8, r.GetCell(1).RichStringCellValue.GetFontAtIndex(6));
Assert.AreEqual(8, r.GetCell(1).RichStringCellValue.GetFontAtIndex(7));
}
public static HSSFWorkbook Export(DataTable dt, string[] headerList, string[] headercode)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = true;
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Double;
style.BorderLeft = BorderStyle.Double;
style.BorderRight = BorderStyle.Double;
style.BorderTop = BorderStyle.Double;
style.BottomBorderColor = HSSFColor.Grey50Percent.Index;
style.LeftBorderColor = HSSFColor.Grey50Percent.Index;
style.RightBorderColor = HSSFColor.Grey50Percent.Index;
style.TopBorderColor = HSSFColor.Grey50Percent.Index;
IFont font = book.CreateFont();
font.FontHeightInPoints = 14;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "標楷體";
font.Color = HSSFColor.Black.Index;
style.SetFont(font);//HEAD 样式
//貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 80 * 5;
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Left;
cellstyle.VerticalAlignment = VerticalAlignment.Top;
cellstyle.WrapText = true;
for (int i = 0; i < headerList.Length; i++)
{
row1.CreateCell(i).SetCellValue(headerList[i]);
row1.GetCell(i).CellStyle = style;
if (i == 0)
{
sheet1.SetColumnWidth(i, 22 * 256);
}
if (i > 0)
{
sheet1.SetColumnWidth(i, 30 * 256);
}
}
//定义第二个工作簿防止内容溢出报错问题
NPOI.SS.UserModel.ISheet sheet2 = null;
if (dt.Rows.Count > 65535)
{
sheet2 = book.CreateSheet("Sheet2");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row11 = sheet2.CreateRow(0);
row11.Height = 80 * 5;
ICellStyle cellstyle1 = book.CreateCellStyle();
cellstyle1.Alignment = HorizontalAlignment.Left;
cellstyle1.VerticalAlignment = VerticalAlignment.Top;
cellstyle1.WrapText = true;
for (int i = 0; i < headerList.Length; i++)
{
row11.CreateCell(i).SetCellValue(headerList[i]);
row11.GetCell(i).CellStyle = style;
if (i == 0)
{
sheet2.SetColumnWidth(i, 22 * 256);
}
if (i > 0)
{
sheet2.SetColumnWidth(i, 30 * 256);
}
}
}
int k = 0;
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i > 65534)
{
NPOI.SS.UserModel.IRow rowtemp = sheet2.CreateRow(k);
rowtemp.HeightInPoints = 65;
for (int j = 0; j < headerList.Length; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][headercode[j]].ToString());
rowtemp.GetCell(j).CellStyle = cellstyle;
}
k++;
}
else
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.HeightInPoints = 65;
for (int j = 0; j < headerList.Length; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][headercode[j]].ToString());
rowtemp.GetCell(j).CellStyle = cellstyle;
}
}
}
return(book);
}
/// <summary>
/// 根据模板创建新的文件
/// </summary>
/// <param name="lst"></param>
private static void CreatReport(List <Model> lst)
{
#region 加载xls文件
//模板文件路径
string path = @"C:\Users\1\Desktop\日报\实收款项明细表.xls";
FileStream fs_modle;
using (fs_modle = new FileStream(path, FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook workbook_model = new NPOI.HSSF.UserModel.HSSFWorkbook(fs_modle);
// fs_modle.Close();
ISheet sheet_model = workbook_model.GetSheetAt(0);
IRow row_hj = sheet_model.GetRow(7);//合计行
//设置单元格时
ICellStyle style = workbook_model.CreateCellStyle();
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.WrapText = true;
style.DataFormat = 4;
style.VerticalAlignment = VerticalAlignment.Center;
IRow r;
for (int i = 6; i < lst.Count + 6; i++)
{
r = sheet_model.CreateRow(i); //创建一新行
r.HeightInPoints = 25; //设置行高
for (int J = 0; J < 14; J++)
{
switch (J)
{
case 0:
r.CreateCell(J).SetCellValue(lst[i - 6].序号);
break;
case 1:
r.CreateCell(J).SetCellValue(lst[i - 6].项目名称);
break;
case 2:
r.CreateCell(J).SetCellValue(lst[i - 6].楼栋名称);
break;
case 3:
r.CreateCell(J).SetCellValue(lst[i - 6].房号);
break;
case 4:
r.CreateCell(J).SetCellValue(lst[i - 6].客户名称);
break;
case 5:
r.CreateCell(J).SetCellValue(lst[i - 6].收款日期.ToString());
break;
case 6:
r.CreateCell(J).SetCellValue(lst[i - 6].票据类型);
break;
case 7:
r.CreateCell(J).SetCellValue(lst[i - 6].票据编号);
break;
case 8:
r.CreateCell(J).SetCellValue(lst[i - 6].款项类型);
break;
case 9:
r.CreateCell(J).SetCellValue(lst[i - 6].款项名称);
break;
case 10:
r.CreateCell(J).SetCellValue(lst[i - 6].金额);
break;
case 11:
r.CreateCell(J).SetCellValue(lst[i - 6].支付方式);
break;
case 12:
r.CreateCell(J).SetCellValue(lst[i - 6].银付方式);
break;
case 13:
r.CreateCell(J).SetCellValue(lst[i - 6].摘要);
break;
default:
break;
}
}
var enu = r.GetEnumerator();
while (enu.MoveNext())
{
enu.Current.CellStyle = style;//设置风格
}
}
//创建合计行
IRow r1 = sheet_model.CreateRow(lst.Count + 6);
r1.HeightInPoints = 25;//设置行高
ICell cell;
IFont font = workbook_model.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
for (int i = 0; i < 14; i++)
{
cell = r1.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue("--");
cell.CellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle.SetFont(font);
}
double JE = lst.Sum(t => t.金额);
r1.GetCell(0).SetCellValue(" ");
r1.GetCell(1).SetCellValue("合计");
r1.GetCell(10).SetCellValue(JE);
//统计周期和统计时间
string ZQ = $"统计周期：{DateTime.Now.ToShortDateString()} 至 {DateTime.Now.ToShortDateString()}";
string DT = $"制表日期：{DateTime.Now.ToShortDateString()}";
sheet_model.GetRow(3).GetCell(0).SetCellValue(ZQ);
sheet_model.GetRow(4).GetCell(0).SetCellValue(DT);
string newFileFullPath = $@"{ Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)}";
string filePath = $"{newFileFullPath}\\日报\\日报表{DateTime.Now.ToShortDateString()}.xls";
try
{
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook_model.Write(fs);
}
// fs.Dispose();
Console.WriteLine("文件创建成功!");
// workbook_model.Close();
lst.Clear();
}
catch (Exception)
{
throw;
}
finally
{
Console.WriteLine(File.Exists(filePath));
Process.Start(@"C:\Program Files (x86)\Kingsoft\WPS Office\11.1.0.9912\office6\wps.exe", filePath);
}
}
#endregion
}
public virtual ActionResult Export(int page, string orderBy, string filter)
{
var viewmodel = new List<ProjectView>();
var datasource = TempData["datasource"].ToString();
var userid = TempData["userid"].ToString();
switch (datasource)
{
case "_GetAllProjects": GetAllProjects().ForEach(x => viewmodel.Add(new ProjectView(x))); break;
case "_Get_HaveDone_Projects": GetHaveDoneProject(userid).ForEach(x => viewmodel.Add(new ProjectView(x))); break;
case "_Get_Todo_Projects": GetTodoProject(userid).ForEach(x => viewmodel.Add(new ProjectView(x))); break;
case "_GetFinished": GetFinishedProject().ForEach(x => viewmodel.Add(new ProjectView(x))); break;
}
//Get the data representing the current grid state - page, sort and filter
var count = viewmodel.Count;
GridModel model = viewmodel.AsQueryable().ToGridModel(page, count, orderBy, string.Empty, filter);
var prjs = model.Data.Cast<ProjectView>();
//Create new Excel workbook
var workbook = new HSSFWorkbook();
//Create new Excel sheet
var sheet = workbook.CreateSheet("大型代工项目");
//(Optional) set the width of the columns
sheet.SetColumnWidth(0, 8 * 256);
sheet.SetColumnWidth(1, 40 * 256);
sheet.SetColumnWidth(2, 40 * 256);
sheet.SetColumnWidth(3, 10 * 256);
sheet.SetColumnWidth(4, 12 * 256);
sheet.SetColumnWidth(5, 20 * 256);
var captionRow = sheet.CreateRow(0);
HSSFCell capcell = captionRow.CreateCell(0);
capcell.SetCellValue("上海电力公司金山供电公司大型代工规划项目报表");
HSSFCellStyle style = workbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
capcell.CellStyle = style;
sheet.AddMergedRegion(new Region(0, 0, 0, 5));
//Create a header row
var headerRow = sheet.CreateRow(1);
HSSFFont headfont = workbook.CreateFont();
headfont.FontHeightInPoints = 14;
headfont.Boldweight = 700;
HSSFCellStyle headstyle = workbook.CreateCellStyle();
headstyle.Alignment = HSSFCellStyle.ALIGN_GENERAL;
headstyle.SetFont(headfont);
headstyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
headstyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
headstyle.BorderRight = HSSFCellStyle.BORDER_THIN;
headstyle.BorderTop = HSSFCellStyle.BORDER_THIN;
//Set the column names in the header row
var cell = headerRow.CreateCell(0);
cell.SetCellValue("序号");
cell.CellStyle = headstyle;
cell = headerRow.CreateCell(1);
cell.SetCellValue("代工项目名称");
cell.CellStyle = headstyle;
cell = headerRow.CreateCell(2);
cell.SetCellValue("erp账号");
cell.CellStyle = headstyle;
cell = headerRow.CreateCell(3);
cell.SetCellValue("联系人");
cell.CellStyle = headstyle;
cell = headerRow.CreateCell(4);
cell.SetCellValue("当前流程节点");
cell.CellStyle = headstyle;
//(Optional) freeze the header row so it is not scrolled
sheet.CreateFreezePane(0, 2, 0, 2);
int rowNumber = 2;
//Populate the sheet with values from the grid data
HSSFFont cellfont = workbook.CreateFont();
cellfont.FontHeightInPoints = 12;
cellfont.Boldweight = 20;
HSSFCellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.Alignment = HSSFCellStyle.ALIGN_GENERAL;
cellstyle.SetFont(cellfont);
cellstyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
cellstyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
cellstyle.BorderRight = HSSFCellStyle.BORDER_THIN;
cellstyle.BorderTop = HSSFCellStyle.BORDER_THIN;
foreach (ProjectView layout in prjs)
{
//Create a new row
var row = sheet.CreateRow(rowNumber++);
//Set values for the cells
var datacell = row.CreateCell(0);
datacell.SetCellValue(rowNumber - 2);
datacell.CellStyle = cellstyle;
datacell = row.CreateCell(1);
datacell.SetCellValue(layout.ProjectName);
datacell.CellStyle = cellstyle;
datacell = row.CreateCell(2);
datacell.SetCellValue(layout.ErpAccount);
datacell.CellStyle = cellstyle;
datacell = row.CreateCell(3);
datacell.SetCellValue(layout.ContactPerson);
datacell.CellStyle = cellstyle;
datacell = row.CreateCell(4);
datacell.SetCellValue(layout.ProcessingStep);
datacell.CellStyle = cellstyle;
}
//Write the workbook to a memory stream
var output = new MemoryStream();
workbook.Write(output);
TempData["datasource"] = datasource;
TempData["userid"] = userid;
//Return the result to the end user
return File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
Url.Encode("大型项目输出.xls")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
/// <summary>DataTable导出到Excel的MemoryStream</summary>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string[] str)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
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)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontName = str[5]; //[5]
font.FontHeightInPoints = Convert.ToInt16(str[0]); //[0]
font.Boldweight = Convert.ToInt16(str[1]); //[1]
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontName = str[6]; //[6]
font.FontHeightInPoints = Convert.ToInt16(str[2]); //[2]
font.Boldweight = Convert.ToInt16(str[3]); //[3]
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) * Convert.ToInt32(str[4])); //[4]
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
string result = drValue;
newCell.SetCellValue(result);
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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet;
//workbook.Dispose();
return ms;
}
/// <summary>
/// 导出问卷数据
/// </summary>
/// <param name="list"></param>
public string CreateExcel()
{
list = (from r in _db.tbl_record
join emp in _db.tbl_employeeMK on new { EId = r.eId.Value } equals new { EId = emp.eId }
join ans in
(
(from a in _db.tbl_answer
group a by new
{
a.rId
} into g
select new
{
arid = g.Key.rId.Value,
answer1 = g.Max(p => (p.qId == 1 ? p.aContent : null)),
answer2 = g.Max(p => (p.qId == 2 ? p.aContent : null)),
answer3 = g.Max(p => (p.qId == 3 ? p.aContent : null)),
answer4 = g.Max(p => (p.qId == 4 ? p.aContent : null)),
answer5 = g.Max(p => (p.qId == 5 ? p.aContent : null)),
answer6 = g.Max(p => (p.qId == 6 ? p.aContent : null)),
answer7 = g.Max(p => (p.qId == 7 ? p.aContent : null)),
answer8 = g.Max(p => (p.qId == 8 ? p.aContent : null)),
answer9 = g.Max(p => (p.qId == 9 ? p.aContent : null)),
answer10 = g.Max(p => (p.qId == 10 ? p.aContent : null)),
answer11 = g.Max(p => (p.qId == 11 ? p.aContent : null)),
answer12 = g.Max(p => (p.qId == 12 ? p.aContent : null))
}))on new { RId = r.rId } equals new { RId = ans.arid }
orderby
r.rId
select new RecordModel
{
AnswerId = r.rId,
AnswerName = emp.eName,
AnswerNumber = emp.eNumber,
AnswerPhone = emp.ePhone,
AnswerDuration = r.rDuration.Value,
AnswerTime = r.rEndtime.Value,
AnswerAnswer1 = ans.answer1,
AnswerAnswer2 = ans.answer2,
AnswerAnswer3 = ans.answer3,
AnswerAnswer4 = ans.answer4,
AnswerAnswer5 = ans.answer5,
AnswerAnswer6 = ans.answer6,
AnswerAnswer7 = ans.answer7,
AnswerAnswer8 = ans.answer8,
AnswerAnswer9 = ans.answer9,
AnswerAnswer10 = ans.answer10,
AnswerAnswer11 = ans.answer11,
AnswerAnswer12 = ans.answer12,
}
).ToList();
if (list != null && list.Count > 0) //
{
try
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("问卷数据下载");
//
NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0);
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 18 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 25 * 256);
ICellStyle style = book.CreateCellStyle();
HSSFColor color = new HSSFColor.BLACK();
FillPatternType fillPattern = FillPatternType.SOLID_FOREGROUND;//灰色背景
HSSFColor backGround = new HSSFColor.GREY_25_PERCENT();
IFont font = ExportData.GetFontStyle(book, "宋体", color, 11);
//设置单元格的样式：水平对齐居中
style = ExportData.GetCellStyle(book, font, fillPattern, backGround, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
ICell cell1 = rowHeader.CreateCell(0);
cell1.SetCellValue("姓名");
//将新的样式赋给单元格
cell1.CellStyle = style;
ICell cell2 = rowHeader.CreateCell(1);
cell2.SetCellValue("编号");
cell2.CellStyle = style;
ICell cell3 = rowHeader.CreateCell(2);
cell3.SetCellValue("手机号码");
cell3.CellStyle = style;
ICell cell4 = rowHeader.CreateCell(3);
cell4.SetCellValue("第1题");
cell4.CellStyle = style;
ICell cell5 = rowHeader.CreateCell(4);
cell5.SetCellValue("第2题");
cell5.CellStyle = style;
ICell cell6 = rowHeader.CreateCell(5);
cell6.SetCellValue("第3题");
cell6.CellStyle = style;
ICell cell7 = rowHeader.CreateCell(6);
cell7.SetCellValue("第4题");
cell7.CellStyle = style;
ICell cell8 = rowHeader.CreateCell(7);
cell8.SetCellValue("第5题");
cell8.CellStyle = style;
ICell cell9 = rowHeader.CreateCell(8);
cell9.SetCellValue("第6题");
cell9.CellStyle = style;
ICell cell10 = rowHeader.CreateCell(9);
cell10.SetCellValue("第7题");
cell10.CellStyle = style;
ICell cell11 = rowHeader.CreateCell(10);
cell11.SetCellValue("第8题");
cell11.CellStyle = style;
ICell cell12 = rowHeader.CreateCell(11);
cell12.SetCellValue("第9题");
cell12.CellStyle = style;
ICell cell13 = rowHeader.CreateCell(12);
cell13.SetCellValue("第10题");
cell13.CellStyle = style;
ICell cell14 = rowHeader.CreateCell(13);
cell14.SetCellValue("第11题");
cell14.CellStyle = style;
ICell cell15 = rowHeader.CreateCell(14);
cell15.SetCellValue("第12题");
cell15.CellStyle = style;
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont rowfont = book.CreateFont();
rowfont.FontName = "宋体";
rowfont.FontHeightInPoints = (short)11;
rowfont.Color = color.GetIndex();
cellStyle.SetFont(rowfont);
//
NPOI.SS.UserModel.IRow row = null;
for (int i = 0; i < list.Count; i++)
{
RecordModel model = list[i];
row = sheet.CreateRow(i + 1);
ICell rowcell1 = row.CreateCell(0);
rowcell1.SetCellValue(model.AnswerName);
rowcell1.CellStyle = cellStyle;
ICell rowcell2 = row.CreateCell(1);
rowcell2.SetCellValue(model.AnswerNumber);
rowcell2.CellStyle = cellStyle;
ICell rowcell3 = row.CreateCell(2);
rowcell3.SetCellValue(model.AnswerPhone);
rowcell3.CellStyle = cellStyle;
ICell rowcell4 = row.CreateCell(3);
rowcell4.SetCellValue(model.AnswerAnswer1);
rowcell4.CellStyle = cellStyle;
ICell rowcell5 = row.CreateCell(4);
rowcell5.SetCellValue(model.AnswerAnswer2);
rowcell5.CellStyle = cellStyle;
ICell rowcell6 = row.CreateCell(5);
rowcell6.SetCellValue(model.AnswerAnswer3);
rowcell6.CellStyle = cellStyle;
ICell rowcell7 = row.CreateCell(6);
rowcell7.SetCellValue(model.AnswerAnswer4);
rowcell7.CellStyle = cellStyle;
ICell rowcell8 = row.CreateCell(7);
rowcell8.SetCellValue(model.AnswerAnswer5);
rowcell8.CellStyle = cellStyle;
ICell rowcell9 = row.CreateCell(8);
rowcell9.SetCellValue(model.AnswerAnswer6);
rowcell9.CellStyle = cellStyle;
ICell rowcell10 = row.CreateCell(9);
rowcell10.SetCellValue(model.AnswerAnswer7);
rowcell10.CellStyle = cellStyle;
ICell rowcell11 = row.CreateCell(10);
rowcell11.SetCellValue(model.AnswerAnswer8);
rowcell11.CellStyle = cellStyle;
ICell rowcell12 = row.CreateCell(11);
rowcell12.SetCellValue(model.AnswerAnswer9);
rowcell12.CellStyle = cellStyle;
ICell rowcell13 = row.CreateCell(12);
rowcell13.SetCellValue(model.AnswerAnswer10);
rowcell13.CellStyle = cellStyle;
ICell rowcell14 = row.CreateCell(13);
rowcell14.SetCellValue(model.AnswerAnswer11);
rowcell14.CellStyle = cellStyle;
ICell rowcell15 = row.CreateCell(14);
rowcell15.SetCellValue(model.AnswerAnswer12);
rowcell15.CellStyle = cellStyle;
}
//
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
//string path = @"C:\Users\DNS\Desktop\导出参会人";
//string path = System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\数据导出Excel";
// path = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\数据导出Excel";
string path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Data\\";
DirectoryInfo excelDir = new DirectoryInfo(path);
//如果路径不存在就创建
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = "mk_q" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//string strDate = DateTime.Now.ToString("yyyy-MM-dd-HH ");
path = excelDir.FullName + fileName;
if (File.Exists(path))
{
File.Delete(path);
}
using (Stream localFile = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
//ms.ToArray()转换为字节数组就是想要的图片源字节
localFile.Write(ms.ToArray(), 0, (int)ms.Length);
}
book = null;
ms.Close();
ms.Dispose();
//
string webAddress = System.Configuration.ConfigurationManager.AppSettings["mkDataPath"].ToString();
return(webAddress + fileName);
}
}
catch (Exception)
{
//Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导出格式错误');</script>");
return("Error1");
}
}
else
{
return("Error2");
}
}
public /*ActionResult*/ FileContentResult About()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义表头
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)54, (byte)96, (byte)146);
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
///（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Pink.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
//设置黑色边框
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.Color = HSSFColor.White.Index;
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 11;
cellStyle.SetFont(cellStyleFont);
//设置单元格宽度
sheet.SetColumnWidth(0, 4000); //核销人
sheet.SetColumnWidth(1, 4000); //账号
sheet.SetColumnWidth(2, 5000); //核销时间
sheet.SetColumnWidth(3, 3500);
sheet.SetColumnWidth(4, 3500);
sheet.SetColumnWidth(5, 4000);
sheet.SetColumnWidth(6, 7000);
sheet.SetColumnWidth(7, 3500); //全面值
sheet.SetColumnWidth(8, 4500); //使用门槛
sheet.SetColumnWidth(9, 5000); //核销门店
sheet.SetColumnWidth(10, 10000); //appid
sheet.SetColumnWidth(11, 3500); //核销地址
sheet.SetColumnWidth(12, 3500); //核销id
// 第一行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 350;
row.CreateCell(0).SetCellValue("核销人");
row.CreateCell(1).SetCellValue("核销人账号");
row.CreateCell(2).SetCellValue("核销时间");
row.CreateCell(3).SetCellValue("订单金额");
row.CreateCell(4).SetCellValue("核销金额");
row.CreateCell(5).SetCellValue("核销券码");
row.CreateCell(6).SetCellValue("有效期");
row.CreateCell(7).SetCellValue("券面值");
row.CreateCell(8).SetCellValue("使用门槛");
row.CreateCell(9).SetCellValue("核销门店");
row.CreateCell(10).SetCellValue("核销门店APPID");
row.CreateCell(11).SetCellValue("核销地址");
row.CreateCell(12).SetCellValue("核销ID");
for (int i = 0; i < 13; i++)
{
//设置高度
row.Height = 400;
row.Cells[i].CellStyle = cellStyle;
}
#endregion
//合并单元格
/**
* 第一个参数：从第几行开始合并
* 第二个参数：到第几行结束合并
* 第三个参数：从第几列开始合并
* 第四个参数：到第几列结束合并
**/
CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
sheet.AddMergedRegion(region);
HSSFCellStyle cellStyleItem = (HSSFCellStyle)book.CreateCellStyle();
cellStyleItem.Alignment = HorizontalAlignment.Center; //水平居中
cellStyleItem.VerticalAlignment = VerticalAlignment.Center; //垂直居中
HSSFCellStyle cellStyleItem2 = (HSSFCellStyle)book.CreateCellStyle();
cellStyleItem2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
for (int i = 1; i < 100; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
row2.CreateCell(0).SetCellValue("收银员小吴");
row2.CreateCell(1).SetCellValue("13587639864");
row2.CreateCell(2).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd HH:mm"));
row2.CreateCell(3).SetCellValue("1000");
row2.CreateCell(4).SetCellValue("180");
row2.CreateCell(5).SetCellValue("DJD84K6JFU");
row2.CreateCell(6).SetCellValue($"{DateTime.Now.ToString("yyyy-MM-dd")} ~ {DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}");
row2.CreateCell(7).SetCellValue("60");
row2.CreateCell(8).SetCellValue("满100元可用");
row2.CreateCell(9).SetCellValue("世纪新园中餐厅");
row2.CreateCell(10).SetCellValue(System.Guid.NewGuid().ToString());
var ignoreList = new List <int> {
5, 8, 9, 10
};
for (int j = 0; j < 11; j++)
{
if (ignoreList.Contains(j))
{
continue;
}
row2.Cells[j].CellStyle = cellStyleItem;
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//Response.ContentType = "application/octet-stream";
//Response.AppendHeader("content-disposition", $"attachment;filename={DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xls;");
//Response.BinaryWrite(ms.ToArray());
//book = null;
//ms.Close();
//ms.Dispose();
//return null;
return(File(ms.ToArray(), "application/octet-stream", string.Format("冷链食品处理详情{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
public static HSSFWorkbook Export <T>(List <T> Objs, string[] headerList)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true;
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Double;
style.BorderLeft = BorderStyle.Double;
style.BorderRight = BorderStyle.Double;
style.BorderTop = BorderStyle.Double;
style.BottomBorderColor = HSSFColor.Grey50Percent.Index;
style.LeftBorderColor = HSSFColor.Grey50Percent.Index;
style.RightBorderColor = HSSFColor.Grey50Percent.Index;
style.TopBorderColor = HSSFColor.Grey50Percent.Index;
IFont font = book.CreateFont();
font.FontHeightInPoints = 14;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "標楷體";
font.Color = HSSFColor.White.Index;
style.SetFont(font);//HEAD 样式
//row.CreateCell(0).SetCellValue(getString(syscomment, "Company") + "\n" + getString(syscomment, "Report"));
//貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 80 * 5;
for (int i = 0; i < headerList.Length; i++)
{
row1.CreateCell(i).SetCellValue(headerList[i]);
row1.GetCell(i).CellStyle = style;
if (i == 0)
{
sheet1.SetColumnWidth(i, 22 * 256);
}
if (i > 0)
{
sheet1.SetColumnWidth(i, 30 * 256);
}
}
int m = 0;
//将数据逐步写入sheet1各个行
foreach (T model in Objs)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(m + 1);
int j = 0;
foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
{
rowtemp.CreateCell(j).SetCellValue(double.Parse(propertyInfo.GetValue(model, null).ToString()));
j++;
}
m++;
}
return(book);
}
/// <summary>
/// Create an Excel document with a nice breakdown of Trello.
/// </summary>
/// <returns></returns>
public static byte[] ToExcel()
{
List<Board> _boards = ParseBoardData();
// New workbook
var workbook = new HSSFWorkbook();
foreach (Board _boardInfo in _boards) {
JavaScriptSerializer _worker = new JavaScriptSerializer();
_worker.MaxJsonLength = int.MaxValue;
File.WriteAllText(HttpContext.Current.Server.MapPath("") + @"\output\" + _boardInfo.name.ToString() + ".json", _worker.Serialize(_boardInfo));
// Create a sheet
var sheet = workbook.CreateSheet(_boardInfo.name.ToString());
// Kill everything not supposed to be in there.
var rowIndex = 0;
#region HEADING STYLE
HSSFFont _headingFont = (HSSFFont)workbook.CreateFont();
_headingFont.Boldweight = (short)FontBoldWeight.Bold;
_headingFont.Color = HSSFColor.Grey80Percent.Index;
//_headingFont.FontHeight = 280;
HSSFCellStyle _headingStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_headingStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_headingStyle.VerticalAlignment = VerticalAlignment.Center;
_headingStyle.SetFont(_headingFont);
#endregion
// Create the heading
var row = sheet.CreateRow(rowIndex);
row.Height = 350;
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue("Card name");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(1);
cell.SetCellValue("Date created");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(2);
cell.SetCellValue("Pending");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(3);
cell.SetCellValue("In Progress");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(4);
cell.SetCellValue("In Testing");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(5);
cell.SetCellValue("Released");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(6);
cell.SetCellValue("On Hold");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(7);
cell.SetCellValue("Description");
cell.CellStyle = _headingStyle;
// Next row
rowIndex++;
#region ROW STYLE
HSSFCellStyle _rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_rowStyle.VerticalAlignment = VerticalAlignment.Center;
IDataFormat _dataFormat = workbook.CreateDataFormat();
#endregion
if (_boardInfo.Lists != null) {
foreach (Board.List _listData in _boardInfo.Lists) {
if (_listData.Cards != null) {
foreach (Board.List.Card _cardData in _listData.Cards) {
// Add the row
row = sheet.CreateRow(rowIndex);
row.Height = 350;
// Card name
cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue(_cardData.name.ToString());
cell.CellStyle = _rowStyle;
// Created
cell = (HSSFCell)row.CreateCell(1);
DateTime _startDate = new DateTime(1970, 01, 01);
cell.SetCellValue(_startDate.AddSeconds(int.Parse(_cardData.id.ToString().Substring(0, 8), System.Globalization.NumberStyles.HexNumber)).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
// Actions
foreach (Board.List.Card.Action _actionData in _cardData.Actions) {
if (_actionData.data != null) {
if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("pending")) {
cell = (HSSFCell)row.CreateCell(2);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
} else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("progress")) {
cell = (HSSFCell)row.CreateCell(3);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
} else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("testing")) {
cell = (HSSFCell)row.CreateCell(4);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
} else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("release")) {
cell = (HSSFCell)row.CreateCell(5);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
} else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("hold")) {
cell = (HSSFCell)row.CreateCell(6);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
}
}
}
// Description
cell = (HSSFCell)row.CreateCell(7);
cell.SetCellValue(_cardData.desc.ToString());
cell.CellStyle = _rowStyle;
// Next row.
rowIndex++;
}
}
}
// Size it
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
sheet.AutoSizeColumn(4);
sheet.AutoSizeColumn(5);
sheet.AutoSizeColumn(6);
sheet.AutoSizeColumn(7);
sheet.CreateFreezePane(0, 1);
}
}
#region OUTPUT
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream()) {
workbook.Write(exportData);
return exportData.GetBuffer();
}
#endregion
}
private static MemoryStream GetExcelMemoryStream(List <dynamic> orderItemList)
{
var first = orderItemList.FirstOrDefault();
//创建表格
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义样式
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 10;
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)220, (byte)220, (byte)220);
// titleStyle
HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle();
//（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
titleStyle.FillForegroundColor = HSSFColor.Pink.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
titleStyle.BorderBottom = BorderStyle.Thin;
titleStyle.BorderLeft = BorderStyle.Thin;
titleStyle.BorderRight = BorderStyle.Thin;
titleStyle.BorderTop = BorderStyle.Thin;
titleStyle.SetFont(cellStyleFont);//设置字体
//headStyle
HSSFCellStyle headStyle = (HSSFCellStyle)book.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; //水平居中
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.SetFont(cellStyleFont);
//borderStyle
HSSFCellStyle borderStyle = (HSSFCellStyle)book.CreateCellStyle();
borderStyle.BorderBottom = BorderStyle.Thin;
borderStyle.BorderLeft = BorderStyle.Thin;
borderStyle.BorderRight = BorderStyle.Thin;
borderStyle.BorderTop = BorderStyle.Thin;
#endregion
#region 表头
//设置单元格宽度
sheet.SetColumnWidth(0, 5000); //采购日期
sheet.SetColumnWidth(1, 5000); //门店名称
sheet.SetColumnWidth(2, 14000); //门店地址
sheet.SetColumnWidth(3, 4500); //采购数量
sheet.SetColumnWidth(4, 4500); //操作人
//表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(4);
row.Height = 350;
row.CreateCell(0).SetCellValue("采购日期");
row.CreateCell(1).SetCellValue("门店名称");
row.CreateCell(2).SetCellValue("门店地址");
row.CreateCell(3).SetCellValue("采购数量");
row.CreateCell(4).SetCellValue("操作人");
for (int i = 0; i < 5; i++)
{
row.Cells[i].CellStyle = titleStyle;
}
//合并单元格
CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 4);
sheet.AddMergedRegion(region0);
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("冷链食品处理详情");
row0.Cells[0].CellStyle = headStyle;
var row0Cell4 = row0.CreateCell(4);
row0Cell4.CellStyle = headStyle;
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("处理商品批次码");
row1.CreateCell(1).SetCellValue(first.BatchNo.ToString());
row1.Cells[0].CellStyle = titleStyle;
IRow row2 = sheet.CreateRow(2);
row2.CreateCell(0).SetCellValue("处理日期");
row2.CreateCell(1).SetCellValue(first.NoticeTime?.ToString("yyyy.MM.dd"));
row2.Cells[0].CellStyle = titleStyle;
IRow row3 = sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("处理人");
row3.CreateCell(1).SetCellValue(first.NoticeUserName.ToString());
row3.Cells[0].CellStyle = titleStyle;
//设置黑色边框
for (int i = 1; i < 4; i++)
{
var iRow = sheet.GetRow(i);
for (int j = 1; j < 5; j++)
{
var jCell = (j == 1) ? iRow.GetCell(j) : iRow.CreateCell(j);
jCell.CellStyle = borderStyle;
}
}
#endregion
#region 循环数据
int index = 5;
foreach (var item in orderItemList)
{
IRow itemRow = sheet.CreateRow(index);
var rowCell0 = itemRow.CreateCell(0);
rowCell0.SetCellValue((item.ImportTime ?? item.SubTime).ToString("yyyy.MM.dd"));
rowCell0.CellStyle = borderStyle;
var rowCell1 = itemRow.CreateCell(1);
rowCell1.SetCellValue(item.TargetStoreName.ToString());
rowCell1.CellStyle = borderStyle;
var rowCell2 = itemRow.CreateCell(2);
rowCell2.SetCellValue(item.TargetStoreAddress.ToString());
rowCell2.CellStyle = borderStyle;
var rowCell3 = itemRow.CreateCell(3);
rowCell3.SetCellValue(item.Number?.ToString());
rowCell3.CellStyle = borderStyle;
var rowCell4 = itemRow.CreateCell(4);
rowCell4.SetCellValue(item.ImportUserNames.ToString());
rowCell4.CellStyle = borderStyle;
index++;
}
#endregion
MemoryStream ms = new MemoryStream();
book.Write(ms);
return(ms);
}
private void ExportToExcel(string path)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("系统日志报表");
//第一行样式及内容
IRow row = sheet.CreateRow(0);
row.HeightInPoints = 40;
ICell cell = row.CreateCell(0, CellType.String);
cell.SetCellValue("系统日志报表");
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 18;
font.FontName = "宋体";
font.Boldweight = 800;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Justify;
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
//设置列宽
sheet.SetColumnWidth(0, 12 * 256);
for (int i = 1; i < 6; i++)
{
sheet.SetColumnWidth(i, 20 * 256);
}
ICellStyle style2 = workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Justify;
IFont font2 = workbook.CreateFont();
font2.FontHeightInPoints = 12;
font2.FontName = "宋体";
style2.SetFont(font2);
//第二行(表头)
IRow row2 = sheet.CreateRow(1);
ICell headCell1 = row2.CreateCell(0, CellType.String);
headCell1.CellStyle = style2;
headCell1.SetCellValue("流水号");
ICell headCell2 = row2.CreateCell(1, CellType.String);
headCell2.CellStyle = style2;
headCell2.SetCellValue("菜单名称");
ICell headCell3 = row2.CreateCell(2, CellType.String);
headCell3.CellStyle = style2;
headCell3.SetCellValue("操作类型");
ICell headCell4 = row2.CreateCell(3, CellType.String);
headCell4.CellStyle = style2;
headCell4.SetCellValue("操作时间");
ICell headCell5 = row2.CreateCell(4, CellType.String);
headCell5.SetCellValue("操作内容");
headCell5.CellStyle = style2;
ICell headCell6 = row2.CreateCell(5, CellType.String);
headCell6.SetCellValue("操作用户");
headCell6.CellStyle = style2;
//填充数据到单元格
for (int i = 0; i < currentTable.Rows.Count; i++)
{
IRow irow = sheet.CreateRow(i + 2);
ICell cell1 = irow.CreateCell(0, CellType.Numeric);
cell1.SetCellValue(Convert.ToInt32(currentTable.Rows[i]["NUMB_SYSLOG"]));
ICell cell2 = irow.CreateCell(1, CellType.String);
cell2.SetCellValue(currentTable.Rows[i]["FK_NAME_MENU"].ToString());
ICell cell3 = irow.CreateCell(2, CellType.String);
cell3.SetCellValue(currentTable.Rows[i]["FLAG_LOGSORT"].ToString());
ICell cell4 = irow.CreateCell(3, CellType.String);
cell4.SetCellValue((Convert.ToDateTime(currentTable.Rows[i]["INFO_DATE"])).ToString("yyyy-MM-dd HH:mm:ss"));
ICell cell5 = irow.CreateCell(4, CellType.String);
cell5.SetCellValue(currentTable.Rows[i]["INFO_CONT"].ToString());
ICell cell6 = irow.CreateCell(5, CellType.String);
cell6.SetCellValue(currentTable.Rows[i]["FK_NAME_USER"].ToString());
}
FileStream file = new FileStream(path, FileMode.Create);
workbook.Write(file);
file.Close();
this.Dispatcher.Invoke(new Action(() =>
{
Toolkit.MessageBox.Show("导出成功！", "系统提示", MessageBoxButton.OK, MessageBoxImage.Information);
}));
}
catch (Exception)
{
this.Dispatcher.Invoke(new Action(() =>
{
Toolkit.MessageBox.Show("导出失败！", "系统提示", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}));
}
}
void ExportAll(object filePath)
{
this.Invoke(new Action(() => { }));
lock (obj)
{
DataTable tables_1 = SqlHelper_1.GetTables();
DataTable tables_2 = SqlHelper_2.GetTables();
OrderedEnumerableRowCollection <DataRow> dataRows_1 = tables_1.AsEnumerable().OrderBy(a => a.Field <string>("name"));
OrderedEnumerableRowCollection <DataRow> dataRows_2 = tables_2.AsEnumerable().OrderBy(a => a.Field <string>("name"));
//创建Excel文件的对象
IWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
#region 设置表头样式
//创建样式对象
ICellStyle style = book.CreateCellStyle();
//创建一个字体样式对象
IFont font = book.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 16;
//水平居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection;
//将字体样式赋给样式对象
style.SetFont(font);
#endregion
#region 设置内容样式
//创建样式对象
ICellStyle style2 = book.CreateCellStyle();
//水平居中
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直居中
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection;
#endregion
foreach (DataRow row in dataRows_1)
{
string tablename = row["name"].ToString().Trim();
DataTable tableInfo_1 = SqlHelper_1.GetTableInfo(tablename);
DataTable tableInfo_2 = SqlHelper_2.GetTableInfo(tablename);
IEnumerable <DataRow> query = tableInfo_1.AsEnumerable().Except(tableInfo_2.AsEnumerable(), DataRowComparer.Default);
if (query.Count() > 0)
{
DataTable changesTable = query.CopyToDataTable();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(tablename);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
int c = 0;
foreach (DataColumn item in changesTable.Columns)
{
//设置宽度
sheet1.SetColumnWidth(c, 40 * 150);
ICell cell = row1.CreateCell(c);
cell.SetCellValue(item.Caption);
cell.CellStyle = style;
c++;
}
//将数据逐步写入sheet1各个行
int k = 0;
foreach (DataRow item in changesTable.Rows)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(k + 1);
for (int i = 0; i < changesTable.Columns.Count; i++)
{
ICell cell = rowtemp.CreateCell(i);
cell.SetCellValue(item[i].ToString());
cell.CellStyle = style2;
}
k++;
}
}
}
//写入文件
FileStream xlsfile = new FileStream(filePath.ToString(), FileMode.Create);
book.Write(xlsfile);
xlsfile.Dispose();
this.Invoke(new Action(() => { MessageBox.Show("导出成功，文件存放于桌面", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.None); }));
isFinish = true;
}
}
public void ExportToExcel(int page, int size, int supplierType, int supplierId, string supplierName, string stockCode, string stockName, int country, int market, string enable)
{
// Get the data to report on
var masters = _service.ListCondition(page, size, supplierType, supplierId, supplierName, stockCode, stockName, country, market, enable);
var details = _service.ListConditionDetailExcel(page, size, supplierType, supplierId, supplierName, stockCode, stockName, country, market, enable);
// Create a new workbook
var workbook = new HSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.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;
#endregion
#region Currency Cell Style
var currencyCellStyle = workbook.CreateCellStyle();
currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
currencyCellStyle.DataFormat = formatId;
#endregion
#region Detail Subtotal Style
var detailSubtotalCellStyle = workbook.CreateCellStyle();
detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);
#endregion
#region Detail Currency Subtotal Style
var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
detailCurrencySubtotalCellStyle.DataFormat = formatId;
#endregion
#endregion
#region Master sheet
var sheet = workbook.CreateSheet("Supplier");
// Add header labels
var rowIndex = 0;
// Undestand as row in excel. row + 3 = xuong 3 row.
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(0);
cell.SetCellValue("No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Supplier Id");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Supplier Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Address");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("City");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Country");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Phone 1");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Phone 2");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Mobile");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Fax");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(11);
cell.SetCellValue("Email");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(12);
cell.SetCellValue("Contact");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(13);
cell.SetCellValue("Created Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(14);
cell.SetCellValue("Created By");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(15);
cell.SetCellValue("Modified Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(16);
cell.SetCellValue("Modified By");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
var no = 1;
foreach (var master in masters)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(no);
row.CreateCell(1).SetCellValue(master.Id);
row.CreateCell(2).SetCellValue(master.Name);
row.CreateCell(3).SetCellValue(master.Type);
row.CreateCell(4).SetCellValue(master.Address);
row.CreateCell(5).SetCellValue(master.City);
row.CreateCell(6).SetCellValue(master.Country);
row.CreateCell(7).SetCellValue(master.Phone);
row.CreateCell(8).SetCellValue(master.Phone_2);
row.CreateCell(9).SetCellValue(master.Mobile);
row.CreateCell(10).SetCellValue(master.Fax);
row.CreateCell(11).SetCellValue(master.Email);
row.CreateCell(12).SetCellValue(master.Contact);
row.CreateCell(13).SetCellValue(master.Created_Date != null
? master.Created_Date.Value.ToString("dd/MM/yyyy")
: master.Created_Date.ToString());
row.CreateCell(14).SetCellValue(master.Created_By);
row.CreateCell(15).SetCellValue(master.Modified_Date != null
? master.Modified_Date.Value.ToString("dd/MM/yyyy")
: master.Modified_Date.ToString());
row.CreateCell(16).SetCellValue(master.Modified_By);
rowIndex++;
no++;
}
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
#region Detail sheet
sheet = workbook.CreateSheet("Product");
#region Add header labels
rowIndex = 0;
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("Supplier");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Stock Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Stock Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Stock Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Unit");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Category");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Part No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Ral No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Color");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
#endregion
// Add data rows
var lastProductName = string.Empty;
// For sum in excel
// var startRowIndexForProductDetails = 1;
foreach (var detail in details)
{
// Show a summary row for each new product
var productNameToShow = string.Empty;
if (string.Compare(detail.Supplier_Name, lastProductName) != 0)
{
if (!string.IsNullOrEmpty(lastProductName))
{
// Add the subtotal row
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 3;
}
productNameToShow = detail.Supplier_Name;
lastProductName = detail.Supplier_Name;
// startRowIndexForProductDetails = rowIndex;
}
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(productNameToShow);
row.CreateCell(1).SetCellValue(detail.Stock_Code);
row.CreateCell(2).SetCellValue(detail.Stock_Name);
row.CreateCell(3).SetCellValue(detail.Type);
row.CreateCell(4).SetCellValue(detail.Unit);
row.CreateCell(5).SetCellValue(detail.Category);
row.CreateCell(6).SetCellValue(detail.Part_No);
row.CreateCell(7).SetCellValue(detail.Ral_No);
row.CreateCell(8).SetCellValue(detail.Color);
rowIndex++;
}
// Add the subtotal row for the last product
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 2;
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
// sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
var saveAsFileName = string.Format("Supplier-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Assert.AreEqual(6, wb.NumberOfFonts);
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CENTER_SELECTION;// ((short)22);
NPOI.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.FILL; //((short)111);
NPOI.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
r.CreateCell(3).CellStyle = (cs4);
r.CreateCell(4).CellStyle = (cs5);
r.CreateCell(5).CellStyle = (cs6);
r.CreateCell(6).CellStyle = (cs1);
r.CreateCell(7).CellStyle = (cs2);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(6, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
Assert.AreEqual(22, r.GetCell(1).CellStyle.GetFont(wb).FontHeight);
// cs3 = cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// cs4 --> 24 -> 23
Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
// cs5 --> 25 -> 24
Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
// cs6 = cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);
// Add a new duplicate, and two that aren't used
HSSFCellStyle csD = (HSSFCellStyle)wb.CreateCellStyle();
csD.SetFont(f1);
r.CreateCell(8).CellStyle=(csD);
HSSFFont f3 = (HSSFFont)wb.CreateFont();
f3.FontHeight=((short)23);
f3.FontName=("Testing 3");
HSSFFont f4 = (HSSFFont)wb.CreateFont();
f4.FontHeight=((short)24);
f4.FontName=("Testing 4");
HSSFCellStyle csU1 = (HSSFCellStyle)wb.CreateCellStyle();
csU1.SetFont(f3);
HSSFCellStyle csU2 = (HSSFCellStyle)wb.CreateCellStyle();
csU2.SetFont(f4);
// Check before the optimise
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(28, wb.NumCellStyles);
// Optimise, should remove the two un-used ones and the one duplicate
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// csD -> cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(8)).CellValueRecord.XFIndex);
}