/// <summary>
/// 建置单元格样式
/// </summary>
/// <param name="cellStyle">Excel 单元格样式</param>
/// <returns></returns>
public ICellStyle BuildCellStyle(CellStyle cellStyle)
{
var style = this._workbook.CreateCellStyle();
var dataFormat = this._workbook.CreateDataFormat();
switch (cellStyle)
{
case CellStyle.Date:
style.DataFormat = dataFormat.GetFormat("yyyy/MM/dd");
break;
case CellStyle.DateTime:
style.DataFormat = dataFormat.GetFormat("yyyy/MM/dd HH:mm:ss"); // 24 时制
break;
case CellStyle.Number2:
style.DataFormat =dataFormat.GetFormat("0.00");
break;
case CellStyle.Number3:
style.DataFormat = dataFormat.GetFormat("0.000");
break;
case CellStyle.Percent:
style.DataFormat = dataFormat.GetFormat("0.00%");
break;
case CellStyle.Money:
style.DataFormat = dataFormat.GetFormat("￥#,##0");
break;
}
return style;
}
public static void CreateConditionalFormatting()
{
var workbook = new Workbook(); //creating workbook and adding sheet
var companiesSheet = new Sheet("CompaniesByRevenue");
workbook.Sheets.AddSheet(companiesSheet);
companiesSheet.Columns[0].Width = 5;
companiesSheet.Columns[1].Width = 30;
companiesSheet.Columns[2].Width = 25;
companiesSheet.Columns[3].Width = 25;
companiesSheet.Columns[4].Width = 15;
var headingStyle = new CellStyle(); //define bold and centered header style
headingStyle.Font.Bold = true;
headingStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var thousandsFormatStyle = new CellStyle(); //adding thousands separator format on Employees column
thousandsFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
thousandsFormatStyle.Format = "#,#0";
var currencyFormatStyle = new CellStyle(); //adding $ (dollar) sign on Revenue and Capitalization columns
currencyFormatStyle.Format = "$#,#0";
currencyFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var table = companiesSheet["A2", "E6"];
table.GetRow(-1).SetValues("Rank", "Company", "Revenue (USD billions)", "Capitalization (USD billions)", "Employees").SetStyle(headingStyle);
table.GetColumn(0).SetValues(1, 2, 3, 4, 5);
table.GetColumn(1).SetValues("Wal-Mart Stores, Inc. (USA)", "Royal Dutch Shell (NLD)", "Exxon Mobil Corporation (USA)", "China National Petroleum (CHN)", "Sinopec Group (CHN)");
table.GetColumn(2).SetValues(469, 467, 453, 425, 411).SetStyle(currencyFormatStyle);
table.GetColumn(3).SetValues(248, 132, 406, null, 81).SetStyle(currencyFormatStyle);
table.GetColumn(4).SetValues(2200000, 90000, 76900, 1668072, 401000).SetStyle(thousandsFormatStyle);
var originFormating = new ConditionalFormatting(Range.Parse("B2:B6")); //creating ConditionalFormatting for specified range
originFormating.AddContainsText("USA", new CellStyle { Fill = CellFill.BackColor(Color.Red) });
originFormating.AddEndsWithText("(NLD)", new CellStyle { Fill = CellFill.BackColor(new Color("#0096FF")) });
originFormating.AddContainsText("CHN", new CellStyle { Fill = CellFill.BackColor(new Color("#FFFF96")) });
var revenueFormatting = new ConditionalFormatting();
revenueFormatting.AddRange("C2:C6"); //multiple ranges or cells can be added
revenueFormatting.AddDefaultIconSet(IconSetType.Item5Arrows); //add IconSet with 5 arrows
revenueFormatting.AddGreaterThan("460", new CellStyle { Font = new CellFont { Underline = FontUnderline.Single } });
var capitalizationFormatting = new ConditionalFormatting(Range.Parse("D1:D6")); //specifying range in constructor
capitalizationFormatting.AddColorScale(Color.Yellow, Color.Orange);
capitalizationFormatting.AddDefaultIconSet();
capitalizationFormatting.AddIsBlank(new CellStyle { Fill = CellFill.BackColor(Color.Red) }); //add style applied on blank cell
ConditionalFormatting employeesNumberFormating = new ConditionalFormatting(Range.Parse("E1:E6"));
employeesNumberFormating.AddDefaultDataBar(Color.Blue); //adding blue data bar conditional formatting rule
//adding different conditional formattings to sheet
companiesSheet.ConditionalFormatting.Add(originFormating);
companiesSheet.ConditionalFormatting.Add(revenueFormatting);
companiesSheet.ConditionalFormatting.Add(capitalizationFormatting);
companiesSheet.ConditionalFormatting.Add(employeesNumberFormating);
workbook.Save(@"ConditionalFormatting.xlsx");
}
public PlayerCell(NFLPlayer playerIn, string slotIn, CellStyle style, string teamCode)
{
_mSlot = slotIn;
_mStyle = style;
_mPlayer = playerIn;
_mTeamCode = teamCode;
_mName = _mPlayer == null ? String.Empty : _mPlayer.PlayerName.Trim();
_mBgColour = "MOCCASIN";
}
public static void CreatingDefinedNames()
{
//creating workbook with 2 worksheets
var workbook = new Workbook();
var exchangeRatesSheet = new Sheet("Exchange Rates");
workbook.Sheets.AddSheet(exchangeRatesSheet);
var pricesSheet = new Sheet("Prices");
workbook.Sheets.AddSheet(pricesSheet);
//defining currencies
exchangeRatesSheet["A1"].Value = "EUR";
exchangeRatesSheet["A2"].Value = "GBP";
exchangeRatesSheet["A3"].Value = "USD";
//defining exchange rates
exchangeRatesSheet["B1"].Value = 1.0;
exchangeRatesSheet["B2"].Value = 0.7;
exchangeRatesSheet["B3"].Value = 1.43;
//defining VAT percentage
exchangeRatesSheet["E1"].Value = "VAT";
exchangeRatesSheet["F1"].Value = 0.17;
//set named cell for VAT
workbook.DefinedNames.AddCell("VAT", Cell.Parse("F1"), exchangeRatesSheet);
//set named range for exchange rates
Range exchangeRatesRange = Range.Parse("A1:B3");
workbook.DefinedNames.AddRange("ExchangeRate", exchangeRatesRange, exchangeRatesSheet);
//defining headers in Prices sheet
pricesSheet["A1"].Value = "Product";
pricesSheet["B1"].Value = "Price";
pricesSheet["C1"].Value = "EUR";
pricesSheet["D1"].Value = "USD";
pricesSheet["E1"].Value = "GBP";
//setting headers style
var labelsRange = pricesSheet["A1", "E1"];
CellStyle headerStyle = new CellStyle();
headerStyle.Font.Bold = true;
headerStyle.Alignment.HAlign = HorizontalAlignment.Center;
labelsRange.SetStyle(headerStyle);
//defining products
pricesSheet["A2"].Value = "Xlio";
pricesSheet["B2"].Value = 50;
pricesSheet["A3"].Value = "Azzura";
pricesSheet["B3"].Value = 65;
//set formula for calculating prices in different currencies
string formulaString = "=LOOKUP(C$1, ExchangeRate)*$B2*(1+VAT)";
var sheetRange = pricesSheet["C2", "E3"];
sheetRange.SetFormula(formulaString);
workbook.Save(@"DefinedNames.xlsx");
}
internal void FromCellStyle(CellStyle cs)
{
this.SetAllNull();
if (cs.Name != null) this.Name = cs.Name.Value;
if (cs.FormatId != null) this.FormatId = cs.FormatId.Value;
if (cs.BuiltinId != null) this.BuiltinId = cs.BuiltinId.Value;
if (cs.OutlineLevel != null) this.OutlineLevel = cs.OutlineLevel.Value;
if (cs.Hidden != null) this.Hidden = cs.Hidden.Value;
if (cs.CustomBuiltin != null) this.CustomBuiltin = cs.CustomBuiltin.Value;
}
internal CellStyle ToCellStyle()
{
CellStyle cs = new CellStyle();
if (this.Name != null) cs.Name = this.Name;
cs.FormatId = this.FormatId;
if (this.BuiltinId != null) cs.BuiltinId = this.BuiltinId.Value;
if (this.OutlineLevel != null) cs.OutlineLevel = this.OutlineLevel.Value;
if (this.Hidden != null) cs.Hidden = this.Hidden.Value;
if (this.CustomBuiltin != null) cs.CustomBuiltin = this.CustomBuiltin.Value;
return cs;
}
/// <summary>
/// Creates Stylesheet from collections of border styles, fontstyles, color styles etc...
/// </summary>
public static Stylesheet GetStyleSheet(ExcelType scheme)
{
var stylesheet = new Stylesheet();
var fontCollection = new FontCollection();
var fillsCollection = new FillsCollection();
var borderCollection = new BorderCollection();
var cellStyleFormats = new CellStyleFormats();
var cellStyle = new CellFormat();
cellStyle.NumberFormatId = 0;
cellStyle.FontId = 0;
cellStyle.FillId = 0;
cellStyle.BorderId = 0;
cellStyleFormats.Append(cellStyle);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
var numberingFormats = new CellContentFormat();
var cellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();
cellFormats.Append(GetStyleSheetScheme(scheme));
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
stylesheet.Append(numberingFormats);
stylesheet.Append(fontCollection);
stylesheet.Append(fillsCollection);
stylesheet.Append(borderCollection);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
var css = new DocumentFormat.OpenXml.Spreadsheet.CellStyles();
var cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
stylesheet.Append(css);
var dfs = new DifferentialFormats();
dfs.Count = 0;
stylesheet.Append(dfs);
var tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
stylesheet.Append(tss);
return stylesheet;
}
// ** ctor
public FilterRowLike(C1.Win.C1FlexGrid.C1FlexGrid flex)
{
// save reference to grid
_flex = flex;
// add filter row
_row = _flex.Rows.Fixed;
_flex.Rows.Fixed++;
// customize filter row style
// note: customize margins to align checkboxes correctly in filter cells,
// which have no vertical border <<1.4>>
_style = _flex.Styles.Add("Filter", _flex.Styles.Frozen);
//_style.BackColor = SystemColors.Info;
//_style.ForeColor = SystemColors.InfoText;
//_style.Border.Direction = BorderDirEnum.Horizontal;
//_style.Margins = new System.Drawing.Printing.Margins(1, 2, 1, 1);
_flex.Rows[_row].Style = _style;
// add event handlers
_flex.KeyDown += new KeyEventHandler(_flex_KeyDown);
_flex.BeforeMouseDown += new BeforeMouseDownEventHandler(_flex_BeforeMouseDown);
_flex.RowColChange += new EventHandler(_flex_RowColChange);
_flex.AfterEdit += new RowColEventHandler(_flex_AfterEdit);
// initialize boolean cells <<1.4>>
CellStyle cs = _flex.Styles.Add("BooleanFilterCell");
cs.ImageAlign = ImageAlignEnum.CenterCenter;
foreach (Column col in _flex.Cols)
{
if (col.DataType == typeof(bool))
{
_flex.SetCellCheck(_row, col.Index, CheckEnum.TSGrayed);
_flex.SetCellStyle(_row, col.Index, cs);
}
if (col.DataType != null)
{
if (col.DataType.Name == "DateTime")
{
_flex.AllowEditing = false;
}
}
}
// move cursor to filter row
_flex.Select(_row, _flex.Cols.Fixed);
}
private static CellStyle CreateDataStyle(int row, bool isBold = false, bool isNumber = false)
{
CellStyle style = new CellStyle();
style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 102, 0), LineStyle.Thin);
if (row % 2 == 0)
{
style.FillPattern.SetSolid(Color.FromArgb(227, 243, 230));
}
if (isBold)
{
style.Font.Weight = ExcelFont.BoldWeight;
}
if (isNumber)
{
style.NumberFormat = "#,##0.00";
}
return style;
}
public static void SetCellStyle(CellStyle cellStyle, short bColor, short fColor, FillPatternType pattern)
{
cellStyle.FillBackgroundColor = bColor; // 47
cellStyle.FillForegroundColor = fColor; // 64
cellStyle.FillPattern = pattern;
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = 2U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = 1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = 1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills()
{
Count = 2U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders()
{
Count = 2U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
Color color3 = new Color()
{
Indexed = 64U
};
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color4 = new Color()
{
Indexed = 64U
};
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = 64U
};
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color6 = new Color()
{
Indexed = 64U
};
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = 3U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyBorder = true
};
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyFont = true, ApplyBorder = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
CellStyles cellStyles1 = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles()
{
DefaultTimelineStyle = "TimeSlicerStyleLight1"
};
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
public static void AddStylesToWorkbookPart(WorkbookPart part)
{
var workbookStylesPart = part.AddNewPart <WorkbookStylesPart>("WorkbookStyles");
var stylesheet1 = new Stylesheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
var fonts1 = new Fonts {
Count = 1U, KnownFonts = true
};
var font1 = new Font();
var fontSize1 = new FontSize {
Val = 11D
};
var color1 = new Color {
Theme = 1U
};
var fontName1 = new FontName {
Val = "Calibri"
};
var fontFamilyNumbering1 = new FontFamilyNumbering {
Val = 2
};
var fontScheme1 = new FontScheme {
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
var fills1 = new Fills {
Count = 2U
};
var fill1 = new Fill();
var patternFill1 = new PatternFill {
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
var fill2 = new Fill();
var patternFill2 = new PatternFill {
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
var borders1 = new Borders {
Count = 1U
};
var border1 = new Border();
var leftBorder1 = new LeftBorder();
var rightBorder1 = new RightBorder();
var topBorder1 = new TopBorder();
var bottomBorder1 = new BottomBorder();
var diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
var cellStyleFormats1 = new CellStyleFormats {
Count = 1U
};
var cellStyleFormat = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.Append(cellStyleFormat);
var cellFormats = new CellFormats {
Count = 2U
};
// index 0 (default) => unlocked
var cellFormat0 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U, ApplyProtection = true
}.AppendItem(new Protection {
Locked = false
});
// index 1 => locked
var cellFormat1 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
cellFormats.Append(cellFormat0);
cellFormats.Append(cellFormat1);
var cellStyles1 = new CellStyles {
Count = 1U
};
var cellStyle1 = new CellStyle {
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.Append(cellStyle1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats);
stylesheet1.Append(cellStyles1);
workbookStylesPart.Stylesheet = stylesheet1;
}
public static string ExportToExcel(DataTable elementData, DataTable recommendationData)
{
var fPath = Path.GetTempFileName() + ".xlsx";
try
{
SpreadsheetInfo.SetLicense(AppConstants.GemLic);
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Exported Data - SSK Online");
var styleHeader2 = new CellStyle();
styleHeader2.HorizontalAlignment = HorizontalAlignmentStyle.Left;
styleHeader2.VerticalAlignment = VerticalAlignmentStyle.Center;
styleHeader2.Font.Weight = ExcelFont.BoldWeight;
styleHeader2.FillPattern.SetSolid(System.Drawing.Color.LightGreen);
var styleHeader = new CellStyle();
styleHeader.HorizontalAlignment = HorizontalAlignmentStyle.Left;
styleHeader.VerticalAlignment = VerticalAlignmentStyle.Center;
styleHeader.Font.Weight = ExcelFont.BoldWeight;
styleHeader.FillPattern.SetSolid(System.Drawing.Color.AliceBlue);
int Rows = 0;
int Cols = 0;
foreach (DataColumn dc in elementData.Columns)
{
worksheet.Cells[Rows, Cols].Value = dc.ColumnName;
worksheet.Cells[Rows, Cols].Style = styleHeader;
Cols++;
}
Rows = 1;
foreach (DataRow dr in elementData.Rows)
{
Cols = 0;
foreach (DataColumn dc in elementData.Columns)
{
worksheet.Cells[Rows, Cols].Value = dr[dc.ColumnName].ToString();
Cols++;
}
Rows++;
}
Cols = 0;
foreach (DataColumn dc in recommendationData.Columns)
{
worksheet.Cells[Rows, Cols].Value = dc.ColumnName;
worksheet.Cells[Rows, Cols].Style = styleHeader2;
Cols++;
}
Rows++;
foreach (DataRow dr in recommendationData.Rows)
{
Cols = 0;
foreach (DataColumn dc in recommendationData.Columns)
{
worksheet.Cells[Rows, Cols].Value = dr[dc.ColumnName].ToString();
Cols++;
}
Rows++;
}
workbook.Save(fPath);
}
catch (Exception ex)
{
Console.WriteLine($"Error - save to file : {ex.Message}");
return(null);
}
return(fPath);
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac x16r2 xr"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
stylesheet1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders()
{
Count = (UInt32Value)1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)2U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
OpenXmlUnknownElement openXmlUnknownElement4 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
stylesheetExtension2.Append(openXmlUnknownElement4);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
public ActionResult Export(long studentid,
string test_academic, string test_diligence, string test_attendance, string test_responsible, string test_initiative,
string test_conduct, string test_honesty, string test_reliance, string test_collaborate, string test_appearance,
string test_bm, string test_english, string test_remarks)
{
var usr = repository.GetUser(studentid);
var r = usr.registrations.OrderByDescending(x => x.id).First();
var school = usr.classes_students_allocateds.OrderByDescending(x => x.year).First().school_class.schoolid;
var date = DateTime.Now;
var ecas = usr.eca_students.Where(x => x.eca.schoolid == school).AsQueryable();
// get correct template
string templateFile = "";
switch ((Schools)school)
{
case Schools.Kindergarten:
throw new NotImplementedException();
break;
case Schools.Primary:
templateFile = "/Content/templates/TestimonialPrimary.xls";
break;
case Schools.Secondary:
templateFile = "/Content/templates/TestimonialSecondary.xls";
break;
case Schools.International:
templateFile = "/Content/templates/TestimonialInternational.xls";
break;
}
// save
r.test_academic = test_academic;
r.test_diligence = test_diligence;
r.test_attendance = test_attendance;
r.test_responsible = test_responsible;
r.test_initiative = test_initiative;
r.test_conduct = test_conduct;
r.test_honesty = test_honesty;
r.test_reliance = test_reliance;
r.test_collaborate = test_collaborate;
r.test_appearance = test_appearance;
r.test_bm = test_bm;
r.test_english = test_english;
r.test_remarks = test_remarks;
repository.Save();
var ms = new MemoryStream();
using (FileStream fs =
new FileStream(
AppDomain.CurrentDomain.BaseDirectory + templateFile,
FileMode.Open, FileAccess.Read, FileShare.Read))
{
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.GetSheetAt(0);
// small font
var smallfont = templateWorkbook.CreateFont();
smallfont.FontName = "Arial";
smallfont.Boldweight = (short)FontBoldWeight.NORMAL;
smallfont.FontHeightInPoints = 8;
var smallStyle = templateWorkbook.CreateCellStyle();
smallStyle.SetFont(smallfont);
// wrap style
CellStyle wrapStyle = templateWorkbook.CreateCellStyle();
wrapStyle.VerticalAlignment = VerticalAlignment.TOP;
wrapStyle.WrapText = true;
// eca sytle
var ecaStyle = templateWorkbook.CreateCellStyle();
ecaStyle.WrapText = true;
ecaStyle.VerticalAlignment = VerticalAlignment.CENTER;
ecaStyle.Alignment = HorizontalAlignment.CENTER;
ecaStyle.BorderLeft = CellBorderType.THIN;
ecaStyle.SetFont(smallfont);
// reference number
var row = sheet.GetRow(5);
row.GetCell(2).SetCellValue(usr.ToReferenceNumber(date.Year));
// date
row.GetCell(12).SetCellValue(date.ToShortDateString().Replace("/", "."));
// name
row = sheet.GetRow(8);
row.GetCell(4).SetCellValue(usr.ToName());
// nric
row = sheet.GetRow(11);
row.GetCell(3).SetCellValue(usr.nric_new);
// admission date
if (r.admissionDate.HasValue)
{
row = sheet.GetRow(14);
row.GetCell(3).SetCellValue(r.admissionDate.Value.ToShortDateString().Replace("/", "."));
}
// leaving date
if (r.leftDate.HasValue)
{
row = sheet.GetRow(17);
row.GetCell(3).SetCellValue(r.leftDate.Value.ToShortDateString().Replace("/", "."));
}
// assessments
sheet.GetRow(20).GetCell(5).SetCellValue(r.test_academic.ToUpper());
sheet.GetRow(22).GetCell(5).SetCellValue(r.test_diligence.ToUpper());
sheet.GetRow(24).GetCell(5).SetCellValue(r.test_attendance.ToUpper());
sheet.GetRow(26).GetCell(5).SetCellValue(r.test_responsible.ToUpper());
sheet.GetRow(28).GetCell(5).SetCellValue(r.test_initiative.ToUpper());
sheet.GetRow(30).GetCell(5).SetCellValue(r.test_conduct.ToUpper());
sheet.GetRow(32).GetCell(5).SetCellValue(r.test_honesty.ToUpper());
sheet.GetRow(34).GetCell(5).SetCellValue(r.test_reliance.ToUpper());
sheet.GetRow(36).GetCell(5).SetCellValue(r.test_collaborate.ToUpper());
sheet.GetRow(38).GetCell(5).SetCellValue(r.test_appearance.ToUpper());
sheet.GetRow(40).GetCell(5).SetCellValue(r.test_bm.ToUpper());
sheet.GetRow(42).GetCell(5).SetCellValue(r.test_english.ToUpper());
// ECA
var sports = ecas.Where(x => x.type == EcaType.SPORTS.ToString()).OrderByDescending(x => x.year).Take(4);
var clubs = ecas.Where(x => x.type == EcaType.CLUBS.ToString() || x.type == EcaType.UNIFORM.ToString()).OrderByDescending(x => x.year).Take(5);
var duties = ecas.Where(x => x.type == EcaType.DUTIES.ToString()).OrderByDescending(x => x.year).Take(3);
int rowcount = 22;
foreach (var sport in sports)
{
row = sheet.GetRow(rowcount);
var activitycell = row.GetCell(7);
activitycell.SetCellValue(sport.eca.name);
activitycell.CellStyle = ecaStyle;
var postcell = row.GetCell(11) ?? row.CreateCell(11);
postcell.SetCellValue(sport.post);
postcell.CellStyle = ecaStyle;
var achievementcell = row.GetCell(13) ?? row.CreateCell(13);
achievementcell.SetCellValue(sport.achievement);
achievementcell.CellStyle = ecaStyle;
rowcount += 2;
}
rowcount = 34;
foreach (var club in clubs)
{
row = sheet.GetRow(rowcount);
var activitycell = row.GetCell(7);
activitycell.SetCellValue(club.eca.name);
activitycell.CellStyle = ecaStyle;
var postcell = row.GetCell(11) ?? row.CreateCell(11);
postcell.SetCellValue(club.post);
postcell.CellStyle = ecaStyle;
var achievementcell = row.GetCell(13) ?? row.CreateCell(13);
achievementcell.SetCellValue(club.achievement);
achievementcell.CellStyle = ecaStyle;
rowcount += 2;
}
rowcount = 46;
foreach (var duty in duties)
{
row = sheet.GetRow(rowcount);
var activitycell = row.GetCell(7);
activitycell.SetCellValue(duty.eca.name);
activitycell.CellStyle = ecaStyle;
var postcell = row.GetCell(11) ?? row.CreateCell(11);
postcell.SetCellValue(duty.post);
postcell.CellStyle = ecaStyle;
var achievementcell = row.GetCell(13) ?? row.CreateCell(13);
achievementcell.SetCellValue(duty.year);
achievementcell.CellStyle = ecaStyle;
rowcount += 2;
}
// remarks
var remarkcell = sheet.GetRow(53).GetCell(0);
remarkcell.SetCellValue(r.test_remarks);
remarkcell.CellStyle = wrapStyle;
templateWorkbook.Write(ms);
}
// return created file path);
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Testimonial_{0}.xls", usr.ToName())));
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
int BEGIN_COLUMN_INDEX = 1;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
ws.Columns[0].Width = 20 * 20; // make the first column smaller
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "Session Length by Camp (Online Data Only)";
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
// Create Report SubHeader - usually it's camp year and report generation time
CellStyle styleReportSubHeader = new CellStyle();
styleReportSubHeader.Font.Size = 16 * 20;
styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Style = styleReportSubHeader;
SubHeader.Value = string.Format("Camp Year: {0}. Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
iRow += 4;
CellStyle styleTableTitle = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
styleTableTitle.Font.Size = 16 * 20;
styleTableTitle.FillPattern.SetSolid(Color.LightBlue);
styleTableTitle.Font.Weight = ExcelFont.BoldWeight;
CellStyle styleTableDataRow = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
CellStyle styleTableDataRowCampName = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Left
};
CellStyle styleTableHeaderColumns = new CellStyle();
styleTableHeaderColumns.Font.Weight = ExcelFont.BoldWeight;
styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
// Data Content of report
DataSet dsModified = GetDataFromRepository();
// Get rid of first columns for other tables except first one
for (int i = 1; i < dsModified.Tables.Count; i++)
{
dsModified.Tables[i].Columns.Remove("Camp Name");
}
int table_column_count = dsModified.Tables[0].Columns.Count;
int times = 0, current_starting_column = 0;
foreach (DataTable dt in dsModified.Tables)
{
if (times == 1)
{
BEGIN_COLUMN_INDEX += 1;
}
// Get the header location
current_starting_column = BEGIN_COLUMN_INDEX + times * table_column_count;
int temp_column = 0;
if (times == 0)
{
temp_column = current_starting_column + 1;
}
else
{
temp_column = current_starting_column;
}
// Table Title
CellRange TableTitle = ws.Cells.GetSubrangeAbsolute(iRow - 1, temp_column, iRow - 1, current_starting_column + dt.Columns.Count - 1);
TableTitle.Merged = true;
TableTitle.Value = dt.TableName;
TableTitle.Style = styleTableTitle;
// this creats the real table
ws.InsertDataTable(dt, iRow, current_starting_column, true);
// loop through each column and set style accordingly
for (int i = current_starting_column; i <= (dt.Columns.Count + current_starting_column - 1); i++)
{
ws.Cells[iRow, i].Style = styleTableHeaderColumns;
ws.Cells[iRow + dt.Rows.Count, i].Style = styleTableHeaderColumns;
ws.Columns[i].Width = 11 * 256;
// first column of first table, e.g. camp naem
if (times == 0)
{
if (i == current_starting_column)
{
ws.Columns[i].Width = 55 * 256; // camp/program name
}
else if (i == current_starting_column + 1)
{
ws.Columns[i].Width = 15 * 256;
}
}
else if (i == current_starting_column)
{
ws.Columns[i].Width = 15 * 256;
}
}
// Set the data row style
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Rows[j].Style = styleTableDataRow;
}
// left justify the camp names
if (times == 0)
{
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Cells[j, current_starting_column].Style = styleTableDataRowCampName;
}
}
times++;
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}SessionLengthByCamp.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
public static CellStyle ToBold(this CellStyle style)
{
style.Font.Weight = ExcelFont.BoldWeight;
return(style);
}
private void saveButton_Click(object sender, EventArgs e)
{
if (dataTable.RowCount == 0)
{
return;
}
int rowIndex = dataTable.Rows.Add();
string filename;
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Xlsx files|*.xlsx|Xls files|*.xls";
DialogResult res = sfd.ShowDialog();
filename = sfd.FileName;
var regExp = @"^(?:[\w]\:|\\)(\\[\p{L}_\-\.\s0-9]+)+\.((xlsx)|(xls))$";
Regex regex = new Regex(regExp);
if (res == DialogResult.OK && regex.IsMatch(filename))
{
filename = sfd.FileName;
}
else if (res == DialogResult.Cancel)
{
return;
}
else
{
MessageBoxIcon messageBoxIcon = MessageBoxIcon.Error;
MessageBox.Show("Could not save file\nTry again!", "Saving failed", MessageBoxButtons.OK, messageBoxIcon);
return;
}
using (ExcelPackage excel = new ExcelPackage())
{
//var xlApp = new OfficeOpenXml.FormulaParsing.Excel.Application();
excel.Workbook.Worksheets.Add("Worksheet1");
FileInfo excelFile = new FileInfo(filename);
OfficeOpenXml.ExcelWorksheet excelWorkSheet = excel.Workbook.Worksheets["Worksheet1"];
var style = new CellStyle();
style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
style.VerticalAlignment = VerticalAlignmentStyle.Center;
excelWorkSheet.Cells["A1"].Value = "Date:";
excelWorkSheet.Cells["B1"].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
excelWorkSheet.Column(2).Width = 20;
excelWorkSheet.Cells["A2"].Value = "Available sources:";
excelWorkSheet.Cells["B2"].Value = "Robots:";
excelWorkSheet.Cells["B2"].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Right;
excelWorkSheet.Cells["B3"].Value = "Operators:";
excelWorkSheet.Cells["B3"].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Right;
excelWorkSheet.Cells["C3"].Value = robotLimit;
excelWorkSheet.Cells["C2"].Value = operatorLimit;
excelWorkSheet.Cells["A5"].Value = "Product";
excelWorkSheet.Cells["B5"].Value = "Number of robots";
excelWorkSheet.Cells["C5"].Value = "Number of operators";
excelWorkSheet.Cells["D5"].Value = "Profit";
var mergedRange = excelWorkSheet.Cells["A4:D4"];
mergedRange.Merge = true;
mergedRange.Value = "Allocation by products";
mergedRange.Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
for (int i = 1; i < 10; i++)
{
excelWorkSheet.Cells["A" + i].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
excelWorkSheet.Cells["B" + i].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
excelWorkSheet.Cells["C" + i].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
excelWorkSheet.Cells["D" + i].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
}
//writeText.WriteLine("Available sources:;" + "robots;" + 10);
//writeText.WriteLine(";operators;" + 4);
//writeText.Write("All products: " + ";" + prodCount + "\n");
//writeText.WriteLine("Allocation by products:");
//writeText.Write("Product" + ";" + "Number of robots" + ";" + "Number of operators" + ";" + "Profit\n");
string fromYellowLine;
string toYellowLine;
string all;
int prodcnt = 1;
int rowCnt = 6;
foreach (var i in indexList) //
{
Console.WriteLine("forban");
Table table = tables[i.Product];
double profit = form.getU(i.Product, i.Robot, i.Worker);
Console.WriteLine("pr: " + profit);
excelWorkSheet.Cells["A" + rowCnt].Value = i.Product + 1 + ".";
excelWorkSheet.Cells["B" + rowCnt].Value = i.Robot;
excelWorkSheet.Cells["C" + rowCnt].Value = i.Worker;
excelWorkSheet.Cells["D" + rowCnt].Value = profit;
//excelWorkSheet.Cells["D" + rowCnt].Style.Numberformat = (OfficeOpenXml.Style.ExcelNumberFormat)NumberFormatBuilder.Accounting(3, true);
//writeText.WriteLine(prodcnt + "." + ";" + i[j] + ";" + i[j + 1] + ";" + profit);
fromYellowLine = "A" + rowCnt.ToString();
toYellowLine = "D" + rowCnt.ToString();
all = fromYellowLine + ":" + toYellowLine;
excelWorkSheet.Cells[all].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
all = fromYellowLine + ":" + toYellowLine;
prodcnt++;
rowCnt++;
}
excelWorkSheet.Cells["A" + rowCnt].Value = "Total";
excelWorkSheet.Cells["B" + rowCnt].Value = allRobot;
excelWorkSheet.Cells["C" + rowCnt].Value = allWorker;
excelWorkSheet.Cells["D" + rowCnt].Value = profitSum;
fromYellowLine = "A" + rowCnt.ToString();
toYellowLine = "D" + rowCnt.ToString();
all = fromYellowLine + ":" + toYellowLine;
excelWorkSheet.Cells[all].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
excelWorkSheet.Cells[all].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[all].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
excelWorkSheet.Column(1).AutoFit();
excelWorkSheet.Column(2).AutoFit();
excelWorkSheet.Column(3).AutoFit();
excelWorkSheet.Column(4).AutoFit();
excelWorkSheet.Column(5).AutoFit();
excel.SaveAs(excelFile);
}
}
public static void ExportToExcelExtended(DataTable table, string Name)
{
//ExcelHelper excel = new ExcelHelper();
//excel.CreateFile(Name);
//excel.CreateSheet(Name);
InitializeWorkbook();
Sheet sheet = hssfworkbook.CreateSheet("new sheet");
//ExcelDocument document = new ExcelDocument();
//document.UserName = "******";
//document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;
//document.ColumnWidth(0, 120);
//document.ColumnWidth(1, 80);
//document[0, 0].Value = Name;
//document[0, 0].Font = new System.Drawing.Font("Tahoma", 10, System.Drawing.FontStyle.Bold);
//document[0, 0].ForeColor = ExcelColor.DarkRed;
//document[0, 0].Alignment = Alignment.Centered;
//document[0, 0].BackColor = ExcelColor.Silver;
int i = 1; //стрічка
int j = 1; //колонка
NPOI.SS.UserModel.Row header = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)
{
NPOI.SS.UserModel.Cell cell = header.CreateCell(j);
cell.SetCellValue(column.ColumnName);
j++;
}
j = 1;
foreach (DataRow r in table.Rows)
{
j = 1;
//NPOI.SS.UserModel.Row row = excel.CreateRow(i);
NPOI.SS.UserModel.Row row = sheet.CreateRow(i);
foreach (DataColumn column in table.Columns)
{
NPOI.SS.UserModel.Cell cell = row.CreateCell(j);
Type dataType = column.DataType;
switch (dataType.Name)
{
case "String":
{
cell.SetCellValue(r.ItemArray[j - 1].ToString());
break;
}
case "DateTime":
{
DateTime date = (Convert.ToDateTime(r.ItemArray[j - 1]));
cell.SetCellValue(date);
CellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyyy-MM-dd h:mm:ss");
cell.CellStyle = cellStyle;
break;
}
case "Decimal":
{
if (r.ItemArray[j - 1] != null)
{
if (r.ItemArray[j - 1] != null)
{
if (r.ItemArray[j - 1].ToString().Length > 0)
{
cell.SetCellValue(Convert.ToDouble(r.ItemArray[j - 1]));
}
}
}
break;
}
default:
{
cell.SetCellValue(r.ItemArray[j - 1].ToString());
break;
}
}
//excel.SetCellValue( r.ItemArray[j]
j++;
}
//foreach (object item in r.ItemArray)
//{
// //document.Cell(i,j).Format
// excel.CreateCell(j);
// excel.SetCellValue( item == null ? "" : (item);
// j++;
//}
i++;
}
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.FileName = Name;
saveFileDialog1.DefaultExt = "xls";
saveFileDialog1.ShowDialog();
//FileStream stream = new FileStream(saveFileDialog1.FileName, FileMode.Create);
WriteToFile(saveFileDialog1.FileName);
//System.Diagnostics.Process.Start(saveFileDialog1.FileName);
}
static void Main()
{
// 1. BASIC TABLE EXAMPLE
var basicTable = new TextTable(3);
basicTable.AddCell("Artist");
basicTable.AddCell("Album");
basicTable.AddCell("Year");
basicTable.AddCell("Jamiroquai");
basicTable.AddCell("Emergency on Planet Earth");
basicTable.AddCell("1993");
basicTable.AddCell("Jamiroquai");
basicTable.AddCell("The Return of the Space Cowboy");
basicTable.AddCell("1994");
Console.WriteLine(basicTable.Render());
// +----------+-------------------------------+-----+
// |Artist |Album |Year|
// +----------+-------------------------------+-----+
// |Jamiroquai|Emergency on Planet Earth |1993|
// |Jamiroquai|The Return of the Space Cowboy|1994|
// +----------+-------------------------------+-----+
// 2. ADVANCED TABLE EXAMPLE
var numberStyleAdvancedTable = new CellStyle(CellHorizontalAlignment.Right);
var advancedTable = new TextTable(3, TableBordersStyle.DESIGN_FORMAL, TableVisibleBorders.SURROUND_HEADER_FOOTER_AND_COLUMNS);
advancedTable.SetColumnWidthRange(0, 6, 14);
advancedTable.SetColumnWidthRange(1, 4, 12);
advancedTable.SetColumnWidthRange(2, 4, 12);
advancedTable.AddCell("Region");
advancedTable.AddCell("Orders", numberStyleAdvancedTable);
advancedTable.AddCell("Sales", numberStyleAdvancedTable);
advancedTable.AddCell("North");
advancedTable.AddCell("6,345", numberStyleAdvancedTable);
advancedTable.AddCell("$87.230", numberStyleAdvancedTable);
advancedTable.AddCell("Center");
advancedTable.AddCell("837", numberStyleAdvancedTable);
advancedTable.AddCell("$12.855", numberStyleAdvancedTable);
advancedTable.AddCell("South");
advancedTable.AddCell("5,344", numberStyleAdvancedTable);
advancedTable.AddCell("$72.561", numberStyleAdvancedTable);
advancedTable.AddCell("Total", numberStyleAdvancedTable, 2);
advancedTable.AddCell("$172.646", numberStyleAdvancedTable);
Console.WriteLine(advancedTable.Render());
// ======================
// Region Orders Sales
// ------ ------ --------
// North 6,345 $87.230
// Center 837 $12.855
// South 5,344 $72.561
// ------ ------ --------
// Total $172.646
// ======================
// 3. FANCY TABLE EXAMPLE
var numberStyleFancyTable = new CellStyle(CellHorizontalAlignment.Right);
var fancyTable = new TextTable(3, TableBordersStyle.DESIGN_PAPYRUS, TableVisibleBorders.SURROUND_HEADER_FOOTER_AND_COLUMNS);
fancyTable.AddCell("Region");
fancyTable.AddCell("Orders", numberStyleFancyTable);
fancyTable.AddCell("Sales", numberStyleFancyTable);
fancyTable.AddCell("North");
fancyTable.AddCell("6,345", numberStyleFancyTable);
fancyTable.AddCell("$87.230", numberStyleFancyTable);
fancyTable.AddCell("Center");
fancyTable.AddCell("837", numberStyleFancyTable);
fancyTable.AddCell("$12.855", numberStyleFancyTable);
fancyTable.AddCell("South");
fancyTable.AddCell("5,344", numberStyleFancyTable);
fancyTable.AddCell("$72.561", numberStyleFancyTable);
fancyTable.AddCell("Total", numberStyleFancyTable, 2);
fancyTable.AddCell("$172.646", numberStyleFancyTable);
Console.WriteLine(fancyTable.Render());
// o~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~o
// ) Region Orders Sales (
// ) ~~~~~~ ~~~~~~ ~~~~~~~~ (
// ) North 6,345 $87.230 (
// ) Center 837 $12.855 (
// ) South 5,344 $72.561 (
// ) ~~~~~~ ~~~~~~ ~~~~~~~~ (
// ) Total $172.646 (
// o~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~o
// 4. UNICODE TABLE EXAMPLE
var numberStyleUnicodeTable = new CellStyle(CellHorizontalAlignment.Right);
var unicodeTable = new TextTable(3, TableBordersStyle.UNICODE_BOX_DOUBLE_BORDER_WIDE, TableVisibleBorders.SURROUND_HEADER_FOOTER_AND_COLUMNS, true);
unicodeTable.AddCell("Region");
unicodeTable.AddCell("Orders", numberStyleUnicodeTable);
unicodeTable.AddCell("Sales", numberStyleUnicodeTable);
unicodeTable.AddCell("North");
unicodeTable.AddCell("6,345", numberStyleUnicodeTable);
unicodeTable.AddCell("$87.230", numberStyleUnicodeTable);
unicodeTable.AddCell("Center");
unicodeTable.AddCell("837", numberStyleUnicodeTable);
unicodeTable.AddCell("$12.855", numberStyleUnicodeTable);
unicodeTable.AddCell("South");
unicodeTable.AddCell("5,344", numberStyleUnicodeTable);
unicodeTable.AddCell("$72.561", numberStyleUnicodeTable);
unicodeTable.AddCell("Total", numberStyleUnicodeTable, 2);
unicodeTable.AddCell("$172.646", numberStyleUnicodeTable);
var unicodeTableStringArray = unicodeTable.RenderAsStringArray();
var sb = new StringBuilder("<html><body><pre>");
foreach (string line in unicodeTableStringArray)
{
sb.Append(line);
sb.Append("<br>");
}
sb.Append("</pre></html>");
File.WriteAllText("unicode.html", sb.ToString(), Encoding.UTF8);
// unicode.html
// ╔════════╤════════╤══════════╗
// ║ Region │ Orders │ Sales ║
// ╟────────┼────────┼──────────╢
// ║ North │ 6,345 │ $87.230 ║
// ║ Center │ 837 │ $12.855 ║
// ║ South │ 5,344 │ $72.561 ║
// ╟────────┴────────┼──────────╢
// ║ Total │ $172.646 ║
// ╚═════════════════╧══════════╝
}
public CustomStylesheet()
{
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
//Boarder Index 1
border = new Border();
border.LeftBorder = new LeftBorder();
border.LeftBorder.Style = BorderStyleValues.Thin;
border.RightBorder = new RightBorder();
border.RightBorder.Style = BorderStyleValues.Thin;
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
//Boarder Index 2
border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// index 1
// Format dd/mm/yyyy
cf = new CellFormat();
cf.NumberFormatId = 14;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
// Format #,##0.00
cf = new CellFormat();
cf.NumberFormatId = 4;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
// Header text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 8
// column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
// coloured 2 decimal text
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 10
// coloured column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
this.Append(nfs);
this.Append(fts);
this.Append(fills);
this.Append(borders);
this.Append(csfs);
this.Append(cfs);
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
this.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
this.Append(tss);
}
/// <summary>
/// Creates a basic stylesheet
/// Can be overriden for custom cell styles
/// </summary>
/// <returns></returns>
private Stylesheet AddStylesheet()
{
Stylesheet stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// create fonts
Fonts fonts = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
int fontId;
var simpleFont = GetFont(new BsGridExcelStyle(), out fontId);
var boldFont = GetFont(new BsGridExcelStyle()
{
Font = new BsGridExcelFont()
{
Bold = true
}
}, out fontId);
fonts.Append(simpleFont);
fonts.Append(boldFont);
// create fills
Fills fills = new Fills()
{
Count = (UInt32Value)1U
};
int fillId;
var noneFill = GetFill(new BsGridExcelStyle()
{
FillPattern = PatternValues.None
}, out fillId); //needed, reserved by excel
var greyFill = GetFill(new BsGridExcelStyle()
{
FillPattern = PatternValues.Gray125
}, out fillId); //needed, reserved by excel
fills.Append(noneFill);
fills.Append(greyFill);
// create borders
Borders borders = new Borders()
{
Count = (UInt32Value)1U
};
var border = ExcelHelpers.CreateBorder();
borders.Append(border);
// create cell style formats
CellStyleFormats cellStyleFormats = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats.Append(cellFormat);
CellFormats cellFormats = new CellFormats()
{
Count = (UInt32Value)2U
};
int formatId;
CellFormat headerCellFormat = GetCellFormat(new BsGridExcelStyle
{
FontId = 1,
FillId = 0
}, out formatId);
CellFormat dataCellFormat = GetCellFormat(new BsGridExcelStyle
{
FontId = 0,
FillId = 0
}, out formatId);
cellFormats.Append(headerCellFormat);
cellFormats.Append(dataCellFormat);
CellStyles cellStyles = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles.Append(cellStyle);
// add to stylesheet
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
stylesheet.Append(cellStyles);
return(stylesheet);
}
internal static Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
Font ft = new Font();
FontName ftn = new FontName
{
Val = "Calibri"
};
FontSize ftsz = new FontSize
{
Val = 11
};
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
Borders borders = new Borders();
Border border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
};
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cfs.Append(cf);
NumberingFormat nf;
nf = new NumberingFormat
{
NumberFormatId = iExcelIndex++,
FormatCode = "yyyy-mm-dd hh:mm:ss"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
//DifferentialFormats dfs = new DifferentialFormats();
//dfs.Count = 0;
//ss.Append(dfs);
//TableStyles tss = new TableStyles();
//tss.Count = 0;
//tss.DefaultTableStyle = "TableStyleMedium9";
//tss.DefaultPivotStyle = "PivotStyleLight16";
//ss.Append(tss);
return(ss);
}
/// <summary>
/// 设置标题样式
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel TitleStyle(IWorkSheet sheet, CellStyle style)
{
return(this);
}
public static CellStyle ToItalic(this CellStyle style)
{
style.Font.Italic = true;
return(style);
}
private static Stylesheet CreateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet();
DocumentFormat.OpenXml.Spreadsheet.Fonts fonts1 = new DocumentFormat.OpenXml.Spreadsheet.Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontSize fontSize1 = new FontSize()
{
Val = 11
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
DocumentFormat.OpenXml.Spreadsheet.FontScheme fontScheme1 = new DocumentFormat.OpenXml.Spreadsheet.FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
DocumentFormat.OpenXml.Spreadsheet.Font font2 = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontSize fontSize2 = new FontSize()
{
Val = 14
};
Color color2 = new Color()
{
Rgb = "FF0070C0"
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
DocumentFormat.OpenXml.Spreadsheet.FontScheme fontScheme2 = new DocumentFormat.OpenXml.Spreadsheet.FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(fontSize1);
font1.Append(color2);
font1.Append(fontName2);
font1.Append(fontFamilyNumbering2);
font1.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)4U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
stylesheet1.Append(fonts1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
return(stylesheet1);
}
public static CellStyle ToUnderline(this CellStyle style, UnderlineStyle underlineStyle = UnderlineStyle.Single)
{
style.Font.UnderlineStyle = underlineStyle;
return(style);
}
private Stylesheet CreateStylesheet()
{
var ss = new Stylesheet();
var fts = new Fonts();
var ftn = new FontName {
Val = "Arial"
};
var ftsz = new FontSize {
Val = 11
};
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font {
FontName = ftn, FontSize = ftsz
};
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
var csfs = new CellStyleFormats();
var cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
};
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
// dd/mm/yyyy is also Excel style index 14
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var cfs = new CellFormats();
cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
var nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 165;
nfs = new NumberingFormats();
cfs = new CellFormats();
cf = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 170;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
iExcelIndex = 171;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "#,##0.00"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// @ is also Excel style index 49
iExcelIndex = 172;
nf = new NumberingFormat {
NumberFormatId = iExcelIndex, FormatCode = "@"
};
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
var css = new CellStyles();
var cs = new CellStyle {
Name = "Normal", FormatId = 0, BuiltinId = 0
};
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
ss.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
};
ss.Append(tss);
return(ss);
}
public static CellStyle ToFontSize(this CellStyle style, int fontSize)
{
style.Font.Size = 20 * fontSize;
return(style);
}
/// <summary>
/// 设置正文样式
/// </summary>
/// <param name="style">单元格样式</param>
public IExport BodyStyle(CellStyle style)
{
_bodyStyle = style;
return(this);
}
public static void HighlightedCell(CellStyle style)
{
style.FillPattern.SetSolid(RowColor);
}
/// <summary>
/// 默认标题单元格样式应用程序
/// </summary>
/// <param name="cellStyle">单元格样式</param>
internal static void DefaultTitleCellStyleApplier(CellStyle cellStyle)
{
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.FontBoldWeight = 700;
}
/// <summary>
/// If receives CellStyle Text, returns CellStyle.LightBlue. If receives style LightBlue
/// (or any other style) it returns CellStyle.Text.
/// </summary>
/// <param name="currStyle">CellStyle.Text or CellStyle.LightBlue</param>
/// <returns></returns>
private CellStyle toggleStyle(CellStyle currStyle)
{
if (currStyle == CellStyle.Text)
{
return CellStyle.LightBlue;
}
else
{
return CellStyle.Text;
}
}
public void WriteExcelStyledCell(object value, CellStyle style)
{
if (_writer == null) throw new InvalidOperationException("Cannot write after closing.");
_writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("StyleID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
_writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
switch (style)
{
case CellStyle.General:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
break;
case CellStyle.Number:
case CellStyle.Currency:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "Number");
break;
case CellStyle.ShortDate:
case CellStyle.DateTime:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "DateTime");
break;
}
_writer.WriteValue(value);
// tag += String.Format("{1}\"><ss:Data ss:Type=\"DateTime\">{0:yyyy\\-MM\\-dd\\THH\\:mm\\:ss\\.fff}</ss:Data>", value,
_writer.WriteEndElement();
_writer.WriteEndElement();
}
public static void TitleCell(CellStyle style)
{
style.FillPattern.SetSolid(TitleColor);
style.Font.Weight = ExcelFont.BoldWeight;
}
public static void SetCellStyle(CellStyle cellStyle, CellBorderType borderType, HorizontalAlignment hAlign, VerticalAlignment vAlign)
{
cellStyle.BorderTop = borderType;
cellStyle.BorderBottom = borderType;
cellStyle.BorderLeft = borderType;
cellStyle.BorderRight = borderType;
cellStyle.Alignment = hAlign;
cellStyle.VerticalAlignment = vAlign;
}
public static CellStyle ToDate(this CellStyle style)
{
style.NumberFormat = "mm/dd/yyyy";
return(style);
}
public static CellStyle ToColorForeground(this CellStyle style, SpreadsheetColor color)
{
style.Font.Color = color;
return(style);
}
public static Stylesheet GenerateStylesheet2()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "#,##0.0000";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "#,##0.00";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// @ is also Excel style index 49
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "@";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = "Normal";
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return(ss);
}
/// <summary>
/// Write out the columns we must display for this row.
/// </summary>
/// <param name="row"></param>
/// <param name="recon"></param>
/// <param name="formatType">What CellFormatType to use for the cell</param>
private void writeRowCommonColumns(DataRow row, List<QueryColumn> columns, reportSection section, CellStyle formatType)
{
QueryColumn queryColumn;
excelRow = new Dictionary<string, Cell>();
string columnName;
int currColumnIndex = 0;
// Write out identifying columns
for (int i = 0; i < columns.Count; i++)
{
queryColumn = columns[i];
if (queryColumn.IdentifyingColumn)
{
if (section == reportSection.FirstQueryRowWithoutMatch)
{
columnName = queryColumn.FirstQueryColName;
}
else
{
columnName = queryColumn.SecondQueryColName;
}
excelRow.Add(columnLetters[currColumnIndex].ToString(), new Cell(row[columnName].ToString(), formatType));
++currColumnIndex;
}
}
// Write out columns marked AlwaysDisplay (that aren't also identifying columns).
for (int i = 0; i < columns.Count; i++)
{
queryColumn = columns[i];
if ((queryColumn.AlwaysDisplay || queryColumn.CheckDataMatch) && !queryColumn.IdentifyingColumn)
{
if (section == reportSection.FirstQueryRowWithoutMatch)
{
columnName = queryColumn.FirstQueryColName;
}
else
{
columnName = queryColumn.SecondQueryColName;
}
// Some columns marked 'AlwaysDisplay' may not exist on one of the two queries
if (columnName != null)
{
excelRow.Add(columnLetters[currColumnIndex].ToString(), new Cell(row[columnName].ToString(), formatType));
}
else
{
excelRow.Add(columnLetters[currColumnIndex].ToString(), new Cell("", formatType));
}
++currColumnIndex;
}
}
rrExcelService.AddRow(excelRow);
}
public static CellStyle ToColorBackground(this CellStyle style, SpreadsheetColor color)
{
style.FillPattern.SetSolid(color);
return(style);
}
/// <summary>
/// Write out the columns we must display for this row.
/// </summary>
/// <param name="row"></param>
/// <param name="recon"></param>
/// <param name="formatType">What CellFormatType to use for the cell</param>
private int writeDiffRowCommonColumns(DataRow q1Row, DataRow q2Row, List<QueryColumn> columns, CellStyle formatType)
{
QueryColumn queryColumn;
excelRow = new Dictionary<string, Cell>();
string columnValue;
int currColumnIndex = 0;
// Write out identifying columns
for (int i = 0; i < columns.Count; i++)
{
queryColumn = columns[i];
if (queryColumn.IdentifyingColumn)
{
excelRow.Add(columnLetters[currColumnIndex].ToString(), new Cell(q1Row[queryColumn.FirstQueryColName].ToString(), formatType));
++currColumnIndex;
}
}
// Write out columns marked AlwaysDisplay (that aren't also identifying columns).
for (int i = 0; i < columns.Count; i++)
{
queryColumn = columns[i];
if (queryColumn.AlwaysDisplay && !queryColumn.IdentifyingColumn)
{
// Some columns marked 'AlwaysDisplay' may not exist on one of the two queries.
// Use value on first query if it exists on either the first query or both, otherwise use
// the value in the second query.
if (queryColumn.FirstQueryColName != null)
{
columnValue = q1Row[queryColumn.FirstQueryColName].ToString();
}
else
{
columnValue = q2Row[queryColumn.SecondQueryColName].ToString();
}
excelRow.Add(columnLetters[currColumnIndex].ToString(), new Cell(columnValue, formatType));
++currColumnIndex;
}
}
return currColumnIndex;
}
public static CellStyle To2Digits(this CellStyle style)
{
return(style.ToCurrency(false, true));
}
private void WriteExcelStyleElement(CellStyle style, string NumberFormat)
{
_writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
_writer.WriteStartElement("NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("Format", "urn:schemas-microsoft-com:office:spreadsheet", NumberFormat);
_writer.WriteEndElement();
_writer.WriteEndElement();
}
public static CellStyle ToPercent(this CellStyle style)
{
style.NumberFormat = "0.0%";
return(style);
}
/// <summary> Adds the controls for this result viewer to the place holder on the main form </summary>
/// <param name="placeHolder"> Main place holder ( "mainPlaceHolder" ) in the itemNavForm form into which the the bulk of the result viewer's output is displayed</param>
/// <param name="Tracer"> Trace object keeps a list of each method executed and important milestones in rendering </param>
/// <returns> Sorted tree with the results in hierarchical structure with volumes and issues under the titles and sorted by serial hierarchy </returns>
public override void Add_HTML(PlaceHolder placeHolder, Custom_Tracer Tracer)
{
if (Tracer != null)
{
Tracer.Add_Trace("Export_File_ResultsViewer.Add_HTML", "Rendering results in text view");
}
//// Sort this by BIbID
//resultTable.Sort("BibID");
//// Determine which rows to display
//int lastRow = base.LastRow;
//int startRow = base.StartRow(lastRow);
//// prepare to step through each title to display
//List<SobekCM_Item_Collection.SobekCM_Item_Row> itemRows = new List<SobekCM_Item_Collection.SobekCM_Item_Row>();
// Prepare to build an output
StringBuilder resultsBldr = new StringBuilder(5000);
int currentPage = RequestSpecificValues.Current_Mode.Page.HasValue ? RequestSpecificValues.Current_Mode.Page.Value : 1;
if (currentPage < 2)
{
resultsBldr.Append("<br />" + Environment.NewLine + "<div class=\"SobekHomeText\">" + Environment.NewLine + "<blockquote>" + Environment.NewLine );
resultsBldr.Append("This option allows you to export the list of results which match your search or browse to an excel document or CSV file for download.<br /><br />");
resultsBldr.Append("Select the file type below to create the report:");
resultsBldr.Append("<blockquote>");
//currentMode.Page = 2;
//resultsBldr.Append("<a href=\"" + currentMode.Redirect_URL() + "\">Excel Spreadsheet file (XLSX)</a><br /><br />");
RequestSpecificValues.Current_Mode.Page = 3;
resultsBldr.Append("<a href=\"" + UrlWriterHelper.Redirect_URL(RequestSpecificValues.Current_Mode) + "\">Excel Spreadsheet file (XLS)</a><br /><br />");
RequestSpecificValues.Current_Mode.Page = 4;
resultsBldr.Append("<a href=\"" + UrlWriterHelper.Redirect_URL(RequestSpecificValues.Current_Mode) + "\">Comma-seperated value text file (CSV)</a><br /><br />");
//currentMode.Page = 5;
//resultsBldr.Append("<a href=\"" + currentMode.Redirect_URL() + "\">HTML Table (HTML)</a><br /><br />");
resultsBldr.Append("</blockquote>");
resultsBldr.Append("</blockquote>");
resultsBldr.Append("<br /><br />");
resultsBldr.Append("</div>");
// Add this to the page
Literal mainLiteral = new Literal {Text = resultsBldr.ToString()};
placeHolder.Controls.Add(mainLiteral);
}
else
{
string filename = RequestSpecificValues.Current_Mode.Instance_Name;
// Set the Gembox spreadsheet license key
string from_db = String.Empty;
string key = String.Empty;
if (UI_ApplicationCache_Gateway.Settings.Additional_Settings.ContainsKey("Spreadsheet Library License"))
{
try
{
key = UI_ApplicationCache_Gateway.Settings.Additional_Settings["Spreadsheet Library License"];
SecurityInfo thisDecryptor = new SecurityInfo();
string encryptedPassword = thisDecryptor.DecryptString(from_db, "*h3kj(83", "unsalted");
}
catch (Exception )
{
}
}
SpreadsheetInfo.SetLicense(key);
// Create the excel file and worksheet
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet excelSheet = excelFile.Worksheets.Add(RequestSpecificValues.Current_Mode.Instance_Name);
excelFile.Worksheets.ActiveWorksheet = excelSheet;
// Create the header cell style
CellStyle headerStyle = new CellStyle
{ HorizontalAlignment = HorizontalAlignmentStyle.Left, Font = {Weight = ExcelFont.BoldWeight} };
headerStyle.FillPattern.SetSolid(Color.Yellow);
headerStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Create the title cell style
CellStyle titleStyle = new CellStyle
{ HorizontalAlignment = HorizontalAlignmentStyle.Left, Font = {Weight = ExcelFont.BoldWeight, Size = 12*20} };
// Set the default style
CellStyle defaultStyle = new CellStyle();
defaultStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
const int columnSelector = 0;
int row = 0;
if (RequestSpecificValues.Current_Mode.Page != 4)
{
// Add the title
excelSheet.Cells[1, columnSelector].Value = "Search or browse result list";
excelSheet.Cells[1, columnSelector].Style = titleStyle;
row = 3;
}
// Add the column headers
excelSheet.Cells[row, columnSelector].Value = "System ID";
excelSheet.Cells[row, columnSelector].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 1].Value = "Link";
excelSheet.Cells[row, columnSelector + 1].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 2].Value = "Group Title";
excelSheet.Cells[row, columnSelector + 2].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 3].Value = "Item Title";
excelSheet.Cells[row, columnSelector + 3].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 4].Value = "Date";
excelSheet.Cells[row, columnSelector + 4].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 5].Value = "Author";
excelSheet.Cells[row, columnSelector + 5].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 6].Value = "Publisher";
excelSheet.Cells[row, columnSelector + 6].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 7].Value = "Format";
excelSheet.Cells[row, columnSelector + 7].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 8].Value = "Edition";
excelSheet.Cells[row, columnSelector + 8].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 9].Value = "Subjects";
excelSheet.Cells[row, columnSelector + 9].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 10].Value = "Measurement";
excelSheet.Cells[row, columnSelector + 10].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 11].Value = "Style / Period";
excelSheet.Cells[row, columnSelector + 11].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 12].Value = "Technique";
excelSheet.Cells[row, columnSelector + 12].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 13].Value = "Institution";
excelSheet.Cells[row, columnSelector + 13].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 14].Value = "Donor";
excelSheet.Cells[row, columnSelector + 14].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 15].Value = "OCLC";
excelSheet.Cells[row, columnSelector + 15].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 16].Value = "ALEPH";
excelSheet.Cells[row, columnSelector + 16].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 17].Value = "Serial1";
excelSheet.Cells[row, columnSelector + 17].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 18].Value = "Serial2";
excelSheet.Cells[row, columnSelector + 18].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 19].Value = "Serial3";
excelSheet.Cells[row, columnSelector + 19].Style = headerStyle;
// Set the correct widths
excelSheet.Columns[columnSelector].Width = 12 * 256;
excelSheet.Columns[columnSelector + 1].Width = 8 * 256;
excelSheet.Columns[columnSelector + 2].Width = 40 * 256;
excelSheet.Columns[columnSelector + 3].Width = 40 * 256;
excelSheet.Columns[columnSelector + 4].Width = 20 * 256;
excelSheet.Columns[columnSelector + 5].Width = 14 * 256;
excelSheet.Columns[columnSelector + 6].Width = 14 * 256;
excelSheet.Columns[columnSelector + 7].Width = 20 * 256;
excelSheet.Columns[columnSelector + 8].Width = 20 * 256;
excelSheet.Columns[columnSelector + 9].Width = 20 * 256;
excelSheet.Columns[columnSelector + 10].Width = 20 * 256;
excelSheet.Columns[columnSelector + 11].Width = 20 * 256;
excelSheet.Columns[columnSelector + 12].Width = 20 * 256;
excelSheet.Columns[columnSelector + 13].Width = 20 * 256;
excelSheet.Columns[columnSelector + 14].Width = 20 * 256;
excelSheet.Columns[columnSelector + 15].Width = 20 * 256;
excelSheet.Columns[columnSelector + 16].Width = 20 * 256;
excelSheet.Columns[columnSelector + 17].Width = 20 * 256;
excelSheet.Columns[columnSelector + 18].Width = 20 * 256;
excelSheet.Columns[columnSelector + 19].Width = 20 * 256;
row++;
// Add each row
foreach (iSearch_Title_Result titleResult in RequestSpecificValues.Paged_Results)
{
for (int item_count = 0; item_count < titleResult.Item_Count; item_count++)
{
iSearch_Item_Result itemResult = titleResult.Get_Item(item_count);
excelSheet.Cells[row, columnSelector].Value = titleResult.BibID + "_" + itemResult.VID;
excelSheet.Cells[row, columnSelector + 1].Value = RequestSpecificValues.Current_Mode.Base_URL + titleResult.BibID + "/" + itemResult.VID;
excelSheet.Cells[row, columnSelector + 2].Value = titleResult.GroupTitle.Replace("<i>","").Replace("</i>","").Replace("&","&");
excelSheet.Cells[row, columnSelector + 3].Value = itemResult.Title.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 4].Value = itemResult.PubDate.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 5].Value = titleResult.Author.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 6].Value = titleResult.Publisher.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 7].Value = titleResult.Format.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 8].Value = titleResult.Edition.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 9].Value = titleResult.Subjects.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 10].Value = titleResult.Measurement.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 11].Value = titleResult.Style_Period.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 12].Value = titleResult.Technique.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 13].Value = titleResult.Institution.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 14].Value = titleResult.Donor.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//if (titleResult.OCLC_Number > 1)
// excelSheet.Cells[row, columnSelector + 15].Value = titleResult.OCLC_Number.ToString();
//if (titleResult.ALEPH_Number > 1)
// excelSheet.Cells[row, columnSelector + 16].Value = titleResult.ALEPH_Number.ToString();
//excelSheet.Cells[row, columnSelector + 17].Value = itemResult.Level1_Text.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 18].Value = itemResult.Level2_Text.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 19].Value = itemResult.Level3_Text.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
row++;
}
}
// Set the border
excelSheet.Cells.GetSubrange("A4", "T" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("A4", "t4").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
if (Tracer != null)
{
Tracer.Add_Trace("Export_File_ResultsViewer.Add_HTML", "Clearing response");
}
// Clear any response until now
HttpContext.Current.Response.Clear();
// Output in proper format to the user
switch (RequestSpecificValues.Current_Mode.Page)
{
//case 2:
// System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats";
// System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xlsx");
// // With XLSX it is a bit more complicated as MS Packaging API can't write
// // directly to Response.OutputStream. Therefore we use temporary MemoryStream.
// System.IO.MemoryStream ms = new System.IO.MemoryStream();
// excelFile.SaveXlsx(ms);
// ms.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
// break;
case 3:
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");
excelFile.SaveXls(HttpContext.Current.Response.OutputStream);
break;
case 4:
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
excelFile.SaveCsv(HttpContext.Current.Response.OutputStream, CsvType.CommaDelimited);
break;
//case 5:
// System.Web.HttpContext.Current.Response.ContentType = "text/html";
// System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + "Report18.html");
// System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(System.Web.HttpContext.Current.Response.OutputStream, new UTF8Encoding(false));
// excelFile.SaveHtml(writer, null, true);
// writer.Close();
// break;
}
if (Tracer != null)
{
Tracer.Add_Trace("Export_File_ResultsViewer.Add_HTML", "Ending response");
}
HttpContext.Current.Response.End();
}
}
/// <summary>
/// 初始化单元格样式解析器
/// </summary>
/// <param name="workbook">工作薄</param>
/// <param name="style">单元格样式</param>
private CellStyleResolver(IWorkbook workbook, CellStyle style)
{
_workbook = workbook;
_style = style;
}
private void SetColor(Cell cell, CellStyle style)
{
if (cell.BackgroundColor != Color.Empty && cell.BackgroundColor != Color.White)
{
style.FillForegroundColor = cell.BackgroundColor.ToNPOIColor();
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
}
}
public CustomStylesheet()
{
NumberStyles = new Dictionary<NumberStyle, NumberingFormat>();
CellFormatStyles = new Dictionary<CellFormatId, uint>();
Borders = new Borders();
BorderStyles = new Dictionary<string, uint>();
Fonts = new Fonts();
FontStyles = new Dictionary<string, uint>();
RegisterBorder("None");
RegisterFont("Default", new Font
{
FontName = new FontName { Val = StringValue.FromString("Calibri") },
FontSize = new FontSize { Val = DoubleValue.FromDouble(10) }
});
var fills = CreateFills();
var numberingFormats = CreateNumberFormats();
MyCellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();
MyCellFormats.Append(new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
});
//BuildCellFormat(BorderStyle.All, NumberStyle.ForcedText);
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat
{
Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center },
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count =
UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
//var cellFormats = CreateCellFormats();
this.Append(numberingFormats);
this.Append(Fonts); // OK
this.Append(fills);
this.Append(Borders); // OK
this.Append(cellStyleFormats);
this.Append(MyCellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
private void SetBold(Cell cell, CellStyle style)
{
if (cell.IsBold)
{
Font font = _sheet.Workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
}
}
private void SetBorder(Cell cell, CellStyle style)
{
foreach (var border in cell.Borders)
{
if (border.Location != BorderLocation.None)
{
switch (border.Location)
{
case BorderLocation.Top:
style.BorderTop = border.Style.NPOIBorderType();
style.TopBorderColor = border.Color.ToNPOIColor();
break;
case BorderLocation.Bottom:
style.BorderBottom = border.Style.NPOIBorderType();
style.BottomBorderColor = border.Color.ToNPOIColor();
break;
case BorderLocation.Left:
style.BorderLeft = border.Style.NPOIBorderType();
style.LeftBorderColor = border.Color.ToNPOIColor();
break;
case BorderLocation.Right:
style.BorderRight = border.Style.NPOIBorderType();
style.RightBorderColor = border.Color.ToNPOIColor();
break;
default:
throw new InvalidOperationException("Trying to set border for non-existent side");
}
}
}
}
/**
* Clones all the style information from another
* HSSFCellStyle, onto this one. This
* HSSFCellStyle will then have all the same
* properties as the source, but the two may
* be edited independently.
* Any stylings on this HSSFCellStyle will be lost!
*
* The source HSSFCellStyle could be from another
* HSSFWorkbook if you like. This allows you to
* copy styles from one HSSFWorkbook to another.
*/
public void CloneStyleFrom(CellStyle source)
{
if (source is HSSFCellStyle)
{
this.CloneStyleFrom((HSSFCellStyle)source);
}
else
{
throw new ArgumentException("Can only clone from one HSSFCellStyle to another, not between HSSFCellStyle and XSSFCellStyle");
}
}
private bool write_spreadsheet(string filename, DataSet space_utilized, string code1, string code2 )
{
try
{
string worksheet_name = code1;
if (code2.Length > 0)
worksheet_name = code1 + " intersect " + code2;
// Create the excel file and worksheet
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet excelSheet = excelFile.Worksheets.Add(worksheet_name);
excelFile.Worksheets.ActiveWorksheet = excelSheet;
// Create the header cell style
CellStyle headerStyle = new CellStyle();
headerStyle.HorizontalAlignment = HorizontalAlignmentStyle.Right;
headerStyle.FillPattern.SetSolid(Color.Yellow);
headerStyle.Font.Weight = ExcelFont.BoldWeight;
headerStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Create the title cell style
CellStyle titleStyle = new CellStyle();
titleStyle.HorizontalAlignment = HorizontalAlignmentStyle.Left;
titleStyle.Font.Weight = ExcelFont.BoldWeight;
titleStyle.Font.Size = 12 * 20;
// Set the two decimal places number format style
CellStyle numberStyle = new CellStyle();
numberStyle.NumberFormat = "#,#0.00";
numberStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Set the no decimal places number format style
CellStyle numberStyle2 = new CellStyle();
numberStyle2.NumberFormat = "#,#0";
numberStyle2.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Set the default style
CellStyle defaultStyle = new CellStyle();
defaultStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
excelSheet.Columns[0].Width = 4 * 256;
// Write the online utilized space
int column_selector = 1;
if (space_utilized.Tables[0].Rows.Count > 0)
{
excelSheet.Cells[1, column_selector].Value = "Total size of items online for " + worksheet_name + " by Month/Year";
excelSheet.Cells[1, column_selector].Style = titleStyle;
excelSheet.Cells[3, column_selector].Value = "Year";
excelSheet.Cells[3, column_selector].Style = headerStyle;
excelSheet.Cells[3, column_selector + 1].Value = "Month";
excelSheet.Cells[3, column_selector + 1].Style = headerStyle;
excelSheet.Cells[3, column_selector + 2].Value = "Added Online (KB)";
excelSheet.Cells[3, column_selector + 2].Style = headerStyle;
excelSheet.Cells[3, column_selector + 3].Value = "Total Online (MB)";
excelSheet.Cells[3, column_selector + 3].Style = headerStyle;
excelSheet.Cells[3, column_selector + 4].Value = "Total Online (GB)";
excelSheet.Cells[3, column_selector + 4].Style = headerStyle;
int row = 4;
double total_size = 0;
int year = Convert.ToInt16(space_utilized.Tables[0].Rows[0][0]);
int month = Convert.ToInt16(space_utilized.Tables[0].Rows[0][0]);
foreach (DataRow thisRow in space_utilized.Tables[0].Rows)
{
int this_month = Convert.ToInt32(thisRow[1]);
int this_year = Convert.ToInt32(thisRow[0]);
while (( this_month > month + 1 ) || ( this_year > year ))
{
month++;
if (month > 12)
{
month = 0;
year++;
}
else
{
excelSheet.Cells[row, column_selector].Value = year;
excelSheet.Cells[row, column_selector].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 1].Value = month;
excelSheet.Cells[row, column_selector + 1].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 2].Value = 0;
excelSheet.Cells[row, column_selector + 2].Style = numberStyle2;
excelSheet.Cells[row, column_selector + 3].Value = total_size;
excelSheet.Cells[row, column_selector + 3].Style = numberStyle;
excelSheet.Cells[row, column_selector + 4].Value = total_size / 1024;
excelSheet.Cells[row, column_selector + 4].Style = numberStyle;
row++;
}
}
excelSheet.Cells[row, column_selector].Value = this_year;
excelSheet.Cells[row, column_selector].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 1].Value = this_month;
excelSheet.Cells[row, column_selector + 1].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 2].Value = Convert.ToInt32(thisRow[2]);
excelSheet.Cells[row, column_selector + 2].Style = numberStyle2;
total_size = total_size + (Convert.ToDouble(thisRow[2]) / 1024);
excelSheet.Cells[row, column_selector + 3].Value = total_size;
excelSheet.Cells[row, column_selector + 3].Style = numberStyle;
excelSheet.Cells[row, column_selector + 4].Value = total_size / 1024;
excelSheet.Cells[row, column_selector + 4].Style = numberStyle;
month = this_month;
year = this_year;
row++;
}
// Set the correct widths
excelSheet.Columns[column_selector].Width = 8 * 256;
excelSheet.Columns[column_selector + 1].Width = 8 * 256;
excelSheet.Columns[column_selector + 2].Width = 20 * 256;
excelSheet.Columns[column_selector + 3].Width = 20 * 256;
excelSheet.Columns[column_selector + 4].Width = 20 * 256;
// Set the border
excelSheet.Cells.GetSubrange("B4", "F" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("B4", "F4").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
// excelSheet.Cells.GetSubrange("B4", "F" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thick);
// Add to the column selector
column_selector = 7;
}
// Write the archived utilized space
if (( space_utilized.Tables.Count > 1 ) && ( space_utilized.Tables[1].Rows.Count > 0))
{
excelSheet.Cells[1, column_selector].Value = "Total size of items archived with CNS for " + worksheet_name + " by Month/Year";
excelSheet.Cells[1, column_selector].Style = titleStyle;
excelSheet.Cells[3, column_selector].Value = "Year";
excelSheet.Cells[3, column_selector].Style = headerStyle;
excelSheet.Cells[3, column_selector + 1].Value = "Month";
excelSheet.Cells[3, column_selector + 1].Style = headerStyle;
excelSheet.Cells[3, column_selector + 2].Value = "Added Archive (MB)";
excelSheet.Cells[3, column_selector + 2].Style = headerStyle;
excelSheet.Cells[3, column_selector + 3].Value = "Total Archive (MB)";
excelSheet.Cells[3, column_selector + 3].Style = headerStyle;
excelSheet.Cells[3, column_selector + 4].Value = "Total Archive (GB)";
excelSheet.Cells[3, column_selector + 4].Style = headerStyle;
int row = 4;
double total_size = 0;
int year = Convert.ToInt16(space_utilized.Tables[1].Rows[0][0]);
int month = Convert.ToInt16(space_utilized.Tables[1].Rows[0][0]);
foreach (DataRow thisRow in space_utilized.Tables[1].Rows)
{
int this_month = Convert.ToInt16(thisRow[1]);
int this_year = Convert.ToInt16(thisRow[0]);
while ((this_month > month + 1) || (this_year > year))
{
month++;
if (month > 12)
{
month = 0;
year++;
}
else
{
excelSheet.Cells[row, column_selector].Value = year;
excelSheet.Cells[row, column_selector].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 1].Value = month;
excelSheet.Cells[row, column_selector + 1].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 2].Value = 0;
excelSheet.Cells[row, column_selector + 2].Style = numberStyle2;
excelSheet.Cells[row, column_selector + 3].Value = total_size;
excelSheet.Cells[row, column_selector + 3].Style = numberStyle2;
excelSheet.Cells[row, column_selector + 4].Value = total_size / 1024;
excelSheet.Cells[row, column_selector + 4].Style = numberStyle;
row++;
}
}
excelSheet.Cells[row, column_selector].Value = this_year;
excelSheet.Cells[row, column_selector].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 1].Value = this_month;
excelSheet.Cells[row, column_selector + 1].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 2].Value = Convert.ToInt32(thisRow[2]);
excelSheet.Cells[row, column_selector + 2].Style = numberStyle2;
total_size = total_size + (Convert.ToDouble(thisRow[2]));
excelSheet.Cells[row, column_selector + 3].Value = total_size;
excelSheet.Cells[row, column_selector + 3].Style = numberStyle2;
excelSheet.Cells[row, column_selector + 4].Value = total_size / 1024;
excelSheet.Cells[row, column_selector + 4].Style = numberStyle;
month = this_month;
year = this_year;
row++;
}
// Set the correct widths
excelSheet.Columns[column_selector].Width = 8 * 256;
excelSheet.Columns[column_selector + 1].Width = 8 * 256;
excelSheet.Columns[column_selector + 2].Width = 20 * 256;
excelSheet.Columns[column_selector + 3].Width = 20 * 256;
excelSheet.Columns[column_selector + 4].Width = 20 * 256;
// Set the border
if (column_selector > 1)
{
excelSheet.Cells.GetSubrange("H4", "L4").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("H4", "L" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
}
else
{
excelSheet.Cells.GetSubrange("B4", "F4").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("B4", "F" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
}
}
// Save the file
if (filename.ToUpper().IndexOf(".XLSX") > 0)
{
excelFile.SaveXlsx(filename);
}
else
{
excelFile.SaveXls(filename);
}
return true;
}
catch ( Exception ee )
{
return false;
}
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
NumberingFormats numberingFormats1 = new NumberingFormats() { Count = (UInt32Value)2U };
NumberingFormat numberingFormat1 = new NumberingFormat() { NumberFormatId = (UInt32Value)44U, FormatCode = "_ \"$\"\\ * #,##0.00_ ;_ \"$\"\\ * \\-#,##0.00_ ;_ \"$\"\\ * \"-\"??_ ;_ @_ " };
NumberingFormat numberingFormat2 = new NumberingFormat() { NumberFormatId = (UInt32Value)165U, FormatCode = "mmmm/yyyy" };
numberingFormats1.Append(numberingFormat1);
numberingFormats1.Append(numberingFormat2);
Fonts fonts1 = new Fonts() { Count = (UInt32Value)10U };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 10D };
FontName fontName1 = new FontName() { Val = "Arial" };
font1.Append(fontSize1);
font1.Append(fontName1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize() { Val = 10D };
FontName fontName2 = new FontName() { Val = "Arial" };
font2.Append(fontSize2);
font2.Append(fontName2);
Font font3 = new Font();
Bold bold1 = new Bold();
FontSize fontSize3 = new FontSize() { Val = 10D };
FontName fontName3 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
font3.Append(bold1);
font3.Append(fontSize3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering1);
Font font4 = new Font();
Bold bold2 = new Bold();
FontSize fontSize4 = new FontSize() { Val = 10D };
Color color1 = new Color() { Indexed = (UInt32Value)9U };
FontName fontName4 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
font4.Append(bold2);
font4.Append(fontSize4);
font4.Append(color1);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering2);
Font font5 = new Font();
FontSize fontSize5 = new FontSize() { Val = 8D };
FontName fontName5 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering() { Val = 2 };
font5.Append(fontSize5);
font5.Append(fontName5);
font5.Append(fontFamilyNumbering3);
Font font6 = new Font();
Bold bold3 = new Bold();
FontSize fontSize6 = new FontSize() { Val = 8D };
FontName fontName6 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering4 = new FontFamilyNumbering() { Val = 2 };
font6.Append(bold3);
font6.Append(fontSize6);
font6.Append(fontName6);
font6.Append(fontFamilyNumbering4);
Font font7 = new Font();
Bold bold4 = new Bold();
FontSize fontSize7 = new FontSize() { Val = 15D };
FontName fontName7 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering5 = new FontFamilyNumbering() { Val = 2 };
font7.Append(bold4);
font7.Append(fontSize7);
font7.Append(fontName7);
font7.Append(fontFamilyNumbering5);
Font font8 = new Font();
FontSize fontSize8 = new FontSize() { Val = 10D };
FontName fontName8 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering6 = new FontFamilyNumbering() { Val = 2 };
font8.Append(fontSize8);
font8.Append(fontName8);
font8.Append(fontFamilyNumbering6);
Font font9 = new Font();
FontSize fontSize9 = new FontSize() { Val = 8D };
FontName fontName9 = new FontName() { Val = "Arial" };
font9.Append(fontSize9);
font9.Append(fontName9);
Font font10 = new Font();
Bold bold5 = new Bold();
FontSize fontSize10 = new FontSize() { Val = 10D };
Color color2 = new Color() { Indexed = (UInt32Value)12U };
FontName fontName10 = new FontName() { Val = "Arial" };
FontFamilyNumbering fontFamilyNumbering7 = new FontFamilyNumbering() { Val = 2 };
font10.Append(bold5);
font10.Append(fontSize10);
font10.Append(color2);
font10.Append(fontName10);
font10.Append(fontFamilyNumbering7);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
fonts1.Append(font5);
fonts1.Append(font6);
fonts1.Append(font7);
fonts1.Append(font8);
fonts1.Append(font9);
fonts1.Append(font10);
Fills fills1 = new Fills() { Count = (UInt32Value)6U };
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Indexed = (UInt32Value)23U };
BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor() { Indexed = (UInt32Value)9U };
BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor3 = new ForegroundColor() { Indexed = (UInt32Value)10U };
BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)26U };
patternFill5.Append(foregroundColor3);
patternFill5.Append(backgroundColor3);
fill5.Append(patternFill5);
Fill fill6 = new Fill();
PatternFill patternFill6 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor4 = new ForegroundColor() { Indexed = (UInt32Value)10U };
BackgroundColor backgroundColor4 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill6.Append(foregroundColor4);
patternFill6.Append(backgroundColor4);
fill6.Append(patternFill6);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
fills1.Append(fill5);
fills1.Append(fill6);
Borders borders1 = new Borders() { Count = (UInt32Value)11U };
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color3 = new Color() { Indexed = (UInt32Value)64U };
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color4 = new Color() { Indexed = (UInt32Value)64U };
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color5 = new Color() { Indexed = (UInt32Value)64U };
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder();
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
Border border3 = new Border();
LeftBorder leftBorder3 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color6 = new Color() { Indexed = (UInt32Value)64U };
leftBorder3.Append(color6);
RightBorder rightBorder3 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color7 = new Color() { Indexed = (UInt32Value)64U };
rightBorder3.Append(color7);
TopBorder topBorder3 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color8 = new Color() { Indexed = (UInt32Value)64U };
topBorder3.Append(color8);
BottomBorder bottomBorder3 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color9 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder3.Append(color9);
DiagonalBorder diagonalBorder3 = new DiagonalBorder();
border3.Append(leftBorder3);
border3.Append(rightBorder3);
border3.Append(topBorder3);
border3.Append(bottomBorder3);
border3.Append(diagonalBorder3);
Border border4 = new Border();
LeftBorder leftBorder4 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color10 = new Color() { Indexed = (UInt32Value)64U };
leftBorder4.Append(color10);
RightBorder rightBorder4 = new RightBorder();
TopBorder topBorder4 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color11 = new Color() { Indexed = (UInt32Value)64U };
topBorder4.Append(color11);
BottomBorder bottomBorder4 = new BottomBorder();
DiagonalBorder diagonalBorder4 = new DiagonalBorder();
border4.Append(leftBorder4);
border4.Append(rightBorder4);
border4.Append(topBorder4);
border4.Append(bottomBorder4);
border4.Append(diagonalBorder4);
Border border5 = new Border();
LeftBorder leftBorder5 = new LeftBorder();
RightBorder rightBorder5 = new RightBorder();
TopBorder topBorder5 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color12 = new Color() { Indexed = (UInt32Value)64U };
topBorder5.Append(color12);
BottomBorder bottomBorder5 = new BottomBorder();
DiagonalBorder diagonalBorder5 = new DiagonalBorder();
border5.Append(leftBorder5);
border5.Append(rightBorder5);
border5.Append(topBorder5);
border5.Append(bottomBorder5);
border5.Append(diagonalBorder5);
Border border6 = new Border();
LeftBorder leftBorder6 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color13 = new Color() { Indexed = (UInt32Value)64U };
leftBorder6.Append(color13);
RightBorder rightBorder6 = new RightBorder();
TopBorder topBorder6 = new TopBorder();
BottomBorder bottomBorder6 = new BottomBorder();
DiagonalBorder diagonalBorder6 = new DiagonalBorder();
border6.Append(leftBorder6);
border6.Append(rightBorder6);
border6.Append(topBorder6);
border6.Append(bottomBorder6);
border6.Append(diagonalBorder6);
Border border7 = new Border();
LeftBorder leftBorder7 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color14 = new Color() { Indexed = (UInt32Value)64U };
leftBorder7.Append(color14);
RightBorder rightBorder7 = new RightBorder();
TopBorder topBorder7 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color15 = new Color() { Indexed = (UInt32Value)64U };
topBorder7.Append(color15);
BottomBorder bottomBorder7 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color16 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder7.Append(color16);
DiagonalBorder diagonalBorder7 = new DiagonalBorder();
border7.Append(leftBorder7);
border7.Append(rightBorder7);
border7.Append(topBorder7);
border7.Append(bottomBorder7);
border7.Append(diagonalBorder7);
Border border8 = new Border();
LeftBorder leftBorder8 = new LeftBorder();
RightBorder rightBorder8 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color17 = new Color() { Indexed = (UInt32Value)64U };
rightBorder8.Append(color17);
TopBorder topBorder8 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color18 = new Color() { Indexed = (UInt32Value)64U };
topBorder8.Append(color18);
BottomBorder bottomBorder8 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color19 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder8.Append(color19);
DiagonalBorder diagonalBorder8 = new DiagonalBorder();
border8.Append(leftBorder8);
border8.Append(rightBorder8);
border8.Append(topBorder8);
border8.Append(bottomBorder8);
border8.Append(diagonalBorder8);
Border border9 = new Border();
LeftBorder leftBorder9 = new LeftBorder();
RightBorder rightBorder9 = new RightBorder();
TopBorder topBorder9 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color20 = new Color() { Indexed = (UInt32Value)64U };
topBorder9.Append(color20);
BottomBorder bottomBorder9 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color21 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder9.Append(color21);
DiagonalBorder diagonalBorder9 = new DiagonalBorder();
border9.Append(leftBorder9);
border9.Append(rightBorder9);
border9.Append(topBorder9);
border9.Append(bottomBorder9);
border9.Append(diagonalBorder9);
Border border10 = new Border();
LeftBorder leftBorder10 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color22 = new Color() { Indexed = (UInt32Value)64U };
leftBorder10.Append(color22);
RightBorder rightBorder10 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color23 = new Color() { Indexed = (UInt32Value)64U };
rightBorder10.Append(color23);
TopBorder topBorder10 = new TopBorder();
BottomBorder bottomBorder10 = new BottomBorder();
DiagonalBorder diagonalBorder10 = new DiagonalBorder();
border10.Append(leftBorder10);
border10.Append(rightBorder10);
border10.Append(topBorder10);
border10.Append(bottomBorder10);
border10.Append(diagonalBorder10);
Border border11 = new Border();
LeftBorder leftBorder11 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color24 = new Color() { Indexed = (UInt32Value)64U };
leftBorder11.Append(color24);
RightBorder rightBorder11 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color25 = new Color() { Indexed = (UInt32Value)64U };
rightBorder11.Append(color25);
TopBorder topBorder11 = new TopBorder();
BottomBorder bottomBorder11 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color26 = new Color() { Indexed = (UInt32Value)64U };
bottomBorder11.Append(color26);
DiagonalBorder diagonalBorder11 = new DiagonalBorder();
border11.Append(leftBorder11);
border11.Append(rightBorder11);
border11.Append(topBorder11);
border11.Append(bottomBorder11);
border11.Append(diagonalBorder11);
borders1.Append(border1);
borders1.Append(border2);
borders1.Append(border3);
borders1.Append(border4);
borders1.Append(border5);
borders1.Append(border6);
borders1.Append(border7);
borders1.Append(border8);
borders1.Append(border9);
borders1.Append(border10);
borders1.Append(border11);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)2U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)44U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, ApplyFont = false, ApplyFill = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false };
cellStyleFormats1.Append(cellFormat1);
cellStyleFormats1.Append(cellFormat2);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)64U };
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat6 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat7 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true };
CellFormat cellFormat8 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat9 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat10 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment1 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat10.Append(alignment1);
CellFormat cellFormat11 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment2 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat11.Append(alignment2);
CellFormat cellFormat12 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment3 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat12.Append(alignment3);
CellFormat cellFormat13 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)3U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat14 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat15 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)5U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat16 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment4 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left };
cellFormat16.Append(alignment4);
CellFormat cellFormat17 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment5 = new Alignment() { Horizontal = HorizontalAlignmentValues.Right };
cellFormat17.Append(alignment5);
CellFormat cellFormat18 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)6U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat19 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment6 = new Alignment() { Horizontal = HorizontalAlignmentValues.Right };
cellFormat19.Append(alignment6);
CellFormat cellFormat20 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)5U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat21 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat22 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)7U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)6U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment7 = new Alignment() { Horizontal = HorizontalAlignmentValues.Right };
cellFormat22.Append(alignment7);
CellFormat cellFormat23 = new CellFormat() { NumberFormatId = (UInt32Value)1U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)7U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment8 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left };
cellFormat23.Append(alignment8);
CellFormat cellFormat24 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)8U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)8U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat25 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment9 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat25.Append(alignment9);
CellFormat cellFormat26 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)10U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment10 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat26.Append(alignment10);
CellFormat cellFormat27 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)10U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat28 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment11 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat28.Append(alignment11);
CellFormat cellFormat29 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment12 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat29.Append(alignment12);
CellFormat cellFormat30 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat31 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment13 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat31.Append(alignment13);
CellFormat cellFormat32 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment14 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat32.Append(alignment14);
CellFormat cellFormat33 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat34 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment15 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat34.Append(alignment15);
CellFormat cellFormat35 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment16 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat35.Append(alignment16);
CellFormat cellFormat36 = new CellFormat() { NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)9U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment17 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat36.Append(alignment17);
CellFormat cellFormat37 = new CellFormat() { NumberFormatId = (UInt32Value)165U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment18 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat37.Append(alignment18);
CellFormat cellFormat38 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment19 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat38.Append(alignment19);
CellFormat cellFormat39 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)5U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment20 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat39.Append(alignment20);
CellFormat cellFormat40 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment21 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top };
cellFormat40.Append(alignment21);
CellFormat cellFormat41 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat42 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat43 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment22 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, WrapText = true };
cellFormat43.Append(alignment22);
CellFormat cellFormat44 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
CellFormat cellFormat45 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat46 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat47 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat48 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)8U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)7U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat49 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment23 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat49.Append(alignment23);
CellFormat cellFormat50 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment24 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat50.Append(alignment24);
CellFormat cellFormat51 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment25 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat51.Append(alignment25);
CellFormat cellFormat52 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment26 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat52.Append(alignment26);
CellFormat cellFormat53 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true };
CellFormat cellFormat54 = new CellFormat() { NumberFormatId = (UInt32Value)4U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true };
CellFormat cellFormat55 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat56 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)4U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat57 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat58 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)8U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)8U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat59 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment27 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat59.Append(alignment27);
CellFormat cellFormat60 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment28 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat60.Append(alignment28);
CellFormat cellFormat61 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment29 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat61.Append(alignment29);
CellFormat cellFormat62 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment30 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat62.Append(alignment30);
CellFormat cellFormat63 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment31 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };
cellFormat63.Append(alignment31);
CellFormat cellFormat64 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true };
CellFormat cellFormat65 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
CellFormat cellFormat66 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment32 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, WrapText = true };
cellFormat66.Append(alignment32);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
cellFormats1.Append(cellFormat18);
cellFormats1.Append(cellFormat19);
cellFormats1.Append(cellFormat20);
cellFormats1.Append(cellFormat21);
cellFormats1.Append(cellFormat22);
cellFormats1.Append(cellFormat23);
cellFormats1.Append(cellFormat24);
cellFormats1.Append(cellFormat25);
cellFormats1.Append(cellFormat26);
cellFormats1.Append(cellFormat27);
cellFormats1.Append(cellFormat28);
cellFormats1.Append(cellFormat29);
cellFormats1.Append(cellFormat30);
cellFormats1.Append(cellFormat31);
cellFormats1.Append(cellFormat32);
cellFormats1.Append(cellFormat33);
cellFormats1.Append(cellFormat34);
cellFormats1.Append(cellFormat35);
cellFormats1.Append(cellFormat36);
cellFormats1.Append(cellFormat37);
cellFormats1.Append(cellFormat38);
cellFormats1.Append(cellFormat39);
cellFormats1.Append(cellFormat40);
cellFormats1.Append(cellFormat41);
cellFormats1.Append(cellFormat42);
cellFormats1.Append(cellFormat43);
cellFormats1.Append(cellFormat44);
cellFormats1.Append(cellFormat45);
cellFormats1.Append(cellFormat46);
cellFormats1.Append(cellFormat47);
cellFormats1.Append(cellFormat48);
cellFormats1.Append(cellFormat49);
cellFormats1.Append(cellFormat50);
cellFormats1.Append(cellFormat51);
cellFormats1.Append(cellFormat52);
cellFormats1.Append(cellFormat53);
cellFormats1.Append(cellFormat54);
cellFormats1.Append(cellFormat55);
cellFormats1.Append(cellFormat56);
cellFormats1.Append(cellFormat57);
cellFormats1.Append(cellFormat58);
cellFormats1.Append(cellFormat59);
cellFormats1.Append(cellFormat60);
cellFormats1.Append(cellFormat61);
cellFormats1.Append(cellFormat62);
cellFormats1.Append(cellFormat63);
cellFormats1.Append(cellFormat64);
cellFormats1.Append(cellFormat65);
cellFormats1.Append(cellFormat66);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)2U };
CellStyle cellStyle1 = new CellStyle() { Name = "Moneda", FormatId = (UInt32Value)1U, BuiltinId = (UInt32Value)4U };
CellStyle cellStyle2 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
cellStyles1.Append(cellStyle2);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" };
stylesheet1.Append(numberingFormats1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
private void ExportExcel(DataTable exportDataTable)
{
string strSampleFileName = string.Empty;
if (dpQueryType.Value.Equals("Opex", StringComparison.CurrentCultureIgnoreCase) ||
dpQueryType.Value.Equals("Capex", StringComparison.CurrentCultureIgnoreCase))
{
strSampleFileName = "PaymentRequestReportSample.xls";
}
else
{
strSampleFileName = "ClaimReportSample.xls";
}
string sSaveFileName = new Random().Next() + "_Report" + ".xls";
string sFullPath = Server.MapPath("/tmpfiles/PaymentRequestReport/");
string sFullPathSampleName = string.Concat(sFullPath, strSampleFileName);
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new GemBox.Spreadsheet.ExcelFile(); //new ExcelFile();
objExcelFile.LoadXls(sFullPathSampleName);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
for (int i = 0; i < exportDataTable.Rows.Count; i++)
{
if (dpQueryType.Value.Equals("Opex", StringComparison.CurrentCultureIgnoreCase) ||
dpQueryType.Value.Equals("Capex", StringComparison.CurrentCultureIgnoreCase))
{
worksheet1.Rows[i + 1].Cells[0].Value = exportDataTable.Rows[i]["Applicant"].ToString();
worksheet1.Rows[i + 1].Cells[1].Value = exportDataTable.Rows[i]["Department"].ToString();
worksheet1.Rows[i + 1].Cells[2].Value = exportDataTable.Rows[i]["SAPNumber"].ToString();
worksheet1.Rows[i + 1].Cells[3].Value = exportDataTable.Rows[i]["Title"].ToString();
worksheet1.Rows[i + 1].Cells[4].Value = exportDataTable.Rows[i]["SystemPONo"].ToString();
//worksheet1.Rows[i + 1].Cells[5].Value = exportDataTable.Rows[i]["PONo"].ToString();
worksheet1.Rows[i + 1].Cells[5].Value = exportDataTable.Rows[i]["Created"].ToString();
worksheet1.Rows[i + 1].Cells[6].Value = exportDataTable.Rows[i]["VendorNo"].ToString();
worksheet1.Rows[i + 1].Cells[7].Value = exportDataTable.Rows[i]["VendorName"].ToString();
worksheet1.Rows[i + 1].Cells[8].Value = exportDataTable.Rows[i]["PaymentDesc"].ToString();
worksheet1.Rows[i + 1].Cells[9].Value = exportDataTable.Rows[i]["TotalAmount"].ToString();
worksheet1.Rows[i + 1].Cells[10].Value = exportDataTable.Rows[i]["Currency"].ToString();
worksheet1.Rows[i + 1].Cells[11].Value = exportDataTable.Rows[i]["Status"].ToString();
worksheet1.Rows[i + 1].Cells[12].Value = exportDataTable.Rows[i]["PaidDate"].ToString();
worksheet1.Rows[i + 1].Cells[13].Value = exportDataTable.Rows[i]["IsAttachedInvoice"].ToString();
}
else
{
worksheet1.Rows[i + 1].Cells[0].Value = exportDataTable.Rows[i]["Applicant"].ToString();
worksheet1.Rows[i + 1].Cells[1].Value = exportDataTable.Rows[i]["Department"].ToString();
worksheet1.Rows[i + 1].Cells[2].Value = exportDataTable.Rows[i]["SAPNumber"].ToString();
worksheet1.Rows[i + 1].Cells[3].Value = exportDataTable.Rows[i]["Title"].ToString();
worksheet1.Rows[i + 1].Cells[4].Value = exportDataTable.Rows[i]["Created"].ToString();
worksheet1.Rows[i + 1].Cells[5].Value = exportDataTable.Rows[i]["VendorNo"].ToString();
worksheet1.Rows[i + 1].Cells[6].Value = exportDataTable.Rows[i]["VendorName"].ToString();
worksheet1.Rows[i + 1].Cells[7].Value = exportDataTable.Rows[i]["PaymentDesc"].ToString();
worksheet1.Rows[i + 1].Cells[8].Value = exportDataTable.Rows[i]["TotalAmount"].ToString();
worksheet1.Rows[i + 1].Cells[9].Value = exportDataTable.Rows[i]["Currency"].ToString();
worksheet1.Rows[i + 1].Cells[10].Value = exportDataTable.Rows[i]["Status"].ToString();
worksheet1.Rows[i + 1].Cells[11].Value = exportDataTable.Rows[i]["PaidDate"].ToString();
}
}
CellStyle cs = new CellStyle();
cs.Font.Weight = ExcelFont.BoldWeight;
string sSavePath = string.Concat(sFullPath, sSaveFileName);
objExcelFile.SaveXls(sSavePath);
SendExcelToClient(sSavePath, sSaveFileName);
}
private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart, SaveContext context)
{
var defaultStyle = new XLStyle(null, DefaultStyle);
var defaultStyleId = GetStyleId(defaultStyle);
if (!context.SharedFonts.ContainsKey(defaultStyle.Font))
context.SharedFonts.Add(defaultStyle.Font, new FontInfo {FontId = 0, Font = defaultStyle.Font as XLFont});
var sharedFills = new Dictionary<IXLFill, FillInfo>
{{defaultStyle.Fill, new FillInfo {FillId = 2, Fill = defaultStyle.Fill as XLFill}}};
var sharedBorders = new Dictionary<IXLBorder, BorderInfo>
{{defaultStyle.Border, new BorderInfo {BorderId = 0, Border = defaultStyle.Border as XLBorder}}};
var sharedNumberFormats = new Dictionary<IXLNumberFormat, NumberFormatInfo>
{
{
defaultStyle.NumberFormat,
new NumberFormatInfo
{NumberFormatId = 0, NumberFormat = defaultStyle.NumberFormat}
}
};
//Dictionary<String, AlignmentInfo> sharedAlignments = new Dictionary<String, AlignmentInfo>();
//sharedAlignments.Add(defaultStyle.Alignment.ToString(), new AlignmentInfo() { AlignmentId = 0, Alignment = defaultStyle.Alignment });
if (workbookStylesPart.Stylesheet == null)
workbookStylesPart.Stylesheet = new Stylesheet();
// Cell styles = Named styles
if (workbookStylesPart.Stylesheet.CellStyles == null)
workbookStylesPart.Stylesheet.CellStyles = new CellStyles();
UInt32 defaultFormatId;
if (workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().Any(c => c.Name == "Normal"))
{
defaultFormatId =
workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().Single(c => c.Name == "Normal").FormatId.Value;
}
else if (workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().Any())
{
defaultFormatId =
workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().Max(c => c.FormatId.Value) + 1;
}
else
defaultFormatId = 0;
context.SharedStyles.Add(defaultStyleId,
new StyleInfo
{
StyleId = defaultFormatId,
Style = defaultStyle,
FontId = 0,
FillId = 0,
BorderId = 0,
NumberFormatId = 0
//AlignmentId = 0
});
UInt32 styleCount = 1;
UInt32 fontCount = 1;
UInt32 fillCount = 3;
UInt32 borderCount = 1;
var numberFormatCount = 1;
var xlStyles = new HashSet<Int32>();
foreach (var worksheet in WorksheetsInternal)
{
foreach (var s in worksheet.GetStyleIds().Where(s => !xlStyles.Contains(s)))
xlStyles.Add(s);
foreach (
var s in
worksheet.Internals.ColumnsCollection.Select(kp => kp.Value.GetStyleId()).Where(
s => !xlStyles.Contains(s)))
xlStyles.Add(s);
foreach (
var s in
worksheet.Internals.RowsCollection.Select(kp => kp.Value.GetStyleId()).Where(
s => !xlStyles.Contains(s))
)
xlStyles.Add(s);
}
foreach (var xlStyle in xlStyles.Select(GetStyleById))
{
if (!context.SharedFonts.ContainsKey(xlStyle.Font))
context.SharedFonts.Add(xlStyle.Font,
new FontInfo {FontId = fontCount++, Font = xlStyle.Font as XLFont});
if (!sharedFills.ContainsKey(xlStyle.Fill))
sharedFills.Add(xlStyle.Fill, new FillInfo {FillId = fillCount++, Fill = xlStyle.Fill as XLFill});
if (!sharedBorders.ContainsKey(xlStyle.Border))
sharedBorders.Add(xlStyle.Border,
new BorderInfo {BorderId = borderCount++, Border = xlStyle.Border as XLBorder});
if (xlStyle.NumberFormat.NumberFormatId != -1
|| sharedNumberFormats.ContainsKey(xlStyle.NumberFormat))
continue;
sharedNumberFormats.Add(xlStyle.NumberFormat,
new NumberFormatInfo
{
NumberFormatId = numberFormatCount + 164,
NumberFormat = xlStyle.NumberFormat
});
numberFormatCount++;
}
var allSharedNumberFormats = ResolveNumberFormats(workbookStylesPart, sharedNumberFormats);
ResolveFonts(workbookStylesPart, context);
var allSharedFills = ResolveFills(workbookStylesPart, sharedFills);
var allSharedBorders = ResolveBorders(workbookStylesPart, sharedBorders);
foreach (var id in xlStyles)
{
var xlStyle = GetStyleById(id);
if (context.SharedStyles.ContainsKey(id)) continue;
var numberFormatId = xlStyle.NumberFormat.NumberFormatId >= 0
? xlStyle.NumberFormat.NumberFormatId
: allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId;
context.SharedStyles.Add(id,
new StyleInfo
{
StyleId = styleCount++,
Style = xlStyle,
FontId = context.SharedFonts[xlStyle.Font].FontId,
FillId = allSharedFills[xlStyle.Fill].FillId,
BorderId = allSharedBorders[xlStyle.Border].BorderId,
NumberFormatId = numberFormatId
});
}
ResolveCellStyleFormats(workbookStylesPart, context);
ResolveRest(workbookStylesPart, context);
if (workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().All(c => c.Name != "Normal"))
{
//var defaultFormatId = context.SharedStyles.Values.Where(s => s.Style.Equals(DefaultStyle)).Single().StyleId;
var cellStyle1 = new CellStyle {Name = "Normal", FormatId = defaultFormatId, BuiltinId = 0U};
workbookStylesPart.Stylesheet.CellStyles.AppendChild(cellStyle1);
}
workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyles.Count();
var newSharedStyles = new Dictionary<Int32, StyleInfo>();
foreach (var ss in context.SharedStyles)
{
var styleId = -1;
foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats)
{
styleId++;
if (CellFormatsAreEqual(f, ss.Value))
break;
}
if (styleId == -1)
styleId = 0;
var si = ss.Value;
si.StyleId = (UInt32)styleId;
newSharedStyles.Add(ss.Key, si);
}
context.SharedStyles.Clear();
newSharedStyles.ForEach(kp => context.SharedStyles.Add(kp.Key, kp.Value));
AddDifferentialFormats(workbookStylesPart, context);
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
Color color1 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
Fills fills1 = new Fills() { Count = (UInt32Value)2U };
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders() { Count = (UInt32Value)1U };
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
cellFormats1.Append(cellFormat2);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)3U };
DifferentialFormat differentialFormat1 = new DifferentialFormat();
Font font2 = new Font();
Condense condense1 = new Condense() { Val = false };
Extend extend1 = new Extend() { Val = false };
Color color2 = new Color() { Rgb = "FF9C0006" };
font2.Append(condense1);
font2.Append(extend1);
font2.Append(color2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill();
BackgroundColor backgroundColor1 = new BackgroundColor() { Rgb = "FFFFC7CE" };
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
differentialFormat1.Append(font2);
differentialFormat1.Append(fill3);
DifferentialFormat differentialFormat2 = new DifferentialFormat();
Font font3 = new Font();
Condense condense2 = new Condense() { Val = false };
Extend extend2 = new Extend() { Val = false };
Color color3 = new Color() { Rgb = "FF006100" };
font3.Append(condense2);
font3.Append(extend2);
font3.Append(color3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill();
BackgroundColor backgroundColor2 = new BackgroundColor() { Rgb = "FFC6EFCE" };
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
differentialFormat2.Append(font3);
differentialFormat2.Append(fill4);
DifferentialFormat differentialFormat3 = new DifferentialFormat();
Font font4 = new Font();
Condense condense3 = new Condense() { Val = false };
Extend extend3 = new Extend() { Val = false };
Color color4 = new Color() { Rgb = "FF006100" };
font4.Append(condense3);
font4.Append(extend3);
font4.Append(color4);
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill();
BackgroundColor backgroundColor3 = new BackgroundColor() { Rgb = "FFC6EFCE" };
patternFill5.Append(backgroundColor3);
fill5.Append(patternFill5);
differentialFormat3.Append(font4);
differentialFormat3.Append(fill5);
differentialFormats1.Append(differentialFormat1);
differentialFormats1.Append(differentialFormat2);
differentialFormats1.Append(differentialFormat3);
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" };
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
public static Stylesheet CreateStylesheet()
{
var ss = new Stylesheet();
var fts = new Fonts();
var ftn = new FontName { Val = "Arial" };
var ftsz = new FontSize { Val = 11 };
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font { FontName = ftn, FontSize = ftsz };
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
var csfs = new CellStyleFormats();
var cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 };
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
// dd/mm/yyyy is also Excel style index 14
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
var nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 165;
nfs = new NumberingFormats();
cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 170;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
iExcelIndex = 171;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.00" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// @ is also Excel style index 49
iExcelIndex = 172;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "@" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
var css = new CellStyles();
var cs = new CellStyle { Name = "Normal", FormatId = 0, BuiltinId = 0 };
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
ss.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
};
ss.Append(tss);
return ss;
}
private CellStyle GetDataStyle()
{
if (dataStyle == null)
{
//数据样式
dataStyle = workbook.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.LEFT;//左对齐
//数据单元格的边框
dataStyle.BorderTop = CellBorderType.THIN;
dataStyle.TopBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderRight = CellBorderType.THIN;
dataStyle.RightBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderBottom = CellBorderType.THIN;
dataStyle.BottomBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderLeft = CellBorderType.THIN;
dataStyle.LeftBorderColor = HSSFColor.BLACK.index;
//数据的字体
var datafont = workbook.CreateFont();
datafont.FontHeightInPoints = 11;//字号
dataStyle.SetFont(datafont);
}
return dataStyle;
}
public void TwoInstancesOfCellStyleAreEqual()
{
Assert.AreEqualAndHaveSameHash(new CellStyle(), new CellStyle());
Assert.AreEqualAndHaveSameHash(new BorderEdge() { Style = BorderStyle.DashDot }, new BorderEdge() { Style = BorderStyle.DashDot });
Assert.AreEqualAndHaveSameHash(new Color(1, 2, 3, 4), new Color(1, 2, 3, 4));
Assert.AreEqualAndHaveSameHash(new CellFont { Name = "T", Size = 10 }, new CellFont { Name = "T", Size = 10 });
var c1 = new CellStyle
{
Alignment = new CellAlignment { HAlign = HorizontalAlignment.Center, Indent = 2 },
Border = new CellBorder { Bottom = new BorderEdge { Color = new Color(1, 2, 3, 4), Style = BorderStyle.DashDot } },
Fill = new CellFill { Background = new Color(2, 3, 4, 5), Pattern = FillPattern.Solid },
Font = new CellFont { Bold = true, Name = "Tahoma" },
Format = "{0}"
};
var c2 = new CellStyle
{
Alignment = new CellAlignment { HAlign = HorizontalAlignment.Center, Indent = 2 },
Border = new CellBorder { Bottom = new BorderEdge { Color = new Color(1, 2, 3, 4), Style = BorderStyle.DashDot } },
Fill = new CellFill { Background = new Color(2, 3, 4, 5), Pattern = FillPattern.Solid },
Font = new CellFont { Bold = true, Name = "Tahoma" },
Format = "{0}"
};
}
