public void TestCanComputeWidthXSSF()
{
IWorkbook wb = new XSSFWorkbook();
// cannot check on result because on some machines we get back false here!
SheetUtil.CanComputeColumnWidth(wb.GetFontAt((short)0));
wb.Close();
}
public void GetFontAt()
{
XSSFWorkbook workbook = new XSSFWorkbook();
StylesTable styleSource = workbook.GetStylesSource();
short i = 0;
//get default font
IFont fontAt = workbook.GetFontAt(i);
Assert.IsNotNull(fontAt);
//get customized font
XSSFFont customFont = new XSSFFont();
customFont.IsItalic = (true);
int x = styleSource.PutFont(customFont);
fontAt = workbook.GetFontAt((short)x);
Assert.IsNotNull(fontAt);
}
public override void ExecuteSampler(string outputFilename, Dictionary <int, int> selectedRow, IProgress <double> progressBar)
{
//Write the stream data of workbook to the root directory
var source_file_stream = new FileStream(MasterFilename, FileMode.Open);
var workbook = WorkbookFactory.Create(source_file_stream);
var destination_workbook = new XSSFWorkbook();
var sheet = workbook.GetSheetAt(0);
for (short i = 0; i < workbook.NumberOfFonts; i++)
{
var dst_font = destination_workbook.NumberOfFonts > i?destination_workbook.GetFontAt(i) : destination_workbook.CreateFont();
var src_font = workbook.GetFontAt(i);
dst_font.IsBold = src_font.IsBold;
dst_font.FontName = src_font.FontName;
dst_font.FontHeight = src_font.FontHeight;
}
//here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
//So we insert three sheet just like what Excel does
var dest_sheet = destination_workbook.CreateSheet(sheet.SheetName);
var dest_row = 1;
var max_count = (double)sheet.LastRowNum;
CopyHeaderRow(dest_sheet, sheet);
// first the range in the first column;
for (int src_row = 1; sheet.GetRow(src_row)?.Cells[SampleColumn].CellType == CellType.Numeric; src_row++)
{
var cell_val = (int)sheet.GetRow(src_row).Cells[SampleColumn].NumericCellValue;
progressBar?.Report(src_row / max_count);
if (!selectedRow.ContainsKey(cell_val))
{
continue;
}
CopyRow(dest_sheet, dest_row, sheet, src_row);
dest_row++;
}
// get last row on destination and autosize the worksheet.
var _row = dest_sheet.GetRow(dest_row - 1); //
for (var i_col = 0; i_col < _row.Cells.Count; i_col++)
{
dest_sheet.AutoSizeColumn(i_col);
}
source_file_stream.Close();
//Write the stream data of workbook to the root directory
var dst_file = new FileStream(outputFilename, FileMode.Create);
destination_workbook.Write(dst_file);
dst_file.Close();
}
private static ICellStyle CreateHeaderStyle(XSSFWorkbook workbook)
{
var style = (XSSFCellStyle)workbook.CreateCellStyle();
style.FillForegroundXSSFColor = new XSSFColor(new byte[] { 192, 192, 192 });
style.FillPattern = FillPattern.SolidForeground;
style.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
var defaultFont = workbook.GetFontAt(0);
font.FontName = defaultFont.FontName;
font.FontHeightInPoints = defaultFont.FontHeightInPoints;
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
return(style);
}
private string fontStyle(ICellStyle style)
{
StringBuilder sbRet = new StringBuilder();
IFont font = null;
if (isXSSFWorkbook)
{
font = _work.GetFontAt(0);
}
else
{
font = style.GetFont(wb);
}
if (font.Boldweight == 0)
{
sbRet.Append(" font-weight: bold;\n");
}
if (font.IsItalic)
{
sbRet.Append(" font-style: italic;\n");
}
double fontheight = font.FontHeight / 10 - 10;
if (fontheight == 9)
{
//fix for stupid ol Windows
fontheight = 10;
}
short a = font.Color;
Color color = Color.FromArgb(font.Color);
Color color1 = Color.FromArgb(color.A, color.R, color.G, color.B);
sbRet.Append(string.Format(" font-size: {0}pt;\n", fontheight));
sbRet.Append(string.Format(" font-color: {0};\n", "red"));
return(sbRet.ToString());
}
public static async Task CreateÜberzeitkontrolle(string department, DatabaseContext db)
{
await Task.Run(() =>
{
var templatePath = @"C:\temp\Überzeitkontrolle_template.xlsx";
using (var outFile = new FileStream(templatePath.Replace("template", department), FileMode.Create, FileAccess.ReadWrite))
{
// Workbook
var wb = new XSSFWorkbook();
// Style
// All users which are used by Overtimes in this year
var year = DateTime.Now.Year;
var users = (from u in db.User
join o in db.Overtime on u.ID equals o.IdUser
where (o.Date.Year == year) && (u.Department == department) && (u.Deactivated == false)
orderby u.Username
select u).Distinct();
// Create Overview sheet and add static text
var sh = wb.CreateSheet("Übersicht");
var rowNum = 0;
var row = sh.CreateRow(rowNum);
var colNum = 0;
row.CreateCell(colNum).SetCellValue("Übersicht");
row = sh.CreateRow(++rowNum);
row.CreateCell(colNum).SetCellValue("Kürzel");
row.CreateCell(++colNum).SetCellValue("Stunden");
row.CreateCell(++colNum).SetCellValue("Tage");
var region = CellRangeAddress.ValueOf("A2:C2");
var border = (int)BorderStyle.Medium;
RegionUtil.SetBorderTop(border, region, sh, wb);
RegionUtil.SetBorderRight(border, region, sh, wb);
RegionUtil.SetBorderBottom(border, region, sh, wb);
RegionUtil.SetBorderLeft(border, region, sh, wb);
// Users loop
foreach (var user in users)
{
// Create sheet per user
sh = wb.CreateSheet(user.Username);
// Row 0
rowNum = 0;
row = sh.CreateRow(rowNum);
colNum = 0;
row.CreateCell(colNum).SetCellValue(year);
row.CreateCell(++colNum).SetCellValue(user.Username);
// Row 1
row = sh.CreateRow(++rowNum);
colNum = 0;
row.CreateCell(colNum).SetCellValue("Datum");
row.CreateCell(++colNum).SetCellValue("Kunde");
row.CreateCell(++colNum).SetCellValue("OK");
row.CreateCell(++colNum).SetCellValue("Zeit");
row.CreateCell(++colNum).SetCellValue("Zuschlag");
row.CreateCell(++colNum).SetCellValue("Total");
// Borders
region = CellRangeAddress.ValueOf("A2:F2");
border = (int)BorderStyle.Medium;
RegionUtil.SetBorderTop(border, region, sh, wb);
RegionUtil.SetBorderRight(border, region, sh, wb);
RegionUtil.SetBorderBottom(border, region, sh, wb);
RegionUtil.SetBorderLeft(border, region, sh, wb);
// Row 2+
var overtimes = (from o in db.Overtime
where (o.Date.Year == year) && (o.IdUser == user.ID)
orderby o.Date
select o).ToList();
foreach (var overtime in overtimes)
{
row = sh.CreateRow(++rowNum);
colNum = 0;
row.CreateCell(colNum).SetCellValue(overtime.Date.ToShortDateString());
row.CreateCell(++colNum).SetCellValue(overtime.Customer);
row.CreateCell(++colNum).SetCellValue("");
row.CreateCell(++colNum).SetCellValue(overtime.Hours.ToString());
var rate = db.OvertimeDetail.Where(od => od.ID == overtime.IdOvertimeDetail).Select(s => s.Rate);
row.CreateCell(++colNum).SetCellValue(Convert.ToDouble(rate.Single()));
row.CreateCell(++colNum).SetCellFormula($"IF(C{rowNum + 1}=\"ok\",D{rowNum + 1}*E{rowNum + 1},0)");
}
// Last row
rowNum = 49; // Is row 50 in excel
row = sh.CreateRow(rowNum);
colNum = 1;
row.CreateCell(colNum).SetCellValue("TOTAL");
row.CreateCell(colNum += 2).SetCellFormula("F50/8.5");
row.CreateCell(++colNum).SetCellValue("<<<<");
row.CreateCell(++colNum).SetCellFormula("SUM(F3:F49)");
var style = (XSSFCellStyle)wb.CreateCellStyle();
var font = wb.CreateFont();
font.FontHeight = wb.GetFontAt(0).FontHeight;
font.IsBold = true;
style.SetFont(font);
foreach (var cell in row)
{
cell.CellStyle = style;
}
// Styles for entry rows
var cs = (XSSFCellStyle)wb.CreateCellStyle();
cs.BorderTop = cs.BorderRight = cs.BorderBottom = cs.BorderLeft = BorderStyle.Thin;
border = (int)BorderStyle.Thin;
for (var i = 2; i <= 48; i++)
{
var r = sh.GetRow(i);
if (r != null)
{
foreach (var cell in r.Cells)
{
cell.CellStyle = cs;
}
}
}
// Conditional formatting
IConditionalFormattingRule rule;
IPatternFormatting pf;
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.NotEqual, "123456");
pf = (XSSFPatternFormatting)rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.LightTurquoise.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf("A50:F50"), CellRangeAddress.ValueOf("F3:F50") },
rule
);
// Add user to overview sheet
sh = wb.GetSheet("Übersicht");
row = sh.CreateRow(sh.LastRowNum + 1);
colNum = 0;
row.CreateCell(colNum).SetCellValue(user.Username);
row.CreateCell(++colNum).SetCellFormula(user.Username + "!$F$50");
row.CreateCell(++colNum).SetCellFormula(user.Username + "!$D$50");
// Borders for these cells
style = null;
style = (XSSFCellStyle)wb.CreateCellStyle();
style.BorderTop = style.BorderRight = style.BorderBottom = style.BorderLeft = BorderStyle.Thin;
border = (int)BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = style;
}
// Conditional formatting
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.NotBetween, "0", "5");
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Red.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"C3:C{sh.LastRowNum + 1}") },
rule
);
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "4", "5");
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Orange.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"C3:C{sh.LastRowNum + 1}") },
rule
);
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "3", "4");
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Yellow.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"C3:C{sh.LastRowNum + 1}") },
rule
);
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "0", "5");
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Green.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"C3:C{sh.LastRowNum + 1}") },
rule
);
}
wb.Write(outFile);
outFile.Close();
}
});
}
public IFont GetFontAt(short idx)
{
return(XssfWorkbook.GetFontAt(idx));
}