public IActionResult DownloadExcelEmprestimo(RelatorioViewModel item)
{
if (ModelState.IsValid)
{
var list = _EmprestRep.ListarEmprestimo().Where(x => x.DataEmprestimo.ToString("MM/yyyy") == item.DataRelatorio.ToString("MM/yyyy")).ToArray();
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string fileName = $"Relatorio Emprestimos {item.DataRelatorio.ToString("MMMM-yyyy")}.xlsx";
try
{
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet =
workbook.Worksheets.Add("EMPRESTIMOS");
worksheet.Cell("B1").Value = $"LISTA DE EMPRESTIMOS DO MÊS {item.DataRelatorio.ToString("MMMM/yyyy")}";
worksheet.Cell(2, 2).Value = "LIVRO";
worksheet.Cell(2, 3).Value = "ALUNO";
worksheet.Cell(2, 4).Value = "DATA EMPRESTIMO";
worksheet.Cell(2, 5).Value = "DATA DEVOLUÇÃO";
worksheet.Row(2).Style.Font.Bold = true;
worksheet.Row(2).Style.Font.FontColor = XLColor.White;
var rngHeaders = worksheet.Range("B2:E2");
rngHeaders.Style.Font.Bold = true;
rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rngHeaders.Style.Fill.BackgroundColor = XLColor.BluePigment;
worksheet.Columns(2, 5).AdjustToContents();
worksheet.Columns(2, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
for (int index = 1; index <= list.Count(); index++)
{
worksheet.Cell(index + 2, 2).Value = list[index - 1].Livro.Nome;
worksheet.Cell(index + 2, 3).Value = list[index - 1].Aluno;
worksheet.Cell(index + 2, 4).Value = list[index - 1].DataEmprestimo.ToString("dd/MM/yyyy");
if (list[index - 1].DataDevolucao.ToString("dd/MM/yyyy") == "01/01/0001")
{
worksheet.Cell(index + 2, 5).Value = "Não foi devolvido";
}
else
{
worksheet.Cell(index + 2, 5).Value = list[index - 1].DataDevolucao.ToString("dd/MM/yyyy");
}
}
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(content, contentType, fileName));
}
}
}
catch (Exception ex)
{
return(View(ex));
}
}
else
{
return(View(nameof(Index), item));
}
}
/// <summary>
/// Infoシートにヘッダ情報を書き込む
/// </summary>
/// <param name="info">Infoシート</param>
private void setInfoHeader(IXLWorksheet info)
{
// タイトル
info.Cell(1, 1).SetValue(Text)
.Style.Font.SetFontSize(20.0)
.Font.SetBold();
// 画像情報
info.Cell(3, 1).SetValue("ディレクトリ");
info.Cell(4, 1).SetValue("サイズ");
info.Cell(5, 1).SetValue("パレット");
info.Range(3, 1, 5, 1).Style.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
.Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
.Border.SetInsideBorder(XLBorderStyleValues.Dashed)
.Fill.SetBackgroundColor(XLColor.LightGray);
// ビーズ情報
info.Cell(7, 1).SetValue("No");
info.Cell(7, 2).SetValue("名前");
info.Cell(7, 3).SetValue("個数");
info.Range(7, 1, 7, 3).Style.Font.SetBold().Fill.SetBackgroundColor(XLColor.LightGray);
// 行列幅
info.Columns(1, 1).Width = 16.43;
info.Columns(2, 2).Width = 23.57;
info.Rows(1, 1).Height = 26.25;
}
/// <summary>
/// Sortieren der Daten
/// Überschriften auf BOLD setzen
/// Autofilter Aktivieren
/// Spaltenbreite an Inhalt anpassen
/// </summary>
/// <param name="worksheet"></param>
private void SortAndFormatXlsSheet(IXLWorksheet worksheet)
{
////Sortieren der Daten
var lastCellUsed = worksheet.LastCellUsed();
var lastCellUsedAddress = $"A2:{lastCellUsed.Address}";
var DataRange = worksheet.Range(lastCellUsedAddress);
DataRange.Sort("F, C, D, E");
//Autofilter und Spaltenbreite an Inhalt anpassen
worksheet.RangeUsed().SetAutoFilter();
lastCellUsed = worksheet.LastCellUsed();
//Spaltenbreite an Inhalt anpassen
worksheet.Columns().AdjustToContents(1, lastCellUsed.Address.RowNumber);
worksheet.Row(1).Style.Font.SetBold();
//Spalten als Zahl formartieren
worksheet.Columns("F,J").AdjustToContents(1, lastCellUsed.Address.RowNumber).Style.NumberFormat.NumberFormatId = 2;
worksheet.Range($"I2:I{lastCellUsed.Address.RowNumber}").SetDataType(XLDataType.Number);
//Formatieren
//Druckbereich
//Druckeigenschaften
}
private void UpdateWorksheetStyles(IXLWorksheet worksheet, int dataCount, int columnsCount, ExcelReportConfigurationDocument config)
{
//worksheet.Range(config.SkipRow + HEADER_ROW_COUNT + 1, config.SkipColumn + 1, config.SkipRow + HEADER_ROW_COUNT + dataCount, config.SkipColumn + 1 + columnsCount * 2)
// .CreateTable();
worksheet.Columns(1, config.SkipColumn).Width = 4;
worksheet.Columns(config.SkipColumn + 1, config.SkipColumn + 1 + columnsCount * 2).AdjustToContents(10.0, 50.0);
}
/// <summary>
/// Adjusts all the column widths to match the content
/// </summary>
/// <param name="minWidth">Minimum width in twips</param>
/// <param name="maxWidth">Maximum width in twips</param>
public void AdjustColumnsToContent(
double minWidth,
double maxWidth)
{
var columns = _sheet.Columns();
columns.AdjustToContents(minWidth, maxWidth);
}
private IXLWorksheet formatSheet(IXLWorksheet xlWorksheet, int iRows)
{
IXLRange objRange = xlWorksheet.Range("A2", Convert.ToChar(64 + 12) + iRows.ToString());
objRange.Style.Font.SetFontName("Times New Roman");
objRange.Style.Font.SetFontSize(8);
xlWorksheet.Columns("7:10").Style.NumberFormat.Format = "#,##0.00";
xlWorksheet.Columns(1, 12).AdjustToContents();
return(xlWorksheet);
}
internal void GenerateSuiteSheet(TestSuite suite)
{
IXLWorksheet sheet = workbook.AddWorksheet(suite.Name);
sheet.Columns("A:Z").Style.Alignment.WrapText = true;
sheet.Columns("A:Z").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
sheet.Columns("A:Z").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
deep = suite.GetSuitsDeep();
row = 1;
MakeHeaders(sheet, suite);
WriteSuite(sheet, suite, new List <string>());
}
public IXLWorksheet ColumnWidthAndFontSize(IXLWorksheet ws, string label)
{
switch (label)
{
case "task":
{
ws.Columns("A").Width = 3;
//No列の幅
ws.Columns("B").Width = 3;
//タイプ列の幅
ws.Columns("C").Width = 5;
//内容、詳細列の幅
ws.Columns("D:E").Width = 64;
//完了判断列の幅
ws.Columns("F").Width = 30;
//備考列の幅
ws.Columns("G").Width = 40;
//担当者、日付列の幅
ws.Columns("H:J").Width = 15;
//文字サイズ指定
ws.Columns("A:K").Style.Font.FontSize = 9;
break;
}
}
return(ws);
}
void SetTableHeader(int month, int year, IXLWorksheet ws)
{
var allDates = AllDatesInMonth(year, month);
if (allDates.Count() == 28)
{
ws.Columns("34:36").Delete();
}
if (allDates.Count() == 29)
{
ws.Columns("35:36").Delete();
}
if (allDates.Count() == 30)
{
ws.Columns("AJ").Delete();
}
int column = 6;
foreach (DateTime dtime in AllDatesInMonth(year, month))
{
ws.Cell(6, column).Style.DateFormat.SetFormat("dd/MM");
ws.Cell(6, column).Value = dtime;
switch (dtime.DayOfWeek)
{
case DayOfWeek.Monday:
ws.Cell(7, column).Value = "T2"; break;
case DayOfWeek.Tuesday:
ws.Cell(7, column).Value = "T3"; break;
case DayOfWeek.Wednesday:
ws.Cell(7, column).Value = "T4"; break;
case DayOfWeek.Thursday:
ws.Cell(7, column).Value = "T5"; break;
case DayOfWeek.Friday:
ws.Cell(7, column).Value = "T6"; break;
case DayOfWeek.Saturday:
ws.Cell(7, column).Value = "T7"; break;
case DayOfWeek.Sunday:
ws.Cell(7, column).Value = "CN"; break;
}
column++;
}
}
private void ExportExcel(DataTable dt, string fileame)
{
string FileSavePath = ConfigurationManager.AppSettings["ExcelSavePath"].ToString();
string tempFile = System.IO.Path.Combine(FileSavePath, fileame);
using (XLWorkbook wb = new XLWorkbook())
{
IXLWorksheet ws = wb.Worksheets.Add("Export Data");
int z = 1;
foreach (DataColumn column in dt.Columns)
{
ws.Cell(1, z).Value = column.ColumnName;
ws.Cell(1, z).Style.Font.Bold = true;
ws.Cell(1, z).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell(1, z).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
z += 1;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.Cell(i + 2, j + 1).Value = dt.Rows[i][j];
ws.Cell(i + 2, j + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell(i + 2, j + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell(i + 2, 6).Style.DateFormat.Format = "dd-MMM-yyyy hh:mm:ss";
}
}
ws.Columns().AdjustToContents();
wb.SaveAs(tempFile);
Console.WriteLine(tempFile);
}
}
public byte[] GenerateReport(List <object> itemList, GridConfigurationBase gridConfig)
{
List <GridColumnBase> columnList = gridConfig.GridColumnBases.Where(x => !x.KeyColumn).ToList();
this.Columns = GetColumns(columnList.Count());
using (var workbook = new XLWorkbook())
{
IXLWorksheet ws = workbook.Worksheets.Add("Info General");
CreateHeader(columnList, ws);
foreach (object obj in itemList)
{
int file = itemList.IndexOf(obj) + 2;
foreach (GridColumnBase cName in columnList)
{
int column = columnList.IndexOf(cName);
var value = GridColumnBase.GetKeyValue(obj, cName.PropertyInfo);
ws.Cell($"{Columns[column]}{file}")
.Value = value;
}
}
ws.Columns().AdjustToContents();
using (var ms = new MemoryStream())
{
workbook.SaveAs(ms);
return(ms.ToArray());
}
}
}
internal void SaveMainStatistics(XLWorkbook workbook)
{
IXLWorksheet worksheet = workbook.Worksheets.Add("Общая статистика");
worksheet.Cell(1, 1).Value = "№";
MainMethods.SetStyleHeader(worksheet.Cell(1, 1));
foreach (ColumnMainStatistics itemColumns in _columns)
{
worksheet.Cell(1, itemColumns.Id + 1).Value = itemColumns.Description;
MainMethods.SetStyleHeader(worksheet.Cell(1, itemColumns.Id + 1));
}
for (int i = 0; i < TournamentReplays.Count; i++)
{
worksheet.Cell(i + 2, 1).Value = i.ToString();
foreach (ColumnMainStatistics itemColumns in _columns)
{
worksheet.Cell(i + 2, itemColumns.Id + 1).Value = GetValueReplay(TournamentReplays[i], itemColumns);
}
}
worksheet.Columns(_columns[0].Id + 1, _columns.Last().Id + 1).AdjustToContents();
}
private static int DrawDataTable <T>(this IXLWorksheet worksheet, IEnumerable <T> data, int rowNumberStart = 1, int colNumberStart = 1) where T : class, new()
{
var colNumber = colNumberStart;
var rowNumber = rowNumberStart;
var first = data.FirstOrDefault();
var dataProps = GetColumnList(first?.GetType(), null);
dataProps.ForEach(p =>
{
var cell = worksheet.Cell(rowNumber, colNumber++);
DrawHeaderCell(cell, p.Key);
});
data.ForEach(item =>
{
rowNumber++;
colNumber = 1;
dataProps.ForEach(p =>
{
var cell = worksheet.Cell(rowNumber, colNumber++);
DrawDataCell(cell, p.Value.GetValue(item), p.Value.GetCustomAttribute <ExportDisplayAttribute>());
});
});
worksheet.Columns(colNumberStart, dataProps.Count).AdjustToContents();
return(rowNumber);
}
/// <summary>
/// Allows you to write excel files in .xlsx format. Repository: https://github.com/MarcinMichnik-HiQ/Frends.Office
/// </summary>
/// <param name="input"></param>
/// <returns>Returns JToken.</returns>
public static JToken WriteExcelFile(WriteExcelFileInput input)
{
JToken taskResponse = JToken.Parse("{}");
IXLWorkbook workbook = new XLWorkbook();
DataTable dataTable;
try
{
dataTable = input.CsvToDataTable();
}
catch (Exception ex)
{
throw new Exception("Unable to build DataTable from csv.", ex);
}
IXLWorksheet mainWorksheet = workbook.Worksheets.Add(dataTable, "Default");
// Adjust rows and columns to text length
mainWorksheet.Rows().AdjustToContents();
mainWorksheet.Columns().AdjustToContents();
try
{
workbook.SaveAs(input.path);
}
catch (Exception ex)
{
throw new Exception("Unable to save the file.", ex);
}
taskResponse["message"] = "The file has been written correctly.";
taskResponse["filePath"] = input.path;
return(taskResponse);
}
/// <summary>
/// Сохраняет отчет в файл.
/// </summary>
private bool SaveFile()
{
try
{
// Экземпляр книги.
using (XLWorkbook wb = new XLWorkbook())
{
foreach (var report in Reports)
{
// Вкладка с названием по ОСП отчета с датой.
IXLWorksheet ws = wb.Worksheets.Add($"{report.OspName}");
// Вставить таблицу.
ws.Cell(1, 1).InsertTable(GetTable(report));
// Установить автоподбор ширины столбцов.
ws.Columns().AdjustToContents();
}
// Сохранения файла.
wb.SaveAs(Filepath);
}
return(true);
}
catch
{
return(false);
}
}
public void RemoveEmptyHeaders(Visibility visibility, int celIndx, IXLWorksheet worksheet)
{
if (!visibility.Equals(Visibility.Visible))
{
worksheet.Columns(celIndx, celIndx).Delete();
}
}
private void InitializeWorksheet(IXLWorksheet worksheet)
{
void SetColumn(string columnIndex, string columnName, float columnWidth)
{
worksheet.Cell(columnIndex).Value = columnName;
worksheet.Column(columnIndex[0] - 'A' + 1).Width = columnWidth; // 'A' -> 1 ; 'C' -> 3
}
SetColumn("A1", "Poster", 8.11f);
SetColumn("B1", "AnimeName", 36.8f);
SetColumn("C1", "Rating", 8.11f);
SetColumn("D1", "StudioName", 16.5f);
SetColumn("E1", "Status", 12.8f);
SetColumn("F1", "AgeRating", 40.7f);
SetColumn("G1", "Type", 25f);
SetColumn("H1", "Description", 19.2f);
SetColumn("I1", "Source", 10.8f);
SetColumn("J1", "Season", 10.1f);
SetColumn("K1", "Genres", 10.4f);
worksheet.Row(1).Style.Font.Bold = true;
worksheet.Columns(1, 11).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Column(8).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Column(11).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Range("A1", "K1").Style.Fill.BackgroundColor = XLColor.FromArgb(112, 173, 71);
}
private static void CreateTable(IEnumerable <Purchase> purchases, IXLWorksheet sheet)
{
int currentRow = 1;
foreach (var purchase in purchases)
{
int tableRowBegin = currentRow;
OutDate(sheet, ref currentRow, purchase);
CreateHeader(sheet, currentRow++);
var pItems = purchase.GetPurchaseItems().OrderBy(x => x.Type).ThenBy(x => x.GetTotal);
int rowDataBegin = currentRow;
foreach (var item in pItems)
{
OutGood(sheet, item, currentRow++);
}
OutPurchaseSum(sheet, ref currentRow, rowDataBegin);
EditRange(sheet, currentRow, tableRowBegin, rowDataBegin);
}
sheet.Columns().AdjustToContents();
}
private IXLWorksheet CreateStatsSheet <T>(IXLWorkbook wb, IEnumerable <T> results, double durationSla, double sizeSla)
{
IXLWorksheet wsStats = wb.Worksheets.Add("Perfx_Stats");
var stats = results.GetStats();
var statsDataTable = this.ToDataTable(stats.AsEnumerable());
var statsTable = wsStats.Cell(1, 1).InsertTable(statsDataTable, "Stats");
statsTable.Theme = XLTableTheme.TableStyleLight8;
var statsRowCount = (statsDataTable.Rows.Count + 1).ToString();
var durations = wsStats.Range("B2:I" + statsRowCount);
SetFormat(durations, durationSla);
var sizes = wsStats.Range("J2:K" + statsRowCount);
SetFormat(sizes, sizeSla);
wsStats.Range("L2:L" + statsRowCount).Style.Font.SetFontColor(XLColor.SeaGreen);
wsStats.Range("M2:M" + statsRowCount).Style.Font.SetFontColor(XLColor.OrangeRed);
try
{
wsStats.SheetView.Freeze(1, 1);
wsStats.Columns().AdjustToContents();
}
catch (Exception)
{
// Expected on Windows Server
}
return(wsStats);
}
private static void AddFooterToWorksheet(IXLWorksheet worksheet, int actualRowNumber)
{
var actualLetter = 'B';
actualLetter = GetNextLetter(actualLetter);
worksheet.Cell(actualLetter.ToString() + actualRowNumber).Value = "Instructores";
var rngInst = worksheet.Range(actualLetter.ToString() + actualRowNumber);
rngInst.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
rngInst.Style.Font.Bold = true;
rngInst.Style.Font.Underline = XLFontUnderlineValues.Single;
rngInst.Style.Font.FontSize = 28;
rngInst.Style.Font.FontName = "Calibri";
actualLetter = GetNextLetter(actualLetter);
worksheet.Cell(actualLetter.ToString() + actualRowNumber).Value = "Sciancalepore, Juan C.";
worksheet.Cell(actualLetter.ToString() + (actualRowNumber + 1)).Value = "Zechillo, Pablo";
var rngNames =
worksheet.Range(actualLetter.ToString() + actualRowNumber + ":" + actualLetter.ToString() + (actualRowNumber + 1));
rngNames.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
rngNames.Style.Font.Bold = true;
rngNames.Style.Font.FontSize = 26;
rngNames.Style.Font.FontName = "Calibri";
worksheet.Column(1).AdjustToContents(11, 12);
worksheet.Columns(2, 7).AdjustToContents();
}
/// <summary>
/// Method used to add values to excel file
/// </summary>
/// <param name="jsonText">Is a json value for which excel needs to be created.</param>
/// <param name="workSheet">Worksheet for which header needs to be added</param>
private static void AddDataToExcel(string jsonText, IXLWorksheet workSheet)
{
//Add data rows
int rowCount = 2;
//make a new string list
List <string> jsonList = jsonText.Split(Constants.CHAR_NEWLINE).ToList();
foreach (string jsonListItem in jsonList)
{
List <string> rowValue = new List <string>(); //make a new string list
rowValue.AddRange(jsonListItem.Split(Constants.CHAR_SEMICOLON));
for (int iCount = 0; iCount < rowValue.Count; iCount++)
{
if (0 > rowValue[iCount].Trim().Length)
{
workSheet.Cell(rowCount, iCount + 1).Value = string.Empty;
}
else
{
workSheet.Cell(rowCount, iCount + 1).Value = (string.IsNullOrWhiteSpace(Convert.ToString(rowValue[iCount], CultureInfo.InvariantCulture))) ? string.Empty : rowValue[iCount];
}
}
rowCount++;
workSheet.Columns().AdjustToContents();
}
}
private static void createValvesSheet(XLWorkbook workbook, TECTemplates templates)
{
IXLWorksheet worksheet = workbook.Worksheets.Add("Valves");
int x = 1, y = 2;
y = addHardwareHeader(worksheet, x, y).nextColumn;
y = addEndDeviceHeader(worksheet, x, y).nextColumn;
worksheet.Cell(x, y).Value = "Actuator";
worksheet.Cell(x, y).Style.Font.SetBold();
worksheet.Cell(x, y).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
x++;
int startY = 2;
foreach (TECValve valve in templates.Catalogs.Valves)
{
int rowY = startY;
startY = addHardwareRow(worksheet, valve, x, startY).nextColumn;
startY = addEndDeviceRow(worksheet, valve, x, startY).nextColumn;
worksheet.Cell(x, startY).Value = valve.Actuator.Name;
x++;
}
worksheet.Columns().AdjustToContents();
worksheet.PageSetup.PageOrientation = XLPageOrientation.Landscape;
worksheet.PageSetup.FitToPages(1, 1);
}
private List <DiferenceWs> PaintDifferences(IXLWorksheet ws1, IXLWorksheet ws2)
{
var ws1CountRows = ws1.Rows().Count();
var ws2CountRows = ws2.Rows().Count();
var ws1CountCols = ws1.Columns().Count();
var ws2CountCols = ws2.Columns().Count();
List <DiferenceWs> diffList = new();
if (ws1CountCols != ws2CountCols && ws1CountRows != ws2CountRows)
{
throw new Exception($"number cols/rows are different for worksheet target {ws1.Name} and worksheet source {ws2.Name} , table : " + TableName);
}
for (var irow = 1; irow <= ws1CountRows; irow++)
{
for (var icol = 1; icol <= ws1CountCols; icol++)
{
if ((ws1.Cell(irow, icol).GetValue <string>() == ws2.Cell(irow, icol).GetValue <string>()))
{
continue;
}
ws1.Cell(irow, icol).Style.Fill.BackgroundColor = XLColor.OrangePeel;
ws2.Cell(irow, icol).Style.Fill.BackgroundColor = XLColor.OrangePeel;
diffList.Add(new DiferenceWs(irow, icol, ws1.Cell(irow, icol).GetValue <string>(), ws2.Cell(irow, icol).GetValue <string>(), TableName));
}
}
return(diffList);
}
private static void createSystemSheet(XLWorkbook workbook, TECTemplates templates)
{
IXLWorksheet worksheet = workbook.Worksheets.Add("Systems");
int x = 1, y = 2;
y = addScopeHeader(worksheet, x, y).nextColumn;
y = addScopeHeader(worksheet, x, y).nextColumn;
x = addSubScopeHeader(worksheet, x, y).nextRow;
foreach (TECSystem system in templates.Templates.SystemTemplates)
{
foreach (TECEquipment equipment in system.Equipment)
{
foreach (TECSubScope scope in equipment.SubScope)
{
y = 2;
y = addScopeRow(worksheet, system, x, y).nextColumn;
y = addScopeRow(worksheet, equipment, x, y).nextColumn;
x = addSubScopeRow(worksheet, scope, x, y).nextRow;
}
}
}
worksheet.Columns().AdjustToContents();
worksheet.PageSetup.PageOrientation = XLPageOrientation.Landscape;
worksheet.PageSetup.FitToPages(1, 1);
}
public async Task <XLWorkbook> WriteDataToFile()
{
string path = "";
string actualPath = path.SetDirectoryPath();
string newlyCreatedFilePath = $@"{actualPath}\ExcelFiles\ClosedXMLGeneratedFile.xlsx";
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Primary", 1);
//IXLWorksheet ws2 = wb.Worksheets.Add("Secondary", 2);
var dataTable = await ReadExcelDataAsync();
ws.Range(1, 1, 1, 5).Merge().AddToNamed("Titles");
//ws2.Range(1, 1, 1, 5).Merge().AddToNamed("Workbook");
var rangeWithData = ws.Cell(2, 1).InsertData(dataTable.AsEnumerable());
//var rangeWithData2 = ws2.Cell(2, 1).InsertData(dataTable.AsEnumerable());
ws.Column(1).SetDataType(XLDataType.Number);
ws.Column(2).SetDataType(XLDataType.Text);
ws.Column(3).SetDataType(XLDataType.Boolean);
ws.Column(4).SetDataType(XLDataType.Text);
ws.Column(5).Style.NumberFormat.Format = "mm/dd/yyyy";
//ws2.Column(5).Style.NumberFormat.Format = "mm/dd/yyyy";
//Adjust column widths to their content
ws.Columns(1, 5).AdjustToContents();
//ws2.Columns(1, 5).AdjustToContents();
// Prepare the style
var dataStyle = ws.Style;
dataStyle.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// wingdings column
var rangeForWingDings = ws.Range(2, 6, 100000, 6).AddToNamed("wingdings");
rangeForWingDings.Value = char.ConvertFromUtf32(0x00002713);
rangeForWingDings.Style.Fill.BackgroundColor = XLColor.Red;
// Merge cells
var mergedCellrange = ws.Range(2, 10, 2, 12);
mergedCellrange.Cell(1, 1).Value = "merged";
mergedCellrange.Merge();
// outside border
var outsideBorderRange = ws.Range(2, 10, 2, 12);
outsideBorderRange.Style.Border.OutsideBorder = XLBorderStyleValues.Double;
wb.SaveAs(newlyCreatedFilePath);
return(wb);
}
public async Task <byte[]> ExportTasksAsXlsl(Guid userId, bool restrictData)
{
List <Core.Entities.Task> tasks = null;
if (!restrictData)
{
tasks = await taskRepository.GetAllTasksWithChildEntities();
}
else
{
tasks = await taskRepository.GetAllTasksByUserIdWithChildEntities(userId.ToString());
}
if (tasks == null)
{
return(null);
}
var tasksDto = tasks.Select(x => mapper.Map <TaskDto>(x))
.Where(x => !x.Removed)
.ToList();
var workBook = new XLWorkbook();
IXLWorksheet worksheet = workBook.Worksheets.Add($"Tarefas - {tasks.First().Attendant.FirstName}");
worksheet.Cell(1, 1).Value = "Tarefa";
worksheet.Cell(1, 2).Value = "Descrição";
worksheet.Cell(1, 3).Value = "Status";
worksheet.Cell(1, 4).Value = "Prioridade";
worksheet.Cell(1, 5).Value = "Data de Criação";
worksheet.Cell(1, 6).Value = "Data de Conclusão";
worksheet.Cell(1, 7).Value = "Concluída em";
var headerCells = worksheet.Range("A1:G1");
headerCells.Style.Font.SetBold()
.Fill.SetBackgroundColor(XLColor.BabyBlue)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
for (int i = 1; i <= tasksDto.Count(); i++)
{
worksheet.Cell(i + 1, 1).Value = tasksDto[i - 1].Title;
worksheet.Cell(i + 1, 2).Value = tasksDto[i - 1].Description;
worksheet.Cell(i + 1, 3).Value = tasksDto[i - 1].Status;
worksheet.Cell(i + 1, 4).Value = tasksDto[i - 1].Priority;
worksheet.Cell(i + 1, 5).Value = tasksDto[i - 1].CreatedDate;
worksheet.Cell(i + 1, 6).Value = tasksDto[i - 1].ConclusionDate.HasValue ? tasksDto[i - 1].ConclusionDate.Value.ToString("dd/MM/yyyy") : "Sem data de conclusão";
worksheet.Cell(i + 1, 7).Value = tasksDto[i - 1].ConcludedDate.HasValue ? tasksDto[i - 1].ConcludedDate.Value.ToString("dd/MM/yyyy") : "Não concluída";
}
worksheet.Columns().AdjustToContents();
using MemoryStream stream = new MemoryStream();
workBook.SaveAs(stream);
stream.Seek(0, SeekOrigin.Begin);
return(stream.ToArray());
}
public void FormatAllReport(IXLWorksheet ws, int currentRow, int totalCols)
{
ws.Range(1, 1, currentRow - 1, totalCols).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, currentRow - 1, totalCols).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 1, totalCols).Style.Border.BottomBorder = XLBorderStyleValues.None;
ws.Range(2, 1, 2, totalCols).Style.Border.TopBorder = XLBorderStyleValues.None;
ws.Columns(1, totalCols).AdjustToContents();
}
public void WorkSheetExcelStyle(IXLWorksheet worksheet)
{
worksheet.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Rows().AdjustToContents();
worksheet.Columns().AdjustToContents();
worksheet.Style.Alignment.WrapText = true;
}
/// <summary>
/// Resultシートに情報を書き込む
/// </summary>
/// <param name="result">Resultシート</param>
public void setResult(IXLWorksheet result)
{
// タイトル
result.Cell(1, 1).SetValue(Text)
.Style.Font.SetFontSize(20.0)
.Font.SetBold();
// パレット情報
result.Cell(3, 1).SetValue("パレット")
.Style.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
.Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
.Border.SetInsideBorder(XLBorderStyleValues.Dashed)
.Fill.SetBackgroundColor(XLColor.LightGray);
result.Cell(3, 2).SetValue(PaletteName);
// ビーズ情報
result.Cell(5, 1).SetValue("No");
result.Cell(5, 2).SetValue("名前");
result.Cell(5, 3).SetValue("個数");
result.Range(5, 1, 5, 3).Style.Font.SetBold()
.Fill.SetBackgroundColor(XLColor.LightGray);
int i = 0;
Palette.Where((x) => x.Total > 0).ForEach((bead) =>
{
int row = 6 + i++;
result.Cell(row, 1).SetValue(bead.No)
.Style.Fill.SetBackgroundColor(bead.Color)
.Font.SetFontColor(bead.FontColor);
result.Cell(row, 2).SetValue(bead.Name);
result.Cell(row, 3).Value = bead.Total;
});
result.Range(result.Cell(5, 1), result.LastCellUsed())
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center)
.Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
.Border.SetInsideBorder(XLBorderStyleValues.Dashed);
// 行列幅
result.Columns(1, 1).Width = 16.43;
result.Columns(2, 2).Width = 23.57;
result.Rows(1, 1).Height = 26.25;
}
private static void setColumnWidths(IXLWorksheet ws)
{
ws.Columns().Width = 5.22;
ws.Column(1).Width = 6.89;
ws.Column(2).Width = 6.89;
ws.Column(3).Width = 6.89;
ws.Column(4).Width = 1.67;
}
public static double getTotalWidth(IXLWorksheet ws, int startCol)
{
var totalWidth = 0.0;
foreach (var col in ws.Columns(startCol, ws.LastColumnUsed().ColumnNumber()))
{
totalWidth += col.Width * 5.69;
}
return totalWidth;
}
/// <summary>
/// Create a worksheet
/// </summary>
/// <param name="worksheet">IXLWorksheet</param>
/// <param name="sheet">CExcelSheet</param>
private void CreateAWorkSheet(IXLWorksheet worksheet, CExcelSheet sheet)
{
//title
IXLAddress firstAdd = worksheet.Cell(1, COL_BEGIN).Address;
worksheet.Cell(1, 1).Value = sheet.Title;
worksheet.Cell(1, 1).Style.Font.Bold = true;
worksheet.Cell(1, 1).Style.Font.FontSize = 15;
worksheet.Cell(1, 1).Style.Font.FontColor = XLColor.White;
worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0x0066cc);
#region Export By
int rowIdx = ROW_BEGIN;
if (sheet.ExportBy != null)
{
foreach (var item in sheet.ExportBy)
{
// reset column
int colIdx = COL_BEGIN;
worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
worksheet.Cell(rowIdx, colIdx).Value = "'" + item.key;
worksheet.Range(
worksheet.Cell(rowIdx, colIdx).Address,
worksheet.Cell(rowIdx, colIdx + 2).Address).Merge();
colIdx += 3;
worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
worksheet.Cell(rowIdx, colIdx).Value = "'" + item.value;
// new row
rowIdx++;
}
}
#endregion
// Header
int col = COL_BEGIN;
#region Header
//Add No
if (sheet.IsRenderNo)
{
worksheet.Cell(rowIdx, col).Value = "No";
worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(rowIdx, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
col++;
}
foreach (string header in sheet.Header)
{
worksheet.Cell(rowIdx, col).Value = header;
worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
col++;
}
//Style for header
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.Bold = true;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.FontSize = 12;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
//merge title
IXLAddress secondAdd = worksheet.Cell(1, col - 1).Address;
worksheet.Range(firstAdd, secondAdd).Merge();
worksheet.Range(firstAdd, secondAdd).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// Merge export by
for (int i = ROW_BEGIN; i < rowIdx; i++)
{
worksheet.Range(
worksheet.Cell(i, 4).Address,
worksheet.Cell(i, col - 1).Address).Merge();
}
#endregion
// detail
// Comment when adding export by, rowIdx is the number of export by
//int idx_row = ROW_BEGIN + 1;
int idx_row = rowIdx + 1;
string preGroup = string.Empty;
ArrayList beginSubList = new ArrayList();
ArrayList endSubList = new ArrayList();
//linh.quang.le: Freeze panels
worksheet.SheetView.FreezeRows(sheet.FreezeRow);
worksheet.SheetView.FreezeColumns(sheet.FreezeColumn);
//linh.quang.le number
int no = 1;
#region Detail
foreach (Object row in sheet.List)
{
int idx_col = COL_BEGIN;
int index = 0;
bool hasMainColumnValue = HasMainColumnValue(row, sheet);
//linh.quang.le
#region GroupName
if (sheet.IsGroup)
{
string groupName = string.Empty;
groupName = row.GetType().GetProperty(sheet.GroupName).GetValue(row, null).ToString();
if (!String.IsNullOrEmpty(groupName) && preGroup != groupName)
{
if (beginSubList.Count != 0)
endSubList.Add(idx_row - 1);
worksheet.Cell(idx_row, COL_BEGIN).Value = groupName;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.Bold = true;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.FontSize = 12;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.FontColor = XLColor.Black;
worksheet.Cell(idx_row, COL_BEGIN).Style.Fill.BackgroundColor = XLColor.FromArgb(0xD9D9D9);
IXLAddress firstGroupAddr = worksheet.Cell(idx_row, COL_BEGIN).Address;
IXLAddress secondGroupAddr = worksheet.Cell(idx_row, (COL_BEGIN + (sheet.IsRenderNo ? sheet.ColumnList.Length : sheet.ColumnList.Length - 1))).Address;
worksheet.Range(firstGroupAddr, secondGroupAddr).Merge();
worksheet.Range(firstGroupAddr, secondGroupAddr).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
preGroup = groupName;
no = 1;
idx_row++;
beginSubList.Add(idx_row);
}
if (sheet.IsRenderNo && hasMainColumnValue)
{
worksheet.Cell(idx_row, idx_col).Value = no.ToString();
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
no++;
idx_col++;
}
}
else
{
if (sheet.IsRenderNo)
{
worksheet.Cell(idx_row, idx_col).Value = (idx_row - ROW_BEGIN).ToString();
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
idx_col++;
}
}
#endregion
if (sheet.IsGroup)
{
if (hasMainColumnValue)
{
foreach (string header in sheet.ColumnList)
{
string[] headerArr = header.Split(Convert.ToChar(":"));
Object obj = null;
if (row.GetType().GetProperty(headerArr[0].ToString()) != null)
obj = row.GetType().GetProperty(headerArr[0].ToString()).GetValue(row, null);
else
{
string[] arr = (string[])row;
if (arr != null)
obj = arr[index++];
}
string strValue = string.Empty;
strValue = obj == null ? "" : obj.ToString();
#region Format item
if (headerArr.Count() == 2)
{
switch (headerArr[1].ToString().ToLower())
{
case "text":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = "'" + strValue;
break;
case "date":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_VIEW);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_VIEW;
break;
case "datetime":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_TIME);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_TIME;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
break;
// Convert from Int of Hour and Minute to "Hour : Minute" string
// Using in Time Mangement Module
// @author : tai.pham
case "hour":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = (obj == null || obj == string.Empty) ? string.Empty : "'" + ConvertUtil.ConvertToDouble(obj).ToString("0#:##");
break;
// Convert from location code to location string
// Using in Time Mangement Module
// @author : tai.pham
case "location":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
strValue = obj == null ? "" : CommonFunc.GenerateStringOfLocation((string)obj);
break;
case "gender":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MALE ? "Male" : "Female";
break;
case "married":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MARRIED ? "Married" : "Single";
break;
case "labor":
strValue = obj == null ? "" : (bool)obj == Constants.LABOR_UNION_FALSE ? "No" : "Yes";
break;
case "hhmm":
worksheet.Cell(idx_row, idx_col).Style.NumberFormat.NumberFormatId = 20;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = (obj == null || obj == "") ? "" : CommonFunc.FormatTime((double)obj);
break;
case "jr":
strValue = obj == null ? "" : Constants.JOB_REQUEST_PREFIX + obj;
break;
case "pr":
strValue = obj == null ? "" : Constants.PR_REQUEST_PREFIX + obj;
break;
case "sr":
strValue = obj == null ? "" : Constants.SR_SERVICE_REQUEST_PREFIX + obj;
break;
case "candidate":
strValue = obj == null ? "" : CommonFunc.GetCandidateStatus((int)obj);
break;
case "actionsendmail":
strValue = obj == null ? "" : (bool)obj != true ? "No" : "Yes";
break;
case "jr_request":
strValue = obj == null ? "" : (int)obj == Constants.JR_REQUEST_TYPE_NEW ? "New" : "Replace";
break;
case "dayofweek":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).DayOfWeek.ToString();
break;
case "number":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case "duration":
strValue = obj == null ? "" : obj + " " + Constants.TC_DURATION_PREFIX;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
default:
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
}
worksheet.Cell(idx_row, idx_col).Value = strValue;
}
#endregion
else
{
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
worksheet.Cell(idx_row, idx_col).Value = "'" + strValue;
}
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
idx_col++;
}
worksheet.Columns(COL_BEGIN, idx_col).AdjustToContents();
idx_row++;
}
}
else
{
foreach (string header in sheet.ColumnList)
{
string[] headerArr = header.Split(Convert.ToChar(":"));
Object obj = null;
if (row.GetType().GetProperty(headerArr[0].ToString()) != null)
obj = row.GetType().GetProperty(headerArr[0].ToString()).GetValue(row, null);
else
{
string[] arr = (string[])row;
if (arr != null)
obj = arr[index++];
}
string strValue = string.Empty;
strValue = obj == null ? "" : obj.ToString();
#region Format item
if (headerArr.Count() == 2)
{
switch (headerArr[1].ToString().ToLower())
{
case "text":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = "'" + strValue;
break;
case "date":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_VIEW);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_VIEW;
break;
case "datetime":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_TIME);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_TIME;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
break;
case "gender":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MALE ? "Male" : "Female";
break;
case "married":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MARRIED ? "Married" : "Single";
break;
case "labor":
strValue = obj == null ? "" : (bool)obj == Constants.LABOR_UNION_FALSE ? "No" : "Yes";
break;
// Convert from Int of Hour and Minute to "Hour : Minute" string
// Using in Time Management Module
// @author : tai.pham
case "hour":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = (obj == null || obj == string.Empty) ? string.Empty : "'" + ConvertUtil.ConvertToDouble(obj).ToString("0#:##");
break;
// Convert from location code to location string
// Using in Time Mangement Module
// @author : tai.pham
case "location":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
strValue = obj == null ? "" : CommonFunc.GenerateStringOfLocation((string)obj);
break;
case "hhmm":
worksheet.Cell(idx_row, idx_col).Style.NumberFormat.NumberFormatId = 20;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = (obj == null || obj == "") ? "" : CommonFunc.FormatTime((double)obj);
break;
case "jr":
strValue = obj == null ? "" : Constants.JOB_REQUEST_PREFIX + obj;
break;
case "pr":
strValue = obj == null ? "" : Constants.PR_REQUEST_PREFIX + obj;
break;
case "sr":
strValue = obj == null ? "" : Constants.SR_SERVICE_REQUEST_PREFIX + obj;
break;
case "candidate":
strValue = obj == null ? "" : CommonFunc.GetCandidateStatus((int)obj);
break;
case "actionsendmail":
strValue = obj == null ? "" : (bool)obj != true ? "No" : "Yes";
break;
case "jr_request":
strValue = obj == null ? "" : (int)obj == Constants.JR_REQUEST_TYPE_NEW ? "New" : "Replace";
break;
case "dayofweek":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).DayOfWeek.ToString();
break;
case "number":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case "duration":
strValue = obj == null ? "" : obj + " " + Constants.TC_DURATION_PREFIX;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
default:
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
}
worksheet.Cell(idx_row, idx_col).Value = strValue;
}
#endregion
else
{
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
worksheet.Cell(idx_row, idx_col).Value = "'" + strValue;
}
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
idx_col++;
}
worksheet.Columns(COL_BEGIN, idx_col).AdjustToContents();
idx_row++;
}
}
if (sheet.IsGroup)
{
if (!String.IsNullOrEmpty(sheet.GroupName))
{
for (int i = 0; i < beginSubList.Count; i++)
{
worksheet.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;
if (i >= endSubList.Count)
worksheet.Rows((int)beginSubList[i], idx_row - 1).Group();
else
worksheet.Rows((int)beginSubList[i], (int)endSubList[i]).Group();
}
}
}
#endregion
#region Footer
if (sheet.Footer != null)
{
col = COL_BEGIN;
if (sheet.IsRenderNo)
{
worksheet.Cell(idx_row, col).Value = "Total";
worksheet.Cell(idx_row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
col++;
}
foreach (string footer in sheet.Footer)
{
worksheet.Cell(idx_row, col).Value = footer;
worksheet.Cell(idx_row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
col++;
}
//Style for footer
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Font.Bold = true;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Font.FontSize = 12;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
}
#endregion
}
// parameters:
// output_columns 输出列定义。如果为空，表示全部输出。这是一个数字列表，例如 "1,2,3,5"
// return:
// -1 出错
// 0 放弃或中断
// 1 成功
public static int ExportToExcel(
Stop stop,
ListView list,
List<string> output_columns,
List<ListViewItem> items,
IXLWorksheet sheet,
out string strError)
{
strError = "";
#if NO
if (items == null || items.Count == 0)
{
strError = "items == null || items.Count == 0";
return -1;
}
#endif
// ListView list = items[0].ListView;
if (stop != null)
stop.SetProgressRange(0, items.Count);
List<int> indices = new List<int>();
if (output_columns == null && output_columns.Count == 0)
{
int i = 0;
foreach (ColumnHeader header in list.Columns)
{
indices.Add(i++);
}
}
else
{
foreach (string s in output_columns)
{
int v = 0;
if (Int32.TryParse(s, out v) == false)
{
strError = "output_columns 数组中有非数字的字符串，格式错误";
return -1;
}
indices.Add(v - 1); // 从 0 开始计数
}
}
// 每个列的最大字符数
List<int> column_max_chars = new List<int>();
List<XLAlignmentHorizontalValues> alignments = new List<XLAlignmentHorizontalValues>();
//foreach (ColumnHeader header in list.Columns)
foreach (int index in indices)
{
ColumnHeader header = list.Columns[index];
if (header.TextAlign == HorizontalAlignment.Center)
alignments.Add(XLAlignmentHorizontalValues.Center);
else if (header.TextAlign == HorizontalAlignment.Right)
alignments.Add(XLAlignmentHorizontalValues.Right);
else
alignments.Add(XLAlignmentHorizontalValues.Left);
column_max_chars.Add(0);
}
string strFontName = list.Font.FontFamily.Name;
int nRowIndex = 1;
int nColIndex = 1;
// foreach (ColumnHeader header in list.Columns)
foreach (int index in indices)
{
ColumnHeader header = list.Columns[index];
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(header.Text);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontName = strFontName;
cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
}
nRowIndex++;
//if (stop != null)
// stop.SetMessage("");
foreach (ListViewItem item in items)
{
Application.DoEvents();
if (stop != null
&& stop.State != 0)
{
strError = "用户中断";
return 0;
}
// List<CellData> cells = new List<CellData>();
nColIndex = 1;
// foreach (ListViewItem.ListViewSubItem subitem in item.SubItems)
foreach (int index in indices)
{
string strText = "";
ListViewItem.ListViewSubItem subitem = null;
if (index < item.SubItems.Count)
{
subitem = item.SubItems[index];
strText = subitem.Text;
}
else
{
}
// 统计最大字符数
int nChars = column_max_chars[nColIndex - 1];
if (string.IsNullOrEmpty(strText) == false && strText.Length > nChars)
{
column_max_chars[nColIndex - 1] = strText.Length;
}
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(strText);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.FontName = strFontName;
cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
}
if (stop != null)
stop.SetProgressValue(nRowIndex - 1);
nRowIndex++;
}
if (stop != null)
stop.SetMessage("正在调整列宽度 ...");
Application.DoEvents();
double char_width = ClosedXmlUtil.GetAverageCharPixelWidth(list);
// 字符数太多的列不要做 width auto adjust
const int MAX_CHARS = 30; // 60
{
int i = 0;
foreach (IXLColumn column in sheet.Columns())
{
int nChars = column_max_chars[i];
if (nChars < MAX_CHARS)
column.AdjustToContents();
else
column.Width = (double)list.Columns[i].Width / char_width; // Math.Min(MAX_CHARS, nChars);
i++;
}
}
return 1;
}
private void AddPerformanceFormatting(IXLWorksheet performanceSheet)
{
int lastRowUsed = performanceSheet.LastRowUsed().RowNumber();
//freeze panels
performanceSheet.SheetView.Freeze(1, 2);
//performance global styles
performanceSheet.Range(1, 1, performanceSheet.LastCellUsed().Address.RowNumber, performanceSheet.LastCellUsed().Address.ColumnNumber)
.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
//column specific formatting
//date columns
performanceSheet.Range(string.Format("A2:A{0}", lastRowUsed)).Style.DateFormat.Format = "dd/MM/yyyy";
//percentage formatting (0.00%)
performanceSheet.Ranges(string.Format("E2:E{0},H2:I{1},Y2:Y{2},AC2:AC{3},AE2:AF{4},AH2:AI{5},AK2:AL{6},AN2:AO{7}",
lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.NumberFormatId = 10;
//no decimal points
performanceSheet.Range(string.Format("R2:R{0}", lastRowUsed)).Style.NumberFormat.Format = "0";
//decimal format (0.00)
performanceSheet.Ranges(string.Format("J2:J{0},L2:N{1}",
lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "0.00";
//three decimal points (0.000)
performanceSheet.Range(string.Format("U2:U{0}",
lastRowUsed
)).Style.NumberFormat.Format = "0.000";
//money with two decimals ($ 0.00)
performanceSheet.Ranges(string.Format("T2:T{0},W2:W{1},Z2:Z{2}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.00";
//money with three decimals ($ 0.000)
performanceSheet.Range(string.Format("V2:V{0}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.000";
// adjust to content
performanceSheet.Columns().AdjustToContents();
}