private void footerPrint(ExcelXmlParser parser)
{
cols = parser.getColumnsInfo("foot");
ExcelBorder border = getBorder();
if (parser.getWithoutHeader() == false)
{
ExcelFont font = wb.CreateFont(FontFamily, FooterFontSize);
font.Bold = true;
if (HeaderTextColor != "FF000000")
{
font.Color = HeaderTextColor;
}
for (uint row = 1; row <= cols.Length; row++)
{
uint rowInd = (uint)(row + headerOffset);
sheet.Rows[rowInd].Height = RowHeight;
for (uint col = 1; col <= cols[row - 1].Length; col++)
{
if (BGColor != "FFFFFFFF")
{
sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = BGColor;
}
sheet.Cells[rowInd, col].Style.Font = font;
//TODO add text color, vertical alignment, horizontal alignment
sheet.Cells[rowInd, col].Style.Border = border;
sheet.Cells[rowInd, col].Value = cols[row - 1][col - 1].GetName();
}
}
}
headerOffset += cols.Length;
}
public void Generate(string xml, Stream output)
{
parser = new ExcelXmlParser();
try
{
parser.setXML(xml);
createExcel(output);
setColorProfile();
headerPrint(parser);
rowsPrint(parser, output);
wb.Workbook.Document.Styles.Save();
// remove repeat foot # charlot
// footerPrint(parser);
insertHeader(parser, output);
insertFooter(parser, output);
watermarkPrint(parser);
//// merge head
//sheet.MergeTwoCells("A1","A3");
//sheet.Save();
wb.Dispose();
}
catch (Exception e)
{
// e.printStackTrace();
}
}
private void insertFooter(ExcelXmlParser parser, Stream resp)
{
/* if (parser.getFooter() == true) {
* sheet.setRowView(headerOffset, 5000);
* File imgFile = new File(pathToImgs + "/footer.png");
* WritableImage img = new WritableImage(0, headerOffset, cols[0].length, 1, imgFile);
* sheet.addImage(img);
* }*/
}
private void footerPrint(ExcelXmlParser parser)
{
cols = parser.getColumnsInfo("foot");
ExcelBorder border = getBorder();
if (parser.getWithoutHeader() == false)
{
ExcelFont font = wb.CreateFont("Arial", 10);
font.Bold = true;
if (headerTextColor != "FF000000")
{
font.Color = headerTextColor;
}
for (uint row = 1; row <= cols.Length; row++)
{
uint rowInd = (uint)(row + headerOffset);
// sheet.Rows[rowInd].Height = 22.5;
for (uint col = 1; col <= cols[row - 1].Length; col++)
{
if (bgColor != "FFFFFFFF")
{
sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = bgColor;
}
sheet.Cells[rowInd, col].Style.Font = font;
//TODO add text color, vertical alignment, horizontal alignment
sheet.Cells[rowInd, col].Style.Border = border;
sheet.Cells[rowInd, col].Value = cols[row - 1][col - 1].GetName();
}
}
/* for (int col = 0; col < cols.Length; col++) {
* for (int row = 0; row < cols[col].Length; row++) {
* int cspan = cols[col][row].GetColspan();
* if (cspan > 0) {
* sheet.mergeCells(row, headerOffset + col, row + cspan - 1, headerOffset + col);
* }
* int rspan = cols[col][row].GetRowspan();
* if (rspan > 0) {
* sheet.mergeCells(row, headerOffset + col, row, headerOffset + col + rspan - 1);
* }
* }
* }*/
}
headerOffset += cols.Length;
}
private void watermarkPrint(ExcelXmlParser parser)
{
if (watermark == null)
{
return;
}
ExcelFont font = wb.CreateFont(FontFamily, WatermarkFontSize);
font.Bold = true;
font.Color = WatermarkTextColor;
ExcelBorder border = getBorder();
// f.setAlignment(Alignment.CENTRE);
sheet.Cells[(uint)(headerOffset + 1), 0].Value = watermark;
}
private void watermarkPrint(ExcelXmlParser parser)
{
if (watermark == null)
{
return;
}
ExcelFont font = wb.CreateFont("Arial", 10);
font.Bold = true;
font.Color = watermarkTextColor;
ExcelBorder border = getBorder();
//f.setAlignment(Alignment.CENTRE);
sheet.Cells[(uint)(headerOffset + 1), 0].Value = watermark;
// Label label = new Label(0, headerOffset, watermark , f);
// sheet.addCell(label);
// sheet.mergeCells(0, headerOffset, colsNumber, headerOffset);*/
}
public void Generate(string xml, Stream output)
{
parser = new ExcelXmlParser();
try {
parser.setXML(xml);
createExcel(output);
setColorProfile();
headerPrint(parser);
rowsPrint(parser, output);
wb.Workbook.Document.Styles.Save();
if (PrintFooter)
{
footerPrint(parser);
}
insertHeader(parser, output);
insertFooter(parser, output);
watermarkPrint(parser);
wb.Dispose();
} catch (Exception e) {
throw;
}
}
private void rowsPrint(ExcelXmlParser parser, Stream resp)
{
ExcelRow[] rows = parser.getGridContent();
this.rows_stat = rows.Length;
ExcelBorder border = getBorder();
ExcelFont font = wb.CreateFont("Arial", 10);
// if (gridTextColor != "FF000000")
// font.Color = gridTextColor;
for (uint row = 1; row <= rows.Length; row++)
{
ExcelCell[] cells = rows[row - 1].getCells();
uint rowInd = (uint)(row + headerOffset);
// sheet.Rows[rowInd].Height = 20;
for (uint col = 1; col <= cells.Length; col++)
{
if (cells[col - 1].GetBold() || cells[col - 1].GetItalic())
{
ExcelFont curFont = wb.CreateFont("Arial", 10);;
// if (gridTextColor != "FF000000")
// font.Color = gridTextColor;
if (cells[col - 1].GetBold())
{
font.Bold = true;
}
if (cells[col - 1].GetItalic())
{
font.Italic = true;
}
sheet.Cells[rowInd, col].Style.Font = curFont;
}
else
{
sheet.Cells[rowInd, col].Style.Font = font;
}
sheet.Cells[rowInd, col].Style.Border = border;
if ((!cells[col - 1].GetBgColor().Equals("")) && (parser.getProfile().Equals("full_color")))
{
sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = "FF" + cells[col - 1].GetBgColor();
}
else
{
//Colour bg;
if (row % 2 == 0 && scaleTwoColor != "FFFFFFFF")
{
sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = scaleTwoColor;
}
else
{
if (scaleOneColor != "FFFFFFFF")
{
sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = scaleOneColor;
}
}
}
int intVal;
double dbVal;
if (int.TryParse(cells[col - 1].GetValue(), out intVal))
{
sheet.Cells[rowInd, col].Value = intVal;
}
else if (double.TryParse(cells[col - 1].GetValue(), out dbVal))
{
sheet.Cells[rowInd, col].Value = dbVal;
}
else
{
sheet.Cells[rowInd, col].Value = cells[col - 1].GetValue();
}
//COLOR!
//String al = cells[row].getAlign();
//if (al == "")
// al = cols[0][row].getAlign();
//if (al.equalsIgnoreCase("left"))
//{
// f.setAlignment(Alignment.LEFT);
//}
//else
//{
// if (al.equalsIgnoreCase("right"))
// {
// f.setAlignment(Alignment.RIGHT);
// }
// else
// {
// f.setAlignment(Alignment.CENTRE);
// }
//}
}
}
headerOffset += rows.Length;
}
//public void Generate(string xml, HttpResponseBase resp)
//{
// var data = new MemoryStream();
// resp.ContentType = ContentType;
// resp.HeaderEncoding = Encoding.UTF8;
// resp.AppendHeader("Content-Disposition", "attachment;filename=my_report.xlsx");
// resp.AppendHeader("Cache-Control", "max-age=0");
// Generate(xml, data);
// data.WriteTo(resp.OutputStream);
//}
private void headerPrint(ExcelXmlParser parser)
{
cols = parser.getColumnsInfo("head");
int[] widths = parser.getWidths();
this.cols_stat = widths.Length;
int sumWidth = 0;
for (int i = 0; i < widths.Length; i++)
{
sumWidth += widths[i];
}
if (parser.getWithoutHeader() == false)
{
ExcelFont font = wb.CreateFont("Arial", 10);
font.Bold = true;
if (headerTextColor != "FF000000")
{
font.Color = headerTextColor;
}
ExcelBorder border = getBorder();
for (uint row = 1; row <= cols.Length; row++)
{
// sheet.Rows[row].Height = 22.5;
for (uint col = 1; col <= cols[row - 1].Length; col++)
{
// trick before font
//sheet.Cells[row, col].Style.SetAlignmentCenter = true;
sheet.Cells[row, col, true].IsHead = true;
sheet.Cells[row, col, true].Style.Font = font;//if bold font assigned after border - all table will be bold, weird, find out later
sheet.Cells[row, col].Style.Border = border;
sheet.Columns[col].Width = widths[col - 1] / scale;
String name = cols[row - 1][col - 1].GetName();
if (bgColor != "FFFFFFFF")
{
sheet.Cells[row, col, true].Style.Fill.ForegroundColor = bgColor;
}
///TODO:
///font color, merge cells, alignment
sheet.Cells[row, col, true].Value = name;
colsNumber = (int)col;
}
}
headerOffset = cols.Length;
/* for (int col = 0; col < cols.Length; col++) {
* for (int row = 0; row < cols[col].Length; row++) {
* int cspan = cols[col][row].GetColspan();
* if (cspan > 0) {
* sheet.mergeCells(row, col, row + cspan - 1, col);
* }
* int rspan = cols[col][row].GetRowspan();
* if (rspan > 0) {
* sheet.mergeCells(row, col, row, col + rspan - 1);
* }
* }
* }*/
}
}
private void headerPrint(ExcelXmlParser parser)
{
cols = parser.getColumnsInfo("head");
//Widths
int[] widths = parser.getWidths();
if (this.Widths != null)
{
foreach (var index in this.Widths.Keys)
{
if (index >= 0 && index < widths.Length)
{
widths[index] = Widths[index];
}
}
}
this.cols_stat = widths.Length;
int sumWidth = 0;
for (int i = 0; i < widths.Length; i++)
{
sumWidth += widths[i];
}
if (parser.getWithoutHeader() == false)
{
ExcelFont font = wb.CreateFont(FontFamily, HeaderFontSize);
font.Bold = true;
if (HeaderTextColor != "FF000000")
{
font.Color = HeaderTextColor;
}
ExcelBorder border = getBorder();
for (uint row = 1; row <= cols.Length; row++)
{
sheet.Rows[row].Height = RowHeight;
for (uint col = 1; col <= cols[row - 1].Length; col++)
{
sheet.Cells[row, col].Style.Font = font;//if bold font assigned after border - all table will be bold, weird, find out later
sheet.Cells[row, col].Style.Border = border;
sheet.Columns[col].Width = widths[col - 1] / scale;
String name = cols[row - 1][col - 1].GetName();
if (BGColor != "FFFFFFFF")
{
sheet.Cells[row, col].Style.Fill.ForegroundColor = BGColor;
}
///TODO:
///font color, merge cells, alignment
sheet.Cells[row, col].Value = name;
colsNumber = (int)col;
}
}
headerOffset = cols.Length;
}
}
