static void Main() { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); var workbook = new ExcelFile(); var worksheet = workbook.Worksheets.Add("Hyperlinks"); worksheet.Cells["A1"].Value = "Hyperlink examples:"; var cell = worksheet.Cells["B3"]; cell.Value = "GemBoxSoftware"; cell.Style.Font.UnderlineStyle = UnderlineStyle.Single; cell.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue); cell.Hyperlink.Location = "https://www.gemboxsoftware.com"; cell.Hyperlink.IsExternal = true; cell = worksheet.Cells["B5"]; cell.Value = "Jump"; cell.Style.Font.UnderlineStyle = UnderlineStyle.Single; cell.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue); cell.Hyperlink.ToolTip = "This is tool tip! This hyperlink jumps to E1."; cell.Hyperlink.Location = worksheet.Name + "!E1"; worksheet.Cells["E1"].Value = "Destination"; cell = worksheet.Cells["B8"]; cell.Formula = "=HYPERLINK(\"https://www.gemboxsoftware.com/spreadsheet/examples/excel-cell-hyperlinks/207\", \"Example of HYPERLINK formula\")"; cell.Style.Font.UnderlineStyle = UnderlineStyle.Single; cell.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue); workbook.Save("Hyperlinks.xlsx"); }
public static void StandartInlineCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); x.Style.Font.Weight = ExcelFont.NormalWeight; x.Style.VerticalAlignment = VerticalAlignmentStyle.Top; x.Style.Font.Name = "Calibri"; x.Style.Font.Size = 212; x.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black); }
public static void SCHeaderCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(81, 118, 232), SpreadsheetColor.FromArgb(81, 118, 232)); x.Style.VerticalAlignment = VerticalAlignmentStyle.Top; x.Style.Font.Weight = ExcelFont.MaxWeight; x.Style.Font.Name = "Arial"; x.Style.Font.Size = 201; x.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black); }
public static void StandartHeaderGroupCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(218, 227, 250), SpreadsheetColor.FromName(ColorName.LightBlue)); x.Style.VerticalAlignment = VerticalAlignmentStyle.Top; x.Style.Font.Weight = ExcelFont.MaxWeight; x.Style.Font.Name = "Calibri"; x.Style.Font.Size = 220; x.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black); }
public static CellStyle SetBorders(this CellStyle style, MultipleBorders borders, LineStyle lineStyle, SpreadsheetColor borderColor = default(SpreadsheetColor)) { if (borderColor.IsEmpty) { borderColor = SpreadsheetColor.FromName(ColorName.Black); } style.Borders.SetBorders(borders, borderColor, lineStyle); return(style); }
protected void Button2_Click(object sender, EventArgs e) { string connString = ConfigurationManager.ConnectionStrings["employeesDatabase"].ConnectionString; SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile xlsx = new ExcelFile(); ExcelWorksheet mySheet = xlsx.Worksheets.Add("sheet1"); mySheet.Cells[1, 1].Value = "表單編號"; mySheet.Cells[1, 1].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[1, 2].Value = "員工編號"; mySheet.Cells[1, 2].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[1, 3].Value = "表單類型"; mySheet.Cells[1, 3].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[1, 4].Value = "開始時間"; mySheet.Cells[1, 4].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[1, 5].Value = "結束時間"; mySheet.Cells[1, 5].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[1, 6].Value = "申請時間"; mySheet.Cells[1, 6].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[1, 7].Value = "審核結果"; mySheet.Cells[1, 7].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); using (SqlConnection cn = new SqlConnection(connString)) { DataTable ds = new DataTable(); string str = "Select * from personnelfurloughwait Where statusfromowaitok in (1,2)"; SqlDataAdapter da = new SqlDataAdapter(str, cn); da.Fill(ds); for (int i = 0; i < ds.Rows.Count; i++) { mySheet.Cells[i + 2, 1].Value = ds.Rows[i]["Id"].ToString(); mySheet.Cells[i + 2, 2].Value = ds.Rows[i]["idpersonnel"].ToString(); mySheet.Cells[i + 2, 3].Value = ds.Rows[i]["type"].ToString(); mySheet.Cells[i + 2, 4].Value = ds.Rows[i]["timestart"].ToString(); mySheet.Cells[i + 2, 5].Value = ds.Rows[i]["timeend"].ToString(); mySheet.Cells[i + 2, 6].Value = ds.Rows[i]["daytime"].ToString(); mySheet.Cells[i + 2, 7].Value = ds.Rows[i]["statusfromowaitresatnooryes"].ToString(); } } xlsx.Save(Server.MapPath(@"Output\Form.xlsx")); Response.AddHeader("Content-Type", "application/octet-stream"); Response.AddHeader("Content-Transfer-Encoding", "Binary"); Response.AddHeader("Content-disposition", "attachment; filename=\"Form.xlsx\""); Response.WriteFile( HttpRuntime.AppDomainAppPath + @"05_Tid\Output\Form.xlsx"); Response.End(); }
static void zabatHeader(ref ExcelWorksheet sheet, ref Data data, string[] yearMonths) { sheet.Cells.GetSubrangeAbsolute(4, 0, 5, 3).Merged = true; sheet.Cells.GetSubrangeAbsolute(6, 0, 6, 3).Merged = true; sheet.Cells.GetSubrangeAbsolute(4, 0, 6, 3).Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromName(ColorName.Purple), SpreadsheetColor.FromName(ColorName.Purple)); for (int i = 4; i < 7; i += 2) { sheet.Cells[i, 0].Value = i == 4 ? "Monthly Report" : yearMonths[data.bMonth - 1] + " - " + yearMonths[data.eMonth - 1] + " /" + System.DateTime.Now.Year; sheet.Cells[i, 0].Style.Font.Size = i == 4 ? 20 * 20 : 10 * 20; sheet.Cells[i, 0].Style.Font.Color = i == 4 ? SpreadsheetColor.FromName(ColorName.Yellow) : SpreadsheetColor.FromName(ColorName.White); sheet.Cells[i, 0].Style.Font.Weight = ExcelFont.BoldWeight; sheet.Cells[i, 0].Style.VerticalAlignment = VerticalAlignmentStyle.Center; sheet.Cells[i, 0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; } }
protected void ExcelBtn_Click(object sender, EventArgs e) { DataTable dt = DBHelper.GetDataTable("select * from CRM_Customer", null); SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile xlsx = new ExcelFile(); ExcelWorksheet mySheet = xlsx.Worksheets.Add("Customers"); mySheet.Cells[1, 2].Value = "統編"; mySheet.Cells[1, 2].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 3].Value = "公司名稱"; mySheet.Cells[1, 3].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 4].Value = "公司地址"; mySheet.Cells[1, 4].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 5].Value = "公司電話"; mySheet.Cells[1, 5].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 6].Value = "公司官網"; mySheet.Cells[1, 6].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 7].Value = "負責窗口"; mySheet.Cells[1, 7].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 8].Value = "負責窗口電話"; mySheet.Cells[1, 8].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 9].Value = "負責窗口E-mail"; mySheet.Cells[1, 9].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 10].Value = "公司規模"; mySheet.Cells[1, 10].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 11].Value = "產業類別"; mySheet.Cells[1, 11].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.InsertDataTable(dt, new InsertDataTableOptions() { StartColumn = 2, StartRow = 2, }); xlsx.Save(Server.MapPath(@"~\Output\CRM_CustomersList.xlsx")); MsgLab.Text = "Excel檔案匯出成功"; Response.AddHeader("Content-Type", "application/octet-stream"); Response.AddHeader("Content-Transfer-Encoding", "Binary"); Response.AddHeader("Content-disposition", "attachment; filename=\"CRM_CustomersList.xlsx\""); Response.WriteFile( HttpRuntime.AppDomainAppPath + @"Output\CRM_CustomersList.xlsx"); Response.End(); }
public static void StandartInlineCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { var borders = x.Style.Borders; x.Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(105, 105, 105), LineStyle.Thin); //borders[XlBordersIndex.xlInsideHorizontal].Color = XlRgbColor.rgbDarkSlateGrey; //borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin; //borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous; x.Style.VerticalAlignment = VerticalAlignmentStyle.Top; x.Style.Font.Weight = ExcelFont.NormalWeight; x.Style.Font.Name = "Calibri"; x.Style.Font.Size = 201; x.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black); //x.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); //IF 1 then Value must be between ExcelFont.MinWeight and ExcelFont.MaxWeight. }
public ReportingHelper() { #region Styles initialization titleStyles = new CellStyle(); titleStyles.Font.Weight = ExcelFont.BoldWeight; titleStyles.Font.Size = 18 * 20; titleStyles.HorizontalAlignment = HorizontalAlignmentStyle.CenterAcross; titleStyles.Borders.SetBorders ( MultipleBorders.Top | MultipleBorders.Right | MultipleBorders.Left | MultipleBorders.Bottom | MultipleBorders.Right, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin ); headerStyle = new CellStyle(); headerStyle.Font.Weight = ExcelFont.BoldWeight; // Weight. headerStyle.Font.Size = 12 * 20; // Font size. headerStyle.Borders.SetBorders // Border. ( MultipleBorders.Top | MultipleBorders.Right | MultipleBorders.Left | MultipleBorders.Bottom | MultipleBorders.Right, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin ); // Normal cell style. normalStyle = new CellStyle(); normalStyle.Borders.SetBorders // Border. ( MultipleBorders.Top | MultipleBorders.Right | MultipleBorders.Left | MultipleBorders.Bottom | MultipleBorders.Right, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin ); // Total style. totalStyle = new CellStyle(); totalStyle.Font.Weight = ExcelFont.BoldWeight; totalStyle.Font.Size = 12 * 20; #endregion #region Headers headers = new string[] { "ID", "Nombre", "Artista", "Genero", "Álbum", "Duración (minutos)", "Calidad", "Formato" }; #endregion }
static void Main() { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); var workbook = new ExcelFile(); var worksheet = workbook.Worksheets.Add("Inline Text Formatting"); worksheet.Cells[0, 0].Value = "Inline text formatting examples:"; worksheet.PrintOptions.PrintGridlines = true; // Column width of 20 characters. worksheet.Columns[0].Width = 20 * 256; worksheet.Cells[2, 0].Value = "This is big and red text!"; // Apply size to 'big and red' part of text worksheet.Cells[2, 0].GetCharacters(8, 11).Font.Size = 400; // Apply color to 'red' part of text worksheet.Cells[2, 0].GetCharacters(16, 3).Font.Color = SpreadsheetColor.FromName(ColorName.Red); // Format cell content worksheet.Cells[4, 0].Value = "Formatting selected characters with GemBox.Spreadsheet component."; worksheet.Cells[4, 0].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue); worksheet.Cells[4, 0].Style.Font.Italic = true; worksheet.Cells[4, 0].Style.WrapText = true; // Get characters from index 36 to the end of string var characters = worksheet.Cells[4, 0].GetCharacters(36); // Apply color and underline to selected characters characters.Font.Color = SpreadsheetColor.FromName(ColorName.Orange); characters.Font.UnderlineStyle = UnderlineStyle.Single; // Write selected characters worksheet.Cells[6, 0].Value = "Selected characters: " + characters.Text; workbook.Save("Inline Text Formatting.xlsx"); }
protected void ExcelBtn_Click(object sender, EventArgs e) { DataTable dt = DBHelper.GetDataTable("select a.CompanyCategory ,count(a.CompanyCategory) as 'Count' from CRM_Customer as a left join CRM_CompanyCategorys as b on a.CompanyCategory = b.CompanyCategory group by a.CompanyCategory, b.Id", null); var CompanyCategoryAry = dt.AsEnumerable().Select(r => r["CompanyCategory"].ToString()).ToArray(); SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile xlsx = new ExcelFile(); ExcelWorksheet mySheet = xlsx.Worksheets.Add("CompanyCategoryChart"); mySheet.Cells[1, 2].Value = "產業類別"; mySheet.Cells[1, 2].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.Cells[1, 3].Value = "統計筆數"; mySheet.Cells[1, 3].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.LightBlue)); mySheet.InsertDataTable(dt, new InsertDataTableOptions() { StartColumn = 2, StartRow = 2, }); int Length = CompanyCategoryAry.Length; var chart = mySheet.Charts.Add(ChartType.Pie, "G2", "Q20"); chart.SelectData(mySheet.Cells.GetSubrangeAbsolute(1, 2, Length + 1, 3), true); xlsx.Save(Server.MapPath(@"~\Output\CRM_CompanyCategoryStatisticsList.xlsx")); MsgLab.Text = "Excel檔案匯出成功"; Response.AddHeader("Content-Type", "application/octet-stream"); Response.AddHeader("Content-Transfer-Encoding", "Binary"); Response.AddHeader("Content-disposition", "attachment; filename=\"CRM_CompanyCategoryStatisticsList.xlsx\""); Response.WriteFile( HttpRuntime.AppDomainAppPath + @"Output\CRM_CompanyCategoryStatisticsList.xlsx"); Response.End(); }
static void Example1() { var workbook = new ExcelFile(); var worksheet = workbook.Worksheets.Add("Sheet1"); // Get the cell range. var range = worksheet.Cells.GetSubrange("B2:E5"); // Merge cells in the current range. range.Merged = true; // Set the value of the merged range. range.Value = "Merged"; // Set the style of the merged range. range.Style.VerticalAlignment = VerticalAlignmentStyle.Center; // Set the style of the merged range using a cell within. worksheet.Cells["C3"].Style.Borders .SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Red), LineStyle.Double); workbook.Save("Merged Cells.xlsx"); }
static void Main(string[] args) { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile ef = new ExcelFile(); ExcelWorksheet ws = ef.Worksheets.Add("Comments"); ws.Cells[0, 0].Value = "Comment examples:"; ws.Cells[2, 1].Comment.Text = "Empty cell."; ws.Cells[4, 1].Value = 5; ws.Cells[4, 1].Comment.Text = "Cell with a number."; ws.Cells["B7"].Value = "Cell B7"; ExcelComment comment = ws.Cells["B7"].Comment; comment.Text = "Some formatted text.\nComment is:\na) multiline,\nb) large,\nc) visible, and \nd) formatted."; comment.IsVisible = true; comment.TopLeftCell = new AnchorCell(ws.Columns[3], ws.Rows[4], true); comment.BottomRightCell = new AnchorCell(ws.Columns[5], ws.Rows[10], false); // Get first 20 characters of a string FormattedCharacterRange characters = comment.GetCharacters(0, 20); // Apply color, italic and size to selected characters characters.Font.Color = SpreadsheetColor.FromName(ColorName.Orange); characters.Font.Italic = true; characters.Font.Size = 300; // Apply color to 'formatted' part of text comment.GetCharacters(5, 9).Font.Color = SpreadsheetColor.FromName(ColorName.Red); ef.Save("Comments.xlsx"); }
public void setTemplateFormat(int whatTemplate, ref ExcelFile workbook) { if (whatTemplate == 1) { // Row 1 Text/Cell Size and boldness workbook.Worksheets[0].Rows[0].Height = (workbook.Worksheets[0].Rows[0].Height) + 2000; workbook.Worksheets[0].Columns[0].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Rows[0].Style.Font.Size = 560; workbook.Worksheets[0].Rows[0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Columns[3].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; // Alignments workbook.Worksheets[0].Columns[0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left; workbook.Worksheets[0].Cells[0, 0].Style.VerticalAlignment = VerticalAlignmentStyle.Center; workbook.Worksheets[0].Columns[6].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right; workbook.Worksheets[0].Cells.GetSubrange("A20:G29").Style.VerticalAlignment = VerticalAlignmentStyle.Center; workbook.Worksheets[0].Cells.GetSubrange("A20:F28").Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; workbook.Worksheets[0].Cells["F29"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right; // Invoice details text left side workbook.Worksheets[0].Cells[2, 0].Value = "ABN:"; workbook.Worksheets[0].Cells[2, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[4, 0].Value = "Email:"; workbook.Worksheets[0].Cells[4, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[6, 0].Value = "Contact No:"; workbook.Worksheets[0].Cells[6, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[8, 0].Value = "Address:"; workbook.Worksheets[0].Cells[8, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[13, 0].Value = "Bank BSB:"; workbook.Worksheets[0].Cells[13, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[14, 0].Value = "Account No:"; workbook.Worksheets[0].Cells[14, 0].Style.Font.Weight = ExcelFont.BoldWeight; // Invoice details text right side workbook.Worksheets[0].Cells[0, 6].Value = "Invoice"; workbook.Worksheets[0].Cells[0, 6].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[2, 6].Value = "Date of Invoice:"; workbook.Worksheets[0].Cells[2, 6].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[4, 6].Value = "Invoice No:"; workbook.Worksheets[0].Cells[4, 6].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[7, 6].Value = "Invoice To:"; workbook.Worksheets[0].Cells[7, 6].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[10, 6].Value = "Address:"; workbook.Worksheets[0].Cells[10, 6].Style.Font.Weight = ExcelFont.BoldWeight; // Table Colum descriptions workbook.Worksheets[0].Cells.GetSubrange("A20:G20").Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells.GetSubrange("A20:C20").Merged = true; workbook.Worksheets[0].Cells.GetSubrange("G21:G29").Style.NumberFormat = "$#,##0.00"; workbook.Worksheets[0].Cells["A20"].Value = "Description"; workbook.Worksheets[0].Cells["D20"].Value = "Date of Work"; workbook.Worksheets[0].Cells["E20"].Value = "Price Per Unit"; workbook.Worksheets[0].Cells["F20"].Value = "Quanitity"; workbook.Worksheets[0].Cells["G20"].Value = "Price"; workbook.Worksheets[0].Cells["F29"].Value = "Total - "; // Job Item table formatting workbook.Worksheets[0].Rows[19].Height = 600; workbook.Worksheets[0].Rows[20].Height = 500; workbook.Worksheets[0].Rows[21].Height = 500; workbook.Worksheets[0].Rows[22].Height = 500; workbook.Worksheets[0].Rows[23].Height = 500; workbook.Worksheets[0].Rows[24].Height = 500; workbook.Worksheets[0].Rows[25].Height = 500; workbook.Worksheets[0].Rows[26].Height = 500; workbook.Worksheets[0].Rows[27].Height = 500; workbook.Worksheets[0].Rows[28].Height = 500; workbook.Worksheets[0].Rows[29].Height = 500; workbook.Worksheets[0].Columns[1].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[2].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[3].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[4].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[5].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[6].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; // Table fill and borders workbook.Worksheets[0].Cells.GetSubrange("A20:G20").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A22:G22").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A24:G24").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A26:G26").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A28:G28").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A20:G28").Style.Borders.SetBorders(MultipleBorders.InsideHorizontal, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); workbook.Worksheets[0].Cells.GetSubrange("A20:G28").Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); workbook.Worksheets[0].Cells.GetSubrange("D20:D28").Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); workbook.Worksheets[0].Cells.GetSubrange("F20:G28").Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); workbook.Worksheets[0].Cells.GetSubrange("G20:G29").Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); // Paid Cell workbook.Worksheets[0].Cells["B32"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Red); workbook.Worksheets[0].Cells["B32"].Style.Font.Size = 1200; } }
private void Fejlec(int sor, int oszlop, string f) { _sheet.Cells[sor, oszlop].Value = f; _sheet.Cells[sor, oszlop].Style.Borders.SetBorders(MultipleBorders.Bottom, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Double); }
private void Vegosszeg(int sor, int oszlop, decimal d) { _sheet.Cells[sor, oszlop].Value = Calc.RealRound(d, (decimal)0.01); _sheet.Cells[sor, oszlop].Style.Borders.SetBorders(MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Double); }
// Export Excel public static void ExportToExcel(Message[] messages) { SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); var workbook = new ExcelFile(); var worksheet = workbook.Worksheets.Add("Exported from messages"); // Create headers var dataTable = new DataTable(); dataTable.Columns.Add("Id", typeof(int)); dataTable.Columns.Add("Date Added", typeof(DateTime)); dataTable.Columns.Add("Type", typeof(string)); dataTable.Columns.Add("Message", typeof(string)); dataTable.Columns.Add("Reply", typeof(string)); // Create rows foreach (var message in messages) { // If message is a question if (message.Subject == MessageSubject.Question) { dataTable.Rows.Add(message.Id, message.DateCreated, "Question", message.MessageText, message.Reply); } // If message is a complaint else { dataTable.Rows.Add(message.Id, message.DateCreated, "Complaint", message.MessageText, message.Reply); } } // Insert DataTable to an Excel worksheet. worksheet.InsertDataTable(dataTable, new InsertDataTableOptions() { ColumnHeaders = true, StartRow = 0 }); /* Style */ // Auto fit columns, add color and background color int columnCount = worksheet.CalculateMaxUsedColumns(); for (int i = 0; i < columnCount; i++) { worksheet.Columns[i].AutoFit(1, worksheet.Rows[0], worksheet.Rows[worksheet.Rows.Count - 1]); worksheet.Rows[0].Cells[i].Style.FillPattern.SetGradient(GradientShadingStyle.HorizontalHigh, SpreadsheetColor.FromName(ColorName.Accent5Darker25Pct), SpreadsheetColor.FromName(ColorName.Accent5Darker50Pct)); worksheet.Rows[0].Cells[i].Style.Font.Color = SpreadsheetColor.FromName(ColorName.White); } // Apply borders to specific cells worksheet.ConditionalFormatting.AddContainText("C2:C" + (messages.Length + 1), ContainTextOperator.Contains, "Complaint"). Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Red), LineStyle.Double); worksheet.ConditionalFormatting.AddContainText("C2:C" + (messages.Length + 1), ContainTextOperator.Contains, "Question"). Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Green), LineStyle.Thick); // Font weight worksheet.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight; // Wrap text worksheet.Cells.Style.WrapText = true; // Header horizontal alignment worksheet.Rows[0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; workbook.Save("messages.xlsx"); }
public static void ReportDataOfElectricityMeterConsumption_tariffz(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { if (dataRow != null) { int mean_index = 10; if (dataRow.Length > mean_index && (string)dataRow[mean_index] == "Сумма") { x.Offset(mean_index).Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromName(ColorName.Orange), SpreadsheetColor.FromName(ColorName.Orange)); } } }
public static void Export(string filePath, double[] data) { SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile excel = new ExcelFile(); //Excel默认字体 excel.DefaultFontName = TIMESNEWROMAN; //Excel文档属性设置 excel.DocumentProperties.BuiltIn.Add(new KeyValuePair <BuiltInDocumentProperties, string>(BuiltInDocumentProperties.Title, TITLE)); excel.DocumentProperties.BuiltIn.Add(new KeyValuePair <BuiltInDocumentProperties, string>(BuiltInDocumentProperties.Author, "CNXY")); excel.DocumentProperties.BuiltIn.Add(new KeyValuePair <BuiltInDocumentProperties, string>(BuiltInDocumentProperties.Company, "CNXY")); excel.DocumentProperties.BuiltIn.Add(new KeyValuePair <BuiltInDocumentProperties, string>(BuiltInDocumentProperties.Comments, "By CNXY.Website: http://www.cnc6.cn")); //新建一个Sheet表格 ExcelWorksheet sheet = excel.Worksheets.Add(TITLE); //设置表格保护 sheet.ProtectionSettings.SetPassword("cnxy"); sheet.Protected = true; //设置网格线不可见 sheet.ViewOptions.ShowGridLines = false; //定义一个B2-G3的单元格范围 CellRange range = sheet.Cells.GetSubrange("B2", "J3"); range.Value = "Chart"; range.Merged = true; //定义一个单元格样式 CellStyle style = new CellStyle(); //设置边框 style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Red), LineStyle.Thin); //设置水平对齐模式 style.HorizontalAlignment = HorizontalAlignmentStyle.Center; //设置垂直对齐模式 style.VerticalAlignment = VerticalAlignmentStyle.Center; //设置字体 style.Font.Size = 22 * PT; style.Font.Weight = ExcelFont.BoldWeight; style.Font.Color = SpreadsheetColor.FromName(ColorName.Red); range.Style = style; //增加Chart LineChart chart = (LineChart)sheet.Charts.Add(ChartType.Line, "B4", "J22"); chart.Title.IsVisible = false; chart.Axes.Horizontal.Title.Text = "Time"; chart.Axes.Vertical.Title.Text = "Voltage"; ValueAxis axisY = chart.Axes.VerticalValue; //Y轴最大刻度与最小刻度 axisY.Minimum = -100; axisY.Maximum = 100; //Y轴主要与次要单位大小 axisY.MajorUnit = 20; axisY.MinorUnit = 10; //Y轴主要与次要网格是否可见 axisY.MajorGridlines.IsVisible = true; axisY.MinorGridlines.IsVisible = true; //Y轴刻度线类型 axisY.MajorTickMarkType = TickMarkType.Cross; axisY.MinorTickMarkType = TickMarkType.Inside; Random random = new Random(); data = new double[LENGTH]; for (int i = 0; i < LENGTH; i++) { if (random.Next(0, 100) > 50) { data[i] = random.NextDouble() * 100; } else { data[i] = -random.NextDouble() * 100; } } chart.Series.Add("Random", data); //尾部信息 range = sheet.Cells.GetSubrange("B23", "J24"); range.Value = $"Write Time:{DateTime.Now:yyyy-MM-dd HH:mm:ss} By CNXY"; range.Merged = true; //B25(三种单元格模式) sheet.Cells["B25"].Value = "http://www.cnc6.cn"; sheet.Cells[24, 1].Style.FillPattern.PatternStyle = FillPatternStyle.Solid; sheet.Rows[24].Cells[1].Style.FillPattern.PatternForegroundColor = SpreadsheetColor.FromName(ColorName.Red); //B25,J25 sheet.Cells.GetSubrangeAbsolute(24, 1, 24, 9).Merged = true; try { excel.Save(filePath); Process.Start(filePath); MessageBox.Show("Write successfully"); } catch (Exception ex) { MessageBox.Show(ex.Message); } MessageBox.Show("Press any key to continue."); }
static void Main() { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); var workbook = new ExcelFile(); var worksheet = workbook.Worksheets.Add("Conditional Formatting"); int rowCount = 20; // Specify sheet formatting. worksheet.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight; worksheet.Columns[0].SetWidth(3, LengthUnit.Centimeter); worksheet.Columns[1].SetWidth(3, LengthUnit.Centimeter); worksheet.Columns[2].SetWidth(3, LengthUnit.Centimeter); worksheet.Columns[3].SetWidth(3, LengthUnit.Centimeter); worksheet.Columns[3].Style.NumberFormat = "[$$-409]#,##0.00"; worksheet.Columns[4].SetWidth(3, LengthUnit.Centimeter); worksheet.Columns[4].Style.NumberFormat = "yyyy-mm-dd"; var cells = worksheet.Cells; // Specify header row. cells[0, 0].Value = "Departments"; cells[0, 1].Value = "Names"; cells[0, 2].Value = "Years of Service"; cells[0, 3].Value = "Salaries"; cells[0, 4].Value = "Deadlines"; // Insert random data to sheet. var random = new Random(); var departments = new string[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" }; var names = new string[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" }; for (int i = 0; i < rowCount; ++i) { cells[i + 1, 0].Value = departments[random.Next(departments.Length)]; cells[i + 1, 1].Value = names[random.Next(names.Length)] + ' ' + (i + 1).ToString(); cells[i + 1, 2].SetValue(random.Next(1, 31)); cells[i + 1, 3].SetValue(random.Next(10, 101) * 100); cells[i + 1, 4].SetValue(DateTime.Now.AddDays(random.Next(-1, 2))); } // Apply shading to alternate rows in a worksheet using 'Formula' based conditional formatting. worksheet.ConditionalFormatting.AddFormula(worksheet.Cells.Name, "MOD(ROW(),2)=0"). Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Accent1Lighter40Pct); worksheet.ConditionalFormatting.AddFormula(worksheet.Cells.Name, "MOD(ROW(),2)=1"). Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Accent5Lighter80Pct); // Apply '2-Color Scale' conditional formatting to 'Years of Service' column. worksheet.ConditionalFormatting.Add2ColorScale("C2:C" + (rowCount + 1)); // Apply '3-Color Scale' conditional formatting to 'Salaries' column. worksheet.ConditionalFormatting.Add3ColorScale("D2:D" + (rowCount + 1)); // Apply 'Data Bar' conditional formatting to 'Salaries' column. worksheet.ConditionalFormatting.AddDataBar("D2:D" + (rowCount + 1)); // Apply 'Icon Set' conditional formatting to 'Years of Service' column. worksheet.ConditionalFormatting.AddIconSet("C2:C" + (rowCount + 1)).IconStyle = SpreadsheetIconStyle.FourTrafficLights; // Apply green font color to cells in a 'Years of Service' column which have values between 15 and 20. worksheet.ConditionalFormatting.AddContainValue("C2:C" + (rowCount + 1), ContainValueOperator.Between, 15, 20). Style.Font.Color = SpreadsheetColor.FromName(ColorName.Green); // Apply double red border to cells in a 'Names' column which contain text 'Doe'. worksheet.ConditionalFormatting.AddContainText("B2:B" + (rowCount + 1), ContainTextOperator.Contains, "Doe"). Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Red), LineStyle.Double); // Apply red shading to cells in a 'Deadlines' column which are equal to yesterday's date. worksheet.ConditionalFormatting.AddContainDate("E2:E" + (rowCount + 1), ContainDateOperator.Yesterday). Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Red); // Apply bold font weight to cells in a 'Salaries' column which have top 10 values. worksheet.ConditionalFormatting.AddTopOrBottomRanked("D2:D" + (rowCount + 1), false, 10). Style.Font.Weight = ExcelFont.BoldWeight; // Apply double underline to cells in a 'Years of Service' column which have below average value. worksheet.ConditionalFormatting.AddAboveOrBelowAverage("C2:C" + (rowCount + 1), true). Style.Font.UnderlineStyle = UnderlineStyle.Double; // Apply italic font style to cells in a 'Departments' column which have duplicate values. worksheet.ConditionalFormatting.AddUniqueOrDuplicate("A2:A" + (rowCount + 1), true). Style.Font.Italic = true; workbook.Save("Conditional Formatting.xlsx"); }
protected void Button1_Click(object sender, EventArgs e) { SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile xlsx = new ExcelFile(); ExcelWorksheet mySheet = xlsx.Worksheets.Add("sheet1"); mySheet.Cells[0, 0].Value = "員工編號"; mySheet.Cells[0, 0].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 1].Value = "姓名"; mySheet.Cells[0, 1].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 2].Value = "部門"; mySheet.Cells[0, 2].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 3].Value = "職稱"; mySheet.Cells[0, 3].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 4].Value = "角色"; mySheet.Cells[0, 4].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 5].Value = "分機"; mySheet.Cells[0, 5].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 6].Value = "到職日期"; mySheet.Cells[0, 6].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 7].Value = "生日"; mySheet.Cells[0, 7].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 8].Value = "手機號碼"; mySheet.Cells[0, 8].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 9].Value = "現居地址"; mySheet.Cells[0, 9].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); mySheet.Cells[0, 10].Value = "電子信箱"; mySheet.Cells[0, 10].Style.FillPattern.SetSolid(SpreadsheetColor.FromName(ColorName.Orange)); BusinessLogic logic = new BusinessLogic(); List <Employee> empList = logic.GetEmployeeList(); List <Role> roleList = logic.GetRoleData(); List <Department> departmentList = logic.GetDepartmentData(); for (int i = 1; i < empList.Count(); i++) { mySheet.Cells[i, 0].Value = empList[i].EmployeeID; mySheet.Cells[i, 1].Value = empList[i].EmployeeName; mySheet.Cells[i, 2].Value = departmentList.Single(d => d.DepartmentID == empList[i].DepatmentID).DepartmentName; mySheet.Cells[i, 3].Value = empList[i].JobTitle; mySheet.Cells[i, 4].Value = roleList.Single(r => r.RoleId == empList[i].RoleID).RoleName; mySheet.Cells[i, 5].Value = empList[i].EXT; mySheet.Cells[i, 6].Value = empList[i].DutyDate; mySheet.Cells[i, 7].Value = empList[i].Birthday; mySheet.Cells[i, 8].Value = empList[i].Cellphone; mySheet.Cells[i, 9].Value = empList[i].Address; mySheet.Cells[i, 10].Value = empList[i].Email; } xlsx.Save(Server.MapPath(@"~\Output\employeeSheet.xlsx")); Response.AddHeader("Content-Type", "application/octet-stream"); Response.AddHeader("Content-Transfer-Encoding", "Binary"); Response.AddHeader("Content-disposition", "attachment; filename=\"employeeSheet.xlsx\""); Response.WriteFile( HttpRuntime.AppDomainAppPath + @"Output\employeeSheet.xlsx"); Response.End(); }
public static void Write() { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); // Create new empty workbook. var workbook = new ExcelFile(); // Add new sheet. var worksheet = workbook.Worksheets.Add("Skyscrapers"); // Write title to Excel cell. worksheet.Cells["A1"].Value = "List of tallest buildings (2021):"; // Tabular sample data for writing into an Excel file. var skyscrapers = new object[, ] { { "Rank", "Building", "City", "Country", "Metric", "Imperial", "Floors", "Built (Year)" }, { 1, "Burj Khalifa", "Dubai", "United Arab Emirates", 828, 2717, 163, 2010 }, { 2, "Shanghai Tower", "Shanghai", "China", 632, 2073, 128, 2015 }, { 3, "Abraj Al-Bait Clock Tower", "Mecca", "Saudi Arabia", 601, 1971, 120, 2012 }, { 4, "Ping An Finance Centre", "Shenzhen", "China", 599, 1965, 115, 2017 }, { 5, "Lotte World Tower", "Seoul", "South Korea", 554.5, 1819, 123, 2016 }, { 6, "One World Trade Center", "New York City", "United States", 541.3, 1776, 104, 2014 }, { 7, "Guangzhou CTF Finance Centre", "Guangzhou", "China", 530, 1739, 111, 2016 }, { 8, "Tianjin CTF Finance Centre", "Tianjin", "China", 530, 1739, 98, 2019 }, { 9, "China Zun", "Beijing", "China", 528, 1732, 108, 2018 }, { 10, "Taipei 101", "Taipei", "Taiwan", 508, 1667, 101, 2004 }, { 11, "Shanghai World Financial Center", "Shanghai", "China", 492, 1614, 101, 2008 }, { 12, "International Commerce Centre", "Hong Kong", "China", 484, 1588, 118, 2010 }, { 13, "Central Park Tower", "New York City", "United States", 472, 1550, 98, 2020 }, { 14, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2019 }, { 15, "Landmark 81", "Ho Chi Minh City", "Vietnam", 461.2, 1513, 81, 2018 }, { 16, "Changsha IFS Tower T1", "Changsha", "China", 452.1, 1483, 88, 2018 }, { 17, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 }, { 18, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 }, { 19, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2010 }, { 20, "Suzhou IFS", "Suzhou", "China", 450, 1476, 98, 2019 } }; // // Set row formatting. worksheet.Rows["1"].Style = workbook.Styles[BuiltInCellStyleName.Heading1]; // // Set columns width. worksheet.Columns["A"].SetWidth(8, LengthUnit.ZeroCharacterWidth); // Rank worksheet.Columns["B"].SetWidth(30, LengthUnit.ZeroCharacterWidth); // Building worksheet.Columns["C"].SetWidth(16, LengthUnit.ZeroCharacterWidth); // City worksheet.Columns["D"].SetWidth(20, LengthUnit.ZeroCharacterWidth); // Country worksheet.Columns["E"].SetWidth(9, LengthUnit.ZeroCharacterWidth); // Metric worksheet.Columns["F"].SetWidth(11, LengthUnit.ZeroCharacterWidth); // Imperial worksheet.Columns["G"].SetWidth(9, LengthUnit.ZeroCharacterWidth); // Floors worksheet.Columns["H"].SetWidth(9, LengthUnit.ZeroCharacterWidth); // Built (Year) worksheet.Columns["I"].SetWidth(4, LengthUnit.ZeroCharacterWidth); // Top 10 worksheet.Columns["J"].SetWidth(5, LengthUnit.ZeroCharacterWidth); // Top 20 // Write header data to Excel cells. for (var col = 0; col < skyscrapers.GetLength(1); col++) { worksheet.Cells[3, col].Value = skyscrapers[0, col]; } worksheet.Cells["E3"].Value = "Height"; worksheet.Cells.GetSubrange("A3:A4").Merged = true; // Rank worksheet.Cells.GetSubrange("B3:B4").Merged = true; // Building worksheet.Cells.GetSubrange("C3:C4").Merged = true; // City worksheet.Cells.GetSubrange("D3:D4").Merged = true; // Country worksheet.Cells.GetSubrange("E3:F3").Merged = true; // Height worksheet.Cells.GetSubrange("G3:G4").Merged = true; // Floors worksheet.Cells.GetSubrange("H3:H4").Merged = true; // Built (Year) // // Set header cells formatting. var style = new CellStyle { HorizontalAlignment = HorizontalAlignmentStyle.Center, VerticalAlignment = VerticalAlignmentStyle.Center }; style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(237, 125, 49)); style.Font.Weight = ExcelFont.BoldWeight; style.Font.Color = SpreadsheetColor.FromName(ColorName.White); style.WrapText = true; style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); worksheet.Cells.GetSubrange("A3:H4").Style = style; // // Write "Top 10" cells. style = new CellStyle { HorizontalAlignment = HorizontalAlignmentStyle.Center, VerticalAlignment = VerticalAlignmentStyle.Center, Font = { Weight = ExcelFont.BoldWeight } }; var mergedRange = worksheet.Cells.GetSubrange("I5:I14"); mergedRange.Merged = true; mergedRange.Value = "T o p 1 0"; style.Rotation = -90; style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(198, 239, 206)); mergedRange.Style = style; // // Write "Top 20" cells. mergedRange = worksheet.Cells.GetSubrange("J5:J24"); mergedRange.Merged = true; mergedRange.Value = "T o p 2 0"; style.IsTextVertical = true; style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(255, 235, 156)); mergedRange.Style = style; mergedRange = worksheet.Cells.GetSubrange("I15:I24"); mergedRange.Merged = true; mergedRange.Style = style; // Write sample data and formatting to Excel cells. for (var row = 0; row < skyscrapers.GetLength(0) - 1; row++) { for (var col = 0; col < skyscrapers.GetLength(1); col++) { var cell = worksheet.Cells[row + 4, col]; cell.Value = skyscrapers[row + 1, col]; cell.Style.Borders[IndividualBorder.Right].LineStyle = LineStyle.Thin; if (row % 2 == 0) { cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(221, 235, 247)); } if (col == 0) { cell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; } if (col > 3) { cell.Style.Font.Name = "Courier New"; } cell.Style.NumberFormat = col switch { 4 => "#\" m\"", 5 => "#\" ft\"", _ => cell.Style.NumberFormat }; } } worksheet.Cells.GetSubrange("A5", "J24").Style.Borders.SetBorders( MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium); worksheet.Cells.GetSubrange("A3", "H4").Style.Borders.SetBorders( MultipleBorders.Vertical | MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium); worksheet.Cells.GetSubrange("A5", "I14").Style.Borders.SetBorders( MultipleBorders.Bottom | MultipleBorders.Right, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium); worksheet.PrintOptions.FitWorksheetWidthToPages = 1; // Save workbook as an Excel file. var path = Assembly.GetCallingAssembly().CodeBase; if (path == null) { return; } var projectPath = new Uri(path.Substring(0, path.LastIndexOf("bin", StringComparison.Ordinal))).LocalPath; var excelFilePath = new Uri(projectPath).LocalPath + @"\Excel_With_Styles.xlsx"; workbook.Save(excelFilePath); } }
public void BuildReport(Window owner) { try { // Set license key to use GemBox.Spreadsheet in Free mode. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); string dataDir = System.IO.Directory.GetCurrentDirectory(); string fullFileName = dataDir + @"\templates\"; _ef = ExcelFile.Load(fullFileName + "Template.xlsx"); var ws = _ef.Worksheets.ActiveWorksheet; DataTable data = GetData(); var headers = data.AsEnumerable() .Select(s => s.Field <string>("Name")) .ToArray(); var cellsData = data.AsEnumerable() .Select(s => s.Field <int>("Type")) .ToArray(); CellStyle tmpStyle = new CellStyle(); tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center; tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center; tmpStyle.Font.Weight = ExcelFont.BoldWeight; tmpStyle.Font.Color = System.Drawing.Color.Black; tmpStyle.Rotation = 60; int i = 2; // Write header data to Excel cells. foreach (var header in headers) { ws.Cells[16, i].Value = header; ws.Columns[i].AutoFit(1, ws.Rows[16], ws.Rows[17]); ws.Cells[16, i].Style = tmpStyle; ws.Cells[16, i].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); i++; } tmpStyle = new CellStyle(); tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center; tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center; tmpStyle.Font.Weight = ExcelFont.NormalWeight; i = 2; // Write data to Excel cells. foreach (var cell in cellsData) { ws.Cells[17, i].Value = cell; ws.Cells[17, i].Style = tmpStyle; ws.Cells[17, i].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); ws.Columns[i].Width = 4 * 256; i++; } ws.Rows[1].AutoFit(); ws.PrintOptions.FitWorksheetWidthToPages = 1; ws.PrintOptions.Portrait = false; _ef.Save(fullFileName + "report.xlsx"); System.Windows.MessageBox.Show(owner, "File has been created successfully.", "Information", MessageBoxButton.OK, MessageBoxImage.Information); MessageBox.Show(owner, "Report has been created successfully.", "Information", MessageBoxButton.OK, MessageBoxImage.Information); PrintFile(owner, fullFileName + "report.xlsx"); } catch (Exception ex) { MessageBox.Show(owner, string.Format("An error has occurred while creating document: {0}", ex.Message), "Error", MessageBoxButton.OK, MessageBoxImage.Error); } }
static void Main(string[] args) { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile ef = new ExcelFile(); ExcelWorksheet ws = ef.Worksheets.Add("Styles and Formatting"); ws.Cells[0, 1].Value = "Cell style examples:"; ws.PrintOptions.PrintGridlines = true; int row = 0; // Column width of 4, 30 and 36 characters. ws.Columns[0].Width = 4 * 256; ws.Columns[1].Width = 30 * 256; ws.Columns[2].Width = 36 * 256; ws.Cells[row += 2, 1].Value = ".Style.Borders.SetBorders(...)"; ws.Cells[row, 2].Style.Borders.SetBorders(MultipleBorders.All | MultipleBorders.Diagonal, SpreadsheetColor.FromArgb(252, 1, 1), LineStyle.Thin); ws.Cells[row += 2, 1].Value = ".Style.FillPattern.SetPattern(...)"; ws.Cells[row, 2].Style.FillPattern.SetPattern(FillPatternStyle.ThinHorizontalCrosshatch, SpreadsheetColor.FromName(ColorName.Green), SpreadsheetColor.FromName(ColorName.Yellow)); ws.Cells[row += 2, 1].Value = ".Style.Font.Color ="; ws.Cells[row, 2].Value = "Color.Blue"; ws.Cells[row, 2].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue); ws.Cells[row += 2, 1].Value = ".Style.Font.Italic ="; ws.Cells[row, 2].Value = "true"; ws.Cells[row, 2].Style.Font.Italic = true; ws.Cells[row += 2, 1].Value = ".Style.Font.Name ="; ws.Cells[row, 2].Value = "Comic Sans MS"; ws.Cells[row, 2].Style.Font.Name = "Comic Sans MS"; ws.Cells[row += 2, 1].Value = ".Style.Font.ScriptPosition ="; ws.Cells[row, 2].Value = "ScriptPosition.Superscript"; ws.Cells[row, 2].Style.Font.ScriptPosition = ScriptPosition.Superscript; ws.Cells[row += 2, 1].Value = ".Style.Font.Size ="; ws.Cells[row, 2].Value = "18 * 20"; ws.Cells[row, 2].Style.Font.Size = 18 * 20; ws.Cells[row += 2, 1].Value = ".Style.Font.Strikeout ="; ws.Cells[row, 2].Value = "true"; ws.Cells[row, 2].Style.Font.Strikeout = true; ws.Cells[row += 2, 1].Value = ".Style.Font.UnderlineStyle ="; ws.Cells[row, 2].Value = "UnderlineStyle.Double"; ws.Cells[row, 2].Style.Font.UnderlineStyle = UnderlineStyle.Double; ws.Cells[row += 2, 1].Value = ".Style.Font.Weight ="; ws.Cells[row, 2].Value = "ExcelFont.BoldWeight"; ws.Cells[row, 2].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells[row += 2, 1].Value = ".Style.HorizontalAlignment ="; ws.Cells[row, 2].Value = "HorizontalAlignmentStyle.Center"; ws.Cells[row, 2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells[row += 2, 1].Value = ".Style.Indent"; ws.Cells[row, 2].Value = "five"; ws.Cells[row, 2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left; ws.Cells[row, 2].Style.Indent = 5; ws.Cells[row += 2, 1].Value = ".Style.IsTextVertical = "; ws.Cells[row, 2].Value = "true"; // Set row height to 60 points. ws.Rows[row].Height = 60 * 20; ws.Cells[row, 2].Style.IsTextVertical = true; ws.Cells[row += 2, 1].Value = ".Style.NumberFormat"; ws.Cells[row, 2].Value = 1234; ws.Cells[row, 2].Style.NumberFormat = "#.##0,00 [$Krakozhian Money Units]"; ws.Cells[row += 2, 1].Value = ".Style.Rotation"; ws.Cells[row, 2].Value = "35 degrees up"; ws.Cells[row, 2].Style.Rotation = 35; ws.Cells[row += 2, 1].Value = ".Style.ShrinkToFit"; ws.Cells[row, 2].Value = "This property is set to true so this text appears shrunk."; ws.Cells[row, 2].Style.ShrinkToFit = true; ws.Cells[row += 2, 1].Value = ".Style.VerticalAlignment ="; ws.Cells[row, 2].Value = "VerticalAlignmentStyle.Top"; // Set row height to 30 points. ws.Rows[row].Height = 30 * 20; ws.Cells[row, 2].Style.VerticalAlignment = VerticalAlignmentStyle.Top; ws.Cells[row += 2, 1].Value = ".Style.WrapText"; ws.Cells[row, 2].Value = "This property is set to true so this text appears broken into multiple lines."; ws.Cells[row, 2].Style.WrapText = true; ef.Save("Styles and Formatting.xlsx"); }
public FileContentResult DownloadExcel() { String data = ""; List <ApplicationModel> releaseModelsList = null; if (Session["exportedData"] != null) { data = Session["exportedData"].ToString(); releaseModelsList = (List <ApplicationModel>)Session["exportedData"]; SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile ef = ExcelFile.Load(Server.MapPath(@"~/App_Data/TemplateUse.xlsx")); ExcelWorksheet ws = ef.Worksheets[0]; ws.Columns[0].Width = 30 * 256; ws.Columns[1].Width = 14 * 256; ws.Columns[2].Width = 20 * 256; ws.Columns[3].Width = 20 * 256; ws.Columns[4].Width = 14 * 256; ws.Columns[5].Width = 20 * 256; ws.Columns[6].Width = 20 * 256; ws.Columns[7].Width = 14 * 256; ws.Columns[8].Width = 20 * 256; ws.Columns[9].Width = 20 * 256; ws.Columns[10].Width = 14 * 256; ws.Columns[11].Width = 20 * 256; ws.Columns[12].Width = 20 * 256; ws.Cells["A1"].Value = "NAME"; ws.Cells["A1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["A1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.LightBlue); ws.Cells["A1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["B1"].Value = "DEV"; ws.Cells["B1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["B1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.DarkRed); ws.Cells["B1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["C1"].Value = "IMPACTS"; ws.Cells["C1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["C1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Red); ws.Cells["C1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["D1"].Value = "URL"; ws.Cells["D1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["D1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Purple); ws.Cells["D1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["E1"].Value = "TEST"; ws.Cells["E1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["E1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.DarkRed); ws.Cells["E1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["F1"].Value = "IMPACTS"; ws.Cells["F1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["F1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Red); ws.Cells["F1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["G1"].Value = "URL"; ws.Cells["G1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["G1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Purple); ws.Cells["G1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["H1"].Value = "STAGE"; ws.Cells["H1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["H1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.DarkRed); ws.Cells["H1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["I1"].Value = "IMPACTS"; ws.Cells["I1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["I1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Red); ws.Cells["I1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["J1"].Value = "URL"; ws.Cells["J1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["J1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Purple); ws.Cells["J1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["K1"].Value = "LIVE"; ws.Cells["K1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["K1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.DarkRed); ws.Cells["K1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["L1"].Value = "IMPACTS"; ws.Cells["L1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["L1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Red); ws.Cells["L1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["M1"].Value = "URL"; ws.Cells["M1"].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["M1"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Purple); ws.Cells["M1"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; int i = 1; foreach (ApplicationModel applicationModel in releaseModelsList) { if (applicationModel.releasesList.Count != 0) { i++; ws.Cells["A" + i].Value = applicationModel.Application_Name; ws.Cells["A" + i].Style.Font.Color = SpreadsheetColor.FromName(ColorName.DarkRed); ws.Cells["A" + i].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells["A" + i].Style.WrapText = true; ws.Cells["B" + i].Value = ""; ws.Cells["C" + i].Value = ""; ws.Cells["D" + i].Value = ""; ws.Cells["E" + i].Value = ""; ws.Cells["F" + i].Value = ""; ws.Cells["G" + i].Value = ""; ws.Cells["H" + i].Value = ""; ws.Cells["I" + i].Value = ""; ws.Cells["J" + i].Value = ""; ws.Cells["K" + i].Value = ""; ws.Cells["L" + i].Value = ""; ws.Cells["M" + i].Value = ""; foreach (ReleaseModel releaseModel in applicationModel.releasesList) { if (releaseModel.releaseStatuses.Count != 0) { i++; ws.Cells["A" + i].Value = releaseModel.Release_Name; ws.Cells["A" + i].Style.WrapText = true; ws.Cells["A" + i].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells["A" + i].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue); ws.Cells["A" + i].Style.Font.Weight = ExcelFont.BoldWeight; for (int s = 0; s < releaseModel.releaseStatuses.Count; s++) { switch (releaseModel.releaseStatuses[s].Release_Status) { case "DEV": ws.Cells["B" + i].Value = releaseModel.releaseStatuses[s].Month + " " + releaseModel.releaseStatuses[s].day + ", " + releaseModel.releaseStatuses[s].Year; ws.Cells["B" + i].Style.WrapText = true; ws.Cells["C" + i].Value = releaseModel.releaseStatuses[s].Impacts; ws.Cells["C" + i].Style.WrapText = true; ws.Cells["D" + i].Value = releaseModel.releaseStatuses[s].TFS_Url; ws.Cells["D" + i].Style.WrapText = true; break; case "TEST": ws.Cells["E" + i].Value = releaseModel.releaseStatuses[s].Month + " " + releaseModel.releaseStatuses[s].day + ", " + releaseModel.releaseStatuses[s].Year; ws.Cells["E" + i].Style.WrapText = true; ws.Cells["F" + i].Value = releaseModel.releaseStatuses[s].Impacts; ws.Cells["F" + i].Style.WrapText = true; ws.Cells["G" + i].Value = releaseModel.releaseStatuses[s].TFS_Url; ws.Cells["G" + i].Style.WrapText = true; break; case "STAGE": ws.Cells["H" + i].Value = releaseModel.releaseStatuses[s].Month + " " + releaseModel.releaseStatuses[s].day + ", " + releaseModel.releaseStatuses[s].Year; ws.Cells["H" + i].Style.WrapText = true; ws.Cells["I" + i].Value = releaseModel.releaseStatuses[s].Impacts; ws.Cells["I" + i].Style.WrapText = true; ws.Cells["J" + i].Value = releaseModel.releaseStatuses[s].TFS_Url; ws.Cells["J" + i].Style.WrapText = true; break; case "LIVE": ws.Cells["K" + i].Value = releaseModel.releaseStatuses[s].Month + " " + releaseModel.releaseStatuses[s].day + ", " + releaseModel.releaseStatuses[s].Year; ws.Cells["K" + i].Style.WrapText = true; ws.Cells["L" + i].Value = releaseModel.releaseStatuses[s].Impacts; ws.Cells["L" + i].Style.WrapText = true; ws.Cells["M" + i].Value = releaseModel.releaseStatuses[s].TFS_Url; ws.Cells["M" + i].Style.WrapText = true; break; } } } } } } String path = Server.MapPath(@"~/App_Data"); ef.Save(path + "/Product Development Release Calender Data.xlsx"); Session["exportedData"] = null; var fullPathToFile = Server.MapPath("~/App_Data/Product Development Release Calender Data.xlsx"); var mimeType = "application/vnd.ms-excel"; var fileContents = System.IO.File.ReadAllBytes(fullPathToFile); return(new FileContentResult(fileContents, mimeType) { FileDownloadName = Path.GetFileName(fullPathToFile) }); } else { data = "No Data To Export"; } return(null); }
public void CreateFile(Window owner) { try { // Set license key to use GemBox.Spreadsheet in Free mode. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); _ef = new ExcelFile(); ExcelWorksheet ws = _ef.Worksheets.Add("Report"); DataTable data = GetData(); var headers = data.AsEnumerable() .Select(s => s.Field <string>("Name")) .ToArray(); var cellsData = data.AsEnumerable() .Select(s => s.Field <int>("Type")) .ToArray(); CellStyle tmpStyle = new CellStyle(); tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center; tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center; tmpStyle.Font.Weight = ExcelFont.BoldWeight; tmpStyle.Font.Color = System.Drawing.Color.Black; tmpStyle.Rotation = 60; int i = 1; // Write header data to Excel cells. foreach (var header in headers) { ws.Cells[1, i].Value = header; ws.Columns[i].AutoFit(1, ws.Rows[1], ws.Rows[2]); ws.Cells[1, i].Style = tmpStyle; ws.Cells[1, i].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); i++; } tmpStyle = new CellStyle(); tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center; tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center; tmpStyle.Font.Weight = ExcelFont.NormalWeight; i = 1; // Write data to Excel cells. foreach (var cell in cellsData) { ws.Cells[2, i].Value = cell; ws.Cells[2, i].Style = tmpStyle; ws.Cells[2, i].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); ws.Columns[i].Width = 4 * 256; i++; } ws.Rows[1].AutoFit(); ws.PrintOptions.FitWorksheetWidthToPages = 1; ws.PrintOptions.Portrait = false; _ef.Save(FileName); System.Windows.MessageBox.Show(owner, "File has been created successfully.", "Information", MessageBoxButton.OK, MessageBoxImage.Information); ExcelViewer ev = new ExcelViewer(_ef.ConvertToImageSource(ImageSaveOptions.ImageDefault)); ev.Show(); PrintFile(owner, string.Empty); } catch (Exception ex) { System.Windows.MessageBox.Show(owner, string.Format("An error has occurred while creating document: {0}", ex.Message), "Error", MessageBoxButton.OK, MessageBoxImage.Error); } finally { if (!object.Equals(null, _ef)) { _ef = null; } } }