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); }