public LoadsXLSCreator(DataTable AccountsTbl)
{
this.tblAccounts = AccountsTbl;
this.filename = string.Format("./loads{0:ddMMyy}.xls", DateTime.Now);
book.Properties.Author = "Anthony Lim";
book.Properties.Company = "Kitesystems";
headings = book.Styles.Add("Heading1");
headings.Font.Bold = true;
headings.Font.Size = 12;
headings.Alignment.Horizontal = StyleHorizontalAlignment.Center;
coloredHeading = book.Styles.Add("ColoredHeading");
coloredHeading.Font.Size = 12;
coloredHeading.Font.Color = "#FFFFFF";
coloredHeading.Interior.Color = "#55AAFF";
coloredHeading.Interior.Pattern = StyleInteriorPattern.Solid;
coloredHeading.Font.Italic = true;
coloredHeading.Font.Bold = true;
coloredHeading.Alignment.Horizontal = StyleHorizontalAlignment.Center;
this.normal = book.Styles.Add("Default");
this.normal.Font.Size = 10;
this.normal.Font.Bold = false;
this.rightAlign = book.Styles.Add("RightAligned");
this.rightAlign.Font.Size = 10;
this.normal.Font.Bold = false;
this.rightAlign.Alignment.Horizontal = StyleHorizontalAlignment.Right;
}
private void CreateWorkbookStyles()
{
WorksheetStyle styleHeader = new WorksheetStyle("Header");
styleHeader.Font.FontName = "Tahoma";
styleHeader.Font.Bold = true;
styleHeader.Alignment.Horizontal = StyleHorizontalAlignment.Center;
m_workbook.Styles.Add(styleHeader);
WorksheetStyle style0 = new WorksheetStyle("Default");
m_workbook.Styles.Add(style0);
WorksheetStyle style1 = new WorksheetStyle("DateTime");
style1.NumberFormat = "mm/dd/yy";
m_workbook.Styles.Add(style1);
WorksheetStyle style2 = new WorksheetStyle("F2");
style2.NumberFormat = "0.00";
m_workbook.Styles.Add(style2);
WorksheetStyle style3 = new WorksheetStyle("F3");
style3.NumberFormat = "0.000";
m_workbook.Styles.Add(style3);
WorksheetStyle style4 = new WorksheetStyle("Currency");
style4.NumberFormat = "Currency";
m_workbook.Styles.Add(style4);
}
public ExcelWriter()
{
int ticks = Environment.TickCount;
// Create the workbook
Workbook book = new Workbook();
// Set the author
book.Properties.Author = "Anthony Lim";
// Add some style
WorksheetStyle style = book.Styles.Add("style1");
style.Font.Bold = true;
Worksheet sheet = book.Worksheets.Add("SampleSheet");
WorksheetRow Row0 = sheet.Table.Rows.Add();
// Add a cell
Row0.Cells.Add("Hello World", DataType.String, "style1");
// Save it
book.Save(@"./test.xls");
Console.WriteLine("Time:{0}", Environment.TickCount - ticks);
}
public ExcelLogFile(DataGridView dg, string logFolder)
{
this.logFolder = logFolder;
book = new Workbook();
fullSatistic = book.Worksheets.Add("Full");
row = fullSatistic.Table.Rows.Add();
this.DG = dg;
HeadLine = book.Styles.Add("HeadLine");
HeadLine.Font.Size = 14;
HeadLine.Font.Bold = true;
HeadLine.Alignment.Horizontal = StyleHorizontalAlignment.Center;
Fail = book.Styles.Add("Fail");
Fail.Font.Size = 10;
Fail.Font.Color = "Red";
Fail.Font.Bold = true;
Fail.Alignment.Horizontal = StyleHorizontalAlignment.Center;
Pass = book.Styles.Add("Pass");
Pass.Font.Size = 10;
Pass.Font.Color = "Green";
Pass.Font.Bold = true;
Pass.Alignment.Horizontal = StyleHorizontalAlignment.Center;
Defult = book.Styles.Add("Default");
Defult.Font.Size = 10;
Defult.Font.Bold = true;
Defult.Alignment.Horizontal = StyleHorizontalAlignment.Center;
CerateLogFile();
NewLine();
}
public LoadsXLSCreator(DataTable AccountsTbl)
{
this.tblAccounts = AccountsTbl;
this.filename = string.Format("./loads{0:ddMMyy}.xls", DateTime.Now);
book.Properties.Author = "Anthony Lim";
book.Properties.Company = "Kitesystems";
headings = book.Styles.Add("Heading1");
headings.Font.Bold = true;
headings.Font.Size = 12;
headings.Alignment.Horizontal = StyleHorizontalAlignment.Center;
coloredHeading = book.Styles.Add("ColoredHeading");
coloredHeading.Font.Size = 12;
coloredHeading.Font.Color = "#FFFFFF";
coloredHeading.Interior.Color = "#55AAFF";
coloredHeading.Interior.Pattern = StyleInteriorPattern.Solid;
coloredHeading.Font.Italic = true;
coloredHeading.Font.Bold = true;
coloredHeading.Alignment.Horizontal = StyleHorizontalAlignment.Center;
this.normal = book.Styles.Add("Default");
this.normal.Font.Size = 10;
this.normal.Font.Bold = false;
this.rightAlign = book.Styles.Add("RightAligned");
this.rightAlign.Font.Size = 10;
this.normal.Font.Bold = false;
this.rightAlign.Alignment.Horizontal = StyleHorizontalAlignment.Right;
}
/// <summary>
/// Create the instance of .xls writer.
/// </summary>
/// <param name="fileName">The name of the file to write to.</param>
public XlsWriter(string fileName)
{
this.fileName = fileName;
this.wb = new Workbook();
this.wb.Properties.Version = "10.4219";
WorksheetStyle dateStyle = this.wb.Styles.Add("dateStyle");
dateStyle.NumberFormat = "Short Date";
this.ws = this.wb.Worksheets.Add(TEXT.Get["Menses"]);
WorksheetStyle floatStyle = this.wb.Styles.Add("floatStyle");
floatStyle.NumberFormat = "Fixed";
WorksheetStyle default_ = this.wb.Styles.Add("Default");
default_.Name = "Normal";
default_.Font.FontName = "Arial";
default_.Alignment.Vertical = StyleVerticalAlignment.Bottom;
foreach (string cell in OneDayInfo.Header)
{
this.ws.Table.Columns.Add(new WorksheetColumn(100)
{
AutoFitWidth = true
});
}
}
/**/
/// <summary>
/// 初始化Excel Workbook
/// </summary>
/// <param name="book">book</param>
private void InitializeBook(Workbook book)
{
book.Properties.Author = Author; //作者
book.Properties.LastAuthor = LastAuthor; //最后作者
book.Properties.Created = DateTime.Now; //创建时间
book.Properties.Company = Company; //公司
book.Properties.Version = Version; //版本
book.ExcelWorkbook.WindowHeight = 13500;
book.ExcelWorkbook.WindowWidth = 17100;
book.ExcelWorkbook.WindowTopX = 360;
book.ExcelWorkbook.WindowTopY = 75;
book.ExcelWorkbook.ProtectWindows = false;
book.ExcelWorkbook.ProtectStructure = false;
}
/**/
/// <summary>
/// 设置样式
/// </summary>
/// <param name="styles">样式集合</param>
private void SetStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "宋体";
Default.Font.Size = 12;
Default.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// TitleStyle
// -----------------------------------------------
WorksheetStyle TitleStyle = styles.Add("TitleStyle");
TitleStyle.Font.Bold = true;
TitleStyle.Font.FontName = "黑体";
TitleStyle.Font.Size = 14;
TitleStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
TitleStyle.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// FieldStyle
// -----------------------------------------------
WorksheetStyle FieldStyle = styles.Add("FieldStyle");
FieldStyle.Font.Bold = true;
FieldStyle.Font.FontName = "宋体";
FieldStyle.Font.Size = 12;
FieldStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
FieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// LastFieldStyle
// -----------------------------------------------
WorksheetStyle LastFieldStyle = styles.Add("LastFieldStyle");
LastFieldStyle.Font.Bold = true;
LastFieldStyle.Font.FontName = "宋体";
LastFieldStyle.Font.Size = 12;
LastFieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// DataStyle
// -----------------------------------------------
WorksheetStyle DataStyle = styles.Add("DataStyle");
DataStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
}
/// <summary>
/// Creates a new worksheet writer.
/// </summary>
/// <typeparam name="TClass">The class type encapsulating the data to be written to the worksheet.</typeparam>
/// <typeparam name="TClassMap">A class map identifying how the data will be written.</typeparam>
/// <param name="worksheetName">The name of the worksheet to be written.</param>
/// <param name="worksheetIndex">The worksheet's zero-based position index within the workbook.</param>
/// <param name="worksheetStyle">The worksheet's style definition.</param>
/// <returns>A new worksheet writer.</returns>
public WorksheetWriter <TClass, TClassMap> CreateWorksheetWriter <TClass, TClassMap>(string worksheetName, int worksheetIndex, WorksheetStyle worksheetStyle = null)
where TClass : class
where TClassMap : ClassMap <TClass>
{
this.ValidateClassMap <TClass, TClassMap>();
this.AddWorksheetName(worksheetName);
if (worksheetStyle == null)
{
worksheetStyle = new WorksheetStyle();
}
return(new WorksheetWriter <TClass, TClassMap>(worksheetName, worksheetIndex, this.spreadsheetDocument, worksheetStyle, this.sharedStrings, this.stylesheet));
}
public RawTableDump()
{
book = new Workbook();
heading = book.Styles.Add("heading");
normal = book.Styles.Add("normal");
heading.Alignment.Horizontal = StyleHorizontalAlignment.Center;
heading.Font.Bold = true;
normal.Alignment.Horizontal = StyleHorizontalAlignment.Automatic;
normal.Font.Bold = false;
normal.Alignment.Vertical = StyleVerticalAlignment.Center;
}
public RawTableDump()
{
book = new Workbook();
heading = book.Styles.Add("heading");
normal = book.Styles.Add("normal");
heading.Alignment.Horizontal = StyleHorizontalAlignment.Center;
heading.Font.Bold = true;
normal.Alignment.Horizontal = StyleHorizontalAlignment.Automatic;
normal.Font.Bold = false;
normal.Alignment.Vertical = StyleVerticalAlignment.Center;
}
/// <summary>
/// Creates a new worksheet writer.
/// </summary>
/// <typeparam name="TClass">The class type encapsulating the data to be written to the worksheet.</typeparam>
/// <typeparam name="TClassMap">A class map identifying how the data will be written.</typeparam>
/// <param name="worksheetName">The name of the worksheet to be written.</param>
/// <param name="worksheetStyle">The worksheet's style definition.</param>
/// <returns>A new worksheet writer.</returns>
public WorksheetWriter <TClass, TClassMap> CreateWorksheetWriter <TClass, TClassMap>(string worksheetName, WorksheetStyle worksheetStyle = null)
where TClass : class
where TClassMap : ClassMap <TClass>
{
this.ValidateClassMap <TClass, TClassMap>();
this.AddWorksheetName(worksheetName);
if (worksheetStyle == null)
{
worksheetStyle = new WorksheetStyle();
}
var positionIndex = this.spreadsheetDocument.WorkbookPart.Workbook.Sheets.Count();
return(new WorksheetWriter <TClass, TClassMap>(worksheetName, positionIndex, this.spreadsheetDocument, worksheetStyle, this.sharedStrings, this.stylesheet));
}
/// <summary>
/// Formatiere Calc Dokument-Datei
/// </summary>
/// <param name="sheetName"></param>
private void loadXLSFormatter(String sheetName)
{
_Book = new Workbook();
_Book.ExcelWorkbook.WindowHeight = 9000;
_Book.ExcelWorkbook.WindowWidth = 13860;
_Book.ExcelWorkbook.WindowTopX = 240;
_Book.ExcelWorkbook.WindowTopY = 75;
_Book.ExcelWorkbook.ProtectWindows = false;
_Book.ExcelWorkbook.ProtectStructure = false;
WorksheetStyle style = _Book.Styles.Add("Header");
WorksheetStyle dataStyle = _Book.Styles.Add("DataStyle");
dataStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
dataStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
dataStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
dataStyle.Alignment.Vertical = StyleVerticalAlignment.Center;
dataStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
style.Parent = "Normal";
style.Font.Bold = true;
style.Font.FontName = "Tahoma1";
style.Font.Size = 11;
style.Interior.Color = this._HEADERCOLOR;//"#e6e6ff";
style.Alignment.Horizontal = StyleHorizontalAlignment.Left;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Interior.Pattern = StyleInteriorPattern.Solid;
style.Alignment.WrapText = true;
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
Worksheet sheet = _Book.Worksheets.Add(sheetName + " vom " + DateTime.Now.ToLongDateString());
sheet.Options.Selected = true;
sheet.Options.ProtectObjects = false;
sheet.Options.ProtectScenarios = false;
sheet.Options.PageSetup.Layout.Orientation = Orientation.Landscape;
sheet.Options.PageSetup.Header.Margin = 0.492126F;
sheet.Options.PageSetup.Footer.Margin = 0.492126F;
sheet.Options.PageSetup.PageMargins.Bottom = 0.984252F;
sheet.Options.PageSetup.PageMargins.Left = 0.7874016F;
sheet.Options.PageSetup.PageMargins.Right = 0.7874016F;
sheet.Options.PageSetup.PageMargins.Top = 0.984252F;
sheet.Options.Print.PaperSizeIndex = 9;
sheet.Options.Print.ValidPrinterInfo = true;
this._Sheet = sheet;
}
private static void GenerateStyles(WorksheetStyleCollection styles)
{
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Alignment.Vertical = StyleVerticalAlignment.Bottom;
WorksheetStyle quantity = styles.Add("Number");
quantity.NumberFormat = "0";
WorksheetStyle euro = styles.Add("Euro");
euro.NumberFormat = "\"€\"\\ #,##0.00_-";
WorksheetStyle header = styles.Add("Header");
header.Font.Bold = true;
}
/// <summary>
/// Initializes a new instance of the <see cref="WorksheetWriter{TClass, TClassMap}"/> class.
/// </summary>
/// <param name="worksheetName">The worksheet's name.</param>
/// <param name="worksheetPositionIndex">The position where the worksheet should be inserted in the workbook.</param>
/// <param name="spreadsheetDocument">A reference to the worksheet's parent <see cref="spreadsheetDocument"/>.</param>
/// <param name="worksheetStyle">The workseet's style definitions.</param>
/// <param name="sharedStrings">The shared strings collection shared by all worksheets.</param>
/// <param name="spreadsheetStyles">The spreadsheet stylesheet shared by all worksheets.</param>
public WorksheetWriter(string worksheetName, int worksheetPositionIndex, SpreadsheetDocument spreadsheetDocument, WorksheetStyle worksheetStyle, BidirectionalDictionary <string, string> sharedStrings, StylesCollection spreadsheetStyles)
{
this.sharedStrings = sharedStrings;
this.spreadsheetDocument = spreadsheetDocument;
this.spreadsheetStyles = spreadsheetStyles;
this.worksheetName = worksheetName;
this.worksheetPositionIndex = worksheetPositionIndex;
this.worksheetStyle = worksheetStyle;
// setup worksheet
this.currentRowIndex = this.worksheetStyle.HeaderRowIndex;
this.worksheetPart = this.spreadsheetDocument.WorkbookPart.AddNewPart <WorksheetPart>();
// property maps
this.orderedPropertyMaps = this.CreateOrderedPropertyMaps().ToList();
// worksheet setup
this.CacheWorksheetData();
this.writer = OpenXmlWriter.Create(this.worksheetPart, System.Text.Encoding.UTF8);
this.WriteInitialWorksheetElements();
}
private void GenerateStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "Calibri";
Default.Font.Size = 11;
Default.Font.Color = "#000000";
Default.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// m66637088
// -----------------------------------------------
WorksheetStyle m66637088 = styles.Add("m66637088");
m66637088.Font.Bold = true;
m66637088.Font.FontName = "Times New Roman";
m66637088.Font.Size = 11;
m66637088.Font.Color = "#000000";
m66637088.Interior.Color = "#8DB4E3";
m66637088.Interior.Pattern = StyleInteriorPattern.Solid;
m66637088.Alignment.Horizontal = StyleHorizontalAlignment.Right;
m66637088.Alignment.Vertical = StyleVerticalAlignment.Center;
m66637088.Alignment.WrapText = true;
m66637088.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
m66637088.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
m66637088.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// m66637108
// -----------------------------------------------
WorksheetStyle m66637108 = styles.Add("m66637108");
m66637108.Font.Bold = true;
m66637108.Font.FontName = "Times New Roman";
m66637108.Font.Size = 11;
m66637108.Font.Color = "#FF0000";
m66637108.Interior.Color = "#FFFF00";
m66637108.Interior.Pattern = StyleInteriorPattern.Solid;
m66637108.Alignment.Horizontal = StyleHorizontalAlignment.Right;
m66637108.Alignment.Vertical = StyleVerticalAlignment.Center;
m66637108.Alignment.WrapText = true;
m66637108.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
m66637108.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
m66637108.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
m66637108.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// m66637128
// -----------------------------------------------
WorksheetStyle m66637128 = styles.Add("m66637128");
m66637128.Font.FontName = "Times New Roman";
m66637128.Font.Size = 11;
m66637128.Font.Color = "#000000";
m66637128.Interior.Color = "#FFFFFF";
m66637128.Interior.Pattern = StyleInteriorPattern.Solid;
m66637128.Alignment.Horizontal = StyleHorizontalAlignment.Center;
m66637128.Alignment.Vertical = StyleVerticalAlignment.Center;
m66637128.Alignment.WrapText = true;
m66637128.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
m66637128.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
m66637128.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
m66637128.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
m66637128.NumberFormat = "Standard";
// -----------------------------------------------
// s62
// -----------------------------------------------
WorksheetStyle s62 = styles.Add("s62");
s62.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s62.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s64
// -----------------------------------------------
WorksheetStyle s64 = styles.Add("s64");
s64.Font.Bold = true;
s64.Font.FontName = "Times New Roman";
s64.Font.Size = 11;
s64.Font.Color = "#000000";
s64.Interior.Color = "#FAC090";
s64.Interior.Pattern = StyleInteriorPattern.Solid;
s64.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s64.Alignment.Vertical = StyleVerticalAlignment.Center;
s64.Alignment.WrapText = true;
s64.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s64.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s64.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s64.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// s65
// -----------------------------------------------
WorksheetStyle s65 = styles.Add("s65");
s65.Font.FontName = "Times New Roman";
s65.Font.Size = 11;
s65.Font.Color = "#000000";
s65.Interior.Color = "#FFFFFF";
s65.Interior.Pattern = StyleInteriorPattern.Solid;
s65.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s65.Alignment.Vertical = StyleVerticalAlignment.Center;
s65.Alignment.WrapText = true;
s65.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s65.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s65.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s65.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// s66
// -----------------------------------------------
WorksheetStyle s66 = styles.Add("s66");
s66.Font.FontName = "Times New Roman";
s66.Font.Size = 11;
s66.Font.Color = "#000000";
s66.Interior.Color = "#FFFFFF";
s66.Interior.Pattern = StyleInteriorPattern.Solid;
s66.Alignment.Vertical = StyleVerticalAlignment.Center;
s66.Alignment.WrapText = true;
s66.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s66.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s66.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s66.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
s66.NumberFormat = "Standard";
// -----------------------------------------------
// s67
// -----------------------------------------------
WorksheetStyle s67 = styles.Add("s67");
s67.Font.FontName = "Times New Roman";
s67.Font.Size = 11;
s67.Font.Color = "#000000";
s67.Interior.Color = "#FFFFFF";
s67.Interior.Pattern = StyleInteriorPattern.Solid;
s67.Alignment.Vertical = StyleVerticalAlignment.Center;
s67.Alignment.WrapText = true;
s67.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s67.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s67.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s67.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// s68
// -----------------------------------------------
WorksheetStyle s68 = styles.Add("s68");
s68.Font.Bold = true;
s68.Font.FontName = "Times New Roman";
s68.Font.Size = 11;
s68.Font.Color = "#000000";
s68.Interior.Color = "#8DB4E3";
s68.Interior.Pattern = StyleInteriorPattern.Solid;
s68.Alignment.Vertical = StyleVerticalAlignment.Center;
s68.Alignment.WrapText = true;
s68.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s68.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s68.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s68.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
s68.NumberFormat = "Standard";
// -----------------------------------------------
// s69
// -----------------------------------------------
WorksheetStyle s69 = styles.Add("s69");
s69.Font.Bold = true;
s69.Font.FontName = "Times New Roman";
s69.Font.Size = 11;
s69.Font.Color = "#FF0000";
s69.Interior.Color = "#FFFF00";
s69.Interior.Pattern = StyleInteriorPattern.Solid;
s69.Alignment.Vertical = StyleVerticalAlignment.Center;
s69.Alignment.WrapText = true;
s69.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s69.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s69.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s69.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
s69.NumberFormat = "Standard";
// -----------------------------------------------
// s90
// -----------------------------------------------
WorksheetStyle s90 = styles.Add("s90");
s90.Font.Bold = true;
s90.Font.FontName = "Times New Roman";
s90.Font.Size = 18;
s90.Font.Color = "#000000";
s90.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s90.Alignment.Vertical = StyleVerticalAlignment.Center;
s90.Alignment.WrapText = true;
// -----------------------------------------------
// s91
// -----------------------------------------------
WorksheetStyle s91 = styles.Add("s91");
s91.Font.Italic = true;
s91.Font.FontName = "Times New Roman";
s91.Font.Color = "#000000";
s91.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s91.Alignment.Vertical = StyleVerticalAlignment.Center;
s91.Alignment.WrapText = true;
// -----------------------------------------------
// s92
// -----------------------------------------------
WorksheetStyle s92 = styles.Add("s92");
s92.Font.FontName = "Times New Roman";
s92.Font.Size = 11;
s92.Font.Color = "#000000";
s92.Interior.Color = "#FFFFFF";
s92.Interior.Pattern = StyleInteriorPattern.Solid;
s92.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s92.Alignment.Vertical = StyleVerticalAlignment.Center;
s92.Alignment.WrapText = true;
s92.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s92.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s92.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// s96
// -----------------------------------------------
WorksheetStyle s96 = styles.Add("s96");
s96.Font.Italic = true;
s96.Font.FontName = "Times New Roman";
s96.Font.Color = "#000000";
s96.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s96.Alignment.Vertical = StyleVerticalAlignment.Center;
s96.Alignment.WrapText = true;
s96.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
}
/// <summary>
/// Erstelle die Layout Styles
/// </summary>
/// <param name="styles"></param>
private void GenerateStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Allgemein
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Default";
// -----------------------------------------------
// Ergebnis1
// -----------------------------------------------
WorksheetStyle Result = styles.Add("Result");
Result.Name = "Result";
Result.Font.Bold = true;
Result.Font.Italic = true;
Result.Font.Underline = UnderlineStyle.Single;
Result.Font.Size = 10;
// -----------------------------------------------
// Ergebnis2
// -----------------------------------------------
WorksheetStyle Result2 = styles.Add("Result2");
Result2.Name = "Result2";
Result2.Font.Bold = true;
Result2.Font.Italic = true;
Result2.Font.Underline = UnderlineStyle.Single;
Result2.Font.Size = 10;
// -----------------------------------------------
// Überschrift
// -----------------------------------------------
WorksheetStyle Heading = styles.Add("Heading");
Heading.Name = "Heading";
Heading.Font.Bold = true;
Heading.Font.Italic = true;
Heading.Font.Size = 11;
// -----------------------------------------------
// Überschrift2
// -----------------------------------------------
WorksheetStyle Heading1 = styles.Add("Heading1");
Heading1.Name = "Heading1";
Heading1.Font.Bold = true;
Heading1.Font.Italic = true;
Heading1.Font.Size = 11;
// -----------------------------------------------
// Normales Layout
// -----------------------------------------------
WorksheetStyle Normal = styles.Add("Normal");
Normal.Name = "Normal";
Normal.Font.FontName = "Tahoma1";
Normal.Font.Size = 11;
// -----------------------------------------------
// Spalte1
// -----------------------------------------------
WorksheetStyle co1 = styles.Add("co1");
// -----------------------------------------------
// Spalte2
// -----------------------------------------------
WorksheetStyle co2 = styles.Add("co2");
// -----------------------------------------------
// Spalte3
// -----------------------------------------------
WorksheetStyle co3 = styles.Add("co3");
// -----------------------------------------------
// Spalte4
// -----------------------------------------------
WorksheetStyle co4 = styles.Add("co4");
// -----------------------------------------------
// Spalte5
// -----------------------------------------------
WorksheetStyle co5 = styles.Add("co5");
// -----------------------------------------------
// Spalte6
// -----------------------------------------------
WorksheetStyle co6 = styles.Add("co6");
// -----------------------------------------------
// Spalte7
// -----------------------------------------------
WorksheetStyle co7 = styles.Add("co7");
// -----------------------------------------------
// Spalte8
// -----------------------------------------------
WorksheetStyle co8 = styles.Add("co8");
// -----------------------------------------------
// Spalte9
// -----------------------------------------------
WorksheetStyle co9 = styles.Add("co9");
// -----------------------------------------------
// Spalte10
// -----------------------------------------------
WorksheetStyle co10 = styles.Add("co10");
// -----------------------------------------------
// ta1
// -----------------------------------------------
WorksheetStyle ta1 = styles.Add("ta1");
// -----------------------------------------------
// neuer Style "normal"
// -----------------------------------------------
WorksheetStyle ce1 = styles.Add("ce1");
ce1.Parent = "Normal";
ce1.Font.Bold = true;
ce1.Font.FontName = "Tahoma1";
ce1.Font.Size = 11;
ce1.Interior.Color = _HEADERCOLOR;
ce1.Interior.Pattern = StyleInteriorPattern.Solid;
ce1.Alignment.Horizontal = StyleHorizontalAlignment.Left;
ce1.Alignment.Vertical = StyleVerticalAlignment.Center;
ce1.Alignment.WrapText = true;
ce1.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// WorksheetStyle2
// -----------------------------------------------
WorksheetStyle ce2 = styles.Add("ce2");
ce2.Alignment.Horizontal = StyleHorizontalAlignment.Center;
ce2.Alignment.Vertical = StyleVerticalAlignment.Center;
ce2.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
ce2.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// WorksheetStyle3
// -----------------------------------------------
WorksheetStyle ce3 = styles.Add("ce3");
ce3.Alignment.Horizontal = StyleHorizontalAlignment.Center;
ce3.Alignment.Vertical = StyleVerticalAlignment.Center;
ce3.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// WorksheetStyle4
// -----------------------------------------------
WorksheetStyle ce4 = styles.Add("ce4");
ce4.Parent = "Normal";
ce4.Font.Bold = true;
ce4.Font.FontName = "Tahoma1";
ce4.Font.Size = 11;
ce4.Interior.Color = _HEADERCOLOR;
ce4.Interior.Pattern = StyleInteriorPattern.Solid;
ce4.Alignment.Horizontal = StyleHorizontalAlignment.Center;
ce4.Alignment.Vertical = StyleVerticalAlignment.Center;
ce4.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// WorksheetStyle5
// -----------------------------------------------
WorksheetStyle ce5 = styles.Add("ce5");
ce5.Alignment.Horizontal = StyleHorizontalAlignment.Center;
ce5.Alignment.Vertical = StyleVerticalAlignment.Center;
ce5.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
ce5.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// WorksheetStyle6
// -----------------------------------------------
WorksheetStyle ce6 = styles.Add("ce6");
ce6.Parent = "Normal";
ce6.Font.Bold = true;
ce6.Font.FontName = "Tahoma1";
ce6.Font.Size = 11;
ce6.Interior.Color = _HEADERCOLOR;
ce6.Interior.Pattern = StyleInteriorPattern.Solid;
ce6.Alignment.Horizontal = StyleHorizontalAlignment.Left;
ce6.Alignment.Vertical = StyleVerticalAlignment.Center;
ce6.Alignment.WrapText = true;
ce6.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
ce6.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// WorksheetStyle7
// -----------------------------------------------
WorksheetStyle ce7 = styles.Add("ce7");
ce7.Alignment.Horizontal = StyleHorizontalAlignment.Center;
ce7.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// WorksheetStyle8
// -----------------------------------------------
WorksheetStyle ce8 = styles.Add("ce8");
ce8.Font.Bold = true;
ce8.Font.FontName = "Tahoma";
ce8.Font.Size = 11;
ce8.Interior.Color = _HEADERCOLOR;
ce8.Interior.Pattern = StyleInteriorPattern.Solid;
ce8.Alignment.Horizontal = StyleHorizontalAlignment.Center;
ce8.Alignment.Vertical = StyleVerticalAlignment.Center;
ce8.Alignment.WrapText = true;
ce8.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
ce8.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
ce8.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
ce8.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// WorksheetStyle9
// -----------------------------------------------
WorksheetStyle ce9 = styles.Add("ce9");
ce9.Parent = "Normal";
ce9.Font.Bold = true;
ce9.Font.FontName = "Tahoma1";
ce9.Font.Size = 11;
ce9.Interior.Color = _HEADERCOLOR;
ce9.Interior.Pattern = StyleInteriorPattern.Solid;
ce9.Alignment.Horizontal = StyleHorizontalAlignment.Left;
ce9.Alignment.Vertical = StyleVerticalAlignment.Center;
ce9.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
ce9.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
//
// WorksheetStyle10
//
WorksheetStyle cell10 = styles.Add("ce10");
cell10.Alignment.Horizontal = StyleHorizontalAlignment.Center;
cell10.Alignment.Vertical = StyleVerticalAlignment.Center;
cell10.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
//
// WorksheetStyle11
//
WorksheetStyle cell11 = styles.Add("ce11");
cell11.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
cell11.Alignment.Horizontal = StyleHorizontalAlignment.Left;
//cell11.Alignment.WrapText = true;
cell11.Alignment.Vertical = StyleVerticalAlignment.Center;
cell11.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
}
static Workbook WorkbookStyle(Workbook book, string UserName, string fileName)
{
book.ExcelWorkbook.ActiveSheetIndex = 1;
book.Properties.Author = UserName;
book.Properties.Title = fileName;
book.Properties.Created = DateTime.Now;
WorksheetStyle style = book.Styles.Add("TitleStyle");
style.Font.FontName = "黑体";
style.Font.Size = 22;
style.Font.Bold = true;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style = book.Styles.Add("HeaderStyle");
style.Font.FontName = "宋体";
style.Font.Size = 10;
style.Font.Bold = true;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Alignment.WrapText = true;
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
style = book.Styles.Add("DefaultStyle");
style.Font.FontName = "宋体";
style.Font.Size = 10;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Alignment.WrapText = true;
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
style = book.Styles.Add("LeftStyle");
style.Font.FontName = "宋体";
style.Font.Size = 10;
style.Alignment.Horizontal = StyleHorizontalAlignment.Left;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Alignment.WrapText = true;
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
style = book.Styles.Add("RightStyle");
style.Font.FontName = "宋体";
style.Font.Size = 10;
style.Alignment.Horizontal = StyleHorizontalAlignment.Right;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Alignment.WrapText = true;
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
style = book.Styles.Add("LeftStyleNoBorder");
style.Font.FontName = "宋体";
style.Font.Size = 10;
style.Alignment.Horizontal = StyleHorizontalAlignment.Left;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Alignment.WrapText = true;
style = book.Styles.Add("RightStyleNoBorder");
style.Font.FontName = "宋体";
style.Font.Size = 10;
style.Alignment.Horizontal = StyleHorizontalAlignment.Right;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Alignment.WrapText = true;
return(book);
}
private ResMsg CreateExcel(DataTable dt)
{
ResMsg msg = new ResMsg(false, "");
if ((dt != null) && (dt.Rows.Count > 0))
{
DataTable table = dt;
int count = table.Columns.Count;
Workbook workbook = new Workbook
{
ExcelWorkbook = { ActiveSheetIndex = 1, WindowTopX = 0, WindowTopY = 0, WindowHeight = 0x1b58, WindowWidth = 0x1f40 },
Properties = { Author = "PSWeb", Title = "User Information List", Created = DateTime.Now }
};
WorksheetStyle style = workbook.Styles.Add("HeaderStyle");
style.Font.FontName = "宋体";
style.Font.Size = 14;
style.Font.Bold = true;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Font.Color = "Black";
style = workbook.Styles.Add("ColumnCaptionStyle");
style.Font.FontName = "宋体";
style.Font.Bold = true;
style.Font.Size = 11;
style = workbook.Styles.Add("Default");
style.Font.FontName = "宋体";
style.Font.Size = 10;
Worksheet worksheet = workbook.Worksheets.Add("用户信息列表");
for (int i = 0; i < table.Columns.Count; i++)
{
if (i == (table.Columns.Count - 1))
{
worksheet.Table.Columns.Add(new WorksheetColumn(130));
}
else
{
worksheet.Table.Columns.Add(new WorksheetColumn(0x41));
}
}
WorksheetCell cell = worksheet.Table.Rows.Add().Cells.Add("用户信息列表");
cell.MergeAcross = count - 1;
cell.StyleID = "HeaderStyle";
WorksheetRow row = worksheet.Table.Rows.Add();
for (int j = 0; j < table.Columns.Count; j++)
{
row.Cells.Add(table.Columns[j].Caption).StyleID = "ColumnCaptionStyle";
}
for (int k = 0; k < table.Rows.Count; k++)
{
row = worksheet.Table.Rows.Add();
for (int m = 0; m < table.Columns.Count; m++)
{
row.Cells.Add(table.Rows[k][m].ToString()).StyleID = "Default";
}
}
try
{
string str = DateTime.Now.Ticks.ToString() + ".xls";
workbook.Save(this.context.Server.MapPath(".").Replace("WebServices", "WebAdmin") + @"\" + str);
CommonUtil.RemoveFiles(this.context.Server.MapPath("."), ".xls");
msg.Result = true;
msg.Message = str;
}
catch (Exception exception)
{
msg.Message = exception.Message;
}
}
return(msg);
}
private void GenerateStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "Calibri";
Default.Font.Size = 11;
Default.Font.Color = "#000000";
Default.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// m51494400
// -----------------------------------------------
WorksheetStyle m51494400 = styles.Add("m51494400");
m51494400.Font.FontName = "Verdana";
m51494400.Font.Color = "#000000";
m51494400.Interior.Color = "#ECECEC";
m51494400.Interior.Pattern = StyleInteriorPattern.Solid;
m51494400.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494400.Alignment.WrapText = true;
m51494400.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 3, "#959595");
m51494400.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 3, "#959595");
m51494400.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494400.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494420
// -----------------------------------------------
WorksheetStyle m51494420 = styles.Add("m51494420");
m51494420.Font.FontName = "Verdana";
m51494420.Font.Color = "#000000";
m51494420.Interior.Color = "#ECECEC";
m51494420.Interior.Pattern = StyleInteriorPattern.Solid;
m51494420.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494420.Alignment.WrapText = true;
m51494420.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 3, "#959595");
m51494420.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494420.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494420.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494440
// -----------------------------------------------
WorksheetStyle m51494440 = styles.Add("m51494440");
m51494440.Font.FontName = "Verdana";
m51494440.Font.Color = "#000000";
m51494440.Interior.Color = "#ECECEC";
m51494440.Interior.Pattern = StyleInteriorPattern.Solid;
m51494440.Alignment.Horizontal = StyleHorizontalAlignment.Center;
m51494440.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494440.Alignment.WrapText = true;
m51494440.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 3, "#959595");
m51494440.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494440.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494440.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494460
// -----------------------------------------------
WorksheetStyle m51494460 = styles.Add("m51494460");
m51494460.Font.FontName = "Verdana";
m51494460.Font.Color = "#000000";
m51494460.Interior.Color = "#ECECEC";
m51494460.Interior.Pattern = StyleInteriorPattern.Solid;
m51494460.Alignment.Horizontal = StyleHorizontalAlignment.Center;
m51494460.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494460.Alignment.WrapText = true;
m51494460.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 3, "#959595");
m51494460.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494460.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494460.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494480
// -----------------------------------------------
WorksheetStyle m51494480 = styles.Add("m51494480");
m51494480.Font.FontName = "Verdana";
m51494480.Font.Color = "#000000";
m51494480.Interior.Color = "#ECECEC";
m51494480.Interior.Pattern = StyleInteriorPattern.Solid;
m51494480.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494480.Alignment.WrapText = true;
m51494480.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 3, "#959595");
m51494480.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494480.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 3, "#959595");
m51494480.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494176
// -----------------------------------------------
WorksheetStyle m51494176 = styles.Add("m51494176");
m51494176.Font.FontName = "Verdana";
m51494176.Font.Color = "#000000";
m51494176.Interior.Color = "#ECECEC";
m51494176.Interior.Pattern = StyleInteriorPattern.Solid;
m51494176.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494176.Alignment.WrapText = true;
m51494176.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494176.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 3, "#959595");
m51494176.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494176.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494196
// -----------------------------------------------
WorksheetStyle m51494196 = styles.Add("m51494196");
m51494196.Font.FontName = "Verdana";
m51494196.Font.Color = "#000000";
m51494196.Interior.Color = "#ECECEC";
m51494196.Interior.Pattern = StyleInteriorPattern.Solid;
m51494196.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494196.Alignment.WrapText = true;
m51494196.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494196.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494196.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494196.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494216
// -----------------------------------------------
WorksheetStyle m51494216 = styles.Add("m51494216");
m51494216.Font.FontName = "Verdana";
m51494216.Font.Color = "#000000";
m51494216.Interior.Color = "#ECECEC";
m51494216.Interior.Pattern = StyleInteriorPattern.Solid;
m51494216.Alignment.Horizontal = StyleHorizontalAlignment.Center;
m51494216.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494216.Alignment.WrapText = true;
m51494216.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494216.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494216.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494216.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494236
// -----------------------------------------------
WorksheetStyle m51494236 = styles.Add("m51494236");
m51494236.Font.FontName = "Verdana";
m51494236.Font.Color = "#000000";
m51494236.Interior.Color = "#ECECEC";
m51494236.Interior.Pattern = StyleInteriorPattern.Solid;
m51494236.Alignment.Horizontal = StyleHorizontalAlignment.Center;
m51494236.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494236.Alignment.WrapText = true;
m51494236.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494236.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494236.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494236.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494256
// -----------------------------------------------
WorksheetStyle m51494256 = styles.Add("m51494256");
m51494256.Font.FontName = "Verdana";
m51494256.Font.Color = "#000000";
m51494256.Interior.Color = "#ECECEC";
m51494256.Interior.Pattern = StyleInteriorPattern.Solid;
m51494256.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494256.Alignment.WrapText = true;
m51494256.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494256.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494256.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 3, "#959595");
m51494256.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494276
// -----------------------------------------------
WorksheetStyle m51494276 = styles.Add("m51494276");
m51494276.Font.FontName = "Verdana";
m51494276.Font.Color = "#000000";
m51494276.Interior.Color = "#FFFFFF";
m51494276.Interior.Pattern = StyleInteriorPattern.Solid;
m51494276.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494276.Alignment.WrapText = true;
m51494276.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494276.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 3, "#959595");
m51494276.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494276.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494296
// -----------------------------------------------
WorksheetStyle m51494296 = styles.Add("m51494296");
m51494296.Font.FontName = "Verdana";
m51494296.Font.Color = "#000000";
m51494296.Interior.Color = "#FFFFFF";
m51494296.Interior.Pattern = StyleInteriorPattern.Solid;
m51494296.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494296.Alignment.WrapText = true;
m51494296.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494296.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494296.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494296.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494316
// -----------------------------------------------
WorksheetStyle m51494316 = styles.Add("m51494316");
m51494316.Font.FontName = "Verdana";
m51494316.Font.Color = "#000000";
m51494316.Interior.Color = "#FFFFFF";
m51494316.Interior.Pattern = StyleInteriorPattern.Solid;
m51494316.Alignment.Horizontal = StyleHorizontalAlignment.Center;
m51494316.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494316.Alignment.WrapText = true;
m51494316.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494316.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494316.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494316.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494336
// -----------------------------------------------
WorksheetStyle m51494336 = styles.Add("m51494336");
m51494336.Font.FontName = "Verdana";
m51494336.Font.Color = "#000000";
m51494336.Interior.Color = "#FFFFFF";
m51494336.Interior.Pattern = StyleInteriorPattern.Solid;
m51494336.Alignment.Horizontal = StyleHorizontalAlignment.Center;
m51494336.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494336.Alignment.WrapText = true;
m51494336.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494336.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494336.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
m51494336.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// m51494356
// -----------------------------------------------
WorksheetStyle m51494356 = styles.Add("m51494356");
m51494356.Font.FontName = "Verdana";
m51494356.Font.Color = "#000000";
m51494356.Interior.Color = "#FFFFFF";
m51494356.Interior.Pattern = StyleInteriorPattern.Solid;
m51494356.Alignment.Vertical = StyleVerticalAlignment.Center;
m51494356.Alignment.WrapText = true;
m51494356.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 2, "#959595");
m51494356.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 2, "#959595");
m51494356.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 3, "#959595");
m51494356.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 2, "#959595");
// -----------------------------------------------
// s70
// -----------------------------------------------
WorksheetStyle s70 = styles.Add("s70");
s70.Font.FontName = "Verdana";
s70.Font.Color = "#000000";
s70.Alignment.Vertical = StyleVerticalAlignment.Top;
s70.Alignment.WrapText = true;
// -----------------------------------------------
// s83
// -----------------------------------------------
WorksheetStyle s83 = styles.Add("s83");
s83.Font.FontName = "Verdana";
s83.Font.Color = "#000000";
s83.Interior.Color = "#FFFFFF";
s83.Interior.Pattern = StyleInteriorPattern.Solid;
s83.Alignment.Vertical = StyleVerticalAlignment.Center;
s83.Alignment.WrapText = true;
// -----------------------------------------------
// s89
// -----------------------------------------------
WorksheetStyle s89 = styles.Add("s89");
s89.Font.Bold = true;
s89.Font.FontName = "Verdana";
s89.Font.Color = "#000000";
s89.Interior.Color = "#C2C2C2";
s89.Interior.Pattern = StyleInteriorPattern.Solid;
s89.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s89.Alignment.Vertical = StyleVerticalAlignment.Center;
s89.Alignment.WrapText = true;
s89.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 3, "#959595");
s89.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
s89.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 3, "#959595");
// -----------------------------------------------
// s90
// -----------------------------------------------
WorksheetStyle s90 = styles.Add("s90");
s90.Font.Bold = true;
s90.Font.FontName = "Verdana";
s90.Font.Color = "#000000";
s90.Interior.Color = "#C2C2C2";
s90.Interior.Pattern = StyleInteriorPattern.Solid;
s90.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s90.Alignment.Vertical = StyleVerticalAlignment.Center;
s90.Alignment.WrapText = true;
s90.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 2, "#959595");
s90.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 3, "#959595");
// -----------------------------------------------
// s91
// -----------------------------------------------
WorksheetStyle s91 = styles.Add("s91");
s91.Font.Bold = true;
s91.Font.FontName = "Verdana";
s91.Font.Color = "#000000";
s91.Interior.Color = "#C2C2C2";
s91.Interior.Pattern = StyleInteriorPattern.Solid;
s91.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s91.Alignment.Vertical = StyleVerticalAlignment.Center;
s91.Alignment.WrapText = true;
s91.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 3, "#959595");
s91.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 3, "#959595");
// -----------------------------------------------
// s129
// -----------------------------------------------
WorksheetStyle s129 = styles.Add("s129");
s129.Font.FontName = "Verdana";
s129.Font.Color = "#000000";
s129.Interior.Color = "#FFFFFF";
s129.Interior.Pattern = StyleInteriorPattern.Solid;
s129.Alignment.Horizontal = StyleHorizontalAlignment.Left;
s129.Alignment.Vertical = StyleVerticalAlignment.Center;
s129.Alignment.WrapText = true;
// -----------------------------------------------
// s130
// -----------------------------------------------
WorksheetStyle s130 = styles.Add("s130");
s130.Font.Bold = true;
s130.Font.FontName = "Verdana";
s130.Font.Color = "#000000";
s130.Interior.Color = "#FFFFFF";
s130.Interior.Pattern = StyleInteriorPattern.Solid;
s130.Alignment.Horizontal = StyleHorizontalAlignment.Left;
s130.Alignment.Vertical = StyleVerticalAlignment.Center;
s130.Alignment.WrapText = true;
// -----------------------------------------------
// s131
// -----------------------------------------------
WorksheetStyle s131 = styles.Add("s131");
s131.Font.Bold = true;
s131.Font.FontName = "Verdana";
s131.Font.Color = "#000000";
s131.Interior.Color = "#FFFFFF";
s131.Interior.Pattern = StyleInteriorPattern.Solid;
s131.Alignment.Vertical = StyleVerticalAlignment.Center;
s131.Alignment.WrapText = true;
// -----------------------------------------------
// s132
// -----------------------------------------------
WorksheetStyle s132 = styles.Add("s132");
s132.Font.FontName = "Verdana";
s132.Font.Size = 18;
s132.Font.Color = "#000000";
s132.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s132.Alignment.Vertical = StyleVerticalAlignment.Center;
s132.Alignment.WrapText = true;
// -----------------------------------------------
// s133
// -----------------------------------------------
WorksheetStyle s133 = styles.Add("s133");
s133.Font.FontName = "Verdana";
s133.Font.Color = "#666666";
s133.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s133.Alignment.Vertical = StyleVerticalAlignment.Center;
s133.Alignment.WrapText = true;
// -----------------------------------------------
// s134
// -----------------------------------------------
WorksheetStyle s134 = styles.Add("s134");
s134.Alignment.Vertical = StyleVerticalAlignment.Center;
s134.Alignment.WrapText = true;
// -----------------------------------------------
// s135
// -----------------------------------------------
WorksheetStyle s135 = styles.Add("s135");
s135.Font.Bold = true;
s135.Font.FontName = "Verdana";
s135.Font.Color = "#000000";
s135.Alignment.Vertical = StyleVerticalAlignment.Center;
s135.Alignment.WrapText = true;
// -----------------------------------------------
// s136
// -----------------------------------------------
WorksheetStyle s136 = styles.Add("s136");
s136.Font.FontName = "Verdana";
s136.Font.Color = "#000000";
s136.Alignment.Vertical = StyleVerticalAlignment.Center;
s136.Alignment.WrapText = true;
// -----------------------------------------------
// s137
// -----------------------------------------------
WorksheetStyle s137 = styles.Add("s137");
s137.Font.FontName = "Verdana";
s137.Font.Color = "#000000";
s137.Alignment.Vertical = StyleVerticalAlignment.Center;
s137.Alignment.WrapText = true;
s137.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 3, "#959595");
// -----------------------------------------------
// s141
// -----------------------------------------------
WorksheetStyle s141 = styles.Add("s141");
s141.Alignment.Horizontal = StyleHorizontalAlignment.Left;
s141.Alignment.Vertical = StyleVerticalAlignment.Top;
}
private void GenerateStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "Calibri";
Default.Font.Size = 11;
Default.Font.Color = "#000000";
Default.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// m92103616
// -----------------------------------------------
WorksheetStyle m92103616 = styles.Add("m92103616");
m92103616.Font.Bold = true;
m92103616.Font.FontName = "Times New Roman";
m92103616.Font.Size = 11;
m92103616.Font.Color = "#000000";
m92103616.Interior.Color = "#8DB4E3";
m92103616.Interior.Pattern = StyleInteriorPattern.Solid;
m92103616.Alignment.Horizontal = StyleHorizontalAlignment.Right;
m92103616.Alignment.Vertical = StyleVerticalAlignment.Center;
m92103616.Alignment.WrapText = true;
m92103616.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
m92103616.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
m92103616.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// m92103636
// -----------------------------------------------
WorksheetStyle m92103636 = styles.Add("m92103636");
m92103636.Font.Bold = true;
m92103636.Font.FontName = "Times New Roman";
m92103636.Font.Size = 11;
m92103636.Font.Color = "#000000";
m92103636.Interior.Color = "#D8D8D8";
m92103636.Interior.Pattern = StyleInteriorPattern.Solid;
m92103636.Alignment.Horizontal = StyleHorizontalAlignment.Right;
m92103636.Alignment.Vertical = StyleVerticalAlignment.Center;
m92103636.Alignment.WrapText = true;
m92103636.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
m92103636.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
m92103636.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// m92103656
// -----------------------------------------------
WorksheetStyle m92103656 = styles.Add("m92103656");
m92103656.Font.Bold = true;
m92103656.Font.FontName = "Times New Roman";
m92103656.Font.Size = 11;
m92103656.Font.Color = "#F2F2F2";
m92103656.Interior.Color = "#E46D0A";
m92103656.Interior.Pattern = StyleInteriorPattern.Solid;
m92103656.Alignment.Horizontal = StyleHorizontalAlignment.Right;
m92103656.Alignment.Vertical = StyleVerticalAlignment.Center;
m92103656.Alignment.WrapText = true;
m92103656.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
m92103656.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
m92103656.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
m92103656.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// m92103676
// -----------------------------------------------
WorksheetStyle m92103676 = styles.Add("m92103676");
m92103676.Font.Bold = true;
m92103676.Font.FontName = "Times New Roman";
m92103676.Font.Size = 11;
m92103676.Font.Color = "#FF0000";
m92103676.Interior.Color = "#FFFF00";
m92103676.Interior.Pattern = StyleInteriorPattern.Solid;
m92103676.Alignment.Horizontal = StyleHorizontalAlignment.Right;
m92103676.Alignment.Vertical = StyleVerticalAlignment.Center;
m92103676.Alignment.WrapText = true;
m92103676.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
m92103676.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
m92103676.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
m92103676.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s16
// -----------------------------------------------
WorksheetStyle s16 = styles.Add("s16");
s16.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s16.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s17
// -----------------------------------------------
WorksheetStyle s17 = styles.Add("s17");
s17.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s18
// -----------------------------------------------
WorksheetStyle s18 = styles.Add("s18");
s18.Font.Bold = true;
s18.Font.FontName = "Calibri";
s18.Font.Size = 18;
s18.Font.Color = "#000000";
// -----------------------------------------------
// s19
// -----------------------------------------------
WorksheetStyle s19 = styles.Add("s19");
s19.Font.Bold = true;
s19.Font.FontName = "Times New Roman";
s19.Font.Size = 11;
s19.Font.Color = "#000000";
s19.Interior.Color = "#FAC090";
s19.Interior.Pattern = StyleInteriorPattern.Solid;
s19.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s19.Alignment.Vertical = StyleVerticalAlignment.Center;
s19.Alignment.WrapText = true;
s19.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s19.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s19.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s19.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s110
// -----------------------------------------------
WorksheetStyle s110 = styles.Add("s110");
s110.Font.Bold = true;
s110.Font.FontName = "Times New Roman";
s110.Font.Size = 18;
s110.Font.Color = "#000000";
s110.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s110.Alignment.Vertical = StyleVerticalAlignment.Center;
s110.Alignment.WrapText = true;
// -----------------------------------------------
// s111
// -----------------------------------------------
WorksheetStyle s111 = styles.Add("s111");
s111.Font.FontName = "Times New Roman";
s111.Font.Size = 11;
s111.Font.Color = "#000000";
s111.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s111.Alignment.Vertical = StyleVerticalAlignment.Center;
s111.Alignment.WrapText = true;
// -----------------------------------------------
// s112
// -----------------------------------------------
WorksheetStyle s112 = styles.Add("s112");
s112.Font.Italic = true;
s112.Font.FontName = "Times New Roman";
s112.Font.Color = "#000000";
s112.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s112.Alignment.Vertical = StyleVerticalAlignment.Center;
s112.Alignment.WrapText = true;
s112.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s113
// -----------------------------------------------
WorksheetStyle s113 = styles.Add("s113");
s113.Font.FontName = "Times New Roman";
s113.Font.Size = 11;
s113.Font.Color = "#000000";
s113.Interior.Color = "#FFFFFF";
s113.Interior.Pattern = StyleInteriorPattern.Solid;
s113.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s113.Alignment.Vertical = StyleVerticalAlignment.Center;
s113.Alignment.WrapText = true;
s113.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s113.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s113.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s113.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s114
// -----------------------------------------------
WorksheetStyle s114 = styles.Add("s114");
s114.Font.FontName = "Times New Roman";
s114.Font.Size = 11;
s114.Font.Color = "#000000";
s114.Interior.Color = "#FFFFFF";
s114.Interior.Pattern = StyleInteriorPattern.Solid;
s114.Alignment.Vertical = StyleVerticalAlignment.Center;
s114.Alignment.WrapText = true;
s114.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s114.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s114.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s114.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s115
// -----------------------------------------------
WorksheetStyle s115 = styles.Add("s115");
s115.Font.FontName = "Times New Roman";
s115.Font.Size = 11;
s115.Font.Color = "#000000";
s115.Interior.Color = "#FFFFFF";
s115.Interior.Pattern = StyleInteriorPattern.Solid;
s115.Alignment.Vertical = StyleVerticalAlignment.Center;
s115.Alignment.WrapText = true;
s115.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s115.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s115.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s115.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
s115.NumberFormat = "Standard";
// -----------------------------------------------
// s116
// -----------------------------------------------
WorksheetStyle s116 = styles.Add("s116");
s116.Font.Bold = true;
s116.Font.FontName = "Times New Roman";
s116.Font.Size = 11;
s116.Font.Color = "#000000";
s116.Interior.Color = "#8DB4E3";
s116.Interior.Pattern = StyleInteriorPattern.Solid;
s116.Alignment.Vertical = StyleVerticalAlignment.Center;
s116.Alignment.WrapText = true;
s116.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s116.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s116.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s116.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
s116.NumberFormat = "Standard";
// -----------------------------------------------
// s117
// -----------------------------------------------
WorksheetStyle s117 = styles.Add("s117");
s117.Font.Bold = true;
s117.Font.FontName = "Times New Roman";
s117.Font.Size = 11;
s117.Font.Color = "#FF0000";
s117.Interior.Color = "#FFFF00";
s117.Interior.Pattern = StyleInteriorPattern.Solid;
s117.Alignment.Vertical = StyleVerticalAlignment.Center;
s117.Alignment.WrapText = true;
s117.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s117.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s117.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s117.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
s117.NumberFormat = "Standard";
// -----------------------------------------------
// s118
// -----------------------------------------------
WorksheetStyle s118 = styles.Add("s118");
s118.Font.Bold = true;
s118.Font.FontName = "Times New Roman";
s118.Font.Size = 11;
s118.Font.Color = "#000000";
s118.Interior.Color = "#D8D8D8";
s118.Interior.Pattern = StyleInteriorPattern.Solid;
s118.Alignment.Vertical = StyleVerticalAlignment.Center;
s118.Alignment.WrapText = true;
s118.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s118.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s118.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s118.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
s118.NumberFormat = "Standard";
// -----------------------------------------------
// s119
// -----------------------------------------------
WorksheetStyle s119 = styles.Add("s119");
s119.Font.Bold = true;
s119.Font.FontName = "Times New Roman";
s119.Font.Size = 11;
s119.Font.Color = "#F2F2F2";
s119.Interior.Color = "#E46D0A";
s119.Interior.Pattern = StyleInteriorPattern.Solid;
s119.Alignment.Vertical = StyleVerticalAlignment.Center;
s119.Alignment.WrapText = true;
s119.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s119.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s119.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s119.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
s119.NumberFormat = "Standard";
}
private void GenerateExcelReport(DataTable dtTable, DataTable dtLength, string strFilePath, string ReportName, string strFileName)
{
//Add a workbook
CarlosAg.ExcelXmlWriter.Workbook book = new CarlosAg.ExcelXmlWriter.Workbook();
#region SheetProperties
// Specify which Sheet should be opened and the size of window by default
book.ExcelWorkbook.ActiveSheetIndex = 1;
book.ExcelWorkbook.WindowTopX = 100;
book.ExcelWorkbook.WindowTopY = 200;
book.ExcelWorkbook.WindowHeight = 7000;
book.ExcelWorkbook.WindowWidth = 8000;
// Some optional properties of the Document
book.Properties.Author = "Secure Proctor Report";
book.Properties.Title = "Excel Export";
book.Properties.Created = DateTime.Now;
#endregion
#region CellStyle
WorksheetStyle CellStyle = book.Styles.Add("CellStyle");
CellStyle.Font.FontName = "Arial";
CellStyle.Font.Size = 11;
CellStyle.Font.Color = "#000000";
CellStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
CellStyle.Alignment.Vertical = StyleVerticalAlignment.Center;
CellStyle.Alignment.WrapText = false;
CellStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
CellStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
CellStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
CellStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
CellStyle.NumberFormat = "@";
#endregion
#region HeaderStyle
WorksheetStyle HeaderStyle = book.Styles.Add("HeaderStyle");
HeaderStyle.Font.FontName = "Arial";
HeaderStyle.Font.Size = 11;
HeaderStyle.Font.Bold = true;
HeaderStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
HeaderStyle.Alignment.WrapText = false;
HeaderStyle.Font.Color = "#000000";
HeaderStyle.Interior.Color = "#99CCFF";
HeaderStyle.Interior.Pattern = StyleInteriorPattern.Solid;
HeaderStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
HeaderStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
HeaderStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
HeaderStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
#endregion
// Add a Worksheet with some data
Worksheet sheet = book.Worksheets.Add(ReportName);
//Add row with some properties
WorksheetRow row = sheet.Table.Rows.Add();
row.Index = 0;
row.Height = 35;
row.AutoFitHeight = true;
//Set worksheet column names
int ColNumber = 0;
int ColumnLength = 100;
int colSeedValue = 7;
foreach (DataColumn dc in dtTable.Columns)
{
///* SET COLUMN WIDTH - START */
if (dtLength.Rows[0][ColNumber] != null)
{
if (dtLength.Rows[0][ColNumber].ToString().Length != 0)
{
if (Convert.ToInt32(dtLength.Rows[0][ColNumber].ToString()) > dc.ColumnName.Length)
{
ColumnLength = Convert.ToInt32(dtLength.Rows[0][ColNumber].ToString()) * colSeedValue;
}
else
{
ColumnLength = dc.ColumnName.Length * colSeedValue;
}
}
else
{
ColumnLength = dc.ColumnName.Length * colSeedValue;
}
}
else
{
ColumnLength = dc.ColumnName.Length * colSeedValue;
}
///* SET COLUMN WIDTH - END */
//ColumnLength = 100;
sheet.Table.Columns.Add(new WorksheetColumn(ColumnLength));
WorksheetCell wcHeader = new WorksheetCell(dc.ColumnName, CarlosAg.ExcelXmlWriter.DataType.String, "HeaderStyle");
row.Cells.Add(wcHeader);
ColNumber++;
}
foreach (DataRow dtrrow in dtTable.Rows)
{
//Add row to the excel sheet
row = sheet.Table.Rows.Add();
//row.Height = 30;
row.AutoFitHeight = true;
//Loop through each column
foreach (DataColumn col in dtTable.Columns)
{
WorksheetCell wc = new WorksheetCell(dtrrow[col.ColumnName].ToString(), DataType.String, "CellStyle");
row.Cells.Add(wc);
}
}
//Save the work book
book.Save(strFilePath);
DownloadFile(strFilePath, strFileName);
}
public static void GenerateExcel(String OutputFileName, DeivationList lstDeviation)
{
Workbook workbook = new Workbook();
workbook.ExcelWorkbook.ActiveSheetIndex = (3);
workbook.ExcelWorkbook.WindowTopX = (100);
workbook.ExcelWorkbook.WindowTopY = (200);
workbook.ExcelWorkbook.WindowHeight = (7000);
workbook.ExcelWorkbook.WindowWidth = (10000);
workbook.Properties.Author = ("CarlosAg");
workbook.Properties.Title = "DeviationApp";
workbook.Properties.Created = (DateTime.Now);
workbook.Styles.Add("HeaderStyle").Font.FontName = ("Cordia New");
WorksheetStyle worksheetStyle1 = workbook.Styles.Add("Default");
worksheetStyle1.Font.FontName = "Cordia New";
worksheetStyle1.Font.Size = 14;
worksheetStyle1.Font.Bold = (true);
worksheetStyle1.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
worksheetStyle1.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
worksheetStyle1.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
worksheetStyle1.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
Worksheet worksheet1 = workbook.Worksheets.Add("DeviationSheet");
worksheet1.Table.Columns.Add(new WorksheetColumn(120)); //1
worksheet1.Table.Columns.Add(new WorksheetColumn(120)); //2
worksheet1.Table.Columns.Add(new WorksheetColumn(120)); //3
/// Header
///
WorksheetRow worksheetRow1 = worksheet1.Table.Rows.Add();
worksheetRow1.Cells.Add("");
worksheetRow1 = worksheet1.Table.Rows.Add();
worksheetRow1.Cells.Add("N (seq)");
worksheetRow1.Cells.Add("X (input)");
worksheetRow1.Cells.Add("(Xi - Xavg)^2");
WorksheetStyle style33 = workbook.Styles.Add("s31");
style33.Font.FontName = "Cordia New";
style33.Font.Size = 14;
style33.Font.Bold = false;
style33.Interior.Pattern = StyleInteriorPattern.Solid;
style33.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
style33.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
style33.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
style33.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
WorksheetStyle style = workbook.Styles.Add("s32");
style.Font.FontName = "Cordia New";
style.Font.Size = 14;
style.Font.Bold = false;
style.NumberFormat = "#,###,##0";
WorksheetCell worksheetCell = null;
foreach (DeviationEntity dnEntity in lstDeviation.ListDeviationEntity)
{
worksheetRow1 = worksheet1.Table.Rows.Add();
worksheetCell = new WorksheetCell(dnEntity.NumSeq.ToString(), DataType.Number, "s31");
worksheetRow1.Cells.Add(worksheetCell);
worksheetCell = new WorksheetCell(dnEntity.NumX.ToString(), DataType.Number, "s31");
worksheetRow1.Cells.Add(worksheetCell);
worksheetCell = new WorksheetCell(dnEntity.NumDeviation.ToString(), DataType.Number, "s31");
worksheetRow1.Cells.Add(worksheetCell);
}
worksheetRow1 = worksheet1.Table.Rows.Add();
worksheetCell = new WorksheetCell("Result Summary", DataType.String, "s31");
worksheetRow1.Cells.Add(worksheetCell);
worksheetCell = new WorksheetCell("Sum (X) : " + lstDeviation.SumX, DataType.String, "s31");
worksheetRow1.Cells.Add(worksheetCell);
worksheetCell = new WorksheetCell("Sum (Deviation) : " + lstDeviation.SumDeviation, DataType.String, "s31");
worksheetRow1.Cells.Add(worksheetCell);
worksheetRow1 = worksheet1.Table.Rows.Add();
worksheetCell = new WorksheetCell("", DataType.String, "s31");
worksheetRow1.Cells.Add(worksheetCell);
worksheetCell = new WorksheetCell("Result Power Deviation", DataType.String, "s31");
worksheetRow1.Cells.Add(worksheetCell);
worksheetCell = new WorksheetCell(lstDeviation.DeviationPow1.ToString(), DataType.Number, "s31");
worksheetRow1.Cells.Add(worksheetCell);
workbook.Save(OutputFileName);
Process.Start(OutputFileName);
Console.WriteLine("Please Enter Finish Program (Y/N)");
Console.Read();
}
public static void AddStyles(this Workbook myWb)
{
const string evenBg = "#ffffff";
const string oddBg = "#dddddd";
//var thirdBG = "#AABCC1";
// font
const string font = "Tahoma";
const string fontColor = "#000000";
const int fontSize = 11;
const string borderColor = "#aaaaaa";
const string s31NumFormat = "@";
//var s3100NumFormat = "###,###,##0.00";
//var s310000NumFormat = "###,###,##0.0000";
WorksheetStyle style = myWb.Styles.Add("HeaderStyle");
style.Font.FontName = font;
style.Font.Size = 11;
style.Font.Bold = false;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Font.Color = "White";
style.Interior.Color = "#333333";
style.Alignment.WrapText = true;
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, borderColor);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, borderColor);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, borderColor);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, borderColor);
style.Interior.Pattern = StyleInteriorPattern.Solid;
WorksheetStyle s31 = myWb.Styles.Add("s31");
s31.Font.FontName = font;
s31.Font.Size = fontSize;
s31.Font.Color = fontColor;
s31.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s31.Alignment.Vertical = StyleVerticalAlignment.Center;
s31.Interior.Color = evenBg;
s31.Interior.Pattern = StyleInteriorPattern.Solid;
s31.Alignment.WrapText = false;
s31.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, borderColor);
s31.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, borderColor);
s31.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, borderColor);
s31.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, borderColor);
s31.NumberFormat = s31NumFormat;
WorksheetStyle s31Odd = myWb.Styles.Add("s31Odd");
s31Odd.Font.FontName = font;
s31Odd.Font.Size = fontSize;
s31Odd.Font.Color = fontColor;
s31Odd.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s31Odd.Alignment.Vertical = StyleVerticalAlignment.Center;
s31Odd.Interior.Color = oddBg;
s31Odd.Interior.Pattern = StyleInteriorPattern.Solid;
s31Odd.Alignment.WrapText = false;
s31Odd.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, borderColor);
s31Odd.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, borderColor);
s31Odd.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, borderColor);
s31Odd.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, borderColor);
s31Odd.NumberFormat = s31NumFormat;
}
public static void CreateExcel <T>(string sheetName, string[] sheetFields, int[] titleWidth, string[] dataFiles, string[] fomateFiles, IList <T> list, string strSavePath)
{
Workbook newBook = new Workbook();
Worksheet newSheet = newBook.Worksheets.Add(sheetName);
//设置表格样式
WorksheetStyle titleStyle = newBook.Styles.Add("titleStyle");
titleStyle.Font.Bold = true;
titleStyle.Interior.Color = "#999999";
titleStyle.Interior.Pattern = StyleInteriorPattern.Solid;
titleStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
titleStyle.Alignment.Vertical = StyleVerticalAlignment.Center;
WorksheetStyle dataStyle1 = newBook.Styles.Add("dataStyle1");
dataStyle1.Font.Color = "Red";
WorksheetStyle dataStyle2 = newBook.Styles.Add("dataStyle2");
dataStyle2.Font.Color = "Green";
for (int i = 0; i < sheetFields.Length; i++)
{
newSheet.Table.Columns.Add(titleWidth[i]);
}
//设置表头
WorksheetRow titleRow = newSheet.Table.Rows.Add();
foreach (string s in sheetFields)
{
titleRow.Cells.Add(s, DataType.String, "titleStyle");
}
//设置数据列
WorksheetRow dataRow = null;
DataTable tableDatas = new DataTable();
tableDatas = ListToDataTable(list);
if (tableDatas.Rows.Count > 0)
{
foreach (DataRow row in tableDatas.Rows)
{
dataRow = newSheet.Table.Rows.Add();
for (int i = 0; i < dataFiles.Length; i++)
{
if (!String.IsNullOrEmpty(fomateFiles[i]))
{
dataRow.Cells.Add(String.Format(fomateFiles[i], row[dataFiles[i]].ToString()));
}
else
{
dataRow.Cells.Add(row[dataFiles[i]].ToString());
}
}
}
}
//newSheet.Options.Selected = true;
//newSheet.Options.ProtectObjects = false;
//newSheet.Options.ProtectScenarios = false;
//newSheet.Options.SplitHorizontal = 1;
//newSheet.Options.SplitVertical = 1;
//newSheet.Options.ActivePane = 1;
//数据添加完毕，保存Excel
newBook.Save(System.Web.HttpContext.Current.Server.MapPath(strSavePath));
}
public static void Exporta(DataGridViewPersonalizat dGV, List <string> pListaColoaneImprimare, string pEtichetaTotal, string pFisier, bool pDeschideDupaCreare)
{
string fisierSalvare = pFisier;
DataType tipCelula = DataType.String;
if (string.IsNullOrEmpty(fisierSalvare))
{
fisierSalvare = CCL.UI.IHMUtile.GetCaleSalvareFisier(CCL.UI.IHMUtile.GetFormParinte(dGV), ".xls");
}
if (string.IsNullOrEmpty(fisierSalvare))
{
return;
}
string idStilHeader = "idSHeader";
WorksheetStyle stilHeader = new WorksheetStyle(idStilHeader);
stilHeader.Font.Bold = true;
stilHeader.Interior.Pattern = StyleInteriorPattern.Solid;
stilHeader.Interior.Color = "#c0c0c0";
string idStilTotal = "idSTotal";
WorksheetStyle stilTotal = new WorksheetStyle(idStilTotal);
stilTotal.Font.Bold = true;
stilTotal.Interior.Pattern = StyleInteriorPattern.Solid;
stilTotal.Interior.Color = "#80ff80";
string idStilNormal = "idSNormal";
WorksheetStyle stilNormal = new WorksheetStyle(idStilNormal);
Workbook book = new Workbook();
book.Styles.Add(stilHeader);
book.Styles.Add(stilNormal);
book.Styles.Add(stilTotal);
Worksheet sheet = book.Worksheets.Add("iDava");
List <string> listaNumeColoaneExportabile = new List <string>();
Dictionary <string, DataType> dictColoaneTipuri = new Dictionary <string, DataType>();
string valoareCelula = string.Empty;
WorksheetRow rowHeader = sheet.Table.Rows.Add();
foreach (string numeColoana in pListaColoaneImprimare)
{
if (dGV.Columns[numeColoana].Visible && !dGV.SeIgnoraColoanaLaImprimare(numeColoana))
{
listaNumeColoaneExportabile.Add(numeColoana); //pentru a exporta datele coloanelor pretabile pentru export
dictColoaneTipuri.Add(numeColoana, getDataType(dGV.Columns[numeColoana]));
valoareCelula = CUtil.InlocuiesteDiacritice(dGV.Columns[numeColoana].HeaderText);
rowHeader.Cells.Add(valoareCelula, dictColoaneTipuri[numeColoana], idStilHeader);
}
}
List <DataGridViewRow> listaLiniiSelectate = dGV.GetListaLiniiSelectate();
bool existaValoareNumerica = false;
double totalValoareNumerica = 0;
foreach (DataGridViewRow linie in listaLiniiSelectate)
{
if (linie.Tag == null)
{
continue; //in Tag-ul tuturor liniilor din aplicatie salvam obiectul corespunzator liniei respective; lipsa acestuia implica ignorarea liniei
}
WorksheetRow rowValoare = sheet.Table.Rows.Add();
foreach (string numeColoane in pListaColoaneImprimare)
{
if (listaNumeColoaneExportabile.Contains(numeColoane))
{
if (linie.Cells[numeColoane].Tag != null && linie.Cells[numeColoane].Tag is String)
{
valoareCelula = CUtil.ConvertObjectToString(linie.Cells[numeColoane].Tag, true);
}
else
{
valoareCelula = CUtil.ConvertObjectToString(linie.Cells[numeColoane].EditedFormattedValue, true);
}
if (dGV.esteColoanaNumericaExport(numeColoane))
{
existaValoareNumerica = true;
totalValoareNumerica += CUtil.GetAsDouble(valoareCelula);
rowValoare.Cells.Add(valoareCelula, dictColoaneTipuri[numeColoane], idStilNormal);
}
else
{
rowValoare.Cells.Add(valoareCelula, dictColoaneTipuri[numeColoane], idStilNormal);
}
}
}
}
if (existaValoareNumerica)
{
WorksheetRow rowValoare = sheet.Table.Rows.Add();
int indexCelula = 0;
foreach (string numeColoane in pListaColoaneImprimare)
{
if (listaNumeColoaneExportabile.Contains(numeColoane))
{
if (dGV.esteColoanaNumericaExport(numeColoane))
{
rowValoare.Cells.Add(Convert.ToString(Math.Round(totalValoareNumerica, 2)), dictColoaneTipuri[numeColoane], idStilTotal);
}
else
{
if (indexCelula == 0)
{
rowValoare.Cells.Add(pEtichetaTotal, tipCelula, idStilTotal);
}
else
{
rowValoare.Cells.Add(string.Empty, tipCelula, idStilTotal);
}
indexCelula += 1;
}
}
}
}
if (!string.IsNullOrEmpty(fisierSalvare))
{
book.Save(fisierSalvare);
if (pDeschideDupaCreare)
{
Process.Start(fisierSalvare);
}
}
}
private void GenerateStyles(WorksheetStyleCollection styles)
{
try
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "Calibri";
Default.Font.Size = 11;
Default.Font.Color = "#000000";
Default.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s21
// -----------------------------------------------
WorksheetStyle s21 = styles.Add("s21");
s21.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s21.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s22
// -----------------------------------------------
WorksheetStyle s22 = styles.Add("s22");
s22.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s22.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s34
// -----------------------------------------------
WorksheetStyle s34 = styles.Add("s34");
s34.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s34.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s34.NumberFormat = "0";
// -----------------------------------------------
// s44
// -----------------------------------------------
WorksheetStyle s44 = styles.Add("s44");
s44.Font.FontName = "Verdana";
s44.Font.Color = "#000000";
s44.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s44.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s44.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s44.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s44.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s44.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s45
// -----------------------------------------------
WorksheetStyle s45 = styles.Add("s45");
s45.Font.FontName = "Verdana";
s45.Font.Color = "#000000";
//s45.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
//s45.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
//s45.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
//s45.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
s45.NumberFormat = "0";
s45.Alignment.Horizontal = StyleHorizontalAlignment.Right;
// -----------------------------------------------
// s46
// -----------------------------------------------
WorksheetStyle s46 = styles.Add("s46");
s46.Font.FontName = "Verdana";
s46.Font.Color = "#000000";
s46.Alignment.Horizontal = StyleHorizontalAlignment.Left;
s46.Alignment.Vertical = StyleVerticalAlignment.Bottom;
//s46.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
//s46.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
//s46.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
//s46.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
s46.NumberFormat = "0";
// -----------------------------------------------
// s47
// -----------------------------------------------
WorksheetStyle s47 = styles.Add("s47");
s47.Font.FontName = "Verdana";
s47.Font.Color = "#000000";
s47.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s47.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s47.NumberFormat = "0";
// -----------------------------------------------
// s48
// -----------------------------------------------
WorksheetStyle s48 = styles.Add("s48");
s48.Font.FontName = "Verdana";
s48.Interior.Color = "#FFFFFF";
s48.Interior.Pattern = StyleInteriorPattern.Solid;
s48.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s48.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s48.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s48.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s48.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s48.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s49
// -----------------------------------------------
WorksheetStyle s49 = styles.Add("s49");
s49.Font.FontName = "Verdana";
s49.Font.Color = "#000000";
s49.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s49.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s49.NumberFormat = "0";
// -----------------------------------------------
// s50
// -----------------------------------------------
WorksheetStyle s50 = styles.Add("s50");
s50.Font.FontName = "Verdana";
s50.Font.Color = "#000000";
s50.NumberFormat = "0";
// -----------------------------------------------
// s51
// -----------------------------------------------
WorksheetStyle s51 = styles.Add("s51");
s51.Font.FontName = "Verdana";
s51.Font.Color = "RED";
s51.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s51.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s51.NumberFormat = "Fixed";
// -----------------------------------------------
// s52
// -----------------------------------------------
WorksheetStyle s52 = styles.Add("s52");
s52.Font.FontName = "Verdana";
s52.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s52.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s52.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s52.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s52.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s52.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s54
// -----------------------------------------------
WorksheetStyle s54 = styles.Add("s54");
s54.Font.Bold = true;
s54.Font.FontName = "Verdana";
s54.Font.Size = 11;
s54.Font.Color = "#000000";
s54.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s54.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s54.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s54.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s54.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s54.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s55
// -----------------------------------------------
WorksheetStyle s55 = styles.Add("s55");
s55.Font.Bold = true;
s55.Font.FontName = "Verdana";
s55.Font.Size = 10;
s55.Font.Color = "#000000";
s55.Alignment.Horizontal = StyleHorizontalAlignment.Left;
s55.Alignment.Vertical = StyleVerticalAlignment.Bottom;
//s55.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
//s55.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
//s55.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
//s55.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// s56
// -----------------------------------------------
WorksheetStyle s56 = styles.Add("s56");
s56.Font.Bold = true;
s56.Font.FontName = "Verdana";
s56.Font.Size = 10;
s56.Font.Color = "#000000";
s56.Alignment.WrapText = true;
s56.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s56.Alignment.Vertical = StyleVerticalAlignment.Bottom;
//s56.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
//s56.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
//s56.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
//s56.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
}
catch (Exception ex)
{
}
}
private void GenerarEstilos(WorksheetStyleCollection styles)
{
//[Default]
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "Formata Regular";
Default.Font.Color = "#000000";
Default.Alignment.Vertical = StyleVerticalAlignment.Bottom;
WorksheetStyle s16 = styles.Add("s16");
s16.Name = "Millares";
s16.NumberFormat = "_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * " + "-" + "??_ ;_ @_ ";
WorksheetStyle s31 = styles.Add("s31");
s31.Font.FontName = "Tahoma";
s31.Font.Size = 8;
s31.Font.Color = "#000000";
s31.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s31.Alignment.Vertical = StyleVerticalAlignment.Center;
s31.Alignment.WrapText = true;
s31.NumberFormat = "@";
WorksheetStyle s32 = styles.Add("s32");
s32.Font.FontName = "Formata Regular";
s32.NumberFormat = "#####";
s32.Font.Color = "#000000";
s32.Alignment.Vertical = StyleVerticalAlignment.Center;
WorksheetStyle s62 = styles.Add("s62");
s62.Alignment.Vertical = StyleVerticalAlignment.Center;
WorksheetStyle s63 = styles.Add("s63");
s63.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s63.Alignment.Vertical = StyleVerticalAlignment.Center;
WorksheetStyle s65 = styles.Add("s65");
s65.Alignment.Horizontal = StyleHorizontalAlignment.Left;
s65.Alignment.Vertical = StyleVerticalAlignment.Center;
WorksheetStyle s84 = styles.Add("s84");
s84.Interior.Color = "#BFBFBF";
s84.Interior.Pattern = StyleInteriorPattern.Solid;
s84.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s84.Alignment.Vertical = StyleVerticalAlignment.Center;
s84.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s84.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s84.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s84.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
WorksheetStyle s85 = styles.Add("s85");
s85.Alignment.Vertical = StyleVerticalAlignment.Center;
s85.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s85.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s85.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s85.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
WorksheetStyle s86 = styles.Add("s86");
s86.Parent = "s16";
s86.Font.FontName = "Formata Regular";
s86.Font.Color = "#000000";
s86.Alignment.Vertical = StyleVerticalAlignment.Center;
;
WorksheetStyle s87 = styles.Add("s87");
s87.Font.FontName = "Formata Regular";
s87.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s87.Alignment.Vertical = StyleVerticalAlignment.Center;
s87.Alignment.WrapText = true;
//WorksheetStyle s88 = styles.Add("s88");
//s88.Alignment.Horizontal = StyleHorizontalAlignment.Center;
//s88.Alignment.Vertical = StyleVerticalAlignment.Center;
//s88.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
//s88.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
//s88.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
//s88.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
//s88.NumberFormat = "@";
//WorksheetStyle s89 = styles.Add("s89");
//s89.Parent = "s16";
//s89.Font.FontName = "Formata Regular";
//s89.Font.Color = "#000000";
//s89.Alignment.Vertical = StyleVerticalAlignment.Center;
//s89.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
//s89.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
//s89.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
//s89.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
//s89.NumberFormat = "_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * " + "-" + "??_ ;_ @_ ";
WorksheetStyle s94 = styles.Add("s94");
s94.Alignment.Horizontal = StyleHorizontalAlignment.Left;
s94.Alignment.Vertical = StyleVerticalAlignment.Center;
s94.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s94.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s94.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s94.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
}
/// //////////////////////////////////////////////
public CResultAErreur Export(DataSet ds, IDestinationExport destination)
{
CResultAErreur result = CResultAErreur.True;
if (!(destination is CDestinationExportFile))
{
result.EmpileErreur(I.T("An Excel export can be done only in a file|171"));
return(result);
}
string strNomFichier = ((CDestinationExportFile)destination).FileName;
try
{
ds.EnforceConstraints = false;
if (File.Exists(strNomFichier))
{
File.Delete(strNomFichier);
}
Workbook book = new Workbook();
WorksheetStyle style = book.Styles.Add("SC2I_DATE");
style.NumberFormat = I.T("Short DATE|170");
//Nom de table->true : pour éviter 2 fois le même nom de table
Hashtable tableTablesCrees = new Hashtable();
Hashtable tableColumnsToHide = new Hashtable();
Regex colKeyAuto = new Regex("^Key_");
if (m_bMasquerIdsAuto)
{
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.PrimaryKey)
{
if (col.AutoIncrement || colKeyAuto.IsMatch(col.ColumnName))
{
tableColumnsToHide[col] = true;
}
}
}
foreach (DataRelation rel in ds.Relations)
{
if (rel.ParentColumns.Length == 1 &&
tableColumnsToHide.Contains(rel.ParentColumns[0]) &&
rel.ChildColumns.Length == 1)
{
tableColumnsToHide[rel.ChildColumns[0]] = true;
}
}
}
foreach (DataTable table in ds.Tables)
{
Worksheet sheet = book.Worksheets.Add(table.TableName);
//Création des colonnes
WorksheetRow row = sheet.Table.Rows.Add( );
DataType[] typeCol = new DataType[table.Columns.Count];
int nCol = 0;
foreach (DataColumn col in table.Columns)
{
if (!tableColumnsToHide.Contains(col))
{
WorksheetColumn colSheet = sheet.Table.Columns.Add( );
DataType tp = GetExcelType(col.DataType);
typeCol[nCol] = tp;
nCol++;
row.Cells.Add(ConvertName(col.ColumnName));
if (tp == DataType.DateTime)
{
colSheet.StyleID = "SC2I_DATE";
}
}
}
if (!m_bOnlyStructure)
{
//Insertion des données
foreach (DataRow rowNet in table.Rows)
{
row = sheet.Table.Rows.Add();
nCol = 0;
foreach (DataColumn col in table.Columns)
{
if (!tableColumnsToHide.Contains(col))
{
object val = rowNet[col];
if (val == DBNull.Value)
{
row.Cells.Add();
}
else
{
string strVal = "";
if (typeCol[nCol] == DataType.DateTime)
{
if (val is CDateTimeEx)
{
val = ((CDateTimeEx)val).DateTimeValue;
}
strVal = ((DateTime)val).ToString("yyyy-MM-ddTHH:mm:ss");
}
else
{
strVal = val.ToString();
}
row.Cells.Add(strVal, typeCol[nCol], null);
}
nCol++;
}
}
}
}
}
book.Save(strNomFichier);
}
catch (Exception e)
{
result.EmpileErreur(new CErreurException(e));
return(result);
}
finally
{
ds.EnforceConstraints = true;
}
return(result);
}
private void GenerateStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "Calibri";
Default.Font.Size = 11;
Default.Font.Color = "#000000";
Default.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// m83608040
// -----------------------------------------------
WorksheetStyle m83608040 = styles.Add("m83608040");
m83608040.Font.Italic = true;
m83608040.Font.FontName = "Times New Roman";
m83608040.Font.Size = 11;
m83608040.Font.Color = "#000000";
m83608040.Alignment.Horizontal = StyleHorizontalAlignment.Right;
m83608040.Alignment.Vertical = StyleVerticalAlignment.Center;
m83608040.Alignment.WrapText = true;
m83608040.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
m83608040.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
m83608040.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// m83608060
// -----------------------------------------------
WorksheetStyle m83608060 = styles.Add("m83608060");
m83608060.Font.Bold = true;
m83608060.Font.FontName = "Times New Roman";
m83608060.Font.Size = 12;
m83608060.Font.Color = "#FF0000";
m83608060.Interior.Color = "#FFFF00";
m83608060.Interior.Pattern = StyleInteriorPattern.Solid;
m83608060.Alignment.Horizontal = StyleHorizontalAlignment.Right;
m83608060.Alignment.Vertical = StyleVerticalAlignment.Center;
m83608060.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
m83608060.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
m83608060.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
m83608060.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// s67
// -----------------------------------------------
WorksheetStyle s67 = styles.Add("s67");
s67.Font.Bold = true;
s67.Font.FontName = "Times New Roman";
s67.Font.Size = 12;
s67.Font.Color = "#333399";
s67.Interior.Color = "#FCD5B4";
s67.Interior.Pattern = StyleInteriorPattern.Solid;
s67.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s67.Alignment.Vertical = StyleVerticalAlignment.Center;
s67.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s67.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s67.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s67.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// s68
// -----------------------------------------------
WorksheetStyle s68 = styles.Add("s68");
s68.Font.FontName = "Times New Roman";
s68.Font.Size = 11;
s68.Font.Color = "#000000";
s68.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s68.Alignment.Vertical = StyleVerticalAlignment.Center;
s68.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s68.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s68.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s68.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// s69
// -----------------------------------------------
WorksheetStyle s69 = styles.Add("s69");
s69.Font.FontName = "Times New Roman";
s69.Font.Size = 11;
s69.Font.Color = "#000000";
s69.Alignment.Vertical = StyleVerticalAlignment.Center;
s69.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s69.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s69.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s69.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
// -----------------------------------------------
// s70
// -----------------------------------------------
WorksheetStyle s70 = styles.Add("s70");
s70.Font.Bold = true;
s70.Font.Italic = true;
s70.Font.FontName = "Times New Roman";
s70.Font.Size = 11;
s70.Font.Color = "#000000";
s70.Interior.Color = "#DBE5F1";
s70.Interior.Pattern = StyleInteriorPattern.Solid;
s70.Alignment.Vertical = StyleVerticalAlignment.Center;
s70.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s70.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s70.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s70.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
s70.NumberFormat = "Standard";
// -----------------------------------------------
// s74
// -----------------------------------------------
WorksheetStyle s74 = styles.Add("s74");
s74.Font.Bold = true;
s74.Font.FontName = "Times New Roman";
s74.Font.Size = 12;
s74.Font.Color = "#FF0000";
s74.Interior.Color = "#FFFF00";
s74.Interior.Pattern = StyleInteriorPattern.Solid;
s74.Alignment.Vertical = StyleVerticalAlignment.Center;
s74.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
s74.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
s74.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
s74.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
s74.NumberFormat = "Standard";
// -----------------------------------------------
// s75
// -----------------------------------------------
WorksheetStyle s75 = styles.Add("s75");
s75.Font.Bold = true;
s75.Font.FontName = "Times New Roman";
s75.Font.Size = 16;
s75.Font.Color = "#000000";
s75.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s75.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// s76
// -----------------------------------------------
WorksheetStyle s76 = styles.Add("s76");
s76.Font.Italic = true;
s76.Font.FontName = "Times New Roman";
s76.Font.Size = 11;
s76.Font.Color = "#000000";
s76.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s76.Alignment.Vertical = StyleVerticalAlignment.Center;
}
/// <summary>
/// Writes the provided data to a new worksheet.
/// </summary>
/// <typeparam name="TClass">The class type encapsulating the data to be written to the worksheet.</typeparam>
/// <typeparam name="TClassMap">A class map identifying how the data will be written.</typeparam>
/// <param name="worksheetName">The name of the worksheet to be written.</param>
/// <param name="worksheetIndex">The worksheet's zero-based position index within the workbook.</param>
/// <param name="records">The data to be written.</param>
/// <param name="worksheetStyle">The worksheet's style definition.</param>
public void WriteWorksheet <TClass, TClassMap>(string worksheetName, int worksheetIndex, IEnumerable <TClass> records, WorksheetStyle worksheetStyle = null)
where TClass : class
where TClassMap : ClassMap <TClass>
{
using (var worksheetWriter = this.CreateWorksheetWriter <TClass, TClassMap>(worksheetName, worksheetIndex, worksheetStyle))
{
worksheetWriter.WriteRecords(records);
}
}
private void GenerateStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "Arial";
Default.Font.Size = 11;
Default.Font.Color = "#000000";
Default.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s63
// -----------------------------------------------
WorksheetStyle s63 = styles.Add("s63");
s63.Font.FontName = "Times New Roman";
s63.Font.Size = 20;
s63.Font.Color = "#FFFFFF";
s63.Interior.Color = "#000000";
s63.Interior.Pattern = StyleInteriorPattern.Solid;
s63.Alignment.Horizontal = StyleHorizontalAlignment.Center;
s63.Alignment.Vertical = StyleVerticalAlignment.Center;
s63.Alignment.WrapText = true;
s63.Alignment.ReadingOrder = StyleReadingOrder.RightToLeft;
// -----------------------------------------------
// s64
// -----------------------------------------------
WorksheetStyle s64 = styles.Add("s64");
s64.Font.FontName = "Times New Roman";
s64.Font.Size = 11;
s64.Font.Color = "#FFFFFF";
s64.Interior.Color = "#000000";
s64.Interior.Pattern = StyleInteriorPattern.Solid;
s64.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s64.Alignment.Vertical = StyleVerticalAlignment.Top;
s64.Alignment.WrapText = true;
s64.Alignment.ReadingOrder = StyleReadingOrder.RightToLeft;
// -----------------------------------------------
// s65
// -----------------------------------------------
WorksheetStyle s65 = styles.Add("s65");
s65.Font.FontName = "Times New Roman";
s65.Font.Size = 12;
s65.Font.Color = "#333399";
s65.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s65.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s65.Alignment.ReadingOrder = StyleReadingOrder.LeftToRight;
// -----------------------------------------------
// s66
// -----------------------------------------------
WorksheetStyle s66 = styles.Add("s66");
s66.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s66.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s67
// -----------------------------------------------
WorksheetStyle s67 = styles.Add("s67");
s67.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s67.Alignment.Vertical = StyleVerticalAlignment.Bottom;
// -----------------------------------------------
// s68
// -----------------------------------------------
WorksheetStyle s68 = styles.Add("s68");
s68.Font.FontName = "Times New Roman";
s68.Font.Size = 12;
s68.Font.Color = "#333333";
s68.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s68.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s68.Alignment.ReadingOrder = StyleReadingOrder.LeftToRight;
// -----------------------------------------------
// s69
// -----------------------------------------------
WorksheetStyle s69 = styles.Add("s69");
s69.Font.FontName = "Times New Roman";
s69.Font.Size = 12;
s69.Font.Color = "#333333";
s69.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s69.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s69.Alignment.ReadingOrder = StyleReadingOrder.LeftToRight;
s69.NumberFormat = "Currency";
// -----------------------------------------------
// s70
// -----------------------------------------------
WorksheetStyle s70 = styles.Add("s70");
s70.Font.FontName = "Times New Roman";
s70.Font.Size = 11;
s70.Font.Color = "#000000";
s70.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s70.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s70.NumberFormat = "Currency";
// -----------------------------------------------
// s71
// -----------------------------------------------
WorksheetStyle s71 = styles.Add("s71");
s71.Font.FontName = "Times New Roman";
s71.Font.Size = 12;
s71.Font.Color = "#333399";
s71.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s71.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s71.Alignment.ReadingOrder = StyleReadingOrder.LeftToRight;
// -----------------------------------------------
// s72
// -----------------------------------------------
WorksheetStyle s72 = styles.Add("s72");
s72.Font.FontName = "Segoe UI";
s72.Font.Size = 12;
s72.Font.Color = "#333333";
s72.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s72.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s72.Alignment.ReadingOrder = StyleReadingOrder.LeftToRight;
// -----------------------------------------------
// s73
// -----------------------------------------------
WorksheetStyle s73 = styles.Add("s73");
s73.Font.FontName = "Arial";
s73.Font.Size = 12;
s73.Font.Color = "#333333";
s73.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s73.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s73.Alignment.ReadingOrder = StyleReadingOrder.LeftToRight;
s73.NumberFormat = "Currency";
// -----------------------------------------------
// s74
// -----------------------------------------------
WorksheetStyle s74 = styles.Add("s74");
s74.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s74.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s74.NumberFormat = "Currency";
// -----------------------------------------------
// s75
// -----------------------------------------------
WorksheetStyle s75 = styles.Add("s75");
s75.Font.FontName = "Arial";
s75.Font.Size = 12;
s75.Font.Color = "#000000";
s75.Alignment.Horizontal = StyleHorizontalAlignment.Left;
s75.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s75.Alignment.ReadingOrder = StyleReadingOrder.LeftToRight;
// -----------------------------------------------
// s76
// -----------------------------------------------
WorksheetStyle s76 = styles.Add("s76");
s76.Font.Bold = true;
s76.Font.Italic = true;
s76.Font.FontName = "Times New Roman";
s76.Font.Size = 12;
s76.Font.Color = "#333399";
s76.Interior.Color = "#FFFF99";
s76.Interior.Pattern = StyleInteriorPattern.Solid;
s76.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s76.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s76.Alignment.ReadingOrder = StyleReadingOrder.RightToLeft;
// -----------------------------------------------
// s77
// -----------------------------------------------
WorksheetStyle s77 = styles.Add("s77");
s77.Font.Bold = true;
s77.Font.Italic = true;
s77.Font.FontName = "Times New Roman";
s77.Font.Size = 12;
s77.Font.Color = "#333399";
s77.Interior.Color = "#FFFF99";
s77.Interior.Pattern = StyleInteriorPattern.Solid;
s77.Alignment.Horizontal = StyleHorizontalAlignment.Right;
s77.Alignment.Vertical = StyleVerticalAlignment.Bottom;
s77.Alignment.ReadingOrder = StyleReadingOrder.RightToLeft;
s77.NumberFormat = "\"¤\"\\ #,##0.00";
}
/// <summary>
/// 创建Excel 2010-06-23
/// </summary>
/// <param name="sheetName">表格名称</param>
/// <param name="sheetFields">表头说明</param>
/// <param name="titleWidth">定义每列宽度</param>
/// <param name="dataFiles">字段名称--要与表头文字对应</param>
/// <param name="fomateFiles">字段格式化方式</param>
/// <param name="tableDatas">数据源</param>
/// <param name="strSavePath">保存路径(不需要Server.MapPath)</param>
public static void CreateExcel(string sheetName, string[] sheetFields, int[] titleWidth, string[] dataFiles, string[] fomateFiles, DataTable tableDatas, string strSavePath)
{
Workbook newBook = new Workbook();
Worksheet newSheet = newBook.Worksheets.Add(sheetName);
//设置表格样式
WorksheetStyle titleStyle = newBook.Styles.Add("titleStyle");
titleStyle.Font.Bold = true;
WorksheetStyle dataStyle1 = newBook.Styles.Add("dataStyle1");
dataStyle1.Font.Color = "Red";
WorksheetStyle dataStyle2 = newBook.Styles.Add("dataStyle2");
dataStyle2.Font.Color = "Green";
for (int i = 0; i < sheetFields.Length; i++)
{
newSheet.Table.Columns.Add(titleWidth[i]);
}
//设置表头
WorksheetRow titleRow = newSheet.Table.Rows.Add();
foreach (string s in sheetFields)
{
titleRow.Cells.Add(s, DataType.String, "titleStyle");
}
//设置数据列
WorksheetRow dataRow = null;
if (tableDatas.Rows.Count > 0)
{
foreach (DataRow row in tableDatas.Rows)
{
dataRow = newSheet.Table.Rows.Add();
for (int i = 0; i < dataFiles.Length; i++)
{
if (!String.IsNullOrEmpty(fomateFiles[i]))
{
if (fomateFiles[i].ToUpper().IndexOf("BIT") != -1)
{
dataRow.Cells.Add(Convert.ToBoolean(row[dataFiles[i]]) ? fomateFiles[i].Split(':')[1] : fomateFiles[i].Split(':')[2]);
if (row[dataFiles[i]].ToString() == "待处理")
{
dataRow.Cells[i].StyleID = "dataStyle1";
}
if (row[dataFiles[i]].ToString() == "已处理")
{
dataRow.Cells[i].StyleID = "dataStyle2";
}
}
else
{
dataRow.Cells.Add(String.Format(fomateFiles[i], row[dataFiles[i]].ToString()));
if (row[dataFiles[i]].ToString() == "待处理")
{
dataRow.Cells[i].StyleID = "dataStyle1";
}
if (row[dataFiles[i]].ToString() == "已处理")
{
dataRow.Cells[i].StyleID = "dataStyle2";
}
}
}
else
{
dataRow.Cells.Add(row[dataFiles[i]].ToString());
if (row[dataFiles[i]].ToString() == "待处理")
{
dataRow.Cells[i].StyleID = "dataStyle1";
}
if (row[dataFiles[i]].ToString() == "已处理")
{
dataRow.Cells[i].StyleID = "dataStyle2";
}
}
}
}
}
//数据添加完毕，保存Excel
newBook.Save(System.Web.HttpContext.Current.Server.MapPath(strSavePath));
}
