public static void SetFormatCell(IXLWorksheet ws,
int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn,
bool isFontBold = false, int fontSize = 10, bool isMergeCell = false,
bool isSetColorHeader = false, bool isSetBorder = true,
XLAlignmentHorizontalValues HorizontalAlgn = XLAlignmentHorizontalValues.Center,
XLAlignmentVerticalValues VerticalAlgn = XLAlignmentVerticalValues.Center)
{
// Defining ranges
// From worksheet
var rangeTable = ws.Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn);
rangeTable.Style.Alignment.Horizontal = HorizontalAlgn;
rangeTable.Style.Alignment.Vertical = VerticalAlgn;
rangeTable.Style.Font.Bold = isFontBold;
rangeTable.Style.Font.FontSize = fontSize;
if (isSetColorHeader)
{
rangeTable.Style.Fill.BackgroundColor = XLColor.FromArgb(184, 184, 184);
}
if (isMergeCell)
{
// Merge title cells
//rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge()
rangeTable.Merge();
}
if (isSetBorder)
{
rangeTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
rangeTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
}
private void makeShip(List <Api.PurchaseController.ShipData> data, IXLWorksheet sheet)
{
int row_index = 2;
#region 產品名稱
sheet.Cell(row_index, 3).Value = "咖啡豆重量";
List <string> p_name = data.Select(x => x.p_name).ToList();
int col_index = 4;
foreach (var i in p_name)
{
sheet.Cell(row_index, col_index).Value = i;
sheet.Cell(row_index, col_index).Style.Font.Bold = true;
sheet.Cell(row_index, col_index).Style.Font.FontColor = XLColor.FromArgb(153, 51, 0);
col_index++;
}
sheet.Cell(1, 2).Value = "訂單未出貨統計";
sheet.Range(1, 2, 1, col_index).Merge();
setFontColorAndBg(sheet, 1, 2, XLColor.White, XLColor.FromArgb(156, 101, 0));
int col_end = col_index;
row_index++;
#endregion
col_index = 4;
foreach (var i in data)
{
string name = sheet.Cell(2, col_index).GetValue <string>();
if (i.p_name == name)
{
foreach (var j in i.Detail)
{
sheet.Cell(row_index, 1).Value = j.purchase_no; //訂單編號
sheet.Cell(row_index, 2).Value = j.p_d_pack_name; //訂單編號
sheet.Cell(row_index, 3).Value = j.weight; //咖啡豆重量
sheet.Cell(row_index, col_index).Value = j.qty; //數量
row_index++;
}
col_index++;
}
}
#region 方加總
sheet.Cell(row_index, 2).Value = "未出貨統計(g)";
sheet.Range(row_index, 2, row_index, 3).Merge();
setFontColorAndBg(sheet, row_index, 2, XLColor.FromArgb(153, 51, 0), XLColor.FromArgb(255, 235, 156));
for (var i = 4; i < col_end; i++)
{
sheet.Cell(row_index, i).FormulaA1 = string.Format("SUMPRODUCT(C{0}:C{1},{2}{0}:{2}{1})", 3, row_index - 1, Convert.ToChar(64 + i));
sheet.Cell(row_index, i).Style.Font.FontColor = XLColor.FromArgb(156, 101, 0);
sheet.Cell(row_index, i).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 235, 156);
}
sheet.Cell(row_index, col_end).FormulaA1 = string.Format("SUM({0}{1}:{2}{1})", Convert.ToChar(64 + 4), row_index, Convert.ToChar(63 + col_end));
sheet.Cell(row_index, col_end).Style.Font.FontColor = XLColor.FromArgb(156, 101, 0);
sheet.Cell(row_index, col_end).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 235, 156);
#endregion
sheet.Range(1, 2, row_index, col_end).Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
sheet.Range(1, 2, row_index, col_end).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
sheet.ColumnsUsed().AdjustToContents();//自動調整寬度
}
public async Task <MemoryStream> GenerateXlsx()
{
using (MemoryStream stream = new MemoryStream())
{
var workbook = new XLWorkbook();
var SheetName = "Jobs List";
var worksheet = workbook.Worksheets.Add(SheetName);
worksheet.Cell(1, 1).Value = "Job";
worksheet.Cell(1, 2).Value = "Job Title";
worksheet.Cell(1, 3).Value = "Description";
worksheet.Cell(1, 4).Value = "Created At";
worksheet.Cell(1, 5).Value = "Expires At";
var jobList = await GetJobList();
for (int i = 0; i < jobList.Count(); i++)
{
worksheet.Cell(i + 2, 1).Value = jobList.ElementAt(i).Job;
worksheet.Cell(i + 2, 2).Value = jobList.ElementAt(i).JobTitle;
worksheet.Cell(i + 2, 3).Value = jobList.ElementAt(i).Description;
worksheet.Cell(i + 2, 4).Value = jobList.ElementAt(i).CreatedAt;
worksheet.Cell(i + 2, 5).Value = jobList.ElementAt(i).ExpiresAt;
}
worksheet.Range(1, 1, 1, 5).Style.Font.Bold = true;
worksheet.Range(1, 1, 1, 5).Style.Font.FontColor = XLColor.White;
worksheet.Range(1, 1, 1, 5).Style.Fill.BackgroundColor = XLColor.FromArgb(66, 135, 245);
worksheet.Range(1, 1, 1, 5).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Column(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Column(4).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Column(5).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Column(1).AdjustToContents();
worksheet.Column(2).Width = 30;
worksheet.Column(3).Width = 70;
worksheet.Column(4).Width = 12;
worksheet.Column(5).Width = 12;
int lastRow = worksheet.LastRowUsed().RowNumber();
worksheet.Range("A1:E" + lastRow).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Range("A1:E" + lastRow).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Range("A1:E" + lastRow).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Range("A1:E" + lastRow).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
workbook.SaveAs(stream);
stream.Seek(0, SeekOrigin.Begin);
return(stream);
}
}
public static void FormatTeamCell(IXLWorksheet worksheet, string range)
{
var rng = worksheet.Range(range);
rng.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rng.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 255, 0);
rng.Style.Font.FontSize = 22;
rng.Style.Font.FontName = "Tahoma";
rng.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
rng.Style.Border.LeftBorder = XLBorderStyleValues.Medium;
rng.Style.Border.RightBorder = XLBorderStyleValues.Medium;
rng.DataType = XLDataType.Text;
//rng.Style.
}
//TODO: Delegate convertation to item method.
//TODO: Catch "Too many requests", add timeout and try again.
public static void ObjToExcel(MarketplaceList items, bool open = false)
{
Console.WriteLine("Creating xlsx file. This can take a while.");
XLWorkbook workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("Coins");
worksheet.Cell(1, 1).Value = "Link";
worksheet.Cell(1, 2).Value = "Name";
worksheet.Cell(1, 3).Value = "Code";
worksheet.Cell(1, 4).Value = "Color";
worksheet.Cell(1, 5).Value = "Amount";
worksheet.Cell(1, 6).Value = "Price";
worksheet.Cells(true).Style.Font.SetBold();
for (int i = 0; i < items.Count; i++)
{
MarketplaceItem item = items[i];
worksheet.Cell(i + 2, 1).Hyperlink = new XLHyperlink(@item.Link);
worksheet.Cell(i + 2, 1).Value = @item.Link;
worksheet.Cell(i + 2, 2).Value = item.Name;
worksheet.Cell(i + 2, 3).Value = item.Code;
worksheet.Cell(i + 2, 4).Value = item.ColorStr;
worksheet.Cell(i + 2, 4).Style.Fill.BackgroundColor = XLColor.FromArgb(item.ColorHex);
worksheet.Cell(i + 2, 5).Value = item.Amount;
worksheet.Cell(i + 2, 6).Value = item.Price;
worksheet.Cell(i + 2, 6).Style.NumberFormat.Format = "0.00" + item.PriceCurrency;
}
worksheet.Columns(2, 6).AdjustToContents(10.0, 40.0);
DateTime current = DateTime.Now;
string saveLocation = SaveFolder + current.ToString("yyyy.MM.dd HH.mm.ss") + ".xlsx";
Console.WriteLine("Saving your file to {0}", saveLocation);
workbook.SaveAs(@saveLocation);
if (open)
{
string path = Path.Combine(Environment.CurrentDirectory, saveLocation);
ProcessStartInfo pInfo = new ProcessStartInfo(saveLocation)
{
UseShellExecute = true
};
Process.Start(pInfo);
}
}
public Stream ExportExcel(IEnumerable <Certificate> certificates)
{
string[] headers = new string[]
{
"Code",
"Course",
"Content Info",
"Completion Date",
"Course Url",
"Certificate Url"
};
var data = certificates.Select(cer => new
{
cer.Code,
cer.Course.Title,
cer.Course.Content_info,
cer.Completion_date,
cer.Course.Url,
cer.Long_url
});
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Certificates");
ws.Cell(2, 1).InsertData(data);
for (int i = 0; i < headers.Length; i++)
{
IXLCell cell = ws.Cell(1, i + 1);
cell.Value = headers[i];
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.FromArgb(63, 63, 118);
cell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 204, 153);
ws.Column(i + 1).AdjustToContents();
}
Stream fs = new MemoryStream();
wb.SaveAs(fs);
fs.Position = 0;
return(fs);
}
/// <summary>
/// https://github.com/ClosedXML/ClosedXML/wiki/Tab-Colors
/// </summary>
/// <returns></returns>
public ActionResult TabColors()
{
GetInstance("Red", out XLWorkbook wb, out IXLWorksheet ws);
ws.SetTabColor(XLColor.Red);
var wsAccent3 = wb.Worksheets.Add("Accent3");
wsAccent3.SetTabColor(XLColor.FromTheme(XLThemeColor.Accent3));
var wsIndexed = wb.Worksheets.Add("Indexed");
wsIndexed.TabColor = XLColor.FromIndex(24);
var wsArgb = wb.Worksheets.Add("Argb");
wsArgb.TabColor = XLColor.FromArgb(23, 23, 23);
return(ExportExcel(wb, "TabColors"));
}
