public static ExcelPackage DatatableToExcel(DataTable dt)
{
ExcelPackage result = null;
try
{
ExcelPackage pck = new ExcelPackage();
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Result");
ws.Cells["A1"].LoadFromDataTable(dt, true);
string celdaFinHeader = ws.Cells[1, dt.Columns.Count].Address;
using (ExcelRange rng = ws.Cells[string.Format("A1:{0}", celdaFinHeader)])
{
rng.AutoFitColumns();
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
ws.Cells.AutoFitColumns();
result = pck;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(result);
}
/// <summary>
/// Apply formatting to the data section of the report. Subclasses
/// can override this in order to apply non-default formatting.
/// However, this can also be done by means of the ColumnInfoArray -- if
/// this array is provided, its contents will be used for per-column
/// formatting.
///
/// Note that this.sh.Cell uses one-based indexing.
///
/// </summary>
protected virtual void FormatDataCells()
{
int bottom_row = this.TopDataRow + this.DataRows.Count - 1;
// Define range on the data section:
ExcelRange r = this.sh.Cells[this.TopDataRow, 1, bottom_row, this.ColumnNames.Length];
// Apply default formatting to the range:
r.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
r.AutoFitColumns();
// Apply grid around each cell in range:
foreach (var c in r)
{
c.Style.Border.BorderAround(ExcelBorderStyle.Hair);
}
// Per-column formatting supplied?
if (this.ColumnInfoList != null)
{
for (int i = 0; i < this.ColumnInfoList.Count; i++)
{
ColumnInfo ci = this.ColumnInfoList[i];
// Define range on this column:
r = this.sh.Cells[this.TopDataRow, i + 1,
bottom_row, i + 1];
// Apply values from ColumnInfo object to the range:
r.Style.HorizontalAlignment = ci.HorizAlignment;
r.Style.VerticalAlignment = ci.VerticalAlignment;
r.Style.WrapText = ci.Wraps;
if (ci.Width != -1)
{
this.sh.Column(i + 1).Width = ci.Width;
}
}
}
}
public static void TestWriteExcel()
{
string xlsxPath = Application.dataPath + "/../Data/Excel/G怪物.xlsx";
FileInfo xlsxFile = new FileInfo(xlsxPath);
if (xlsxFile.Exists)
{
xlsxFile.Delete();
xlsxFile = new FileInfo(xlsxPath);
}
using (ExcelPackage package = new ExcelPackage(xlsxFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("怪物配置");
//worksheet.DefaultColWidth = 10;//sheet页面默认行宽度
//worksheet.DefaultRowHeight = 30;//sheet页面默认列高度
//worksheet.Cells.Style.WrapText = true;//设置所有单元格的自动换行
//worksheet.InsertColumn()//插入行 从某一行开始插入多少行
//worksheet.InsertRow() //插入列 从某一列开始插入多少列
//worksheet.DeleteColumn() //删除行 从某一行开始删除多少行
//worksheet.DeleteRow() //删除列 从某一列开始删除多少列
//worksheet.Column(1).Width
//worksheet.Column(1).Hidden
ExcelRange range = worksheet.Cells[1, 1];
range.Value = "测试2222222222\n2222222222222222";
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.None;
//range.Style.Fill.BackgroundColor.SetColor
//range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//对齐方式
range.AutoFitColumns(); //自适应宽度
range.Style.WrapText = true; //自动换行
package.Save();
}
}
public static void TestXlsx()
{
string xlsxPath = Application.dataPath + "/../ExcelData/Excel/G_怪物.xlsx";
FileInfo fileInfo = new FileInfo(xlsxPath);
//重新生成
if (fileInfo.Exists)
{
fileInfo.Delete();
fileInfo = new FileInfo(xlsxPath);
}
//需要引入dll
using (ExcelPackage package = new ExcelPackage(fileInfo)){
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("怪物配置");
//sheet默认宽 高
// sheet.DefaultColWidth = 30;
// sheet.DefaultRowHeight = 30;
// sheet.Cells.Style.WrapText = true;
// sheet.TabColor
ExcelRange range = sheet.Cells[1, 1];
range.Value = "AAA\nAAA";
//自动适应宽
range.AutoFitColumns();
//自动换行
range.Style.WrapText = true;
package.Save();
}
}
private static void CreateCell(ExcelRange cell, object value, ExcelHorizontalAlignment align, bool merge, Color?background,
Color?color, float?size, string font, bool bold = false, bool wrapText = false, bool autoFitColumn = true)
{
cell.Merge = merge;
cell.Value = value;
cell.Style.HorizontalAlignment = align;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
cell.Style.Font.Bold = bold;
cell.Style.Font.Name = font;
cell.Style.WrapText = wrapText;
if (background != null)
{
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(background.Value);
}
if (color != null)
{
cell.Style.Font.Color.SetColor(color.Value);
}
if (size.HasValue)
{
cell.Style.Font.Size = size.Value;
}
else
{
cell.Style.Font.Size = 10;
}
if (autoFitColumn)
{
cell.AutoFitColumns();
}
}
private static ExcelPackage writeToSheet(ExcelPackage package, DataTable dt)
{
ExcelWorksheet sheet = package.Workbook.Worksheets.Add(dt.TableName);
int rows = dt.Rows.Count;
int cols = dt.Columns.Count;
for (int i = 1; i <= rows; i++)
{
DataRow dr = dt.Rows[i - 1];
for (int j = 1; j <= cols; j++)
{
//sheet.Cells[i, j].Style.Numberformat.Format = ((dynamic)dr[j - 1])["format"];
sheet.Cells[i, j].Value = ((dynamic)dr[j - 1])["text"];
if (!string.IsNullOrWhiteSpace(((dynamic)dr[j - 1])["color"]))
{
Color color = System.Drawing.ColorTranslator.FromHtml("#" + ((dynamic)dr[j - 1])["color"]);
sheet.Cells[i, j].Style.Font.Color.SetColor(color);
}
}
}
ExcelRange r = sheet.Cells[1, 1, cols, rows];
r.AutoFitColumns();
return(package);
}
public byte[] Export(List <object[]> dt)
{
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Libro1");
ws.Cells["A1"].LoadFromArrays(dt);
using (ExcelRange col = ws.Cells[2, 5, dt.Count, dt[0].Length])
{
col.Style.Numberformat.Format = "0";
col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
using (ExcelRange col = ws.Cells[1, 1, dt.Count, dt[0].Length])
{
col.AutoFitColumns();
}
this.bytes = pck.GetAsByteArray();
}
return(this.bytes);
}
public static Task GenerateXLSRegistrationRegisted(List <RegistrationInterview> registrationInterviews, string filePath)
{
return(Task.Run(() =>
{
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(DateTime.Now.Date.ToString());
ws.Cells[2, 1].Value = "DANH SÁCH ỨNG VIÊN ĐÃ ĐĂNG KÍ HỒ SƠ";
ws.Cells["A2:I2"].Merge = true;
ws.Cells[3, 1].Value = registrationInterviews.ElementAt(0).ManagementUnit.FullName;
ws.Cells["A3:I3"].Merge = true;
ws.Cells[4, 1].Value = "Tính tới ngày " + DateTime.Now.Day + "/" + DateTime.Now.Month + "/" + DateTime.Now.Year;
ws.Cells["A4:I4"].Merge = true;
ws.Cells[6, 1].Value = "STT";
ws.Cells[6, 2].Value = "HỌ VÀ TÊN LÓT";
ws.Cells[6, 3].Value = "TÊN";
for (int i = 0; i < registrationInterviews.Count(); i++)
{
ws.Cells[i + 7, 1].Value = i + 1;
ws.Cells[i + 7, 2].Value = registrationInterviews.ElementAt(i).CandidateLastName;
ws.Cells[i + 7, 3].Value = registrationInterviews.ElementAt(i).CandidateFirstName;
}
using (ExcelRange rng = ws.Cells["A2:I2"])
{
rng.Style.Font.Bold = true;
rng.Style.Font.Size = 18;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Font.Color.SetColor(Color.Red);
}
using (ExcelRange rng = ws.Cells["A3:I3"])
{
rng.Style.Font.Bold = true;
rng.Style.Font.Size = 14;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Font.Color.SetColor(Color.Red);
}
using (ExcelRange rng = ws.Cells["A4:I4"])
{
rng.Style.Font.Bold = true;
rng.Style.Font.Size = 14;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Font.Color.SetColor(Color.Red);
}
using (ExcelRange rng = ws.Cells["A6:C6"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.SkyBlue); //Set color to blue
rng.Style.Font.Color.SetColor(Color.Black);
rng.AutoFitColumns();
}
pck.SaveAs(new FileInfo(filePath));
}
}));
}
/// <summary>
/// This method get IEnumerable list of any object and returns excel file as byte[]
/// </summary>
/// <param name="report">IEnumerable list of any object</param>
/// <param name="sheetName">Name of worksheet that you want to show in excel</param>
/// <returns>byte[] of excel file</returns>
public static byte[] ToExcel(this IEnumerable <object> report, string sheetName = "Sheet1")
{
//Set ExcelPackage License
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
//populate our Data
if (report.Any())
{
for (int row = 0; row < report.Count(); row++)
{
//Take each property as a column
var column = 1;
foreach (PropertyInfo prop in report
.FirstOrDefault()
.GetType()
.GetProperties())
{
//Set Header
if (row == 0)
{
ws.Cells[1, column].Value = prop.Name.ToUpper();
}
//Set Value
var arrayOfReport = report.ToArray();
ws.Cells[row + 2, column].Value = prop.GetValue(arrayOfReport[row], null);
column++;
}
}
//create a range for the table
ExcelRange range = ws.Cells[1, 1, ws.Dimension.End.Row, ws.Dimension.End.Column];
//Set Alignments
range.AutoFitColumns();
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//add a table to the range
ExcelTable tab = ws.Tables.Add(range, "Table1");
//format the table
tab.TableStyle = TableStyles.Medium2;
}
return(pck.GetAsByteArray());
}
}
public static bool SaveDifrencesInExcel(string filename1, string filename2)
{
List <String> linesFromFile1 = ReadFileLines(CMD.CMDTargetFolderPath + "\\" + filename1);
List <String> linesFromFile2 = ReadFileLines(CMD.CMDTargetFolderPath + "\\" + filename2);
//String header = "";
bool filesAreTheSame = true;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet ws1 = package.Workbook.Worksheets.Add(filename1);
ExcelWorksheet ws2 = package.Workbook.Worksheets.Add(filename2);
//addHeaderRow(ExcelHeaderFooter, ws1, ws2);
int row = 2;
for (int i = 0; i < linesFromFile1.Count; i++)
{
if (linesFromFile1[i] != linesFromFile2[i])
{
int column = 1;
var values1 = linesFromFile1[i].Split('|');
var values2 = linesFromFile2[i].Split('|');
filesAreTheSame = false;
for (int j = 0; j < values1.Count(); j++)
{
ExcelRange cell1 = ws1.Cells[row, column];
cell1.Value = values1[j];
ExcelRange cell2 = ws2.Cells[row, column];
cell2.Value = values2[j];
if (values1[j] != values2[j])
{
cell1.Style.Fill.PatternType = ExcelFillStyle.Solid;
cell1.Style.Fill.BackgroundColor.SetColor(Color.Tomato);
cell2.Style.Fill.PatternType = ExcelFillStyle.Solid;
cell2.Style.Fill.BackgroundColor.SetColor(Color.Tomato);
}
column++;
}
row++;
}
}
if (filesAreTheSame != true)
{
ExcelRange range1 = ws1.Cells[ws1.Dimension.Address];
range1.AutoFitColumns();
ExcelRange range2 = ws2.Cells[ws2.Dimension.Address];
range2.AutoFitColumns();
String path = CMD.CMDTargetFolderPath + "\\" + "CompareResultsOfTwoFlatFiles" + "_" + DateTime.Now.ToString("yyyyMMdd_HH-mm") + ".xlsx";
package.SaveAs(new FileInfo(path));
//OR
//FileInfo fInf = new FileInfo(path);
//package.SaveAs(fInf);
}
}
return(filesAreTheSame);
}
protected void PrepareTitle(int row, string title, ExcelWorksheet excelWorksheet) // A1
{
CheckDict();
excelWorksheet.Cells["A" + row].Value = title;
using (ExcelRange r = excelWorksheet.Cells[row, 1, row, properties.Count])
{
r.AutoFitColumns();
r.Merge = true;
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="headDict"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static byte[] ExportExcel <T>(List <T> data, Dictionary <string, string> headDict, string sheetName = "", bool showSrNo = false)
{
DataTable dt = ListToDataTable <T>(data);
byte[] result = null;
List <string> keyList = new List <string>();
if (showSrNo)
{
keyList.Add("RowNum");
dt.Columns.Add("RowNum");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["RowNum"] = i + 1;
}
}
//通过键的集合取
foreach (string key in headDict.Keys)
{
keyList.Add(key);
}
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet sheet = package.Workbook.Worksheets.Add(sheetName.IsNullOrEmpty() ? "Sheet1" : sheetName);
if (showSrNo)
{
headDict.Add("RowNum", "序号");
}
for (int i = 0; i < keyList.Count; i++)
{
sheet.Cells[1, i + 1].Value = headDict[keyList[i]];
}
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < keyList.Count; j++)
{
sheet.Cells[i + 2, j + 1].Value = dt.Rows[i][keyList[j]].ToString();
}
}
}
ExcelRange cells = sheet.Cells[1, 1, 1 + dt.Rows.Count, keyList.Count];
cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中
cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中
cells.AutoFitColumns(); //自适应列宽
result = package.GetAsByteArray();
}
return(result);
}
protected void SetStyleCellXLS(ExcelRange celda, int indice)
{
celda.Style.Fill.PatternType = ExcelFillStyle.None;
// If indice Mod 2 = 0 Then
celda.Style.Font.Bold = false;
celda.Style.ShrinkToFit = false;
celda.Style.Numberformat = null;
if (indice != 5 & indice != 9 & indice != 12 & indice != 17 & indice != 18 & indice != 19)
{
celda.AutoFitColumns();
}
}
public ExcelPackage CreateCommonReport(List <ReportItemDto> reportItems)
{
var package = new ExcelPackage();
package.Workbook.Properties.Title = "Клиника \"СИРИУС-ВЕТ\"";
package.Workbook.Properties.Author = "";
package.Workbook.Properties.Subject = "Отчет по остаткам";
package.Workbook.Properties.Keywords = "";
var worksheet = package.Workbook.Worksheets.Add("Остатки");
//First add the headers
worksheet.Cells[1, 1].Value = "Наименование";
worksheet.Cells[1, 2].Value = "Приход";
worksheet.Cells[1, 3].Value = "Расход";
worksheet.Cells[1, 4].Value = "Остаток";
worksheet.Cells[1, 5].Value = "Ед. изм.";
//Add values
var dataCellStyleName = "TableNumber";
var numStyle = package.Workbook.Styles.CreateNamedStyle(dataCellStyleName);
var colNumb = 2;
reportItems.ForEach(item =>
{
worksheet.Cells[colNumb, 1].Value = item.Name;
worksheet.Cells[colNumb, 2].Value = item.Incoming;
worksheet.Cells[colNumb, 3].Value = item.Consumption;
worksheet.Cells[colNumb, 4].Value = item.Total;
worksheet.Cells[colNumb, 5].Value = item.Dimension;
worksheet.Cells[colNumb, 2].Style.Numberformat.Format = setAmountNumberFormat(item.Incoming);
worksheet.Cells[colNumb, 3].Style.Numberformat.Format = setAmountNumberFormat(item.Consumption);
worksheet.Cells[colNumb, 4].Style.Numberformat.Format = setAmountNumberFormat(item.Total);
colNumb++;
});
using (ExcelRange Rng = worksheet.Cells[1, 1, 1, 5])
{
Rng.Style.Font.Size = 18;
Rng.Style.Font.Bold = true;
Rng.AutoFitColumns();
}
using (ExcelRange Rng = worksheet.Cells[2, 1, colNumb, 5])
{
Rng.Style.Font.Size = 14;
Rng.AutoFitColumns();
}
return(package);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="ePPlus"></param>
/// <returns></returns>
public static MemoryStream Export(ICollection <EPPlus> ePPlus)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//EPPlus 5.0版本以上要收费了,所以要加这句代码表示非商用。https://epplussoftware.com/developers/licenseexception
MemoryStream stream = new MemoryStream();
ExcelPackage package = new ExcelPackage(stream);
package.Workbook.Worksheets.Add("EPPlus");
ExcelWorksheet sheet = package.Workbook.Worksheets[0];
#region write header
sheet.Cells[1, 1].Value = "用户名";
sheet.Cells[1, 2].Value = "年龄";
sheet.Cells[1, 3].Value = "性别";
sheet.Cells[1, 4].Value = "成绩";
using (ExcelRange range = sheet.Cells[1, 1, 1, 4])
{
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
range.Style.Border.Bottom.Color.SetColor(Color.Black);
range.AutoFitColumns(4);
}
#endregion
#region write content
int pos = 2;
foreach (EPPlus s in ePPlus)
{
sheet.Cells[pos, 1].Value = s.Name;
sheet.Cells[pos, 2].Value = s.Age;
sheet.Cells[pos, 3].Value = s.Gender;
sheet.Cells[pos, 4].Value = s.Achievement;
using (ExcelRange range = sheet.Cells[pos, 1, pos, 4])
{
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Color.SetColor(Color.Black);
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
}
pos++;
}
#endregion
package.Save();
return(stream);
}
public static void ApplyFormattingToARangeByDataType(ExcelRange oRange, DataColumn oDC)
{
if (IsDate(oDC))
{
oRange.Style.Numberformat.Format = @"dd/mm/yyyy hh:mm:ss AM/PM";
}
else if (IsInteger(oDC))
{
//Do Nothing
}
else if (IsNumeric(oDC))
{
oRange.Style.Numberformat.Format = @"#.##";
}
oRange.AutoFitColumns();
}
public static ExcelPackage ListsToExcel <T>(List <T> qry, string namePage, ref ExcelPackage excel)
{
ExcelPackage result = null;
try
{
ExcelPackage pck = excel ?? new ExcelPackage();
if (qry.Any())
{
string celdaFinHeader = null;
ExcelWorksheet workSheet = pck.Workbook.Worksheets.Add(namePage);
//get our column headings
var t = typeof(T);
var Headings = t.GetProperties();
for (int i = 0; i < Headings.Count(); i++)
{
workSheet.Cells[1, i + 1].Value = Headings[i].Name;
celdaFinHeader = workSheet.Cells[1, i + 1].Address;
}
//populate our Data
if (qry.Any())
{
workSheet.Cells["A2"].LoadFromCollection(qry);
}
using (ExcelRange rng = workSheet.Cells[string.Format("A1:{0}", celdaFinHeader)])
{
rng.AutoFitColumns();
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
rng.Style.Font.Color.SetColor(Color.White);
}
workSheet.Cells.AutoFitColumns();
}
result = pck;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
excel = result;
return(result);
}
public void Export(string fileAddress, IList <LoadFile> students)
{
using (FileStream fs = new FileStream(fileAddress, FileMode.Create, FileAccess.Write))
{
ExcelPackage package = new ExcelPackage(fs);
package.Workbook.Worksheets.Add("Students");
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
#region write header
sheet.Cells[1, 1].Value = "文档名";
sheet.Cells[1, 2].Value = "总大小";
sheet.Cells[1, 3].Value = "总完成";
sheet.Cells[1, 4].Value = "进度";
sheet.Cells[1, 5].Value = "状态";
sheet.Cells[1, 6].Value = "压缩密码";
sheet.Cells[1, 7].Value = "密码地址";
sheet.Cells[1, 8].Value = "价格(分)";
sheet.Cells[1, 9].Value = "物理路径";
using (ExcelRange range = sheet.Cells[1, 1, 1, 9])
{
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
range.Style.Border.Bottom.Color.SetColor(Color.Black);
range.AutoFitColumns(4);
}
#endregion
#region write content
int pos = 2;
foreach (LoadFile s in students)
{
sheet.Cells[pos, 1].Value = s.FileName;
sheet.Cells[pos, 2].Value = s.Size;
sheet.Cells[pos, 3].Value = s.SizeInfo;
sheet.Cells[pos, 4].Value = s.Progress;
sheet.Cells[pos, 5].Value = s.Status;
sheet.Cells[pos, 6].Value = s.Pwd;
sheet.Cells[pos, 7].Value = s.PwdFileUrl;
sheet.Cells[pos, 8].Value = s.Price;
sheet.Cells[pos, 9].Value = s.Address;
pos++;
}
package.Save();
#endregion
fs.Close();
}
}
public void ExportDataToExcelUsingEPPlus(ICollection <IServiceModel> dataFromDb, string filePath, string fileName)
{
try
{
using (ExcelPackage excel = new ExcelPackage())
{
var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
workSheet.Cells[1, 1].LoadFromCollection(dataFromDb, true);
//find number of rows loaded and use it in defining the range of data
var numberOfRows = dataFromDb.Count;
using (ExcelRange dataRange = workSheet.Cells["A1:G" + (numberOfRows + 3)])
{
dataRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
dataRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
dataRange.Style.Numberformat.Format = "#,##0";
}
using (ExcelRange dateRange = workSheet.Cells["B2:B" + (numberOfRows + 1)])
{
dateRange.Style.Numberformat.Format = "DD.MM.YYYY";
}
using (ExcelRange sumRange = workSheet.Cells["F" + (numberOfRows + 3)])
{
sumRange.Formula = "=SUM(" + workSheet.Cells[2, 6].Address + ":" + workSheet.Cells[(numberOfRows + 1), 6].Address + ")";
sumRange.Style.Font.Bold = true;
}
FileStream objFileStrm = File.Create(fileName);
using (ExcelRange dataRangeFilled = workSheet.Cells["A:G"])
{
dataRangeFilled.AutoFitColumns();
}
objFileStrm.Close();
File.WriteAllBytes(filePath + fileName, excel.GetAsByteArray());
}
}
catch (Exception)
{
throw;
}
}
public static void WriteDataTableToXLS(DataTable data, string filePath)
{
filePath = filePath + "_" + DateTime.Now.ToString("yyyyMMdd_HH-mm") + ".xlsx";
ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(filePath));
ExcelWorksheet ws = package.Workbook.Worksheets.Add("fact");
//LoadFrom DataTable/Text/DataReader
ws.Cells[1, 1].LoadFromDataTable(data, true, TableStyles.Medium15);
ExcelRange range = ws.Cells[ws.Dimension.Address];
//var table = ws.Tables[0].TableXml.DocumentElement;
//table.Attributes["ref"].Value = range.Address;
range.AutoFitColumns();
package.Save();
}
public static void TestWriteExcel()
{
string xlsxPath = Application.dataPath + "/../Data/Excel/G怪物.xlsx";
FileInfo xlsxFile = new FileInfo(xlsxPath);
if (xlsxFile.Exists)
{
xlsxFile.Delete();
xlsxFile = new FileInfo(xlsxPath);
}
using (ExcelPackage package = new ExcelPackage(xlsxFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("怪物配置");
//worksheet.DefaultColWidth = 10;//sheet页面默认行宽度
//worksheet.DefaultRowHeight = 30;//sheet页面默认列高度
//worksheet.Cells.Style.WrapText = true;//设置所有单元格的自动换行
//worksheet.InsertColumn();//插入行，从某一行开始插入多少行
//worksheet.InsertRow();//插入列，从某一列开始插入多少列
//worksheet.DeleteColumn();//删除行，从某一行开始删除多少行
//worksheet.DeleteRow();//删除列，从某一列开始删除多少列
//worksheet.Column(1).Width = 10;//设定第几行宽度
//worksheet.Row(1).Height = 30;//设定第几列高度
//worksheet.Column(1).Hidden = true;//设定第几行隐藏
//worksheet.Row(1).Hidden = true;//设定第几列隐藏
//worksheet.Column(1).Style.Locked = true;//设定第几行锁定
//worksheet.Row(1).Style.Locked = true;//设定第几列锁定
//worksheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//设定所有单元格对齐方式
worksheet.Cells.AutoFitColumns();
ExcelRange range = worksheet.Cells[1, 1];
range.Value = " 测试sadddddddddddddd\ndddddddddddddddddddasda";
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.None;
//range.Style.Fill.BackgroundColor.SetColor();//设置单元格内背景颜色
//range.Style.Font.Color.SetColor();//设置单元格内字体颜色
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//对齐方式
range.AutoFitColumns();
range.Style.WrapText = true;
ExcelRange range1 = worksheet.Cells[1, 2];
range1.Value = " Siki学院Ocean老师!!!!!!";
package.Save();
}
}
private static void CreateCell(ExcelRange cell, object value, CustomExcelStyle excelStyle)
{
cell.Merge = excelStyle.IsMerge;
cell.Value = value;
cell.Style.Font.Name = "Arial";
cell.Style.Font.Bold = excelStyle.IsBold;
cell.Style.Font.Italic = excelStyle.IsItalic;
cell.Style.Font.UnderLine = excelStyle.IsUnderLine;
cell.Style.Font.Size = excelStyle.FontSize.HasValue ? excelStyle.FontSize.Value : (float)10;
if (!string.IsNullOrWhiteSpace(excelStyle.Formula))
{
cell.Formula = excelStyle.Formula;
}
if (excelStyle.Color.HasValue)
{
cell.Style.Font.Color.SetColor(excelStyle.Color.Value);
}
if (excelStyle.BackgroundColor.HasValue)
{
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(excelStyle.BackgroundColor.Value);
}
cell.Style.HorizontalAlignment = excelStyle.HorizontalAlign.HasValue ? excelStyle.HorizontalAlign.Value : ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = excelStyle.VerticleAlign.HasValue ? excelStyle.VerticleAlign.Value : ExcelVerticalAlignment.Center;
if (excelStyle.IsWrapText.HasValue)
{
cell.Style.WrapText = excelStyle.IsWrapText.Value;
}
if (!excelStyle.AutoFit.HasValue || excelStyle.AutoFit.Value)
{
cell.AutoFitColumns();
}
if (!string.IsNullOrEmpty(excelStyle.NumberFormat))
{
cell.Style.Numberformat.Format = excelStyle.NumberFormat;
}
cell.Calculate();
SetBorder(excelStyle, cell);
}
private static ExcelPackage GenerateExcelFile(IEnumerable <FinalShift> datasource)
{
ExcelPackage pck = new ExcelPackage();
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(DateTime.Now.ToString("dd/MM/yyyy"));
// Sets Headers
for (int i = 1, j = 1; i < 2; i++)
{
foreach (var item in datasource.ElementAt(i).GetType().GetProperties())
{
if (item.Name == "ID" || item.Name == "EmployID" || item.Name == "Employees" || item.Name == "OfDayType")
{
continue;
}
ws.Cells[i, j++].Value = item.Name;
}
}
for (int i = 0, j = 1; i < datasource.Count(); i++, j = 1)
{
ws.Cells[i + 2, j++].Value = datasource.ElementAt(i).Name;
ws.Cells[i + 2, j++].Value = datasource.ElementAt(i).Day;
ws.Cells[i + 2, j++].Value = datasource.ElementAt(i).Morning;
ws.Cells[i + 2, j++].Value = datasource.ElementAt(i).Afternoon;
ws.Cells[i + 2, j++].Value = datasource.ElementAt(i).Night;
ws.Cells[i + 2, j++].Value = datasource.ElementAt(i).Dates.ToString("dd/MM/yyyy");
}
// Format Header of Table
using (ExcelRange rng = ws.Cells["A1:M1"])
{
rng.Style.Font.Bold = true;
}
using (ExcelRange rng = ws.Cells)
{
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.AutoFitColumns();
}
return(pck);
}
private void AddSessionBestInfo(SessionSummary sessionSummary, ExcelWorksheet sheet)
{
ExcelRange range = sheet.Cells["K5:L9"];
range.Style.Border.BorderAround(ExcelBorderStyle.Medium, SessionBestColor.ToDrawingColor());
sheet.Cells["K5"].Value = "Session Best:";
sheet.Cells["K6"].Value = "Sector 1:";
sheet.Cells["K7"].Value = "Sector 2:";
sheet.Cells["K8"].Value = "Sector 3:";
sheet.Cells["K9"].Value = "Lap:";
if (sessionSummary.SessionBestSector1 != null)
{
sheet.Cells["L6"].Value = FormatSessionBest(
sessionSummary.SessionBestSector1,
sessionSummary.SessionBestSector1.Sector1);
}
if (sessionSummary.SessionBestSector2 != null)
{
sheet.Cells["L7"].Value = FormatSessionBest(
sessionSummary.SessionBestSector2,
sessionSummary.SessionBestSector2.Sector2);
}
if (sessionSummary.SessionBestSector3 != null)
{
sheet.Cells["L8"].Value = FormatSessionBest(
sessionSummary.SessionBestSector3,
sessionSummary.SessionBestSector3.Sector3);
}
if (sessionSummary.SessionBestLap != null)
{
sheet.Cells["L9"].Value = FormatSessionBest(
sessionSummary.SessionBestLap,
sessionSummary.SessionBestLap.LapTime);
}
sheet.Cells["K5:K9"].Style.Font.Bold = true;
sheet.Cells["L5:L9"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
range.AutoFitColumns();
}
public async Task WriteFileAsync <T>(FileInfo file, IList <T> data, string sheetName = "Sheet1", bool printHeaders = true)
{
// We create a package variable which is a wrapper to working with excel files.
using ExcelPackage package = new(file);
// Adds a worksheet to the excel file
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);
// Select a range to put the data into
ExcelRange cellsRange = worksheet.Cells["A1"];
// put data into cells range
cellsRange.LoadFromCollection(data, printHeaders);
// auto fit columns
cellsRange.AutoFitColumns();
// Save the changes in the excel file
await package.SaveAsync();
}
public static void WriteExcel()
{
string path = PathConfig.OuterDataExcelPath + "怪物配置.xlsx";
FileInfo fileInfo = new FileInfo(path);
if (File.Exists(path))
{
File.Delete(path);
fileInfo = new FileInfo(path);
}
using (ExcelPackage package = new ExcelPackage(fileInfo))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("怪物");
//workSheet.DefaultColWidth = 10; //默认行宽
//workSheet.DefaultRowHeight = 10; //默认列高
//workSheet.Cells.Style.WrapText = true; //设置所有单元格的自动换行
//workSheet.InsertColumn() //插入行
//workSheet.InsertRow() //插入列
//workSheet.DeleteColumn() //删除行
//workSheet.DeleteRow() //删除列
//workSheet.Column(1).Width = 10; //某一行的宽
//workSheet.Row(1).Height = 10; //某一列的高度
//workSheet.Column(1).Hidden = true; //某一行是否隐藏
//workSheet.Row(1).Hidden = true; //某一列是否隐藏
//workSheet.Column(1).Style.Locked = true; //某一行是否锁定
//设置所有单元格的对齐方式
//workSheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ExcelRange range = workSheet.Cells[1, 1];
range.Value = "test excel";
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.LightUp;
//range.Style.Fill.BackgroundColor.SetColor() //
//range.Style.Font.Color.SetColor()
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
range.AutoFitColumns();
range.Style.WrapText = true;
package.Save();
}
}
public void AutoFitColumns()
{
_excelRange.AutoFitColumns();
}
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elementSet)
{
Document doc = commandData.Application.ActiveUIDocument.Document;
List <string> liste = new List <string>();
foreach (RevitLinkInstance instance in new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_RvtLinks).WhereElementIsNotElementType().ToElements().Cast <RevitLinkInstance>())
{
try
{
List <Room> rooms = new FilteredElementCollector(instance.GetLinkDocument()).OfClass(typeof(SpatialElement)).OfCategory(BuiltInCategory.OST_Rooms).Cast <Room>().ToList();
if (rooms.Count != 0)
{
string RVT_link = instance.GetLinkDocument().PathName;
liste.Add(RVT_link);
}
}
catch
{
}
}
if (liste.Count == 0)
{
TaskDialog td = new TaskDialog("Avertissement");
td.Title = "Avertissement";
td.MainIcon = TaskDialogIcon.TaskDialogIconWarning;
td.MainContent = "Aucun lien Revit contenant des pièces n'est chargé dans le projet actif.";
td.Show();
return(Result.Cancelled);
}
Space_Analyse_Form form = new Space_Analyse_Form(doc);
if (form.DialogResult == System.Windows.Forms.DialogResult.Cancel)
{
return(Result.Cancelled);
}
if (DialogResult.OK == form.ShowDialog())
{
List <Level> levels = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Levels).WhereElementIsNotElementType().ToElements().Cast <Level>().ToList();
using (Transaction t = new Transaction(doc, "Analyser les espaces"))
{
t.Start();
string test = "";
string test2 = "";
string filename = form.GetSelectedRVTlink();
List <Room> roomList = new List <Room>();
List <ElementId> roomId = new List <ElementId>();
List <string> roomNamelist = new List <string>();
List <List <string> > master_list = new List <List <string> >();
RevitLinkInstance link = (from element in new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_RvtLinks).WhereElementIsNotElementType().ToElements().Cast <RevitLinkInstance>()
where Path.GetFileName(element.GetLinkDocument().PathName).Contains(filename)
select element).ToList().First();
Document linkDoc = link.GetLinkDocument();
foreach (Room r in new FilteredElementCollector(linkDoc).OfClass(typeof(SpatialElement)).OfCategory(BuiltInCategory.OST_Rooms).Cast <Room>())
{
if (DistinguishRoom(r) != RoomState.NotPlaced)
{
roomList.Add(r);
roomId.Add(r.Id);
roomNamelist.Add(r.Name);
}
}
List <Room> associatedroomList = new List <Room>();
List <string> associatedroomNameList = new List <string>();
List <ElementId> associatedroomList_Id = new List <ElementId>();
List <string> spaceNamelist = new List <string>();
foreach (Autodesk.Revit.DB.Mechanical.Space s in new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_MEPSpaces).Cast <Autodesk.Revit.DB.Mechanical.Space>())
{
spaceNamelist.Add(s.Name);
}
foreach (Autodesk.Revit.DB.Mechanical.Space s in new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_MEPSpaces).Cast <Autodesk.Revit.DB.Mechanical.Space>())
{
Parameter spaceName = s.get_Parameter(BuiltInParameter.ROOM_NAME);
Parameter spaceNumber = s.get_Parameter(BuiltInParameter.ROOM_NUMBER);
Parameter roomName = s.get_Parameter(BuiltInParameter.SPACE_ASSOC_ROOM_NAME);
Parameter roomNumber = s.get_Parameter(BuiltInParameter.SPACE_ASSOC_ROOM_NUMBER);
Room associated_room;
try
{
associated_room = (from element in new FilteredElementCollector(linkDoc).OfClass(typeof(SpatialElement)).OfCategory(BuiltInCategory.OST_Rooms)
where element.Name == roomName.AsString() + " " + roomNumber.AsString()
select element).Cast <Room>().ToList().First();
associatedroomList.Add(associated_room);
associatedroomNameList.Add(associated_room.Name);
associatedroomList_Id.Add(associated_room.Id);
}
catch
{
associated_room = null;
}
string space = s.Id.ToString() + " " + spaceName.AsString() + " " + spaceNumber.AsString();
bool existing_room = false;
if (roomNamelist.Contains(spaceName.AsString() + " " + spaceNumber.AsString()))
{
existing_room = true;
}
string comment = "";
{
if (associated_room != null && associated_room.Name != space && existing_room == true && !spaceNamelist.Contains(associated_room.Name))
{
comment = "Pièce déplacée : " + space;
}
if (associated_room != null && associated_room.Name != space && existing_room == true && spaceNamelist.Contains(associated_room.Name))
{
comment = "Pièce échangée : " + space;
}
}
if (DistinguishSpace(s) == SpaceState.NotPlaced)
{
master_list.Add(Get_Space_Info(s, null, "Espace non placé", comment));
}
if (DistinguishSpace(s) == SpaceState.NotEnclosed)
{
master_list.Add(Get_Space_Info(s, null, "Espace non fermé", comment));
}
if (DistinguishSpace(s) == SpaceState.Redundant)
{
master_list.Add(Get_Space_Info(s, null, "Espace superflu", comment));
}
if (DistinguishSpace(s) == SpaceState.Placed && (roomName.AsString() == "Non occupé" || roomName.AsString() == "Unoccupied"))
{
master_list.Add(Get_Space_Info(s, null, "Espace sans pièce associée", comment));
}
if (DistinguishSpace(s) == SpaceState.Placed && roomName.AsString() != "Non occupé" && roomName.AsString() != "Unoccupied")
{
List <string> écart = new List <string>();
double space_area = Math.Round(UnitUtils.ConvertFromInternalUnits(s.Area, DisplayUnitType.DUT_SQUARE_METERS), 2);
double room_area = 0;
if (associated_room != null)
{
room_area = Math.Round(UnitUtils.ConvertFromInternalUnits(associated_room.Area, DisplayUnitType.DUT_SQUARE_METERS), 2);
}
if (spaceName.AsString() == roomName.AsString() && spaceNumber.AsString() == roomNumber.AsString() && associated_room != null && space_area == room_area)
{
master_list.Add(Get_Space_Info(s, associated_room, "_Aucun_", comment));
}
if (spaceName.AsString() != roomName.AsString())
{
écart.Add("Nom");
}
if (spaceNumber.AsString() != roomNumber.AsString())
{
écart.Add("Numéro");
}
if (associated_room != null && space_area != room_area)
{
écart.Add("Surface");
}
if ((spaceName.AsString() != roomName.AsString()) || (spaceNumber.AsString() != roomNumber.AsString()) || (associated_room != null && space_area != room_area))
{
master_list.Add(Get_Space_Info(s, associated_room, "Écart : " + string.Join(" + ", écart), comment));
}
}
}
foreach (Room room in roomList)
{
if (DistinguishRoom(room) == RoomState.Placed)
{
ElementId eid = room.Id;
if (!associatedroomList_Id.Contains(eid))
{
test += "\n" + "Pièce sans espace associé : " + room.Name;
}
}
}
//test2
string xls_filename = Path.Combine(Path.GetTempPath(), "myExcelOutput.xlsx");
using (ExcelPackage package = new ExcelPackage(new FileInfo(xls_filename)))
{
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("My Data");
int row = 2;
int row2 = 1;
List <int> columns = new List <int>();
int[] nb = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
columns.AddRange(nb);
sheet.Cells[row2, 1].Value = "ID";
sheet.Cells[row2, 2].Value = "Niveau";
sheet.Cells[row2, 3].Value = "Numéro";
sheet.Cells[row2, 4].Value = "Nom";
sheet.Cells[row2, 5].Value = "Surface (m²)";
sheet.Cells[row2, 6].Value = "ID pièce";
sheet.Cells[row2, 7].Value = "Niveau pièce";
sheet.Cells[row2, 8].Value = "Numéro pièce";
sheet.Cells[row2, 9].Value = "Nom pièce";
sheet.Cells[row2, 10].Value = "Surface pièce";
sheet.Cells[row2, 11].Value = "Écart espace-pièce";
sheet.Cells[row2, 12].Value = "Commentaire";
foreach (List <string> list in master_list)
{
foreach (int i in columns)
{
sheet.Cells[row, i].Value = list[i - 1];
}
row++;
}
using (ExcelRange rng = sheet.Cells["A1:L1"])
{
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(191, 191, 191));
}
using (ExcelRange rng = sheet.Cells["A:L"])
{
rng.AutoFilter = true;
rng.AutoFitColumns();
}
using (ExcelRange rng = sheet.Cells["F:J"])
{
rng.Style.Font.Italic = true;
}
package.Save();
}
Process.Start(xls_filename);
t.Commit();
return(Result.Succeeded);
}
}
return(Result.Succeeded);
}
private static void ImprimirHojaDatos(DataTable dt, ExcelWorksheet ws, Dictionary <string, List <string> > listHeader)
{
List <string> HojaDatos = listHeader["HojaDatos"];
//ws.Cells[1, 1].Value = "Desde:";
//ws.Cells[1, 2].Value = S_Desde;
//ws.Cells[2, 1].Value = "Hasta:";
//ws.Cells[2, 2].Value = S_Hasta;
dataColumn = 1;
dataRow = 1;
#region 'Escritura de Datos'
#region 'Cabecera'
foreach (var subitem in HojaDatos)
{
ws.Cells[dataRow, dataColumn].Value = subitem;
dataColumn++;
}
#endregion
dataRow++;
#region 'Resultados'
foreach (DataRow row in dt.Rows)
{
var i = 0;
while (i < dt.Columns.Count)
{
ws.Cells[dataRow, i + 1].Value = row[i];
i++;
}
dataRow++;
}
#endregion
#endregion
#region 'Formateo de Celdas'
using (ExcelRange subrng = ws.Cells[1, 1, 1, HojaDatos.Count])
{
subrng.Style.Font.Bold = true;
subrng.Style.Font.Size = 10;
subrng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
subrng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
subrng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
subrng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
subrng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
subrng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
subrng.Style.Fill.PatternType = ExcelFillStyle.Solid;
subrng.AutoFitColumns();
subrng.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#009FE0"));
subrng.Style.Font.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#FFFFFF"));
}
using (ExcelRange subrng = ws.Cells[2, 1, dt.Rows.Count + 1, HojaDatos.Count])
{
subrng.Style.Font.Size = 10;
subrng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
subrng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
subrng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
subrng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
subrng.AutoFitColumns();
}
#endregion
// ws.Column(1).Width = 55;
}
public static void OutloadExcelEpplus(string pathTemplate, string pathOutput, DataTable dtRows,
DataTable dtFormat, bool isColorize, bool isOpenAfterCreate, DataTable dtLists = null)
{
try
{
//exApp.TemplatesPath = Path.GetDirectoryName(pathTemplate);
using (ExcelPackage pack = new ExcelPackage(new FileInfo(pathOutput), new FileInfo(pathTemplate)))
{
ExcelWorksheet wsNotFormat = null;
if (dtLists != null)
{
foreach (DataRow row in dtLists.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum <= 0)
{
continue;
}
/*for (int i = 1; i <= listNum - pack.Workbook.Worksheets.Count; i++)
* pack.Workbook.Worksheets.Add("test");*/
string listName = Routines.GetString(row["list_name"]);
int? copyListNum = Routines.TryGetInt(row["copy_list_num"]);
if (copyListNum == null)
{
pack.Workbook.Worksheets.First(worksheet => worksheet.Index == listNum).Name = listName; continue;
}
if (copyListNum > 0)
{
pack.Workbook.Worksheets.Copy(
pack.Workbook.Worksheets.First(worksheet => worksheet.Index == copyListNum).Name,
listName);
}
else
{
wsNotFormat = pack.Workbook.Worksheets.Add(listName);
}
}
}
foreach (DataRow row in dtFormat.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum > pack.Workbook.Worksheets.Count)
{
MessageBox.Show($"Прерывание формирования excel. Лист с номером {listNum} отсутствует.");
}
ExcelWorksheet ws = pack.Workbook.Worksheets[listNum];
string cellAddress = Routines.GetString(row["range"]);
ExcelRange er = ws.Cells[cellAddress];
er.Style.Font.Name = "Arial";
int?cellFontSize = Routines.TryGetInt(row["font_size"]);
er.Style.Font.Size = cellFontSize ?? 8;
int?cellBorderLineStyle = Routines.TryGetInt(row["border"]);
if (cellBorderLineStyle != null)
{
er.Style.Border.Top.Style =
er.Style.Border.Right.Style =
er.Style.Border.Bottom.Style = er.Style.Border.Left.Style = ExcelBorderStyle.Thin;
}
if ("Y".Equals(Routines.GetString(row["is_merged"])))
{
er.Merge = true;
}
if ("Y".Equals(Routines.GetString(row["is_colored"])))
{
er.Style.Font.Color.SetColor(Color.Red);
}
}
foreach (DataRow row in dtRows.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum > pack.Workbook.Worksheets.Count)
{
MessageBox.Show($"Прерывание формирования excel. Лист с номером {listNum} отсутствует.");
}
ExcelWorksheet ws = pack.Workbook.Worksheets[listNum];
string cellAddress = Routines.GetString(row["col_name"]) + Routines.GetString(row["row_num"]);
ExcelRange er = ws.Cells[cellAddress];
string cellValue = Routines.GetString(row["value"]);
if ((cellValue.Length > 0) && (cellValue[0] == '='))
{
if (isColorize)
{
var fill = er.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
}
er.Formula = cellValue;
er.Style.Numberformat.Format = "### ### ##0.00";
}
else
{
if (isColorize)
{
var fill = er.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.Yellow);
}
decimal cellFloatValue;
if (decimal.TryParse(cellValue, out cellFloatValue))
{
er.Value = cellFloatValue;
//er.AutoFitColumns();
}
else
{
er.Value = cellValue;
}
if (wsNotFormat == ws)
{
er.AutoFitColumns();
}
}
//er.Style.WrapText = true; //cells.Rows.AutoFit();
}
//pack.Workbook.Calculate();
pack.Save();
if (dtLists != null)
{
foreach (DataRow row in dtLists.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum <= 1)
{
continue;
}
string listName = Routines.GetString(row["list_name"]);
string newFileName = pathOutput.Replace(".xlsx", $"_{listName.Replace("\"", "")}.xlsx");
using (ExcelPackage packList = new ExcelPackage(new FileInfo(newFileName)))
{
packList.Workbook.Worksheets.Add(listName,
pack.Workbook.Worksheets.First(worksheet => worksheet.Index == listNum));
packList.Save();
packList.Dispose();
}
}
}
pack.Dispose();
}
if (!isOpenAfterCreate)
{
return;
}
Excel.Application createdExcel = new Excel.Application {
Visible = false
};
createdExcel.Workbooks.Open(pathOutput);
createdExcel.Visible = true;
}
catch (Exception e)
{
MessageBox.Show($"{e.Message}\r{pathOutput}");
//exApp.Quit();
}
}