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");
}
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("Cell Referencing");
worksheet.Cells[0].Value = "Cell referencing examples:";
worksheet.Cells["B2"].Value = "Cell B2.";
worksheet.Cells[6, 0].Value = "Cell in row 7 and column A.";
worksheet.Rows[2].Cells[0].Value = "Cell in row 3 and column A.";
worksheet.Rows["4"].Cells["B"].Value = "Cell in row 4 and column B.";
worksheet.Columns[2].Cells[4].Value = "Cell in column C and row 5.";
worksheet.Columns["AA"].Cells["6"].Value = "Cell in AA column and row 6.";
// Referencing Excel row's cell range.
var cellRange = worksheet.Rows[7].Cells;
cellRange[0].Value = cellRange.IndexingMode.ToString();
cellRange[3].Value = "D8";
cellRange["B"].Value = "B8";
// Referencing Excel column's cell range.
cellRange = worksheet.Columns[7].Cells;
cellRange[0].Value = cellRange.IndexingMode.ToString();
cellRange[2].Value = "H3";
cellRange["5"].Value = "H5";
// Referencing arbitrary Excel cell range.
cellRange = worksheet.Cells.GetSubrange("I2", "L8");
cellRange.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromArgb(0, 0, 128), LineStyle.Dashed);
cellRange["J7"].Value = cellRange.IndexingMode.ToString();
cellRange[0, 0].Value = "I2";
cellRange["J3"].Value = "J3";
cellRange[4].Value = "I3"; // Cell range width is 4 (I J K L).
// Set column widths and some print options (for better look when exporting to PDF, XPS and printing).
var columnWidths = new double[] { 175, 174, 174, 24, double.NaN, double.NaN, double.NaN, 54, 19, 81 };
for (int i = 0; i < columnWidths.Length; i++)
{
if (!double.IsNaN(columnWidths[i]))
{
worksheet.Columns[i].SetWidth(columnWidths[i], LengthUnit.Pixel);
}
}
worksheet.PrintOptions.PrintGridlines = true;
worksheet.PrintOptions.PrintHeadings = true;
workbook.Save("Cell Referencing.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);
}
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("Cell Referencing");
ws.Cells[0].Value = "Cell referencing examples:";
ws.Cells["B2"].Value = "Cell B2.";
ws.Cells[6, 0].Value = "Cell in row 7 and column A.";
ws.Rows[2].Cells[0].Value = "Cell in row 3 and column A.";
ws.Rows["4"].Cells["B"].Value = "Cell in row 4 and column B.";
ws.Columns[2].Cells[4].Value = "Cell in column C and row 5.";
ws.Columns["AA"].Cells["6"].Value = "Cell in AA column and row 6.";
// Referencing Excel row's cell range.
CellRange cr = ws.Rows[7].Cells;
cr[0].Value = cr.IndexingMode.ToString();
cr[3].Value = "D8";
cr["B"].Value = "B8";
// Referencing Excel column's cell range.
cr = ws.Columns[7].Cells;
cr[0].Value = cr.IndexingMode.ToString();
cr[2].Value = "H3";
cr["5"].Value = "H5";
// Referencing arbitrary Excel cell range.
cr = ws.Cells.GetSubrange("I2", "L8");
cr.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromArgb(0, 0, 128), LineStyle.Dashed);
cr["J7"].Value = cr.IndexingMode.ToString();
cr[0, 0].Value = "I2";
cr["J3"].Value = "J3";
cr[4].Value = "I3"; // Cell range width is 4 (I J K L).
// Autofit columns and some print options (for better look when exporting to pdf, xps and printing).
var columnCount = ws.CalculateMaxUsedColumns();
for (int i = 0; i < columnCount; i++)
{
ws.Columns[i].AutoFit();
}
ws.PrintOptions.PrintGridlines = true;
ws.PrintOptions.PrintHeadings = true;
ef.Save("Cell Referencing.xlsx");
}
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();
}
public static ExcelWorksheet BorderIndividualCells(this ExcelWorksheet ws, int startRow, int endRow,
SpreadsheetColor borderColor = default(SpreadsheetColor))
{
var colCount = ws.CalculateMaxUsedColumns();
for (int col = 0; col < colCount; col++)
{
for (int row = startRow; row <= endRow; row++)
{
ws.Cells[row, col].SetBorders(MultipleBorders.Left | MultipleBorders.Right | MultipleBorders.Bottom, LineStyle.Thin, borderColor);
}
}
return(ws);
}
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");
}
private void formatSheet()
{
var workbook = ExcelFile.Load("finalReport.csv");
var worksheet = workbook.Worksheets[0];
for (int i = 0; i < 7; i++)
{
var cell = worksheet.Cells[0, i];
cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(0, 0, 0));
cell.Style.Font.Color = SpreadsheetColor.FromArgb(255, 255, 255);
}
for (int i = 1; i < worksheet.Rows.Count; i++)
{
var cell = worksheet.Cells[i, 1];
double res = 0;
try
{
res = (double)cell.Value;
}
catch (Exception)
{
}
if (res > 125)
{
cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(255, 155, 155));
cell.Style.Font.Color = SpreadsheetColor.FromArgb(140, 20, 20);
}
if (res > 75 && res <= 125)
{
cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(255, 235, 120));
cell.Style.Font.Color = SpreadsheetColor.FromArgb(150, 130, 20);
}
if (res <= 75)
{
cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(115, 255, 110));
cell.Style.Font.Color = SpreadsheetColor.FromArgb(30, 140, 25);
}
}
workbook.Save("formatted.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");
}
static void Main(string[] args)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
Console.WriteLine("SpreadsheetExtensions.EPPlus.SampleConsoleApp");
var file = Path.Combine(Directory.GetCurrentDirectory(), "sample_epplus.xlsx");
if (!File.Exists(file))
{
Console.WriteLine($"The file '{file}' does not exists");
return;
}
using var package = new ExcelPackage(new FileInfo(file));
using var workbook = package.Workbook;
using var worksheet = workbook.Worksheets[0];
worksheet.Write(1, 1, 5, 5, "SAMPLE TEXT")
.Merge(true)
.SetFontBold(true)
.SetFontItalic(true)
.SetFontUnderline(true)
.SetBorderBottom(new SpreadsheetBorder
{
Style = SpreadsheetBorderStyle.Thick,
Color = new SpreadsheetColor(255, 0, 0, 255)
})
.SetBorderRight(new SpreadsheetBorder
{
Style = SpreadsheetBorderStyle.Dotted,
Color = SpreadsheetColor.FromDotNetColor(System.Drawing.Color.Green)
})
.SetVerticalAlignment(SpreadsheetVerticalAlignment.Center)
.SetHorizontalAlignment(SpreadsheetHorizontalAlignment.Center);
package.Save();
}
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 StandartInlineRecordNumberCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null)
{
x.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
x.Style.Font.Color = SpreadsheetColor.FromArgb(105, 105, 105);
}
public static void EmptyWeaponStyle(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null)
{
if (dataRow != null)
{
//Link to description COLUMN INDEX ["Description" is 8th]; TODO: fast way to link by column name
int mean_index = 8;
if (dataRow.Length > mean_index && dataRow[mean_index] == null)
{
x.Offset(mean_index).Resize(4).Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(255, 188, 192), SpreadsheetColor.FromArgb(255, 188, 192));
}
//paint next 4 columns
}
}
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 static void SurvivabilityGroupCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null)
{
x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(3, 163, 5), SpreadsheetColor.FromArgb(3, 163, 5));
}
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 static void HeaderGroup3Cell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null)
{
x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(0, 112, 192), SpreadsheetColor.FromArgb(0, 112, 192));
}
public static void StrengthGroupCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null)
{
x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(107, 127, 188), SpreadsheetColor.FromArgb(107, 127, 188));
}
public static void CostGroupCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null)
{
x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(255, 148, 57), SpreadsheetColor.FromArgb(255, 148, 57));
}
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 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);
}
