private void buildWorkbook(IWorkbook wb)
{
ISheet sh = wb.CreateSheet();
IRow row1 = sh.CreateRow(0);
IRow row2 = sh.CreateRow(1);
row3 = sh.CreateRow(2);
row1.CreateCell(0, CellType.Numeric);
row1.CreateCell(1, CellType.Numeric);
row2.CreateCell(0, CellType.Numeric);
row2.CreateCell(1, CellType.Numeric);
row3.CreateCell(0);
row3.CreateCell(1);
CellReference a1 = new CellReference("A1");
CellReference a2 = new CellReference("A2");
CellReference b1 = new CellReference("B1");
CellReference b2 = new CellReference("B2");
sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(35);
sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(0);
sh.GetRow(b1.Row).GetCell(b1.Col).CellFormula = (/*setter*/"A1/A2");
sh.GetRow(b2.Row).GetCell(b2.Col).CellFormula = (/*setter*/"NA()");
Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
}
private void FillData(IWorkbook p_wb)
{
ISheet sheet = p_wb.CreateSheet("sheet123");
sheet.RowSumsBelow = (/*setter*/false);
int i;
for (i = 0; i < ROWS_NUMBER; i++)
{
IRow row = sheet.CreateRow(i);
ICell cell = row.CreateCell(0);
cell.SetCellValue(i + 1);
}
i = 1;
while (i < ROWS_NUMBER)
{
int end = i + (GROUP_SIZE - 2);
int start = i; // natural order
// int start = end - 1; // reverse order
while (start < end)
{ // natural order
// while (start >= i) { // reverse order
sheet.GroupRow(start, end);
//o_groupsNumber++;
bool collapsed = IsCollapsed();
//System.out.Println("Set group " + start + "->" + end + " to " + collapsed);
sheet.SetRowGroupCollapsed(start, collapsed);
start++; // natural order
// start--; // reverse order
}
i += GROUP_SIZE;
}
}
public bool Init()
{
if (!File.Exists(m_StrategyName))
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
IRow Row = m_StrategySheet.CreateRow(0);
Row.CreateCell(0).SetCellValue("-500");
Row.CreateCell(1).SetCellValue("-450");
Row.CreateCell(2).SetCellValue("-400");
Row.CreateCell(3).SetCellValue("-350");
Row.CreateCell(4).SetCellValue("-300");
Row.CreateCell(5).SetCellValue("-250");
Row.CreateCell(6).SetCellValue("-200");
Row.CreateCell(7).SetCellValue("-150");
Row.CreateCell(8).SetCellValue("-100");
Row.CreateCell(9).SetCellValue("-50");
Row.CreateCell(10).SetCellValue("0");
Row.CreateCell(11).SetCellValue("50");
Row.CreateCell(12).SetCellValue("100");
Row.CreateCell(13).SetCellValue("150");
Row.CreateCell(14).SetCellValue("200");
Row.CreateCell(15).SetCellValue("250");
Row.CreateCell(16).SetCellValue("300");
Row.CreateCell(17).SetCellValue("350");
Row.CreateCell(18).SetCellValue("400");
Row.CreateCell(19).SetCellValue("450");
Row.CreateCell(20).SetCellValue("500");
return true;
}
return false;
}
private void Confirm(IWorkbook wb)
{
ISheet sheet = wb.CreateSheet("new sheet");
cell11 = sheet.CreateRow(0).CreateCell(0);
cell11.SetCellType(CellType.Formula);
Confirm("PROPER(\"hi there\")", "Hi There");
Confirm("PROPER(\"what's up\")", "What'S Up");
Confirm("PROPER(\"I DON'T TH!NK SO!\")", "I Don'T Th!Nk So!");
Confirm("PROPER(\"dr\u00dcb\u00f6'\u00e4 \u00e9lo\u015f|\u00eb\u00e8 \")", "Dr\u00fcb\u00f6'\u00c4 \u00c9lo\u015f|\u00cb\u00e8 ");
Confirm("PROPER(\"hi123 the123re\")", "Hi123 The123Re");
Confirm("PROPER(\"-\")", "-");
Confirm("PROPER(\"!\u00a7$\")", "!\u00a7$");
Confirm("PROPER(\"/&%\")", "/&%");
// also test longer string
StringBuilder builder = new StringBuilder("A");
StringBuilder expected = new StringBuilder("A");
for (int i = 1; i < 254; i++)
{
builder.Append((char)(65 + (i % 26)));
expected.Append((char)(97 + (i % 26)));
}
Confirm("PROPER(\"" + builder.ToString() + "\")", expected.ToString());
}
public SheetWriter(IWorkbook wb)
{
ISheet sheet = wb.CreateSheet("Sheet1");
WriteHeaderRow(wb, sheet);
_sheet = sheet;
_rowIndex = 1;
}
public EntryKillsTeamSheet(IWorkbook workbook, Demo demo)
{
Headers = new Dictionary<string, CellType>(){
{ "Name", CellType.String },
{ "Total", CellType.Numeric },
{ "Win", CellType.Numeric },
{ "Loss", CellType.Numeric },
{ "Ratio", CellType.String }
};
Demo = demo;
Sheet = workbook.CreateSheet("Entry Kills Teams");
}
public OpenKillsPlayerSheet(IWorkbook workbook, Demo demo)
{
Headers = new Dictionary<string, CellType>(){
{ "Name", CellType.String },
{ "SteamID", CellType.String },
{ "Total", CellType.Numeric },
{ "Win", CellType.Numeric },
{ "Loss", CellType.Numeric },
{ "Ratio", CellType.String }
};
Demo = demo;
Sheet = workbook.CreateSheet("Open Kills Players");
}
public PlayersSheet(IWorkbook workbook, Demo demo)
{
Headers = new Dictionary<string, CellType>(){
{ "Name", CellType.String },
{ "SteamID", CellType.String },
{ "Rank", CellType.Numeric },
{ "Team", CellType.String },
{ "Kills", CellType.Numeric },
{ "Assists", CellType.Numeric },
{ "Deaths", CellType.Numeric },
{ "K/D", CellType.Numeric },
{ "HS", CellType.Numeric },
{ "HS%", CellType.Numeric },
{ "Team kill", CellType.Numeric },
{ "Entry kill", CellType.Numeric },
{ "Bomb planted", CellType.Numeric },
{ "Bomb defused", CellType.Numeric },
{ "MVP", CellType.Numeric },
{ "Score", CellType.Numeric },
{ "Rating", CellType.Numeric },
{ "KPR", CellType.Numeric },
{ "APR", CellType.Numeric },
{ "DPR", CellType.Numeric },
{ "ADR", CellType.Numeric },
{ "TDH", CellType.Numeric },
{ "TDA", CellType.Numeric },
{ "5K", CellType.Numeric },
{ "4K", CellType.Numeric },
{ "3K", CellType.Numeric },
{ "2K", CellType.Numeric },
{ "1K", CellType.Numeric },
{ "Crouch kill", CellType.Numeric },
{ "Jump kill", CellType.Numeric },
{ "1v1", CellType.Numeric },
{ "1v2", CellType.Numeric },
{ "1v3", CellType.Numeric },
{ "1v4", CellType.Numeric },
{ "1v5", CellType.Numeric },
{ "Flashbang", CellType.Numeric },
{ "Smoke", CellType.Numeric },
{ "HE", CellType.Numeric },
{ "Decoy", CellType.Numeric },
{ "Molotov", CellType.Numeric },
{ "Incendiary", CellType.Numeric },
{ "VAC", CellType.Boolean },
{ "OW", CellType.Boolean },
};
Demo = demo;
Sheet = workbook.CreateSheet("Players");
}
public EntryKillsRoundSheet(IWorkbook workbook, Demo demo)
{
Headers = new Dictionary<string, CellType>(){
{ "Number", CellType.Numeric },
{ "Killer Name", CellType.String},
{ "Killer SteamID", CellType.String },
{ "Killed Name", CellType.String },
{ "Killed SteamID", CellType.String },
{ "Weapon", CellType.String },
{ "Result", CellType.String }
};
Demo = demo;
Sheet = workbook.CreateSheet("Entry Kills Rounds");
}
public GeneralSheet(IWorkbook workbook, List<Demo> demos)
{
Headers = new Dictionary<string, CellType>(){
{ "Filename", CellType.String },
{ "Type", CellType.String },
{ "Source", CellType.String },
{ "Map", CellType.String },
{ "Hostname", CellType.String },
{ "Client", CellType.String },
{ "Server Tickrate", CellType.Numeric },
{ "Framerate", CellType.Numeric },
{ "Duration", CellType.Numeric },
{ "Name team 1", CellType.String },
{ "Name team 2", CellType.String },
{ "Score team 1", CellType.Numeric },
{ "Score team 2", CellType.Numeric },
{ "Score 1st half team 1", CellType.Numeric },
{ "Score 1st half team 2", CellType.Numeric },
{ "Score 2nd half team 1", CellType.Numeric },
{ "Score 2nd half team 2", CellType.Numeric },
{ "Winner", CellType.String },
{ "Kills", CellType.Numeric },
{ "Assists", CellType.Numeric },
{ "5K", CellType.Numeric },
{ "4K", CellType.Numeric },
{ "3K", CellType.Numeric },
{ "2K", CellType.Numeric },
{ "1K", CellType.Numeric },
{ "Average Damage Per Round", CellType.Numeric },
{ "Total Damage Health", CellType.Numeric },
{ "Total Damage Armor", CellType.Numeric },
{ "Clutch", CellType.Numeric },
{ "Bomb Defused", CellType.Numeric },
{ "Bomb Exploded", CellType.Numeric },
{ "Bomb Planted", CellType.Numeric },
{ "Flashbang", CellType.Numeric },
{ "Smoke", CellType.Numeric },
{ "HE", CellType.Numeric },
{ "Decoy", CellType.Numeric },
{ "Molotov", CellType.Numeric },
{ "Incendiary", CellType.Numeric },
{ "Shots", CellType.Numeric },
{ "Hits", CellType.Numeric },
{ "Round", CellType.Numeric },
{ "Comment", CellType.String },
{ "Cheater", CellType.Boolean }
};
Demos = demos;
Sheet = workbook.CreateSheet("General");
}
public WeaponsSheet(IWorkbook workbook, List<Demo> demos)
{
Headers = new Dictionary<string, CellType>()
{
{"Name", CellType.String},
{"Kills", CellType.Numeric},
{"Damage health", CellType.Numeric},
{"Damage armor", CellType.Numeric},
{"Shots", CellType.Numeric},
{"Hits", CellType.Numeric},
{"Accuracy %", CellType.Numeric}
};
Demos = demos;
Sheet = workbook.CreateSheet("Weapons");
}
public void Init()
{
if (!File.Exists(m_sFileName))
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
IRow Row = m_StrategySheet.CreateRow(0);
int nCount = 0;
foreach(KeyValuePair<int, int> kp in m_DicLetter)
{
Row.CreateCell(nCount).SetCellValue(kp.Key);
nCount++;
}
}
}
public void LoadExcel()
{
m_OrderLogWorkBook = new XSSFWorkbook();
m_OrderLogSheet = (ISheet)m_OrderLogWorkBook.CreateSheet("Sheet1");
IRow Row = m_OrderLogSheet.CreateRow(0);
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("其他");
}
public TeamsSheet(IWorkbook workbook, List<Demo> demos)
{
Headers = new Dictionary<string, CellType>()
{
{"Name", CellType.String},
{"Match", CellType.Numeric},
{"Win", CellType.Numeric},
{"Lost", CellType.Numeric},
{"Kills", CellType.Numeric},
{"Deaths", CellType.Numeric},
{"Assists", CellType.Numeric},
{"Rounds", CellType.Numeric},
{"Round win", CellType.Numeric},
{"Round lost", CellType.Numeric},
{"Round CT win", CellType.Numeric},
{"Round CT lost", CellType.Numeric},
{"Round T win", CellType.Numeric},
{"Round T lost", CellType.Numeric},
{"Win pistol round", CellType.Numeric},
{"Win eco round", CellType.Numeric},
{"Win semi-eco round", CellType.Numeric},
{"Win force-buy round", CellType.Numeric},
{"Bomb planted", CellType.Numeric},
{"Bomb defused", CellType.Numeric},
{"Bomb exploded", CellType.Numeric},
{"Bomb planted on A", CellType.Numeric},
{"Bomb planted on B", CellType.Numeric},
{"5K", CellType.Numeric},
{"4K", CellType.Numeric},
{"3K", CellType.Numeric},
{"2K", CellType.Numeric},
{"1K", CellType.Numeric},
{"Jump kill", CellType.Numeric},
{"Crouch kill", CellType.Numeric},
{"Flash", CellType.Numeric},
{"HE", CellType.Numeric},
{"Smoke", CellType.Numeric},
{"Molotov", CellType.Numeric},
{"Incendiary", CellType.Numeric},
{"Decoy", CellType.Numeric},
};
Demos = demos;
Sheet = workbook.CreateSheet("Teams");
}
public SheetLimits(IWorkbook workbook, string date)
{
this.workbook = workbook;
this.date = date;
sheet = workbook.CreateSheet(date + "-Лимиты");
summedCells = new List<int>();
cellStyles = new CellStyles(workbook);
styleInnerHeading = cellStyles.InnerHeadline();
styleHeading = cellStyles.Heading();
styleHeadingSum = cellStyles.HeadingSum();
styleHeadline = cellStyles.Headline();
styleTable = cellStyles.LimitsTable();
styleDouble = cellStyles.LimitsDouble();
styleDoubleBold = cellStyles.LimitsDoubleBold();
styleCorpColor = cellStyles.CorpColor();
styleLimitsDoubleSum = cellStyles.LimitsDoubleSum();
styleResultHeadline = cellStyles.ResultHeadline();
}
public void TestRemoveArrayFormula()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2);
Assert.AreEqual("C4:C6", range.FormatAsString());
ICellRange <ICell> cr = sheet.SetArrayFormula("SUM(A1:A3*B1:B3)", range);
Assert.AreEqual(3, cr.Size);
// remove the formula cells in C4:C6
ICellRange <ICell> dcells = sheet.RemoveArrayFormula(cr.TopLeftCell);
// RemoveArrayFormula should return the same cells as SetArrayFormula
Assert.IsTrue(Arrays.Equals(cr.FlattenedCells, dcells.FlattenedCells));
foreach (ICell acell in cr)
{
Assert.IsFalse(acell.IsPartOfArrayFormulaGroup);
Assert.AreEqual(CellType.Blank, acell.CellType);
}
// cells C4:C6 are not included in array formula,
// invocation of sheet.RemoveArrayFormula on any of them throws ArgumentException
foreach (ICell acell in cr)
{
try
{
sheet.RemoveArrayFormula(acell);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
String ref1 = new CellReference(acell).FormatAsString();
Assert.AreEqual("Cell " + ref1 + " is not part of an array formula.", e.Message);
}
}
}
public void TestFormulaStyle()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet("testSheet1");
IRow r = null;
ICell c = null;
ICellStyle cs = wb.CreateCellStyle();
IFont f = wb.CreateFont();
f.FontHeightInPoints = 20;
f.Color = (IndexedColors.RED.Index);
f.Boldweight = (int)FontBoldWeight.BOLD;
f.FontName = "Arial Unicode MS";
cs.FillBackgroundColor = 3;
cs.SetFont(f);
cs.BorderTop = BorderStyle.THIN;
cs.BorderRight = BorderStyle.THIN;
cs.BorderLeft = BorderStyle.THIN;
cs.BorderBottom = BorderStyle.THIN;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellStyle = cs;
c.CellFormula = ("2*3");
wb = _testDataProvider.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue((c.CellType == CellType.FORMULA), "Formula Cell at 0,0");
cs = c.CellStyle;
Assert.IsNotNull(cs, "Formula Cell Style");
Assert.IsTrue((cs.FontIndex == f.Index), "Font Index Matches");
Assert.IsTrue((cs.BorderTop == BorderStyle.THIN), "Top Border");
Assert.IsTrue((cs.BorderLeft == BorderStyle.THIN), "Left Border");
Assert.IsTrue((cs.BorderRight == BorderStyle.THIN), "Right Border");
Assert.IsTrue((cs.BorderBottom == BorderStyle.THIN), "Bottom Border");
}
public void TestCopyHyperlink()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper createHelper = wb.GetCreationHelper();
ISheet sheet = wb.CreateSheet("Hyperlinks");
IRow row = sheet.CreateRow(0);
ICell cell1, cell2;
IHyperlink link1, link2;
//URL
cell1 = row.CreateCell(0);
cell2 = row.CreateCell(1);
cell1.SetCellValue("URL Link");
link1 = createHelper.CreateHyperlink(HyperlinkType.Url);
link1.Address = ("http://poi.apache.org/");
cell1.Hyperlink = (link1);
link2 = CopyHyperlink(link1);
// Change address (type is not changeable)
link2.Address = ("http://apache.org/");
cell2.Hyperlink = (link2);
// Make sure hyperlinks were deep-copied, and modifying one does not modify the other.
Assert.AreNotSame(link1, link2);
Assert.AreNotEqual(link1, link2);
Assert.AreEqual("http://poi.apache.org/", link1.Address);
Assert.AreEqual("http://apache.org/", link2.Address);
Assert.AreEqual(link1, cell1.Hyperlink);
Assert.AreEqual(link2, cell2.Hyperlink);
// Make sure both hyperlinks were added to the sheet
List <IHyperlink> actualHyperlinks = sheet.GetHyperlinkList();
Assert.AreEqual(2, actualHyperlinks.Count);
Assert.AreEqual(link1, actualHyperlinks[0]);
Assert.AreEqual(link2, actualHyperlinks[1]);
wb.Close();
}
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceDs">要导出数据的DataSet</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="excelType">工资表类型</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataSetToExcel(DataSet sourceDs, ExcelType excelType, String sheetName)
{
IWorkbook workbook = GetIWorkbook(excelType);
MemoryStream ms = new MemoryStream();
String[] sheetNames = sheetName.Split(',');
for (Int32 i = 0; i < sheetNames.Length; i++)
{
ISheet sheet = workbook.CreateSheet(sheetNames[i]);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
Int32 rowIndex = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
workbook = null;
return(ms);
}
public void TestFormulaStyle()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet("testSheet1");
IRow r = null;
ICell c = null;
ICellStyle cs = wb.CreateCellStyle();
IFont f = wb.CreateFont();
f.FontHeightInPoints = 20;
f.Color = (HSSFColor.Red.Index);
f.Boldweight = (int)FontBoldWeight.Bold;
f.FontName = "Arial Unicode MS";
cs.FillBackgroundColor = 3;
cs.SetFont(f);
cs.BorderTop = BorderStyle.Thin;
cs.BorderRight = BorderStyle.Thin;
cs.BorderLeft = BorderStyle.Thin;
cs.BorderBottom = BorderStyle.Thin;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellStyle = cs;
c.CellFormula = ("2*3");
wb = _testDataProvider.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.AreEqual(c.CellType, CellType.Formula, "Formula Cell at 0,0");
cs = c.CellStyle;
Assert.IsNotNull(cs, "Formula Cell Style");
Assert.AreEqual(f.Index, cs.FontIndex, "Font Index Matches");
Assert.AreEqual(BorderStyle.Thin, cs.BorderTop, "Top Border");
Assert.AreEqual(BorderStyle.Thin, cs.BorderLeft, "Left Border");
Assert.AreEqual(BorderStyle.Thin, cs.BorderRight, "Right Border");
Assert.AreEqual(BorderStyle.Thin, cs.BorderBottom, "Bottom Border");
}
private ErrorInfo SaveExcelSheet(int getudoyyyymm, List <DailyAttendanceData> item, IWorkbook workbook_old, out IWorkbook workbook)
{
workbook = workbook_old;
var errorinfo = new ErrorInfo();
try
{
var sheet = workbook.CreateSheet(getudoyyyymm.ToString());
int rowcount = item.Count;
this.GetHeaderAttendanceData(sheet);
for (int i = 0; i < rowcount; i++)
{
var record = new DailyAttendanceData();
IRow row = sheet.CreateRow(i + 1);
ICell cell_GetudoYYYYMM = row.CreateCell(0);
cell_GetudoYYYYMM.SetCellValue(item[i].GetudoYYYYMM);
ICell cell_Day = row.CreateCell(1);
cell_Day.SetCellValue(item[i].Day);
ICell cell_DayKBN = row.CreateCell(2);
cell_DayKBN.SetCellValue((int)item[i].DayKBN);
ICell cell_PlanMMSS_Start = row.CreateCell(3);
cell_PlanMMSS_Start.SetCellValue(item[i].PlanMMSS_Start.ToString());
ICell cell_PlanMMSS_END = row.CreateCell(4);
cell_PlanMMSS_END.SetCellValue(item[i].PlanMMSS_END.ToString());
ICell cell_ResultMMSS_Start = row.CreateCell(5);
cell_ResultMMSS_Start.SetCellValue(item[i].ResultMMSS_Start.ToString());
ICell cell_ResultMMSS_END = row.CreateCell(6);
cell_ResultMMSS_END.SetCellValue(item[i].ResultMMSS_END.ToString());
ICell cell_Bikou = row.CreateCell(7);
cell_Bikou.SetCellValue(item[i].Bikou);
}
}
catch (Exception ex)
{
errorinfo.HasError = true;
errorinfo.ErrorReason = ex.Message;
}
return(errorinfo);
}
public void TestAutoCreateOtherCells()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow row1 = sheet.CreateRow(0);
ICell cellA1 = row1.CreateCell(0);
ICell cellB1 = row1.CreateCell(1);
String formula = "42";
sheet.SetArrayFormula(formula, CellRangeAddress.ValueOf("A1:B2"));
Assert.AreEqual(formula, cellA1.CellFormula);
Assert.AreEqual(formula, cellB1.CellFormula);
IRow row2 = sheet.GetRow(1);
Assert.IsNotNull(row2);
Assert.AreEqual(formula, row2.GetCell(0).CellFormula);
Assert.AreEqual(formula, row2.GetCell(1).CellFormula);
workbook.Close();
}
public MapsSheet(IWorkbook workbook, List <Demo> demos)
{
Headers = new Dictionary <string, CellType>()
{
{ "Name", CellType.String },
{ "Match", CellType.Numeric },
{ "Round", CellType.Numeric },
{ "Round win CT", CellType.Numeric },
{ "Round win T", CellType.Numeric },
{ "Round win pistol round", CellType.Numeric },
{ "Round win eco", CellType.Numeric },
{ "Round win Semi-eco", CellType.Numeric },
{ "Round win force-buy", CellType.Numeric },
{ "Bomb planted", CellType.Numeric },
{ "Bomb defused", CellType.Numeric },
{ "Bomb exploded", CellType.Numeric },
{ "Bomb planted on A", CellType.Numeric },
{ "Bomb planted on B", CellType.Numeric }
};
Demos = demos;
Sheet = workbook.CreateSheet("Maps");
}
public void TestMergedCells()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow row = sheet.CreateRow(0);
sheet.AddMergedRegion(CellRangeAddress.ValueOf("A1:B1"));
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("Apache Software Foundation");
int defaulWidth = sheet.GetColumnWidth(0);
sheet.AutoSizeColumn(0);
// column is unChanged if merged regions are ignored (Excel like behavior)
Assert.AreEqual(defaulWidth, sheet.GetColumnWidth(0));
sheet.AutoSizeColumn(0, true);
Assert.IsTrue(sheet.GetColumnWidth(0) > defaulWidth);
}
public MapsSheet(IWorkbook workbook, List<Demo> demos)
{
Headers = new Dictionary<string, CellType>()
{
{"Name", CellType.String},
{"Match", CellType.Numeric},
{"Round", CellType.Numeric},
{"Round win CT", CellType.Numeric},
{"Round win T", CellType.Numeric},
{"Round win pistol round", CellType.Numeric},
{"Round win eco", CellType.Numeric},
{"Round win Semi-eco", CellType.Numeric},
{"Round win force-buy", CellType.Numeric},
{"Bomb planted", CellType.Numeric},
{"Bomb defused", CellType.Numeric},
{"Bomb exploded", CellType.Numeric},
{"Bomb planted on A", CellType.Numeric},
{"Bomb planted on B", CellType.Numeric}
};
Demos = demos;
Sheet = workbook.CreateSheet("Maps");
}
/// <summary>
///
/// </summary>
/// <param name="workbook"></param>
/// <param name="dataTable"></param>
/// <param name="setTableHead"></param>
/// <returns></returns>
public ISheet DataTableToSheet(IWorkbook workbook, DataTable dataTable, Func <IWorkbook, ISheet, int> setTableHead = null)
{
var sheet = dataTable.TableName.MIsNullOrEmpty() ? workbook.CreateSheet() : workbook.CreateSheet(dataTable.TableName);
var rowNum = 0;
if (setTableHead != null)
{
rowNum = setTableHead(workbook, sheet);
}
var columnNum = dataTable.Columns.Count;
foreach (DataRow dr in dataTable.Rows)
{
var row = sheet.CreateRow(rowNum++);
for (var i = 0; i < columnNum; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(dr[i].ToString());
}
}
return(sheet);
}
public void TestExternalFunctions()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet();
ICell cell1 = sh.CreateRow(0).CreateCell(0);
cell1.CellFormula = ("ISODD(1)+ISEVEN(2)"); // functions from the Excel Analysis Toolpack
Assert.AreEqual("ISODD(1)+ISEVEN(2)", cell1.CellFormula);
ICell cell2 = sh.CreateRow(1).CreateCell(0);
try
{
cell2.CellFormula = ("MYBASEEXTFUNC(\"B1\")");
Assert.Fail("Should fail because MYBASEEXTFUNC is an unknown function");
}
catch (FormulaParseException)
{
; //expected
}
wb.AddToolPack(customToolpack);
cell2.CellFormula = ("MYBASEEXTFUNC(\"B1\")");
Assert.AreEqual("MYBASEEXTFUNC(\"B1\")", cell2.CellFormula);
ICell cell3 = sh.CreateRow(2).CreateCell(0);
cell3.CellFormula = ("MYBASEEXTFUNC2(\"C1\")&\"-\"&A2"); //where A2 is defined above
Assert.AreEqual("MYBASEEXTFUNC2(\"C1\")&\"-\"&A2", cell3.CellFormula);
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(2.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual("B1abc", Evaluator.Evaluate(cell2).StringValue);
Assert.AreEqual("C1abc2-B1abc", Evaluator.Evaluate(cell3).StringValue);
}
/// <summary>
/// DataTable转成Excel文件
/// </summary>
/// <param name="SourceTable"></param>
/// <returns></returns>
public static void DataTableToExcel(DataTable dt, string ExcelPath)
{
IWorkbook workbook = CreateWorkbook(ExcelPath);
if (workbook == null)
{
return;
}
ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);
//建立Eexcel表头行
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//数据
int rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
if (!Directory.Exists(Path.GetDirectoryName(ExcelPath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(ExcelPath));
}
using (FileStream file = new FileStream(ExcelPath, FileMode.Create, FileAccess.Write))
{
workbook.Write(file);
file.Flush();
}
}
public bool Init()
{
if (!File.Exists(m_StrategyName))
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
IRow Row = m_StrategySheet.CreateRow(0);
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("近2年次數");
Row.CreateCell(7).SetCellValue("近2年成功率");
Row.CreateCell(8).SetCellValue("近2年期望值");
Row.CreateCell(9).SetCellValue("多/空");
return true;
}
return false;
}
private void Confirm(IWorkbook wb)
{
ISheet sheet = wb.CreateSheet("new sheet");
cell11 = sheet.CreateRow(0).CreateCell(0);
cell11.SetCellType(CellType.Formula);
Confirm("PROPER(\"hi there\")", "Hi There"); //simple case
Confirm("PROPER(\"what's up\")", "What'S Up"); //apostrophes are treated as word breaks
Confirm("PROPER(\"I DON'T TH!NK SO!\")", "I Don'T Th!Nk So!"); //capitalization is ignored, special punctuation is treated as a word break
Confirm("PROPER(\"dr\u00dcb\u00f6'\u00e4 \u00e9lo\u015f|\u00eb\u00e8 \")", "Dr\u00fcb\u00f6'\u00c4 \u00c9lo\u015f|\u00cb\u00e8 ");
Confirm("PROPER(\"hi123 the123re\")", "Hi123 The123Re"); //numbers are treated as word breaks
Confirm("PROPER(\"-\")", "-"); //nothing happens with ascii punctuation that is not upper or lower case
Confirm("PROPER(\"!\u00a7$\")", "!\u00a7$"); //nothing happens with unicode punctuation (section sign) that is not upper or lower case
Confirm("PROPER(\"/&%\")", "/&%"); //nothing happens with ascii punctuation that is not upper or lower case
Confirm("PROPER(\"Apache POI\")", "Apache Poi"); //acronyms are not special
Confirm("PROPER(\" hello world\")", " Hello World"); //leading whitespace is ignored
String scharfes = "\u00df"; //German lowercase eszett, scharfes s, sharp s
Confirm("PROPER(\"stra" + scharfes + "e\")", "Stra" + scharfes + "e");
// CURRENTLY FAILS: result: "SSUnd"+scharfes
// LibreOffice 5.0.3.2 behavior: "Sund"+scharfes
// Excel 2013 behavior: ???
//Confirm("PROPER(\"" + scharfes + "und" + scharfes + "\")", "SSund" + scharfes);
Confirm("PROPER(\"" + scharfes + "und" + scharfes + "\")", "ßund" + scharfes); //should be ß in .net framework
// also test longer string
StringBuilder builder = new StringBuilder("A");
StringBuilder expected = new StringBuilder("A");
for (int i = 1; i < 254; i++)
{
builder.Append((char)(65 + (i % 26)));
expected.Append((char)(97 + (i % 26)));
}
Confirm("PROPER(\"" + builder.ToString() + "\")", expected.ToString());
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dts">数据表集合</param>
/// <param name="type">文件类型</param>
/// <param name="isWriteColumnHeader">是否将列标题写入</param>
/// <returns>Excel</returns>
public static IWorkbook Export(
IEnumerable <DataTable> dts,
FileType type,
bool isWriteColumnHeader)
{
ISheet sheet = null;
IWorkbook workbook = GetWorkbook(type);
dts.ForEach(t =>
{
var index = isWriteColumnHeader ? 1 : 0;
var sheetName = t.TableName.IsNullOrEmpty() ? ("Sheet" + index) : t.TableName;
sheet = workbook.CreateSheet(sheetName);
if (isWriteColumnHeader)
{
var row = sheet.CreateRow(0);
var columnIndex = 0;
for (int i = 0; i < t.Columns.Count; i++)
{
row.CreateCell(columnIndex, CellType.String).SetCellValue(t.Columns[columnIndex].ColumnName);
row.Cells[columnIndex].SetColumnWidth();
columnIndex++;
}
}
for (int i = 0; i < t.Rows.Count; i++)
{
var row = sheet.CreateRow(index);
var columnIndex = 0;
for (int j = 0; j < t.Columns.Count; j++)
{
row.CreateCell(columnIndex, CellType.String).SetCellValue(t.Rows[i][columnIndex].ToString());
row.Cells[columnIndex].SetColumnWidth();
columnIndex++;
}
index++;
}
});
return(workbook);
}
private static void CreateBuildDetailsSheet(IWorkbook workbook, IEnumerable <QueuedBuildStatus> entries)
{
var buildsDetailsSheet = workbook.CreateSheet("Builds details");
var builds = entries.GroupBy(e => e.Id).Select(g => new
{
Id = g.Key,
Elements = g.ToArray()
}).Select(g => new
{
g.Id,
Beginning = g.Elements.Min(e => e.Timestamp),
Ending = g.Elements.Max(e => e.Timestamp),
g.Elements.First().Type,
g.Elements.First().Branch
}).ToArray();
var rowIndex = 0;
var row = buildsDetailsSheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("Build id");
row.CreateCell(1).SetCellValue("Beginning queue timestamp");
row.CreateCell(2).SetCellValue("Ending queue timestamp");
row.CreateCell(3).SetCellValue("Queue duration");
row.CreateCell(4).SetCellValue("Build type");
row.CreateCell(5).SetCellValue("Branch");
foreach (var status in builds)
{
rowIndex++;
var statusRow = buildsDetailsSheet.CreateRow(rowIndex);
statusRow.CreateCell(0).SetCellValue(status.Id);
statusRow.CreateCell(1).SetCellValue(status.Beginning);
statusRow.CreateCell(2).SetCellValue(status.Ending);
statusRow.CreateCell(3).SetCellValue((DateTime.Parse(status.Ending) - DateTime.Parse(status.Beginning)).TotalMinutes);
statusRow.CreateCell(4).SetCellValue(status.Type);
statusRow.CreateCell(5).SetCellValue(status.Branch);
}
}
/// <summary>
/// Writes cell value on a sheet in an Excel file.
/// </summary>
/// <param name="sheetName">Sheet name where the cell is located.</param>
/// <param name="cellName">Cell address.</param>
/// <param name="cellValue">Value to write in cell.</param>
public void CreateExcel(string sheetName, string cellName, string cellValue)
{
if (!File.Exists(filePath))
{
using (FileStream str = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook = new XSSFWorkbook();
excelWSheet = workbook.CreateSheet(sheetName);
var cr = new CellReference(cellName);
row = excelWSheet.GetRow(cr.Row);
cell = row.GetCell(cr.Col);
cell.SetCellValue(cellValue);
workbook.Write(str);
str.Flush();
str.Close();
}
}
else
{
using (FileStream rstr = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(rstr);
excelWSheet = workbook.GetSheet(sheetName);
using (FileStream wstr = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
var cr = new CellReference(cellName);
row = excelWSheet.GetRow(cr.Row);
cell = row.GetCell(cr.Col);
cell.SetCellValue(cellValue);
//Debug.Print(cell.ToString());
workbook.Write(wstr);
wstr.Flush();
wstr.Close();
}
rstr.Close();
}
}
}
/**
* Test writing a file with large number of unique strings,
* open resulting file in Excel to check results!
* @param num the number of strings to generate
*/
public void BaseTest15375(int num)
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
ICreationHelper factory = wb.GetCreationHelper();
String tmp1 = null;
String tmp2 = null;
String tmp3 = null;
for (int i = 0; i < num; i++)
{
tmp1 = "Test1" + i;
tmp2 = "Test2" + i;
tmp3 = "Test3" + i;
IRow row = sheet.CreateRow(i);
ICell cell = row.CreateCell(0);
cell.SetCellValue(factory.CreateRichTextString(tmp1));
cell = row.CreateCell(1);
cell.SetCellValue(factory.CreateRichTextString(tmp2));
cell = row.CreateCell(2);
cell.SetCellValue(factory.CreateRichTextString(tmp3));
}
wb = _testDataProvider.WriteOutAndReadBack(wb);
for (int i = 0; i < num; i++)
{
tmp1 = "Test1" + i;
tmp2 = "Test2" + i;
tmp3 = "Test3" + i;
IRow row = sheet.GetRow(i);
Assert.AreEqual(tmp1, row.GetCell(0).StringCellValue);
Assert.AreEqual(tmp2, row.GetCell(1).StringCellValue);
Assert.AreEqual(tmp3, row.GetCell(2).StringCellValue);
}
}
/// <summary>
/// 导出Excel
/// </summary>
private byte[] Export(ExportOption exportOption, IWorkbook workbook, DataSet dataSet)
{
string[] tableHeaders = exportOption.Header.SplitTextBySemicolonChar();
string[] sheetNames = exportOption.SheetName.SplitTextBySemicolonChar();
string[] titles = exportOption.Title.SplitTextBySemicolonChar();
ICellStyle titleStyle = this.titleStyleFunc(workbook);
ICellStyle headerStyle = this.headerStyleFunc(workbook);
ICellStyle contentStyle = this.styleFunc(workbook);
for (int sheetIndex = 0; sheetIndex < sheetNames.Length; sheetIndex++)
{
ISheet sheet = workbook.CreateSheet(sheetNames[sheetIndex]);
var rowInfo = this.CreateTitleAndHeader(tableHeaders, titles, sheetIndex, sheet, titleStyle, headerStyle);
int rowIndex = rowInfo.headerRowCount + rowInfo.titleRowCount;
this.CreateDataContent(sheetIndex, rowIndex, sheet, contentStyle, dataSet);
}
return(workbook.ConvertWorkBookToBytes());
}
public ISheet FromDataTable(DataTable dt, int FirstRowIndex, bool OutputTitle)
{
Debug.Assert(FirstRowIndex >= 0);
Debug.Assert(dt != null);
//设置SheetName
HSSFSheet sheet = Workbook.CreateSheet() as HSSFSheet;
int sheetidx = Workbook.GetSheetIndex(sheet);
Workbook.SetSheetName(sheetidx, dt.TableName);
int RowIndex = BeforeTitle(sheet);
//输出标题行
RowIndex += FirstRowIndex;
if (OutputTitle)
{
IRow row = sheet.CreateRow(RowIndex);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
RowIndex++;
}
//输出数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(RowIndex);
for (int j = 0; j < dt.Columns.Count; j++)
{
//TODO::需要针对类型转换
ICell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
RowIndex++;
}
AfterFootbar(sheet);
return(sheet);
}
public RoundsSheet(IWorkbook workbook, Demo demo)
{
Headers = new Dictionary<string, CellType>(){
{ "Number", CellType.Numeric },
{ "Tick", CellType.Numeric},
{ "Duration (s)", CellType.Numeric},
{ "Winner Clan Name", CellType.String },
{ "Winner", CellType.String },
{ "End reason", CellType.String },
{ "Type", CellType.String },
{ "Side", CellType.String },
{ "Team", CellType.String },
{ "Kills", CellType.Numeric },
{ "1K", CellType.Numeric },
{ "2K", CellType.Numeric },
{ "3K", CellType.Numeric },
{ "4K", CellType.Numeric },
{ "5K", CellType.Numeric },
{ "Jump kills", CellType.Numeric },
{ "ADP", CellType.Numeric },
{ "TDH", CellType.Numeric },
{ "TDA", CellType.Numeric },
{ "Bomb Exploded", CellType.Numeric },
{ "Bomb planted", CellType.Numeric },
{ "Bomb defused", CellType.Numeric },
{ "Start money team 1", CellType.Numeric },
{ "Start money team 2", CellType.Numeric },
{ "Equipement value team 1", CellType.Numeric },
{ "Equipement value team 2", CellType.Numeric },
{ "Flashbang", CellType.Numeric },
{ "Smoke", CellType.Numeric },
{ "HE", CellType.Numeric },
{ "Decoy", CellType.Numeric },
{ "Molotov", CellType.Numeric },
{ "Incendiary", CellType.Numeric }
};
Demo = demo;
Sheet = workbook.CreateSheet("Rounds");
}
public void TestNanAndInfInity()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet workSheet = wb.CreateSheet("Sheet1");
IRow row = workSheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue(Double.NaN);
Assert.AreEqual(CellType.Error, cell0.CellType, "Double.NaN should change cell type to CELL_TYPE_ERROR");
Assert.AreEqual(FormulaError.NUM, FormulaError.ForInt(cell0.ErrorCellValue), "Double.NaN should change cell value to #NUM!");
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue(Double.PositiveInfinity);
Assert.AreEqual(CellType.Error, cell1.CellType, "Double.PositiveInfinity should change cell type to CELL_TYPE_ERROR");
Assert.AreEqual(FormulaError.DIV0, FormulaError.ForInt(cell1.ErrorCellValue), "Double.POSITIVE_INFINITY should change cell value to #DIV/0!");
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue(Double.NegativeInfinity);
Assert.AreEqual(CellType.Error, cell2.CellType, "Double.NegativeInfinity should change cell type to CELL_TYPE_ERROR");
Assert.AreEqual(FormulaError.DIV0, FormulaError.ForInt(cell2.ErrorCellValue), "Double.NEGATIVE_INFINITY should change cell value to #DIV/0!");
wb = _testDataProvider.WriteOutAndReadBack(wb);
row = wb.GetSheetAt(0).GetRow(0);
cell0 = row.GetCell(0);
Assert.AreEqual(CellType.Error, cell0.CellType);
Assert.AreEqual(FormulaError.NUM, FormulaError.ForInt(cell0.ErrorCellValue));
cell1 = row.GetCell(1);
Assert.AreEqual(CellType.Error, cell1.CellType);
Assert.AreEqual(FormulaError.DIV0, FormulaError.ForInt(cell1.ErrorCellValue));
cell2 = row.GetCell(2);
Assert.AreEqual(CellType.Error, cell2.CellType);
Assert.AreEqual(FormulaError.DIV0, FormulaError.ForInt(cell2.ErrorCellValue));
}
public void ShowInPaneManyRowsBug55248()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet 1");
sheet.ShowInPane(0, 0);
int i;
for (i = ROW_COUNT / 2; i < ROW_COUNT; i++)
{
sheet.CreateRow(i);
sheet.ShowInPane(i, 0);
// this one fails: sheet.ShowInPane((short)i, 0);
}
i = 0;
sheet.ShowInPane(i, i);
IWorkbook wb = _testDataProvider.WriteOutAndReadBack(workbook);
CheckRowCount(wb);
}
public void TestPrintArea()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet1 = workbook.CreateSheet("Test Print Area");
String sheetName1 = sheet1.SheetName;
// workbook.SetPrintArea(0, reference);
workbook.SetPrintArea(0, 1, 5, 4, 9);
String retrievedPrintArea = workbook.GetPrintArea(0);
Assert.AreEqual("'" + sheetName1 + "'!$B$5:$F$10", retrievedPrintArea);
String reference = "$A$1:$B$1";
workbook.SetPrintArea(0, reference);
retrievedPrintArea = workbook.GetPrintArea(0);
Assert.AreEqual("'" + sheetName1 + "'!" + reference, retrievedPrintArea);
workbook.RemovePrintArea(0);
Assert.IsNull(workbook.GetPrintArea(0));
workbook.Close();
}
/// <summary>
/// write objects to worksheet
/// </summary>
/// <typeparam name="T">any type of model</typeparam>
/// <param name="sheetIndex">the worksheet index</param>
/// <param name="sheetName">the worksheet name</param>
/// <param name="objects">the arry of object</param>
public void Draw <T>(int sheetIndex, string sheetName, params T[] objects)
{
if (objects == null)
{
throw new ArgumentNullException("objects");
}
var type = typeof(T);
var objAttr = type.GetCustomAttribute <NPOIObjectAttribute>() ?? new NPOIObjectAttribute();
var drawings = GetColumnDrawings(type);
var sheet = Workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = Workbook.CreateSheet(sheetName);
DrawHeader(drawings, sheet, objAttr.HeaderRowIndex);
}
for (int i = 0; i < objects.Length; i++)
{
var obj = objects[i];
DrawRow(drawings, sheet, objAttr.StartIndex + i, obj);
}
}
public void TestLastCellNumIsCorrectAfterAddCell_bug43901()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet("test");
IRow row = sheet.CreateRow(0);
// New row has last col -1
Assert.AreEqual(-1, row.LastCellNum);
if (row.LastCellNum == 0)
{
Assert.Fail("Identified bug 43901");
}
// Create two cells, will return one higher
// than that for the last number
row.CreateCell(0);
Assert.AreEqual(1, row.LastCellNum);
row.CreateCell(255);
Assert.AreEqual(256, row.LastCellNum);
workbook.Close();
}
public void Test55265()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
try
{
DataFormatter formatter = new DataFormatter();
IDataFormat fmt = wb.CreateDataFormat();
ISheet sheet = wb.CreateSheet();
IRow r = sheet.CreateRow(0);
ICellStyle cs = wb.CreateCellStyle();
cs.DataFormat = (fmt.GetFormat("#'##0"));
ICell zero = r.CreateCell(0);
zero.SetCellValue(0);
zero.CellStyle = (cs);
ICell sml = r.CreateCell(1);
sml.SetCellValue(12);
sml.CellStyle = (cs);
ICell med = r.CreateCell(2);
med.SetCellValue(1234);
med.CellStyle = (cs);
ICell lge = r.CreateCell(3);
lge.SetCellValue(12345678);
lge.CellStyle = (cs);
Assert.AreEqual("0", formatter.FormatCellValue(zero));
Assert.AreEqual("12", formatter.FormatCellValue(sml));
Assert.AreEqual("1'234", formatter.FormatCellValue(med));
Assert.AreEqual("12'345'678", formatter.FormatCellValue(lge));
}
finally {
wb.Close();
}
}
public void TestAutoSize_bug506819()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
BaseTestSheetAutosizeColumn.FixFonts(wb);
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
String longValue = "www.hostname.com, www.hostname.com, " +
"www.hostname.com, www.hostname.com, www.hostname.com, " +
"www.hostname.com, www.hostname.com, www.hostname.com, " +
"www.hostname.com, www.hostname.com, www.hostname.com, " +
"www.hostname.com, www.hostname.com, www.hostname.com, " +
"www.hostname.com, www.hostname.com, www.hostname.com, www.hostname.com";
cell0.SetCellValue(longValue);
sheet.AutoSizeColumn(0);
Assert.AreEqual(255 * 256, sheet.GetColumnWidth(0)); // maximum column width is 255 characters
sheet.SetColumnWidth(0, sheet.GetColumnWidth(0)); // Bug 506819 reports exception at this point
}
private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e)
{
if (SelectedTag.Count == 0 || dataList.datas.Count == 0)
{
return; //测点没选中或者无数据直接return
}
workbook = new XSSFWorkbook(); //创建Workbook对象 XSSFWorkbook 生成格式为.xlsx，HSSFWorkbook生成格式.xls
ISheet sheet = workbook.CreateSheet("HistoryData"); //创建工作表
ICell cell;
sheet.AutoSizeColumn(7, false);
//处理hole表
IRow row = sheet.CreateRow(0);//在工作表中添加一行
cell = row.CreateCell(0);
cell.SetCellValue("DateTime");
for (int i = 0; i < SelectedTag.Count; i++)
{
cell = row.CreateCell(i + 1); //从i+1列开始
cell.SetCellValue(Transformation(SelectedTag[i].Tag)); //设置当前测点的名称
}
//循环将数据写入excel
for (int i = 0; i < dataList.datas[0].Datas.Count; i++)
{
//注意顺序检查
row = sheet.CreateRow(i + 1);//创建一行
for (int j = 0; j < dataList.datas.Count; j++)
{
//创建时间列
if (j == 0)
{
cell = row.CreateCell(j);
cell.SetCellValue(Comm.ConvertIntDateTime(dataList.datas[j].Datas[i].Timestamp * 1000).ToString());
}
cell = row.CreateCell(j + 1);//从第二列开始
cell.SetCellValue(dataList.datas[j].Datas[i].Value);
}
}
}
public void TestRepeatedEvaluation()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow r = sheet.CreateRow(0);
ICell c = r.CreateCell(0, CellType.Formula);
// Create a value and check it
c.CellFormula = (/*setter*/ "Date(2011,10,6)");
CellValue cellValue = fe.Evaluate(c);
Assert.AreEqual(40822.0, cellValue.NumberValue, 0.0);
cellValue = fe.Evaluate(c);
Assert.AreEqual(40822.0, cellValue.NumberValue, 0.0);
// Change it
c.CellFormula = (/*setter*/ "Date(2011,10,4)");
// Evaluate it, no change as the formula Evaluator
// won't know to clear the cache
cellValue = fe.Evaluate(c);
Assert.AreEqual(40822.0, cellValue.NumberValue, 0.0);
// Manually flush for this cell, and check
fe.NotifySetFormula(c);
cellValue = fe.Evaluate(c);
Assert.AreEqual(40820.0, cellValue.NumberValue, 0.0);
// Change again, without Notifying
c.CellFormula = (/*setter*/ "Date(2010,10,4)");
cellValue = fe.Evaluate(c);
Assert.AreEqual(40820.0, cellValue.NumberValue, 0.0);
// Now manually clear all, will see the new value
fe.ClearAllCachedResultValues();
cellValue = fe.Evaluate(c);
Assert.AreEqual(40455.0, cellValue.NumberValue, 0.0);
}
public void TestSheetHidden()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet("MySheet");
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.Hidden);
Assert.IsTrue(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.VeryHidden);
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsTrue(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.Visible);
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
try
{
wb.SetSheetHidden(0, -1);
Assert.Fail("expectd exception");
}
catch (ArgumentException)
{
// ok
}
try
{
wb.SetSheetHidden(0, 3);
Assert.Fail("expectd exception");
}
catch (ArgumentException)
{
// ok
}
}
public void TestRowStyle()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet("test");
IRow row1 = sheet.CreateRow(0);
IRow row2 = sheet.CreateRow(1);
// Won't be styled currently
Assert.AreEqual(false, row1.IsFormatted);
Assert.AreEqual(false, row2.IsFormatted);
Assert.AreEqual(null, row1.RowStyle);
Assert.AreEqual(null, row2.RowStyle);
// Style one
ICellStyle style = workbook.CreateCellStyle();
style.DataFormat = (/*setter*/ (short)4);
row2.RowStyle = (/*setter*/ style);
// Check
Assert.AreEqual(false, row1.IsFormatted);
Assert.AreEqual(true, row2.IsFormatted);
Assert.AreEqual(null, row1.RowStyle);
Assert.AreEqual(style, row2.RowStyle);
// Save, load and re-check
workbook = _testDataProvider.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
row1 = sheet.GetRow(0);
row2 = sheet.GetRow(1);
style = workbook.GetCellStyleAt(style.Index);
Assert.AreEqual(false, row1.IsFormatted);
Assert.AreEqual(true, row2.IsFormatted);
Assert.AreEqual(null, row1.RowStyle);
Assert.AreEqual(style, row2.RowStyle);
Assert.AreEqual(4, style.DataFormat);
}
public PlayersSheet(IWorkbook workbook, List<Demo> demos)
{
Headers = new Dictionary<string, CellType>(){
{ "Name", CellType.String },
{ "SteamID", CellType.String },
{ "Match", CellType.Numeric },
{ "Kills", CellType.Numeric },
{ "Assists", CellType.Numeric },
{ "Deaths", CellType.Numeric },
{ "K/D", CellType.Numeric },
{ "HS", CellType.Numeric },
{ "HS%", CellType.Numeric },
{ "Rounds", CellType.Numeric },
{ "Rating", CellType.Numeric },
{ "5K", CellType.Numeric },
{ "4K", CellType.Numeric },
{ "3K", CellType.Numeric },
{ "2K", CellType.Numeric },
{ "1K", CellType.Numeric },
{ "Team kill", CellType.Numeric },
{ "Jump kill", CellType.Numeric },
{ "Crouch kill", CellType.Numeric },
{ "Bomb planted", CellType.Numeric },
{ "Bomb defused", CellType.Numeric },
{ "Bomb exploded", CellType.Numeric },
{ "MVP", CellType.Numeric },
{ "Score", CellType.Numeric },
{ "Clutch", CellType.Numeric },
{ "Clutch lost", CellType.Numeric },
{ "1v1", CellType.Numeric },
{ "1v2", CellType.Numeric },
{ "1v3", CellType.Numeric },
{ "1v4", CellType.Numeric },
{ "1v5", CellType.Numeric },
{ "Entry kill", CellType.Numeric },
{ "Entry kill win", CellType.Numeric },
{ "Entry kill lost", CellType.Numeric },
{ "Entry kill win %", CellType.Numeric },
{ "Open kill", CellType.Numeric },
{ "Open kill win", CellType.Numeric },
{ "Open kill lost", CellType.Numeric },
{ "Open kill win %", CellType.Numeric },
{ "KPR", CellType.Numeric },
{ "APR", CellType.Numeric },
{ "DPR", CellType.Numeric },
{ "ADR", CellType.Numeric },
{ "TDH", CellType.Numeric },
{ "TDA", CellType.Numeric },
{ "Flashbang thrown", CellType.Numeric },
{ "Smoke thrown", CellType.Numeric },
{ "HE thrown", CellType.Numeric },
{ "Decoy thrown", CellType.Numeric },
{ "Molotov thrown", CellType.Numeric },
{ "Incendiary thrown", CellType.Numeric },
{ "Rank max", CellType.Numeric },
{ "VAC", CellType.Boolean },
{ "OW", CellType.Boolean }
};
Demos = demos;
Sheet = workbook.CreateSheet("Players");
}
private static void AddNewSheetWithCellsA1toD4(IWorkbook book, int sheet)
{
ISheet sht = book.CreateSheet("s" + sheet);
for (int r = 0; r < 4; r++)
{
IRow row = sht.CreateRow(r);
for (int c = 0; c < 4; c++)
{
ICell cel = row.CreateCell(c);
cel.SetCellValue(sheet * 100 + r * 10 + c);
}
}
}
private static ISheet GetOrCreateSheet (IWorkbook workbook, string sheetName)
{
var defaultSheetName = string.Format ("Sheet {0}", workbook.NumberOfSheets + 1);
return workbook.CreateSheet (string.IsNullOrEmpty (sheetName) ? defaultSheetName : sheetName);
}
private static Int32 WriteDataToSheet(Object data, ref IWorkbook wBook, String sheetName, Int32 startColumn, Int32 startRow, out Int32 maxRow)
{
Int32 result = 0; // row affected
Int32 currentRow = startRow;
Int32 currentColumn = startColumn;
maxRow = 0;
// check whether the sheet is already exis or not
if (data != null && wBook != null && !String.IsNullOrWhiteSpace(sheetName))
{
if (wBook.GetSheetIndex(sheetName) < 0)
wBook.CreateSheet(sheetName);
ISheet sheet = wBook.GetSheet(sheetName);
if (sheet != null)
{
if (data.GetType().GetMethod("GetEnumerator") != null )
{
IEnumerator enumerator = (IEnumerator) data.GetType().GetMethod("GetEnumerator").Invoke(data, null);
Int32 count = (Int32) data.GetType().GetProperty("Count").GetValue(data);
Int32 i = 0;
while (enumerator.MoveNext())
{
currentColumn = WriteDataToSheet(enumerator.Current, ref wBook, sheetName, currentColumn, currentRow, out currentRow);
if (i < count - 1)
{
currentRow++;
currentColumn = startColumn;
}
if (currentRow > maxRow)
maxRow = currentRow;
else
currentRow = maxRow;
i++;
}
}
else
{
PropertyInfo [] propInfos = data.GetType().GetProperties();
if (propInfos != null && propInfos.Count() > 0)
{
foreach (var propInfo in propInfos)
{
ColumnName colNameAttribute = propInfo.GetCustomAttribute<ColumnName>(true);
Skipped skippedAttribute = propInfo.GetCustomAttribute<Skipped>(true);
String fieldFormat = (propInfo.GetCustomAttribute<DateFormat>(true) != null) ? propInfo.GetCustomAttribute<DateFormat>(true).Format : "";
String columnName = "";
String fieldName = propInfo.Name;
Type fieldType = propInfo.PropertyType;
Object fieldValue = propInfo.GetValue(data);
if (skippedAttribute == null || (skippedAttribute != null && !skippedAttribute.IsSkipped(sheetName)))
{
if (!SystemTypes.Contains(fieldType) && !fieldType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
Int32 curMaxRow = 0;
currentColumn = WriteDataToSheet(fieldValue, ref wBook, sheetName, currentColumn, currentRow, out curMaxRow);
if (curMaxRow > maxRow)
maxRow = curMaxRow;
}
else
{
// Get Attributes[
if (colNameAttribute != null)
columnName = colNameAttribute.Name;
else
columnName = propInfo.Name;
// write header if this is the first row
if (currentRow == 0)
WriteToCell(ref sheet, currentRow, currentColumn, columnName);
// write the data
if (currentRow == 0)
WriteToCell(ref sheet, currentRow + 1, currentColumn, fieldValue, fieldFormat);
else
WriteToCell(ref sheet, currentRow, currentColumn, fieldValue, fieldFormat);
currentColumn++;
}
}
}
// add current row
if (currentRow == 0)
currentRow = 1; // we must write header and the first data row
if (currentRow > maxRow)
maxRow = currentRow;
}
}
}
result = currentColumn;
}
return result;
}
internal void WriteToCobie(IWorkbook workbook, TextWriter log, string version = "UK2012")
{
var mappings = GetMapping(GetType(), version).ToList();
if (!mappings.Any())
{
log.WriteLine("There are no mappings for a type '{0}'", GetType().Name);
return;
}
//get or create a sheet
var sheetName = GetSheetName(GetType(), version);
var sheet = workbook.GetSheet(sheetName) ?? workbook.CreateSheet(sheetName);
//write columns
foreach (var mapping in mappings)
{
int rowNum;
if (!int.TryParse(mapping.Column, out rowNum))
{
log.WriteLine(
"Metadata are expected to be defined in a single column with numbered rows. This mapping ({0}) doesn't contain row number.",
version);
continue;
}
rowNum--; //convert number to index
var row = sheet.GetRow(rowNum) ?? sheet.CreateRow(rowNum);
//header cell;
var headerCell = row.GetCell(0) ?? row.CreateCell(0);
if(headerCell.CellType == CellType.Blank)
headerCell.SetCellValue(mapping.Header);
//value cell
var cell = row.GetCell(1) ?? row.CreateCell(1);
var info = GetType().GetProperty(mapping.Path);
if (info == null)
{
log.WriteLine("Property {0} is not defined in {1}", mapping.Path, GetType().Name);
continue;
}
var value = (info.GetValue(this) as string) ?? "n/a";
cell.SetCellValue(value);
}
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int MonthlyStatementDataTableToExcel(string strFilename, Dictionary<int, SelfNPOICell[]> data1, Dictionary<int, SelfNPOICell[]> data2)
{
ISheet sheet1 = null;
ISheet sheet2 = null;
string sheetName1 = "Sheet1";
string sheetName2 = "Sheet2";
try
{
if (workbook == null)
{
workbook = new HSSFWorkbook();
sheet1 = workbook.CreateSheet(sheetName1);
sheet2 = workbook.CreateSheet(sheetName2);
}
MSWriteToSheet1(data1, sheet1);
MSWriteToSheet2(data2, sheet2);
var context = HttpContext.Current;
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", strFilename));
//context.Response.Clear();
MemoryStream file = new MemoryStream();
workbook.Write(file); //写入到excel
context.Response.BinaryWrite(file.GetBuffer());
context.Response.End();
return 0;
}
catch (Exception ex)
{
Log.Error(ex.Message);
return -1;
}
}
private void GenerateReport(IWorkbook workbook)
{
JobTracker jobtracker = new JobTracker();
List<JobTracker> projectList = jobtracker.GetDistinctProjectListIncludingForApproval(Convert.ToDateTime(txtBoxFrom.Text+ " 00:00:00"), Convert.ToDateTime(txtBoxTo.Text+" 23:59:59"));
int currentrow = 1;
IDataFormat format = workbook.CreateDataFormat();
ICellStyle fontBoldNoBorder = CreateSheetStyle(workbook, false, false, false, false, true, false, false, false,false);
ICellStyle fontBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
ICellStyle fontCurrencyBoldRigthBottom = CreateSheetStyle(workbook, false, false, true, true, true, false, false, false);
fontCurrencyBoldRigthBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontBoldTopBottom = CreateSheetStyle(workbook, false, true, false, true, true, false, false, false);
fontCurrencyBoldRigthBottom.Alignment = HorizontalAlignment.Center;
fontCurrencyBoldRigthBottom.VerticalAlignment = VerticalAlignment.Center;
ICellStyle fontCurrencyBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
fontBoldAllBorder.Alignment = HorizontalAlignment.Center;
fontCurrencyBoldAllBorder.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontNormalBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
fontBoldAllBorder.VerticalAlignment = VerticalAlignment.Center;
ICellStyle fontNormalBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true,true, false, false, false, false);
ICellStyle fontCurrencyBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
fontCurrencyBorderLeftRight.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontCurrencyBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true, true, false, false, false, false);
fontCurrencyBorderLeftRightBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
//fontNormalBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//fontNormalBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//fontNormalBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//fontCurrencyBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBoldRigthBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
ISheet sheetReport = workbook.GetSheet("Report");
foreach (JobTracker project in projectList)
{
//Running Total Variable
double runningNTApproved = 0;
double runningNTForApproval = 0;
double runningOTApproved = 0;
double runningOTForApproval = 0;
double runningNTApprovedCost = 0;
double runningNTForApprovalCost = 0;
double runningOTApprovedCost = 0;
double runningOTForApprovalCost = 0;
string jobheader = project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : "HW SO: " + project.HWNo;
jobheader += project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : jobheader == "" ? "SW SO: " + project.SWNo : "; SW SO: " + project.SWNo;
jobheader += project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : jobheader == "" ? "EVAL NO: " + project.EvalNo : "; EVAL NO: " + project.EvalNo;
currentrow += 2;
IRow row = sheetReport.CreateRow(currentrow++);
ICell cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue(jobheader);
for (int i = 1; i < 11; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
#region Header Upper Row
row = sheetReport.CreateRow(currentrow);
cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("JobStage");
cell = row.CreateCell(1);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Time");
for (int i = 2; i < 5; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
cell = row.CreateCell(5);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Labor Cost");
for (int i = 6; i < 9; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
cell = row.CreateCell(9);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Total");
cell = row.CreateCell(10);
cell.CellStyle = fontBoldAllBorder;
//Merging
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow -1, currentrow-1, 0, 10));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,1,4));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,5,8));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,9,10));
#endregion
#region Header Lower Row
row = sheetReport.CreateRow(++currentrow);
cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
for (int i = 0; i < 5; i+=4)
{
cell = row.CreateCell(1 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Normal Time(Approved)");
cell = row.CreateCell(2 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Normal Time(For Approval)");
cell = row.CreateCell(3 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Overtime(Approved)");
cell = row.CreateCell(4 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Overtime(For Approved)");
}
cell = row.CreateCell(9);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Time");
cell = row.CreateCell(10);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Cost");
//Merging
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow - 1, currentrow, 0, 0));
#endregion
#region Data
List<JobTracker> uniqueJobType = jobtracker.GetUniqueComputedJobType(project.HWNo == null ? "" : project.HWNo.Trim(), project.HWNo == null ? "" : project.SWNo.Trim());
#region DETAIL Page
ISheet detailSheet = workbook.CreateSheet(jobheader.Replace(":",""));
int detailRowCount = 0;
IRow detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue(jobheader);
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
detailRowCount++;
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Labor Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
detailRow = detailSheet.CreateRow(detailRowCount++);
string[] detailHeader = { "Department","Name", "Job Task", "Start Time", "End Time","Date", "Remarks","Status", "Normal Time","Overtime", "Normal Time Cost","Overtime Cost" };
for (int i = 0; i < 12; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(detailHeader[i]);
}
for (int i = 0; i < uniqueJobType.Count; i++)
{
row = sheetReport.CreateRow(++currentrow);
cell = row.CreateCell(0);
cell.SetCellValue(uniqueJobType[i].jobtype);
if (i < uniqueJobType.Count - 1)
{
cell.CellStyle = fontNormalBorderLeftRight;
}
else
{
cell.CellStyle = fontNormalBorderLeftRightBottom;
}
List<JobTracker> data = jobtracker.GetJobTrackerListWithJobTypeIdHWSO(Convert.ToInt32(uniqueJobType[i].JobTypeId), project.HWNo == null ? "" : project.HWNo.Trim(), project.SWNo == null ? "" : project.SWNo.Trim(), true);
double ntApproved = 0;
double ntForApproval = 0;
double otApproved = 0;
double otForApproval = 0;
double ntApprovedCost = 0;
double ntForApprovalCost = 0;
double otApprovedCost = 0;
double otForApprovalCost = 0;
for (int x = 0; x < data.Count;x++ )
{
detailRow = detailSheet.CreateRow(detailRowCount++);
if (data[x].Status == "Approved")
{
ntApproved += data[x].normalmins;
otApproved += data[x].otmins;
ntApprovedCost += data[x].normalcost;
otApprovedCost += data[x].otcost;
}
else if (data[x].Status == "For Approval")
{
ntForApproval += data[x].normalmins;
otForApproval += data[x].otmins;
ntForApprovalCost += data[x].normalcost;
otForApprovalCost += data[x].otcost;
}
for (int y = 0; y < 12; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(data[x].department);
else if (y == 1)
detailCell.SetCellValue(data[x].fullname);
else if (y == 2)
detailCell.SetCellValue(data[x].jobtype);
else if (y == 3)
detailCell.SetCellValue(Convert.ToDateTime(data[x].StartTime).ToString("hh:mm tt"));
else if (y == 4)
detailCell.SetCellValue(Convert.ToDateTime(data[x].EndTime).ToString("hh:mm tt"));
else if (y == 5)
detailCell.SetCellValue(Convert.ToDateTime(data[x].ScheduleDate).ToString("dd-MMM-yyyy"));
else if (y == 6)
detailCell.SetCellValue(data[x].Remarks);
else if (y == 7)
detailCell.SetCellValue(data[x].Status);
else if (y == 8)
detailCell.SetCellValue(data[x].normalhours.Trim() == "0 min" ? "-" : data[x].normalhours);
else if (y == 9)
detailCell.SetCellValue(data[x].othours.Trim() == "0 min" ? "-" : data[x].othours);
else if (y == 10)
detailCell.SetCellValue(data[x].normalcost);
else if (y == 11)
detailCell.SetCellValue(data[x].otcost);
if (x == data.Count - 1 && i == uniqueJobType.Count -1)
{
if (y < 10)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
else
{
if (y < 10)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
}
}
#endregion
#region GETTING THE TIME
//Running Time
runningNTApproved += ntApproved;
runningNTForApproval += ntForApproval;
runningOTApproved += otApproved;
runningOTForApproval += otForApproval;
runningNTApprovedCost += ntApprovedCost;
runningNTForApprovalCost += ntForApprovalCost;
runningOTApprovedCost += otApprovedCost;
runningOTForApprovalCost += otForApprovalCost;
string NTApproved = GenerateTimeConsumed(ntApproved);
string NTForApproval = GenerateTimeConsumed(ntForApproval);
string OTApproved = GenerateTimeConsumed(otApproved);
string OTForApproval = GenerateTimeConsumed(otForApproval);
string StotalTime = GenerateTimeConsumed(ntApproved + otApproved + ntForApproval + otForApproval);
#endregion
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue(NTApproved.Trim() == "0 min" ? "-" : NTApproved.Trim());
else if (j == 2)
cell.SetCellValue(NTForApproval.Trim() == "0 min" ? "-" : NTForApproval.Trim());
else if (j == 3)
cell.SetCellValue(OTApproved.Trim() == "0 min" ? "-" : OTApproved.Trim());
else if (j == 4)
cell.SetCellValue(OTForApproval.Trim() == "0 min" ? "-" : OTForApproval.Trim());
else if (j == 5)
cell.SetCellValue(ntApprovedCost);
else if (j == 6)
cell.SetCellValue(ntForApprovalCost);
else if (j == 7)
cell.SetCellValue(otApprovedCost);
else if (j == 8)
cell.SetCellValue(otForApprovalCost);
else if (j == 9)
cell.SetCellValue(StotalTime.Trim() == "0 min" ? "-" : StotalTime.Trim());
else if (j == 10)
cell.SetCellValue(ntForApprovalCost + ntApprovedCost + otApprovedCost + otForApprovalCost);
if (i < uniqueJobType.Count - 1)
{
if(j < 5 || j == 9)
cell.CellStyle = fontNormalBorderLeftRight;
else
cell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (j < 5 || j == 9)
cell.CellStyle = fontNormalBorderLeftRightBottom;
else
cell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
}
#endregion
#region TOTAL, MATERIAL COST, OUTSIDE SERVICE
string projNTApproved = GenerateTimeConsumed(runningNTApproved);
string projNTForApproval = GenerateTimeConsumed(runningNTForApproval);
string projOTApproved = GenerateTimeConsumed(runningOTApproved);
string projOTForApproval = GenerateTimeConsumed(runningOTForApproval);
string projTotalTime = GenerateTimeConsumed(runningNTApproved + runningNTForApproval + runningOTApproved + runningOTForApproval);
string projNTTotal = GenerateTimeConsumed(runningNTApproved + runningNTForApproval);
string projOTTotal = GenerateTimeConsumed(runningOTApproved + runningOTForApproval);
#region DETAIL TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 12; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 8)
detailCell.SetCellValue(projNTTotal.Trim() == "0 min" ? "-" : projNTTotal.Trim());
else if (y == 9)
detailCell.SetCellValue(projOTTotal.Trim() == "0 min" ? "-" : projOTTotal.Trim());
else if (y == 10)
detailCell.SetCellValue(runningNTApprovedCost + runningNTForApprovalCost);
else if (y == 11)
detailCell.SetCellValue(runningOTApprovedCost + runningOTForApprovalCost);
if (y < 10)
detailCell.CellStyle = fontBoldAllBorder;
else
detailCell.CellStyle = fontCurrencyBoldAllBorder;
}
#endregion
#region MATERIAL COST
MaterialCost materialCost = new MaterialCost();
List<MaterialCost> projMaterialCost = new List<MaterialCost>();
projMaterialCost = materialCost.GetMaterialCost(project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : project.HWNo, project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : project.SWNo, project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : project.EvalNo);
double totalmaterialBookedCost = 0;
double totalmaterialActualCost = 0;
if (projMaterialCost.Count > 0)
{
detailRowCount++; //Blank Row
#region HEADER
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Material Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
string[] materialHeader = { "Item", "Description", "Part Number", "Booked Quantity", "Booked Unit Cost", "Booked Extended Cost", "Actual Quantity", "Actual Unit Cost", "Actual Extended Cost","Cost Overrun (Underrun)" };
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int i = 0; i < 10; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(materialHeader[i]);
}
#endregion
#region DETAIL
for (int i = 0; i < projMaterialCost.Count; i++)
{
detailRow = detailSheet.CreateRow(detailRowCount++);
double bookcost = projMaterialCost[i].BookedQuantity * projMaterialCost[i].BookedUnitCost;
double actualcost = projMaterialCost[i].ActualUnitCost * projMaterialCost[i].ActualQuantity;
for (int y = 0; y < 10; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(i + 1);
else if (y == 1)
detailCell.SetCellValue(projMaterialCost[i].PartDescription);
else if (y == 2)
detailCell.SetCellValue(projMaterialCost[i].PartNo);
else if (y == 3)
detailCell.SetCellValue(projMaterialCost[i].BookedQuantity);
else if (y == 4)
detailCell.SetCellValue(projMaterialCost[i].BookedUnitCost);
else if (y == 5)
detailCell.SetCellValue(bookcost);
else if (y == 6)
detailCell.SetCellValue(projMaterialCost[i].ActualQuantity);
else if (y == 7)
detailCell.SetCellValue(projMaterialCost[i].ActualUnitCost);
else if (y == 8)
detailCell.SetCellValue(actualcost);
else if (y == 9)
detailCell.SetCellValue(actualcost - bookcost);
if (i < projMaterialCost.Count - 1)
{
if (y < 4 || y == 6)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (y < 4 || y == 6)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
totalmaterialBookedCost += bookcost;
totalmaterialActualCost += actualcost;
}
#endregion
#region TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 10; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 5)
detailCell.SetCellValue(totalmaterialBookedCost);
else if (y == 8)
detailCell.SetCellValue(totalmaterialActualCost);
else if (y == 9)
detailCell.SetCellValue(totalmaterialActualCost - totalmaterialBookedCost);
if (y == 5 || y == 8 || y == 9)
detailCell.CellStyle = fontCurrencyBoldAllBorder;
else
detailCell.CellStyle = fontBoldAllBorder;
}
#endregion
}
#endregion
#region OUTSIDE SERVICE
OutsideService outsideServiceCost = new OutsideService();
List<OutsideService> projOutServCost = new List<OutsideService>();
projOutServCost = outsideServiceCost.GetOutsideServiceCost(project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : project.HWNo, project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : project.SWNo, project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : project.EvalNo);
double totalOutServBookedCost = 0;
double totalOutServActualCost = 0;
if (projOutServCost.Count > 0)
{
detailRowCount++; //Blank Row
#region HEADER
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Outside Service Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
string[] outsideServiceHeader = { "Item","Type", "Description", "Vendor", "Booked Quantity", "Booked Unit Cost", "Booked Extended Cost", "Actual Quantity", "Actual Unit Cost", "Actual Extended Cost", "Cost Overrun (Underrun)" };
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int i = 0; i < 11; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(outsideServiceHeader[i]);
}
#endregion
#region DETAIL
for (int i = 0; i < projOutServCost.Count; i++)
{
detailRow = detailSheet.CreateRow(detailRowCount++);
double bookcost = projOutServCost[i].BookedQuantity * projOutServCost[i].BookedUnitCost;
double actualcost = projOutServCost[i].ActualUnitCost * projOutServCost[i].ActualQuantity;
for (int y = 0; y < 11; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(i + 1);
else if (y == 1)
detailCell.SetCellValue(projOutServCost[i].PartType);
else if (y == 2)
detailCell.SetCellValue(projOutServCost[i].PartDescription);
else if (y == 3)
detailCell.SetCellValue(projOutServCost[i].Vendor);
else if (y == 4)
detailCell.SetCellValue(projOutServCost[i].BookedQuantity);
else if (y == 5)
detailCell.SetCellValue(projOutServCost[i].BookedUnitCost);
else if (y == 6)
detailCell.SetCellValue(bookcost);
else if (y == 7)
detailCell.SetCellValue(projOutServCost[i].ActualQuantity);
else if (y == 8)
detailCell.SetCellValue(projOutServCost[i].ActualUnitCost);
else if (y == 9)
detailCell.SetCellValue(actualcost);
else if (y == 10)
detailCell.SetCellValue(actualcost - bookcost);
if (i < projMaterialCost.Count - 1)
{
if (y < 5 || y == 7)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (y < 5 || y == 7)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
totalOutServBookedCost += bookcost;
totalOutServActualCost += actualcost;
}
#endregion
#region TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 11; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 6)
detailCell.SetCellValue(totalOutServBookedCost);
else if (y == 9)
detailCell.SetCellValue(totalOutServActualCost);
else if (y == 10)
detailCell.SetCellValue(totalOutServActualCost - totalOutServBookedCost);
if (y == 6 || y == 9 || y == 10)
detailCell.CellStyle = fontCurrencyBoldAllBorder;
else
detailCell.CellStyle = fontBoldAllBorder;
}
#endregion
}
#endregion
row = sheetReport.CreateRow(++currentrow);
for (int j = 0; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 0)
cell.SetCellValue("Total Labor Cost");
else if (j == 1)
cell.SetCellValue(projNTApproved.Trim() == "0 min" ? "-" : projNTApproved.Trim());
else if (j == 2)
cell.SetCellValue(projNTForApproval.Trim() == "0 min" ? "-" : projNTForApproval.Trim());
else if (j == 3)
cell.SetCellValue(projOTApproved.Trim() == "0 min" ? "-" : projOTApproved.Trim());
else if (j == 4)
cell.SetCellValue(projOTForApproval.Trim() == "0 min" ? "-" : projOTForApproval.Trim());
else if (j == 5)
cell.SetCellValue(runningNTApprovedCost);
else if (j == 6)
cell.SetCellValue(runningNTForApprovalCost);
else if (j == 7)
cell.SetCellValue(runningOTApprovedCost);
else if (j == 8)
cell.SetCellValue(runningOTForApprovalCost);
else if (j == 9)
cell.SetCellValue(projTotalTime.Trim() == "0 min" ? "-" : projTotalTime.Trim());
else if (j == 10)
cell.SetCellValue(runningNTApprovedCost + runningNTForApprovalCost + runningOTApprovedCost + runningOTForApprovalCost);
if (j < 5 || j == 9)
cell.CellStyle = fontBoldAllBorder;
else
cell.CellStyle = fontCurrencyBoldAllBorder;
}
row = sheetReport.CreateRow(++currentrow);
row.CreateCell(0).SetCellValue("Material Cost");
row.GetCell(0).CellStyle = fontBoldAllBorder;
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue("Booked Cost:");
else if (j == 2)
cell.SetCellValue(totalmaterialBookedCost);
else if (j == 4)
cell.SetCellValue("Actual Cost:");
else if (j == 5)
cell.SetCellValue(totalmaterialActualCost);
else if (j == 7)
cell.SetCellValue("Cost Overrun (Underrun)");
else if (j == 8)
cell.SetCellValue(totalmaterialActualCost - totalmaterialBookedCost);
if (j == 1 || j == 4 || j == 7)
cell.CellStyle = fontBoldTopBottom;
else
cell.CellStyle = fontCurrencyBoldRigthBottom;
}
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 2, 3));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 5, 6));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 8, 10));
row = sheetReport.CreateRow(++currentrow);
row.CreateCell(0).SetCellValue("Outside Service Cost");
row.GetCell(0).CellStyle = fontBoldAllBorder;
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue("Booked Cost:");
else if (j == 2)
cell.SetCellValue(totalOutServBookedCost);
else if (j == 4)
cell.SetCellValue("Actual Cost:");
else if (j == 5)
cell.SetCellValue(totalOutServActualCost);
else if (j == 7)
cell.SetCellValue("Cost Overrun (Underrun)");
else if (j == 8)
cell.SetCellValue(totalOutServActualCost - totalOutServBookedCost);
if (j == 1 || j == 4 || j == 7)
cell.CellStyle = fontBoldTopBottom;
else
cell.CellStyle = fontCurrencyBoldRigthBottom;
}
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 2, 3));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 5, 6));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 8, 10));
#endregion
}
}
public GeneralSheet(IWorkbook workbook, Demo demo)
{
_demo = demo;
_sheet = workbook.CreateSheet("General");
}
private static void AddListValidations(WorkbookFormatter wf, IWorkbook wb)
{
String cellStrValue
= "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
+ "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 ";
String dataSheetName = "list_data";
// "List" Data Validation type
ISheet fSheet = wf.CreateSheet("Lists");
ISheet dataSheet = wb.CreateSheet(dataSheetName);
wf.CreateDVTypeRow("Explicit lists - list items are explicitly provided");
wf.CreateDVDescriptionRow("Disadvantage - sum of item's length should be less than 255 characters");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(null, ValidationType.LIST);
String listValsDescr = "POIFS,HSSF,HWPF,HPSF";
String[] listVals = listValsDescr.Split(",".ToCharArray());
va.AddListValidation(listVals, null, listValsDescr, false, false);
va.AddListValidation(listVals, null, listValsDescr, false, true);
va.AddListValidation(listVals, null, listValsDescr, true, false);
va.AddListValidation(listVals, null, listValsDescr, true, true);
wf.CreateDVTypeRow("Reference lists - list items are taken from others cells");
wf.CreateDVDescriptionRow("Advantage - no restriction regarding the sum of item's length");
wf.CreateHeaderRow();
va = wf.CreateValidationAdder(null, ValidationType.LIST);
String strFormula = "$A$30:$A$39";
va.AddListValidation(null, strFormula, strFormula, false, false);
strFormula = dataSheetName + "!$A$1:$A$10";
va.AddListValidation(null, strFormula, strFormula, false, false);
IName namedRange = wb.CreateName();
namedRange.NameName = (/*setter*/"myName");
namedRange.RefersToFormula = (/*setter*/dataSheetName + "!$A$2:$A$7");
strFormula = "myName";
va.AddListValidation(null, strFormula, strFormula, false, false);
strFormula = "offset(myName, 2, 1, 4, 2)"; // Note about last param '2':
// - Excel expects single row or single column when entered in UI, but process this OK otherwise
va.AddListValidation(null, strFormula, strFormula, false, false);
// add list data on same sheet
for (int i = 0; i < 10; i++)
{
IRow currRow = fSheet.CreateRow(i + 29);
SetCellValue(currRow.CreateCell(0), cellStrValue);
}
// add list data on another sheet
for (int i = 0; i < 10; i++)
{
IRow currRow = dataSheet.CreateRow(i + 0);
SetCellValue(currRow.CreateCell(0), "Data a" + i);
SetCellValue(currRow.CreateCell(1), "Data b" + i);
SetCellValue(currRow.CreateCell(2), "Data c" + i);
}
}
private bool LoadExcelFile(string sFilePath)
{
if (File.Exists(sFilePath))
{
using (FileStream fs = new FileStream(sFilePath, FileMode.Open, FileAccess.Read))
{
m_StrategyWorkBook = new XSSFWorkbook(fs);
m_StrategySheet = (ISheet)m_StrategyWorkBook.GetSheetAt(0);
}
return true;
}
else
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
return true;
}
}
public OpenKillsRoundSheet(IWorkbook workbook, Demo demo)
{
_demo = demo;
_sheet = workbook.CreateSheet("Open Kills Rounds");
}
public RoundsSheet(IWorkbook workbook, Demo demo)
{
_demo = demo;
_sheet = workbook.CreateSheet("Rounds");
}
