public async Task <bool> SetColumnsOrder() //изменяет порядок столбоц
{
return(await Task.Run(() =>
{
int insColPos = 1;
int foundColPos;
foreach (var columnSynonim in columnSynonims)
{
foundColPos = FindColumnIndex(columnSynonim, 1);
if (foundColPos == insColPos)
{
insColPos++;
}
else if (foundColPos != -1)
{
ws.InsertColumn(insColPos, 1);
foundColPos++;
ws.Cells[1, foundColPos, maxRow, foundColPos].Copy(ws.Cells[1, insColPos, maxRow, insColPos]);
ws.DeleteColumn(foundColPos);
insColPos++;
}
}
return false;
}));
}
public IActionResult ExportReportt()
{
var GroupReport = _group.GetListGroupSV();
var TodoReport = _todo.GetTodoAllGroup();
string fileName = "GroupList.xlsx";
var stream = new MemoryStream();
using (var package = new ExcelPackage(stream))
{
ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("GroupList");
worksheet2.Cells[2, 1].LoadFromCollection(GroupReport, true, TableStyles.Light19);
worksheet2.DeleteColumn(1);
worksheet2.DefaultColWidth = 25;
worksheet2.Cells.Style.WrapText = true;
worksheet2.Cells[1, 2].Value = $"Group List :({GroupReport.Count})";
worksheet2.Cells[2, 5].LoadFromCollection(TodoReport, true, TableStyles.Light19);
worksheet2.DeleteColumn(5);
worksheet2.DeleteColumn(7);
worksheet2.DeleteColumn(8);
worksheet2.DeleteColumn(7);
worksheet2.DefaultColWidth = 30;
worksheet2.Cells.Style.WrapText = true;
worksheet2.Cells[1, 9].Value = $"Todo List :({TodoReport.Count})";
package.Save();
}
stream.Position = 0;
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
public void mergeTable(string title1, string title2, string titleOriginal)
{
int column1 = 0;
int column2 = 0;
for (int i = tableChange.Dimension.Start.Column; i <= tableChange.Dimension.End.Column; i++)
{
if (tableChange.Cells[1, i].Value != null)
{
if (tableChange.Cells[1, i].Value.ToString() == title1)
{
column1 = i;
}
else if (tableChange.Cells[1, i].Value.ToString() == title2)
{
column2 = i;
}
}
}
var col = 0;
while (tableChange.Cells[1, col + 1].Value != null)
{
col++;
}
if (column1 != 0 && column2 != 0)
{
tableChange.Cells[1, col + 1].Value = titleOriginal;
for (int i = 2; i <= tableChange.Dimension.End.Row; i++)
{
if (tableChange.Cells[i, column1].Value != null)
{
tableChange.Cells[i, col + 1].Value = tableChange.Cells[i, column1].Value;
}
if (tableChange.Cells[i, column2].Value != null)
{
tableChange.Cells[i, col + 1].Value = tableChange.Cells[i, column2].Value;
}
}
if (column1 > column2)
{
tableChange.DeleteColumn(column1);
tableChange.DeleteColumn(column2);
}
else
{
tableChange.DeleteColumn(column2);
tableChange.DeleteColumn(column1);
}
}
}
public static bool Export(List <Patient> patients, string path, string name, List <PatientExportParams> @params)
{
try
{
FileInfo file = new(path + "\\" + name + ".xlsx");
if (file.Exists)
{
try { file.Delete(); } catch { return(false); }
}
;
ExcelPackage package = new(file);
ExcelWorksheet ws = package.Workbook.Worksheets.Add("patients");
ws.Cells[1, 1].LoadFromCollection <Patient>(patients, true);
ws.Cells[1, 8].LoadFromCollection <PatientData>(new List <PatientData>(), true);
for (int i = 2, j = 0; j < patients.Count; j++)
{
ws.InsertRow(i + 1, patients[j].PatientDataList.Count - 1);
package.Save();
ws.Cells[i, 8].LoadFromCollection(patients[j].PatientDataList, false);
i += patients[j].PatientDataList.Count;
}
ExcelRangeBase range = ws.Cells;
range.AutoFitColumns();
ws.DeleteColumn(1);
for (int i = 0, j = 1; i < 16; i++)
{
if (!@params[i].IsShown)
{
ws.DeleteColumn(j);
}
else
{
j++;
}
}
package.Save();
return(true);
}
catch
{
return(false);
}
}
// 校验字段是否全
private bool VerifyExcel(ExcelWorksheet _worksheet)
{
if (_worksheet.Cells[1, 4].Value.ToString().Equals("扩展属性"))
{
_worksheet.DeleteColumn(4);
}
if (_worksheet.Cells[1, 2].Value.ToString().Equals("备注"))
{
_worksheet.DeleteColumn(2);
}
return(true);
}
public void CreateExcelFile(Dictionary <string, GroundVehicle> vehicleDetails)
{
// Setup objects to handle creating the spreadsheet
FileInfo excelFile = new FileInfo($"{ConfigurationManager.AppSettings["LocalWikiExcelPath"]}GroundVehicleData.xlsx");
ExcelPackage excelPackage = new ExcelPackage(excelFile);
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.FirstOrDefault() == null
? excelPackage.Workbook.Worksheets.Add("Data")
: excelPackage.Workbook.Worksheets.Single(w => w.Name == "Data");
// Clear out old data before populating the headers again
worksheet.DeleteColumn(1, 30);
CreateGroundVehicleSpreadsheetHeaders(worksheet);
// Populate spreadsheet
Dictionary <string, GroundVehicle> orderedGroundVehicles = vehicleDetails.OrderBy(x => x.Key).ToDictionary(d => d.Key, d => d.Value);
foreach (GroundVehicle groundVehicle in orderedGroundVehicles.Values)
{
AddGroundVehicleRowToSpreadsheet(groundVehicle, worksheet);
}
// Make columns fit content then save the file
worksheet.Cells["A1:S1"].AutoFitColumns();
excelPackage.Save();
}
public void DeleteColumns(ExcelWorksheet wsSheet, int fromColumn, int toColumn)
{
for (int i = toColumn; i >= fromColumn; i--)
{
wsSheet.DeleteColumn(fromColumn);
}
}
private void RemoveTotalColumns(ExcelWorksheet sheet)
{
if (_headerRowsCount < 2)
{
for (var columnIndex = _leftPaneWidth + 1; columnIndex <= sheet.Dimension.Columns; columnIndex++)
{
SaveTotalColumn(sheet, columnIndex, true);
}
return;
}
var deletedColumnsCount = 0;
foreach (var totalColumnIndex in _totalColumnIndexes.OrderBy(x => x))
{
var fixedTotalColumnIndex = totalColumnIndex - deletedColumnsCount;
var firstCell = sheet.Cells[1, fixedTotalColumnIndex];
if (firstCell.Value is string value &&
value.Contains(WorksheetHelpers.TotalRowIndicator))
{
SaveTotalColumn(sheet, fixedTotalColumnIndex);
}
sheet.DeleteColumn(fixedTotalColumnIndex);
deletedColumnsCount++;
}
_totalColumnIndexes.Clear();
}
/// <summary>
/// 添加样式
/// </summary>
/// <param name="exporterHeaders"></param>
/// <param name="sheet"></param>
protected void AddStyle(ExcelExporterAttribute exporter, List <ExporterHeaderInfo> exporterHeaders,
ExcelWorksheet sheet)
{
foreach (var exporterHeader in exporterHeaders)
{
if (exporterHeader.ExporterHeader != null)
{
if (exporterHeader.ExporterHeader.IsIgnore)
{
//TODO:后续直接修改数据导出逻辑（不写忽略列数据）
sheet.DeleteColumn(exporterHeader.Index);
//删除之后，序号依次-1
foreach (var item in exporterHeaders.Where(p => p.Index > exporterHeader.Index))
{
item.Index--;
}
continue;
}
var col = sheet.Column(exporterHeader.Index);
col.Style.Numberformat.Format = exporterHeader.ExporterHeader.Format;
if (exporter.AutoFitAllColumn || exporterHeader.ExporterHeader.IsAutoFit)
{
col.AutoFit();
}
}
}
}
public async Task <string> ExportReportJobList()
{
string rootFolder = hostingEnvironment.WebRootPath;
string fileName = "JobList.xlsx";
string downloadUrl = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, fileName);
FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(rootFolder, fileName));
}
await Task.Yield();
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("joblist");
var JobList = job.JobList();
worksheet.Cells[3, 4].LoadFromCollection(JobList, true, TableStyles.Medium20);
worksheet.DeleteColumn(4);
worksheet.DefaultColWidth = 25;
worksheet.Cells.Style.WrapText = true;
worksheet.Cells[1, 4].Value = $"Job List :({JobList.Count})";
using (ExcelRange Rng = worksheet.Cells[1, 4, 1, 7])
{
Rng.Merge = true;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Size = 18;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
package.Save();
}
return(downloadUrl);
}
public static void RemoveColumn(FileInfo _file, int column)
{
using (ExcelPackage package = new ExcelPackage(_file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
worksheet.DeleteColumn(column);
package.Save();
}
}
public IActionResult ExportExcel(int?categoryId, string keyword)
{
string webRootFolder = _hostingEnvironment.WebRootPath;
const string folder = "export-files";
string directory = Path.Combine(webRootFolder, folder);
//Create directory if it isn't existing
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
string fileName = $"Products";
if (categoryId.HasValue)
{
var productCategory = _productCategoryService.GetById(categoryId.Value);
fileName += $"_{TextHelper.ToUnsignString(productCategory.Name)}";
}
if (!string.IsNullOrWhiteSpace(keyword))
{
fileName += $"_{TextHelper.ToUnsignString(keyword)}";
}
fileName += $"_{ DateTime.Now:yyyyMMddhhmmss}.xlsx";
string fileUrl = $"{Request.Scheme}://{Request.Host}/{folder}/{fileName}";
FileInfo file = new FileInfo(Path.Combine(directory, fileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(webRootFolder, fileName));
}
List <ProductViewModel> products = _productService.GetAll(categoryId, keyword);
using (ExcelPackage package = new ExcelPackage(file))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Products");
worksheet.Cells["A1"].LoadFromCollection(products, true, TableStyles.Light1);
//Format columns
const string integerFormat = "#,##0";
worksheet.Column(1).Style.Numberformat.Format = integerFormat;
//number with 2 decimal places and thousand separator and money symbol
const string currencyFormat = "#,##0 ₫";
worksheet.Column(5).Style.Numberformat.Format = currencyFormat;
worksheet.Column(6).Style.Numberformat.Format = currencyFormat;
worksheet.Column(7).Style.Numberformat.Format = currencyFormat;
const string dateTimeFormat = "dd-MM-yyyy HH:mm:ss";
worksheet.Column(20).Style.Numberformat.Format = dateTimeFormat;
worksheet.Column(21).Style.Numberformat.Format = dateTimeFormat;
worksheet.DeleteColumn(22);
worksheet.Cells.AutoFitColumns();
package.Save(); //Save the workbook.
}
return(new OkObjectResult(fileUrl));
}
//Excel
public static void SaveExcel(DataGridView dgv, string fullPath)
{
//Create a new ExcelPackage
ExcelPackage excelPackage = new ExcelPackage();
//Set some properties of the Excel document
excelPackage.Workbook.Properties.Author = Environment.UserName;
//excelPackage.Workbook.Properties.Title = "Title of Document";
//excelPackage.Workbook.Properties.Subject = "EPPlus demo export data";
excelPackage.Workbook.Properties.Created = DateTime.Now;
//Create the WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet");
int tmp = 1;
for (int i = 0; i < dgv.Rows.Count; i++) //Строки
{
for (int j = 1; j < dgv.ColumnCount; j++) //Столбцы
{
//Добавляем префикс в ячейку с заявками
string prefix = ((dgv.Name == "dgvRequest") && (j == 2)) ?
(dgv.Rows[i].Cells[1].Value).ToString() + " " : string.Empty;
//Сохранение ячейки в файле
worksheet.Cells[i + 1, j].Value = (prefix + dgv.Rows[i].Cells[j].Value).Trim();
//Границы для ячеек
worksheet.Cells[i + 1, j].Style.Border.BorderAround(ExcelBorderStyle.Thin);
tmp = j;
}
}
//Удаляем первый стобец из списка заявок, т.к. он пустой
if (dgv.Name == "dgvRequest")
{
worksheet.DeleteColumn(1);
}
//Выравнимаем ширину ячеек
for (int i = 1; i <= tmp; i++)
{
worksheet.Column(i).AutoFit();
}
//Save your file
FileInfo fi1 = new FileInfo(fullPath);
try
{
excelPackage.SaveAs(fi1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\n\n" + ex.InnerException.InnerException.Message, "Что случилось?", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void Issue15145()
{
using (ExcelPackage p = new ExcelPackage(new System.IO.FileInfo(@"C:\Temp\bug\ColumnInsert.xlsx")))
{
ExcelWorksheet ws = p.Workbook.Worksheets[1];
ws.InsertColumn(12, 3);
ws.InsertRow(30, 3);
ws.DeleteRow(31, 1);
ws.DeleteColumn(7, 1);
p.SaveAs(new System.IO.FileInfo(@"C:\Temp\bug\InsertCopyFail.xlsx"));
}
}
/// <summary>
/// Deletes a column from worksheet by using column header text
/// </summary>
/// <param name="worksheet"></param>
/// <param name="headerText"></param>
/// <returns></returns>
public static ExcelWorksheet DeleteColumn(this ExcelWorksheet worksheet, string headerText)
{
ExcelAddressBase valuedDimension = worksheet.GetValuedDimension();
ExcelRangeBase headerColumn = worksheet.Cells[valuedDimension.Start.Row, valuedDimension.Start.Column, valuedDimension.Start.Row, valuedDimension.End.Column].FirstOrDefault(x => x.Text.Equals(headerText, StringComparison.InvariantCultureIgnoreCase));
if (headerColumn != null)
{
worksheet.DeleteColumn(headerColumn.Start.Column);
}
return(worksheet);
}
/// <summary>
/// Deletes columns from worksheet by using given header text
/// </summary>
/// <param name="worksheet"></param>
/// <param name="headerText"></param>
/// <returns></returns>
public static ExcelWorksheet DeleteColumns(this ExcelWorksheet worksheet, string headerText)
{
ExcelAddressBase valuedDimension = worksheet.GetValuedDimension();
int count = worksheet.Cells[valuedDimension.Start.Row, valuedDimension.Start.Column, valuedDimension.Start.Row, valuedDimension.End.Column].Count(x => x.Text.Equals(headerText, StringComparison.InvariantCultureIgnoreCase));
for (var i = 0; i < count; i++)
{
worksheet.DeleteColumn(headerText);
}
return(worksheet);
}
/// <summary>
/// 导出Excel-EPPLUS
/// </summary>
/// <param name="Data">数据源</param>
/// <param name="WorkSheetName">工作簿名称</param>
/// <returns></returns>
public static byte[] ExportExcel(DataTable Data, String WorkSheetName = null, List <String> ColName = null)
{
byte[] Result = null;
using (ExcelPackage package = new ExcelPackage())
{
//添加工作簿
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(WorkSheetName);
//从A1开始添加数据
workSheet.Cells["A1"].LoadFromDataTable(Data, true);
workSheet.Cells.AutoFitColumns();
//设置表格样式
using (ExcelRange rng = workSheet.Cells[1, 1, Data.Rows.Count + 1, Data.Columns.Count])
{
rng.Style.Font.Name = "宋体";
rng.Style.Font.Size = 10;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Top.Color.SetColor(Color.FromArgb(155, 155, 155));
rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Bottom.Color.SetColor(Color.FromArgb(155, 155, 155));
rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Right.Color.SetColor(Color.FromArgb(155, 155, 155));
}
using (ExcelRange rng = workSheet.Cells[1, 1, 1, Data.Columns.Count])
{
rng.Style.Font.Bold = true;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
}
//删除忽略列
for (int i = Data.Columns.Count - 1; i >= 0; i--)
{
if (ColName != null)
{
if (ColName.Contains(Data.Columns[i].ColumnName))
{
workSheet.DeleteColumn(i + 1);
}
}
}
Result = package.GetAsByteArray();
}
return(Result);
}
public async Task <string> ExportReportWithJobId(int id)
{
string rootFolder = hostingEnvironment.WebRootPath;
string fileName = "CustomerByJob.xlsx";
string downloadUrl = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, fileName);
FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(rootFolder, fileName));
}
await Task.Yield();
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet3 = package.Workbook.Worksheets.Add("CustomerOfJob}");
var CustomerOfJob = customer.CustomerList(id);
worksheet3.Cells[3, 2].LoadFromCollection(CustomerOfJob, true, TableStyles.Medium20);
worksheet3.DeleteColumn(2);
worksheet3.DeleteColumn(11);
worksheet3.DeleteColumn(10);
worksheet3.DefaultColWidth = 25;
worksheet3.Cells.Style.WrapText = true;
worksheet3.Cells[1, 5].Value = $"Customer of Job:({CustomerOfJob.Count})";
using (ExcelRange Rng = worksheet3.Cells[1, 5, 1, 7])
{
Rng.Merge = true;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Size = 18;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
package.Save();
}
return(downloadUrl);
}
/// <summary>
/// Xoa các dòng dinh nghia template
/// </summary>
/// <param name="ws"></param>
/// <param name="def"></param>
private void DeleteDefineRow(ExcelWorksheet ws, CExcelTemplateDefinition def)
{
//xoa row i
if (def.loopDataRowIndex > 0)
{
ws.DeleteRow(def.loopDataRowIndex, 1);
}
//xóa [Row]
if (def.definedRowIndex > 0)
{
ws.DeleteRow(def.definedRowIndex, 1);
}
//xoa column
ws.DeleteColumn(def.definedColumnIndex);
}
public void Complie(Dictionary <string, object> values)
{
this.outputRow = this.rows + 1;
this.Run(values);
foreach (var m in mergedRegions)
{
sheet.Cells[m.Address].Merge = true;
}
sheet.DeleteColumn(1);
//删除一行后，下面一行变成第一行。批量删除的方法有问题，所以一行一行删除
for (var i = this.rows; i >= 1; i--)
{
sheet.DeleteRow(i);
}
}
public void Issue15158()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\Output.xlsx"), new FileInfo(@"C:\temp\bug\DeleteColFormula\FormulasIssue\demo.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
ExcelWorksheet worksheet = workBook.Worksheets[1];
//string column = ColumnIndexToColumnLetter(28);
worksheet.DeleteColumn(28);
if (worksheet.Cells["AA19"].Formula != "")
{
throw new Exception("this cell should not have formula");
}
package.Save();
}
}
public void Should_delete_a_column_by_using_header_text()
{
//-----------------------------------------------------------------------------------------------------------
// Arrange
//-----------------------------------------------------------------------------------------------------------
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["TEST6"];
//-----------------------------------------------------------------------------------------------------------
// Act
//-----------------------------------------------------------------------------------------------------------
worksheet.DeleteColumn("Quantity");
//-----------------------------------------------------------------------------------------------------------
// Assert
//-----------------------------------------------------------------------------------------------------------
worksheet.GetValuedDimension().End.Column.Should().Be(2);
worksheet.Cells[1, 2, 1, 2].Text.Should().Be("UpdatedDate");
}
public void Should_delete_a_column_by_using_header_text()
{
//-----------------------------------------------------------------------------------------------------------
// Arrange
//-----------------------------------------------------------------------------------------------------------
ExcelWorksheet worksheet = excelPackage1.GetWorksheet("TEST6");
const string columnName = "Quantity";
//-----------------------------------------------------------------------------------------------------------
// Act
//-----------------------------------------------------------------------------------------------------------
worksheet.DeleteColumn(columnName);
//-----------------------------------------------------------------------------------------------------------
// Assert
//-----------------------------------------------------------------------------------------------------------
worksheet.GetColumns(1).Any(x => x.Value == columnName).Should().BeFalse();
worksheet.GetValuedDimension().End.Column.Should().Be(2);
worksheet.Cells[2, 2, 2, 2].Text.Should().Be("UpdatedDate");
}
public void Generate()
{
string fileName = string.Format(@"C:\temp\{0:dd_MM_yyyy_HH_mm_ss}.xlsx", DateTime.Now);
//string dir = string.Format(@"C:\temp\images");
//DirectoryInfo di = new DirectoryInfo(dir);
//di.GetFiles().ToList().ForEach(file => File.Delete(file.FullName));
string dir = string.Format(@"C:\temp");
DirectoryInfo di = new DirectoryInfo(dir);
//di.GetFiles("*.xlsx").ToList().ForEach(file => File.Delete(file.FullName));
FileInfo newFile = new FileInfo(fileName);
ExcelPackage pck = new ExcelPackage(newFile);
ws = pck.Workbook.Worksheets.Add("Каталог");
//categories
var categoriesQuery = from orderCategory in db.OrderCategoryRepository.Query()
join category in db.CategoryRepository.Query() on orderCategory.CategoryId equals category.CategoryId
where
orderCategory.OrderId == OrderId && orderCategory.ParentOrderCategoryId == null
select new
{
category.Name,
category.CategoryId,
orderCategory.OrderCategoryId
};
foreach (var category in categoriesQuery.Distinct().OrderBy(n => n.Name).ToArray())
{
Console.WriteLine(category.Name);
ws.Cells[string.Format("A{0}", ri)].Value = category.Name.ToUpper();
SetMainCategoryFormat(string.Format("A{0}:F{0}", ri));
ri++;
GenerateLoopCategories(category.OrderCategoryId, string.Empty);
}
ws.Column(2).Width = 6;
ws.DeleteColumn(2);
pck.Save();
System.Diagnostics.Process.Start(fileName);
}
public static void deleteColumnBetween(ExcelWorksheet sheet, String startColumn, String endColumn)
{
Console.WriteLine("Enter deleteColumnBetween({0}, {1}), sheet={2}\n", startColumn, endColumn, sheet.Name);
int startIndex = getColumnIndex(sheet, startColumn);
int endIndex = getColumnIndex(sheet, endColumn);
if (startIndex > endIndex)
{
int tmp = startIndex;
startIndex = endIndex;
endIndex = tmp;
}
for (int index = startIndex + 1; index < endIndex; index++)
{
Console.WriteLine("Deleting column: {0} ...\n", sheet.Cells[1, index].Value);
sheet.DeleteColumn(index);
}
Console.WriteLine("Exit deleteColumnBetween()\n");
}
public static void DeleteColumn(List <KeyValuePair <string, string> > columnsToExport, PropertyDescriptorCollection properties, ExcelWorksheet workSheet, int startIndex)
{
List <int> columnsToDelete = new List <int>();
for (int i = properties.Count - 1; i >= 0; i--)
{
ExcelRange r = workSheet.Cells[startIndex, 1, startIndex, properties.Count];
if (r[1, i + 1].Value != null)
{
var current = columnsToExport.Find(p => p.Value.ToUpper() == r[1, i + 1].Value.ToString().ToUpper());
if (current.Key == null)
{
columnsToDelete.Add(i + 1);
}
}
}
foreach (int current in columnsToDelete)
{
workSheet.DeleteColumn(current);
}
}
public static void RemoveColumns(this ExcelWorksheet ExcelWorksheet, params string[] UnwantedColumns)
{
if (ExcelWorksheet == null)
{
return;
}
if (UnwantedColumns == null)
{
return;
}
var UnWantedColumnsIndexList = ExcelWorksheet.Cells[ExcelWorksheet.Dimension.Start.Row, ExcelWorksheet.Dimension.Start.Column, 1, ExcelWorksheet.Dimension.End.Column]
.Where(x => UnwantedColumns.Contains(x.Text))
.Select(x => x.Columns)
.ToList();
foreach (int IndexToDelete in UnWantedColumnsIndexList)
{
ExcelWorksheet.DeleteColumn(IndexToDelete);
}
}
void ApplyColumnProperty()
{
for (int i = 1; i <= maxCol; i++)
{
var cellValue = sheet.Cells[headerRowIndex, i].Value;
if (cellValue == null)
{
continue;
}
var columnProperty = GetColumnProperty(cellValue.ToString());
if (columnProperty == null)
{
continue;
}
if (!string.IsNullOrEmpty(columnProperty.AltName))
{
sheet.Cells[headerRowIndex, i].Value = columnProperty.AltName;
}
if (columnProperty.Hide)
{
sheet.Column(i).Hidden = true;
}
if (columnProperty.Delete)
{
sheet.DeleteColumn(i);
maxCol--;
i--;
}
}
}
/// <summary>
/// 导出Excel-EPPLUS
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ColName">列明</param>
/// <param name="Data">数据源</param>
/// <param name="WorkSheetName">工作簿名称</param>
/// <param name="ShowNo">是否显示编号</param>
/// <returns></returns>
public static byte[] ExportExcel <T>(List <T> Data, String WorkSheetName = null, bool ShowNo = false, List <String> ColName = null)
{
byte[] Result = null;
using (ExcelPackage package = new ExcelPackage())
{
//添加工作簿
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(WorkSheetName);
//是否显示行编号
if (ShowNo)
{
int index = 1;
Data.ForEach(t =>
{
t.GetType().GetProperty("编号").SetValue(t, index);
index++;
});
}
//从A1开始添加数据
workSheet.Cells["A1"].LoadFromCollection(Data, true);
workSheet.Cells.AutoFitColumns();
//设置表格样式
using (ExcelRange rng = workSheet.Cells[1, 1, Data.Count + 1, Data[0].GetType().GetProperties().Count()])
{
rng.Style.Font.Name = "宋体";
rng.Style.Font.Size = 10;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Top.Color.SetColor(Color.FromArgb(155, 155, 155));
rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Bottom.Color.SetColor(Color.FromArgb(155, 155, 155));
rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Right.Color.SetColor(Color.FromArgb(155, 155, 155));
}
using (ExcelRange rng = workSheet.Cells[1, 1, 1, Data[0].GetType().GetProperties().Count()])
{
rng.Style.Font.Bold = true;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
}
// 删除忽略的列
int flag = 0;
if (ColName != null)
{
Data.FirstOrDefault().GetType().GetProperties().ToList().ForEach(t =>
{
flag += 1;
if (!ColName.Contains(t.Name))
{
workSheet.DeleteColumn(flag);
}
});
}
Result = package.GetAsByteArray();
}
return(Result);
}
public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0} Data", heading));
int startRowFrom = String.IsNullOrEmpty(heading) ? 1 : 3;
if (showSrNo)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
dataColumn.SetOrdinal(0);
int index = 1;
foreach (DataRow item in dataTable.Rows)
{
item[0] = index;
index++;
}
}
// add the content into the Excel file
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
// autofit width of cells with small content
int columnIndex = 1;
foreach (DataColumn column in dataTable.Columns)
{
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
if (maxLength < 150)
{
workSheet.Column(columnIndex).AutoFit();
}
columnIndex++;
}
// format header - bold, yellow on black
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
// format cells - add borders
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
// removed ignored columns
for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
{
if (i == 0 && showSrNo)
{
continue;
}
if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName))
{
workSheet.DeleteColumn(i + 1);
}
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 5;
}
result = package.GetAsByteArray();
}
return(result);
}