public ExcelDocument(bool parRemoveBr)
{
offset = 0;
workbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Cashflow Funding Limited";
workbook.DocumentSummaryInformation = dsi;
sheet = (HSSFSheet)workbook.CreateSheet("CFF Export Data");
cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.WrapText = true;
cellStyleTitle = (HSSFCellStyle)workbook.CreateCellStyle();
cellHeaderStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellFooterStyle = (HSSFCellStyle)workbook.CreateCellStyle();
this.removeBr = parRemoveBr;
if (this.removeBr == true)
{
cellStyle.BorderLeft = CellBorderType.NONE;
cellStyle.BorderRight = CellBorderType.NONE;
cellStyle.BorderBottom = CellBorderType.NONE;
cellStyle.BorderTop = CellBorderType.NONE;
}
fontNormal = (HSSFFont)workbook.CreateFont();
fontTitle = (HSSFFont)workbook.CreateFont();
fontHeader = (HSSFFont)workbook.CreateFont();
fontFooter = (HSSFFont)workbook.CreateFont();
df = (HSSFDataFormat)workbook.CreateDataFormat();
}
public void AddFooterCellToCurrentRow(string text, int index, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.LEFT)
{
//HSSFCellStyle style = FooterCellStyle(xAlign);
int idx = (index < 0) ? columnIndex:index;
ManualAdjustColumnWidth(text, idx);
//HSSFCell cellFtr = (HSSFCell)_HSSFCurrentRow.GetCell(idx);
HSSFFont fFooter = (HSSFFont)workbook.CreateFont();
fFooter.FontName = "Calibri";
fFooter.Boldweight = (short)FontBoldWeight.BOLD;
fFooter.Color = HSSFColor.BLACK.index;
HSSFCellStyle cFooterStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cFooterStyle.SetFont(fFooter);
cFooterStyle.BorderTop = CellBorderType.THIN;
cFooterStyle.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
HSSFCell cellFtr = (HSSFCell)_HSSFCurrentRow.CreateCell(idx, CellType.STRING);
cellFtr.CellStyle = cFooterStyle;
cellFtr.CellStyle.BorderLeft = CellBorderType.NONE;
cellFtr.CellStyle.BorderRight = CellBorderType.NONE;
cellFtr.SetCellValue(text);
ManualAdjustColumnWidth(text, idx);
cellFtr.CellStyle.Alignment = xAlign;
columnIndex++;
}
/// <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>
/// Translate color palette entries from the source to the destination sheet
/// </summary>
private static void RemapCellStyle(HSSFCellStyle stylish, Dictionary <short, short> paletteMap)
{
if (paletteMap.ContainsKey(stylish.BorderDiagonalColor))
{
stylish.BorderDiagonalColor = paletteMap[stylish.BorderDiagonalColor];
}
if (paletteMap.ContainsKey(stylish.BottomBorderColor))
{
stylish.BottomBorderColor = paletteMap[stylish.BottomBorderColor];
}
if (paletteMap.ContainsKey(stylish.FillBackgroundColor))
{
stylish.FillBackgroundColor = paletteMap[stylish.FillBackgroundColor];
}
if (paletteMap.ContainsKey(stylish.FillForegroundColor))
{
stylish.FillForegroundColor = paletteMap[stylish.FillForegroundColor];
}
if (paletteMap.ContainsKey(stylish.LeftBorderColor))
{
stylish.LeftBorderColor = paletteMap[stylish.LeftBorderColor];
}
if (paletteMap.ContainsKey(stylish.RightBorderColor))
{
stylish.RightBorderColor = paletteMap[stylish.RightBorderColor];
}
if (paletteMap.ContainsKey(stylish.TopBorderColor))
{
stylish.TopBorderColor = paletteMap[stylish.TopBorderColor];
}
}
private static void CreateRows(this HSSFWorkbook workbook, DataRowCollection rows, Dictionary <string, string> columns, string sheetName = "Sheet")
{
HSSFCellStyle headStyle = workbook.SetHeaderStyle();
HSSFCellStyle cellStyle = workbook.SetCellStyle();
short dateFormat = workbook.SetDateFormat();
//行首
HSSFSheet sheet = workbook.CreateSheet(sheetName);
sheet.CreateHeader(columns, headStyle);
int rowCount = 1;
int sheetCount = 1;
foreach (DataRow dr in rows)
{
//超出10000条数据 创建新的工作簿
if (rowCount == 65536)
{
rowCount = 1;
sheetCount++;
sheet = workbook.CreateSheet(sheetName + sheetCount);
sheet.CreateHeader(columns, headStyle);
}
HSSFRow row = sheet.CreateRow(rowCount);
row.CreateCells(dr, columns, cellStyle, dateFormat);
rowCount++;
}
}
private static void CreateRows <T>(this HSSFWorkbook workbook, HSSFSheet sheet, Dictionary <string, string> columns, IEnumerable <T> list, string sheetName = "Sheet")
{
HSSFCellStyle headStyle = workbook.SetHeaderStyle();
HSSFCellStyle cellStyle = workbook.SetCellStyle();
// 表头
sheet.CreateHeader(columns, headStyle);
int rowCount = 1;
int sheetCount = 0;
foreach (T item in list)
{
//超出65536条数据 创建新的工作簿
if (rowCount == 65536)
{
sheetCount++;
rowCount = 1;
sheet = workbook.CreateSheet(sheetName + sheetCount);
sheet.CreateHeader(columns, headStyle);
}
//excel从第2行开始
HSSFRow row = sheet.CreateRow(rowCount);
workbook.CreateCells(row, columns, item, cellStyle);
rowCount++;
}
}
public PictureToExcel()
{
InitializeWorkbook();
HSSFSheet sheet1 = hssfworkbook.CreateSheet("PictureSheet");
TechStoreDocumentsDT = new DataTable();
using (SqlDataAdapter DA = new SqlDataAdapter(@"SELECT TOP (2000) TechStoreName, TechStoreDocumentID FROM TechStore
LEFT JOIN TechStoreDocuments ON TechStore.TechStoreID = TechStoreDocuments.TechID AND DocType = 0
WHERE TechStoreSubGroupID = 30 ORDER BY TechStoreName", ConnectionStrings.CatalogConnectionString))
{
using (DataTable DT = new DataTable())
{
DA.Fill(TechStoreDocumentsDT);
}
}
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
HSSFCell Cell1;
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle();
ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
ReportCS1.BottomBorderColor = HSSFColor.BLACK.index;
ReportCS1.SetFont(HeaderF1);
int RowIndex = 0;
for (int i = 0; i < TechStoreDocumentsDT.Rows.Count; i++)
{
string TechStoreName = TechStoreDocumentsDT.Rows[i]["TechStoreName"].ToString();
Cell1 = sheet1.CreateRow(RowIndex).CreateCell(0);
Cell1.SetCellValue(TechStoreName);
Cell1.CellStyle = ReportCS1;
if (TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"] != DBNull.Value)
{
int TechStoreDocumentID = Convert.ToInt32(TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"]);
anchor = new HSSFClientAnchor(0, 0, 0, 255, 2, RowIndex, 5, RowIndex + 7)
{
AnchorType = 2
};
HSSFPicture picture = patriarch.CreatePicture(anchor, GetTechStoreImage(TechStoreDocumentID, hssfworkbook));
//picture.Resize();
picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;
}
RowIndex = RowIndex + 8;
}
WriteToFile();
}
public void WriteToCell(IRow curRow, int Cellindex, string Value, HSSFCellStyle style)
{
ICell Cell = curRow.CreateCell(Cellindex);
Cell.SetCellValue(Value);
Cell.CellStyle = style;
}
protected void generateDateLine(List <string> tradingDays_, ISheet tab_, HSSFWorkbook wb_)
{
IRow row = tab_.CreateRow(currentLine++);
ICell[] cells = new ICell[5];
for (int i = 0; i < 5; i++)
{
cells[i] = row.CreateCell(i, CellType.String);
}
HSSFCellStyle style = CellStyle.headingStyle(wb_);
cells[0].SetCellValue("TradingDay : ");
cells[0].CellStyle = style;
cells[1].SetCellValue("From ");
cells[1].CellStyle = style;
cells[2].SetCellValue(tradingDays_[0]);
cells[2].CellStyle = style;
cells[3].SetCellValue(" To ");
cells[3].CellStyle = style;
cells[4].SetCellValue(tradingDays_[tradingDays_.Count - 1]);
cells[4].CellStyle = style;
}
/// <summary>
/// 设置单元格样式(数据格式为数字型)(红色)
/// </summary>
/// <param name="workbook">excel表格</param>
/// <param name="horizontal">水平布局</param>
/// <param name="vertical">垂直布局</param>
/// <param name="boldWeight">字体加粗</param>
/// <param name="fontPoint">字体大小</param>
/// <param name="isBorder">是否需要边框</param>
/// <param name="fontName">字体名称：宋体、黑体、微软雅黑，默认微软雅黑</param>
/// <returns>返回单元格样式</returns>
public static HSSFCellStyle CreateStyle3(HSSFWorkbook workbook, HorizontalAlignment horizontal, VerticalAlignment vertical, short boldWeight, short fontPoint, bool isBorder, string fontName = "微软雅黑")
{
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = horizontal;
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.VerticalAlignment = vertical;
if (isBorder)
{
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
}
//创建字体
HSSFFont cellStyleFont = (HSSFFont)workbook.CreateFont();
//字体加粗
cellStyleFont.Boldweight = boldWeight;
cellStyleFont.FontHeightInPoints = fontPoint;
cellStyleFont.FontName = fontName;
cellStyleFont.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(cellStyleFont);
return(cellStyle);
}
public async Task <HSSFWorkbook> GetWorkbook(List <ExcelTestClass> list, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "耐心的雪球有限公司";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "耐心"; //填加xls文件作者信息
si.LastAuthor = "耐心"; //填加xls文件最后保存者信息
si.Comments = "耐心"; //填加xls文件作者信息
si.Subject = "导出文件记录"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
await ListToSheet(workbook, list, headStyle, sheetName);
return(workbook);
}
private void addSummaryLine(ISheet tab_, HSSFWorkbook wb_)
{
HSSFCellStyle style = CellStyle.footerStyle(wb_);
currentLine++; currentLine++;
IRow row = tab_.CreateRow(currentLine++);
ICell[] cells = new ICell[columeNb];
cells[1] = row.CreateCell(1, CellType.String);
cells[1].SetCellValue("TOTAL");
cells[1].CellStyle = style;
for (int i = 2; i < 6; i++)
{
cells[i] = row.CreateCell(i, CellType.String);
cells[i].SetCellValue(ALGO_RESULT_COLUMES[i - 2]);
cells[i].CellStyle = style;
}
cells[6] = row.CreateCell(6, CellType.String);
cells[6].SetCellValue("Slipage Stdev");
cells[6].CellStyle = style;
cells[7] = row.CreateCell(7, CellType.String);
cells[7].SetCellValue("Cancel Rate");
cells[7].CellStyle = style;
cells[8] = row.CreateCell(8, CellType.String);
cells[8].SetCellValue("Fill Rate");
cells[8].CellStyle = style;
writeAggreagetResult(tab_, wb_);
}
private void SetHeaderOptions(SimpleExcelExport.Column column, NPOI.SS.UserModel.IRow row, int columnNumber, ExportToExcel exportToExcel)
{
HSSFCellStyle style = (HSSFCellStyle)document.CreateCellStyle();
var font = document.CreateFont();
if (column.HFontBold)
{
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
}
if (!string.IsNullOrEmpty(column.HFontColor))
{
System.Drawing.Color fontColor = exportToExcel.GetColor(column.ColumnName, column.HFontColor, typeof(string));
if (!fontColor.IsEmpty)
{
font.Color = GetXLColour(fontColor);
}
}
if (!string.IsNullOrEmpty(column.HBackColor))
{
System.Drawing.Color backgroundColor = exportToExcel.GetColor(column.ColumnName, column.HBackColor, typeof(string));
if (!backgroundColor.IsEmpty)
{
style.FillForegroundColor = GetXLColour(backgroundColor);
style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
}
}
style.SetFont(font);
row.Cells[columnNumber].CellStyle = style;
}
private static HSSFCellStyle generate(HSSFWorkbook wb_, int index, int r, int g, int b, bool bold)
{
short colorIndex = (short)index;
HSSFCellStyle style = (HSSFCellStyle)(wb_.CreateCellStyle());
HSSFPalette palette = ((HSSFWorkbook)wb_).GetCustomPalette();
palette.SetColorAtIndex(colorIndex, (byte)r, (byte)g, (byte)b);
style.FillForegroundColor = palette.GetColor(colorIndex).Indexed;
style.FillPattern = FillPattern.SolidForeground;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//是否换行
style.WrapText = true;
if (bold)
{
IFont font = wb_.CreateFont();
font.Boldweight = short.MaxValue;
style.SetFont(font);
}
return(style);
}
private void setCellStyle(HSSFWorkbook workbook, ISheet sheet, ICell cell, I3PrintArea area, I3ReportCell cellData,
Dictionary <string, HSSFCellStyle> styleDic, Dictionary <string, IFont> fontDic, bool hasReturnInText, bool isLastRow, bool isLastCol,
HSSFCellStyle emptyStyle)
{
I3ReportCellStyle cs = null;
if (cellData.MergState == I3MergeState.Merged)
{
cellData = area.ReportData.GetMergedStartedCell(cellData.Row, cellData.Col);
}
cs = area.ReportData.GetCellStyle(cellData.StyleName);
if (cs == null)
{
cell.CellStyle = emptyStyle;
return;
}
string styleKey = getStyleKey(cellData, cs, hasReturnInText, isLastRow, isLastCol);
HSSFCellStyle style = null;
if (styleDic.ContainsKey(styleKey))
{
style = styleDic[styleKey];
}
else
{
style = createStyle(workbook, cellData, cs, fontDic, hasReturnInText, isLastRow, isLastCol);
styleDic.Add(styleKey, style);
}
cell.CellStyle = style;
}
/// <summary>
/// LessData导出
/// </summary>
private void LessDataHandle(List <LessDataModel> list)
{
int dataNum = list.Count(); //数据量
HSSFSheet sheet = lessSheet;
HSSFRow row;
HSSFCell cell;
HSSFCellStyle cellStyle = getCellStyle();
HSSFCellStyle timeCellStyle = getTimeCellStyle();
for (int i = 0; i < dataNum; i++)
{
var item = list[i];
row = sheet.CreateRow(i + 1) as HSSFRow;
cell = row.CreateCell(0) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.pId);
cell = row.CreateCell(1) as HSSFCell;
cell.CellStyle = timeCellStyle;
cell.SetCellValue(item.auctionGoodsTime);
cell = row.CreateCell(2) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.auctionGoodsName);
cell = row.CreateCell(3) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.auctionCount);
cell = row.CreateCell(4) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.isWinPrizeForTheGoods ? "√" : "×");
}
}
/// <summary>
/// 设置边框线
/// </summary>
/// <param name="style"></param>
/// <param name="top"></param>
/// <param name="bottom"></param>
/// <param name="left"></param>
/// <param name="right"></param>
private static void SetBorder(HSSFCellStyle style, BorderStyle top, BorderStyle bottom, BorderStyle left, BorderStyle right)
{
style.BorderTop = top;
style.BorderBottom = bottom;
style.BorderLeft = left;
style.BorderRight = right;
}
//[HttpGet]
//public JsonResult GetResults()
//{
// var listCharts = ItemManager.Instance.GetCharts();
// Gender gender = new Gender();
// gender.Cities1 = new List<City>();
// gender.Cities2 = new List<City>();
// gender.Cities3 = new List<City>();
// foreach (var item in listCharts)
// {
// City city = new City();
// if (item.Gender == "Male")
// {
// city.IdCity = item.IdCity;
// city.CityName = item.CityName;
// city.Amount = item.Amount;
// gender.Cities1.Add(city);
// }
// if (item.Gender == "Female")
// {
// city.IdCity = item.IdCity;
// city.CityName = item.CityName;
// city.Amount = item.Amount;
// gender.Cities2.Add(city);
// }
// if (item.Gender == "Other")
// {
// city.IdCity = item.IdCity;
// city.CityName = item.CityName;
// city.Amount = item.Amount;
// gender.Cities3.Add(city);
// }
// }
// return Json(new { gender = JsonConvert.SerializeObject(gender, Formatting.Indented) }, JsonRequestBehavior.AllowGet);
//}
public void CreateCell(IRow CurrentRow, int CellIndex, string Value, HSSFCellStyle Style)
{
ICell Cell = CurrentRow.CreateCell(CellIndex);
Cell.SetCellValue(Value);
Cell.CellStyle = Style;
}
/// <summary>
/// Function to add a new column to the Excel worksheet
/// </summary>
/// <param name="columnHeader">The column header to be added</param>
/// <param name="cellFormatting">The ExcelCellFormatting to be applied to the column header</param>
public void AddColumn(String columnHeader, ExcelCellFormatting cellFormatting)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
HSSFRow row = (HSSFRow)worksheet.GetRow(0); //0 because header is always in the first row
int lastCellNum = row.LastCellNum;
if (lastCellNum == -1)
{
lastCellNum = 0;
}
HSSFCell cell = (HSSFCell)row.CreateCell(lastCellNum);
cell.SetCellType(CellType.String);
cell.SetCellValue(columnHeader);
if (cellFormatting != null)
{
HSSFCellStyle cellStyle = ApplyCellStyle(workbook, cellFormatting);
cell.CellStyle = cellStyle;
}
WriteIntoFile(workbook);
}
/// <summary>
/// 初始化样式
/// </summary>
/// <param name="style"></param>
public void InitializeStyle(HSSFCellStyle style)
{
if (style == null)
{
style = (HSSFCellStyle)Hssfworkbook.CreateCellStyle();
}
}
private HSSFCellStyle ApplyCellStyle(HSSFWorkbook workbook,
ExcelCellFormatting cellFormatting)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
if (cellFormatting.Centred)
{
cellStyle.Alignment = HorizontalAlignment.Center;
}
cellStyle.FillForegroundColor = cellFormatting.BackColorIndex;
cellStyle.FillPattern = FillPattern.SolidForeground;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = cellFormatting.FontName;
font.FontHeightInPoints = cellFormatting.FontSize;
if (cellFormatting.Bold)
{
font.Boldweight = (short)FontBoldWeight.Bold;
}
font.Color = cellFormatting.ForeColorIndex;
cellStyle.SetFont(font);
return(cellStyle);
}
public void TestDefaultStyles()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFCellStyle style1 = (XSSFCellStyle)wb1.CreateCellStyle();
Assert.AreEqual(IndexedColors.Automatic.Index, style1.FillBackgroundColor);
Assert.IsNull(style1.FillBackgroundColorColor);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb1));
//compatibility with HSSF
HSSFWorkbook wb2 = new HSSFWorkbook();
HSSFCellStyle style2 = (HSSFCellStyle)wb2.CreateCellStyle();
Assert.AreEqual(style2.FillBackgroundColor, style1.FillBackgroundColor);
Assert.AreEqual(style2.FillForegroundColor, style1.FillForegroundColor);
Assert.AreEqual(style2.FillPattern, style1.FillPattern);
Assert.AreEqual(style2.LeftBorderColor, style1.LeftBorderColor);
Assert.AreEqual(style2.TopBorderColor, style1.TopBorderColor);
Assert.AreEqual(style2.RightBorderColor, style1.RightBorderColor);
Assert.AreEqual(style2.BottomBorderColor, style1.BottomBorderColor);
Assert.AreEqual(style2.BorderBottom, style1.BorderBottom);
Assert.AreEqual(style2.BorderLeft, style1.BorderLeft);
Assert.AreEqual(style2.BorderRight, style1.BorderRight);
Assert.AreEqual(style2.BorderTop, style1.BorderTop);
wb2.Close();
}
/// <summary>
/// 创建lessData的表头数据
/// </summary>
/// <param name="sheet"></param>
private void CreateLessDataTableHeader(HSSFSheet sheet)
{
HSSFCellStyle celStyle = getCellStyle();
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
sheet.SetColumnWidth(0, 20 * 256);
var cell = row.CreateCell(0);
cell.SetCellValue("用户id");
cell.CellStyle = celStyle;
sheet.SetColumnWidth(1, 20 * 256);
cell = row.CreateCell(1);
cell.SetCellValue("参与竞拍商品时间");
cell.CellStyle = celStyle;
sheet.SetColumnWidth(2, 20 * 256);
cell = row.CreateCell(2);
cell.SetCellValue("参与竞拍商品名称");
cell.CellStyle = celStyle;
cell = row.CreateCell(3);
cell.SetCellValue("竞拍次数");
cell.CellStyle = celStyle;
cell = row.CreateCell(4);
cell.SetCellValue("是否中奖");
cell.CellStyle = celStyle;
}
public void Test58043()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle cellStyle = wb.CreateCellStyle() as HSSFCellStyle;
Assert.AreEqual(0, cellStyle.Rotation);
cellStyle.Rotation = ((short)89);
Assert.AreEqual(89, cellStyle.Rotation);
cellStyle.Rotation = ((short)90);
Assert.AreEqual(90, cellStyle.Rotation);
cellStyle.Rotation = ((short)-1);
Assert.AreEqual(-1, cellStyle.Rotation);
cellStyle.Rotation = ((short)-89);
Assert.AreEqual(-89, cellStyle.Rotation);
cellStyle.Rotation = ((short)-90);
Assert.AreEqual(-90, cellStyle.Rotation);
cellStyle.Rotation = ((short)-89);
Assert.AreEqual(-89, cellStyle.Rotation);
// values above 90 are mapped to the correct values for compatibility between HSSF and XSSF
cellStyle.Rotation = ((short)179);
Assert.AreEqual(-89, cellStyle.Rotation);
cellStyle.Rotation = ((short)180);
Assert.AreEqual(-90, cellStyle.Rotation);
wb.Close();
}
//条件样式
public static HSSFCellStyle getConditionStytle(HSSFWorkbook book)
{
HSSFCellStyle cellstyle = (HSSFCellStyle)book.CreateCellStyle();
//居中
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
//边框
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
//背景颜色
cellstyle.FillPattern = FillPattern.Squares;
cellstyle.FillPattern = FillPattern.SolidForeground;
cellstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
//字体
HSSFFont font = (NPOI.HSSF.UserModel.HSSFFont)book.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 14;
//font.Boldweight = 12 * 240;
cellstyle.SetFont(font);
return(cellstyle);
}
public void TestWriteDataFormat()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet s1 = wb1.CreateSheet() as HSSFSheet;
HSSFDataFormat format = wb1.CreateDataFormat() as HSSFDataFormat;
HSSFCellStyle cs = wb1.CreateCellStyle() as HSSFCellStyle;
short df = format.GetFormat("0.0");
cs.DataFormat = (df);
HSSFCell c1 = s1.CreateRow(0).CreateCell(0) as HSSFCell;
c1.CellStyle = (cs);
c1.SetCellValue(1.25);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
HSSFSheet s2 = wb2.GetSheetAt(0) as HSSFSheet;
HSSFCell c2 = s2.GetRow(0).GetCell(0) as HSSFCell;
format = wb2.CreateDataFormat() as HSSFDataFormat;
Assert.AreEqual(1.25, c2.NumericCellValue, 1e-10);
Assert.AreEqual(format.GetFormat(df), "0.0");
Assert.AreEqual(format, wb2.CreateDataFormat());
wb2.Close();
wb1.Close();
}
private void SetStyleForRow()
{
#region 标题行
cellstyleForOneRow = (HSSFCellStyle)WorkBook.CreateCellStyle();
HSSFFont font = (HSSFFont)WorkBook.CreateFont();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 15;
cellstyleForOneRow.SetFont(font);
#endregion
#region 校验失败行
cellstyleForVerifyFailed = (HSSFCellStyle)WorkBook.CreateCellStyle();
cellstyleForVerifyFailed.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
cellstyleForVerifyFailed.FillPattern = FillPatternType.SOLID_FOREGROUND;
HSSFFont font1 = (HSSFFont)WorkBook.CreateFont();
font1.Color = HSSFColor.BLACK.index;
font1.FontHeightInPoints = 12;
cellstyleForVerifyFailed.SetFont(font1);
#endregion
#region 校验成功行
cellstyleForVerifySuccess = (HSSFCellStyle)WorkBook.CreateCellStyle();
cellstyleForVerifySuccess.FillForegroundColor = HSSFColor.SKY_BLUE.index;
cellstyleForVerifySuccess.FillPattern = FillPatternType.SOLID_FOREGROUND;
HSSFFont font2 = (HSSFFont)WorkBook.CreateFont();
font2.Color = HSSFColor.BLACK.index;
font2.FontHeightInPoints = 12;
cellstyleForVerifySuccess.SetFont(font2);
#endregion
}
/// <summary>
/// MoreData导出
/// </summary>
private void MoreDataHandle(List <MoreDataModel> list)
{
int dataNum = list.Count(); //数据量
HSSFSheet sheet = moreSheet;
HSSFRow row;
HSSFCell cell;
HSSFCellStyle cellStyle = getCellStyle();
HSSFCellStyle timeCellStyle = getTimeCellStyle();
for (int i = 0; i < dataNum; i++)
{
var item = list[i];
row = sheet.CreateRow(i + 1) as HSSFRow;
cell = row.CreateCell(0) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.pId);
cell = row.CreateCell(1) as HSSFCell;
cell.CellStyle = timeCellStyle;
cell.SetCellValue(item.registTime);
cell = row.CreateCell(2) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.rechargeMoney);
cell = row.CreateCell(3) as HSSFCell;
cell.CellStyle = timeCellStyle;
cell.SetCellValue(item.firstRechargeTime);
cell = row.CreateCell(4) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.rechargeCount);
cell = row.CreateCell(5) as HSSFCell;
cell.CellStyle = timeCellStyle;
cell.SetCellValue(item.firstAuctionGoodsTime);
cell = row.CreateCell(6) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.firstAuctionGoodsName);
cell = row.CreateCell(7) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.firstAuctionGoodsCount);
cell = row.CreateCell(8) as HSSFCell;
cell.CellStyle = timeCellStyle;
cell.SetCellValue(item.lastLoginTime);
cell = row.CreateCell(9) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.isWinPrizeForTheGoods ? "√" : "×");
cell = row.CreateCell(10) as HSSFCell;
cell.CellStyle = cellStyle;
cell.SetCellValue(item.channel);
}
}
private static HSSFCellStyle GetDateStyle(HSSFWorkbook workbook)
{
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");
return(dateStyle);
}
private HSSFCellStyle getTimeCellStyle()
{
HSSFCellStyle cellStyle = getCellStyle();
HSSFDataFormat format = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
cellStyle.DataFormat = format.GetFormat("yyyy.MM.dd HH:mm:ss");
return(cellStyle);
}
