/// <summary>
/// Export a datagridview to xlsx using EPPlus
/// </summary>
/// <param name="dgv">Datagridview</param>
/// <param name="Directory">Save Directory</param>
/// <param name="ReportingTableDesign">Table Style</param>
/// <param name="ReportingWorksheetName">Name of the workbook</param>
public static void ExportXlsx(
DataGridView dgv,
string Directory,
OfficeOpenXml.Table.TableStyles ReportingTableDesign,
string ReportingWorkbookTitle,
string ReportingWorkbookCompany,
string ReportingWorksheetName)
{
FileInfo file = new FileInfo(Directory + "\\" + dgv.Name + DateTime.Now.ToString("ddMMyyyy-HHmmss") + ".xlsx");
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage ep = new ExcelPackage(file))
{
ExcelWorksheet ew = ep.Workbook.Worksheets.Add(ReportingWorksheetName);
ew.Cells["A1"].LoadFromDataTable((DataTable)dgv.DataSource, true, ReportingTableDesign);
ew.Cells.AutoFitColumns(40);
ep.Workbook.Properties.Application = Application.ProductName;
ep.Workbook.Properties.AppVersion = Application.ProductVersion;
ep.Workbook.Properties.Author = Environment.UserName;
ep.Workbook.Properties.Title = ReportingWorkbookTitle + " Export";
ep.Workbook.Properties.Company = ReportingWorkbookCompany;
ep.Workbook.Properties.Comments = "https://laim.scot";
ep.Save();
}
}
public static void CreateExcel(
DataSet ds,
string path,
OfficeOpenXml.Table.TableStyles tableStyle = OfficeOpenXml.Table.TableStyles.Light9,
bool autofitColumns = true)
{
if (ds != null)
{
using (ExcelPackage ep = new ExcelPackage())
{
foreach (DataTable dt in ds.Tables)
{
AddTableWorksheet(tableStyle, autofitColumns, ep, dt);
}
ep.SaveAs(new System.IO.FileInfo(path));
}
}
}
private static void AddTableWorksheet(OfficeOpenXml.Table.TableStyles tableStyle, bool autofitColumns, ExcelPackage ep, DataTable dt)
{
ExcelWorksheet ew = ep.Workbook.Worksheets.Add(dt.TableName);
int row = 1;
int column = 1;
foreach (DataColumn dc in dt.Columns)
{
ew.Cells[row, column].Value = dc.Caption;
column++;
}
foreach (DataRow dr in dt.Rows)
{
column = 1;
row++;
foreach (DataColumn dc in dt.Columns)
{
ew.Cells[row, column].Value = dr[dc];
column++;
}
}
column = 1;
foreach (DataColumn dc in dt.Columns)
{
if (dc.DataType == typeof(DateTime))
{
ew.Cells[1, column, row, column].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
column++;
}
var excelTable = ew.Tables.Add(new ExcelAddressBase(1, 1, row, column - 1), $"tbl_{ dt.TableName }");
excelTable.TableStyle = tableStyle;
if (autofitColumns)
{
ew.Cells[ew.Dimension.Address].AutoFitColumns();
}
}
/// <summary>
/// Create an excel file using MemoryStream.
/// if you want defined Custom Header Sheet Layout You can use packageExist(must be defined before execute this),
/// </summary>
/// <param name="dtSourceList">DataTable With name Sheet</param>
/// <param name="packageExist">Custom Header Layout Sheet</param>
/// <param name="tableColStart">Table Column Start to insert table</param>
/// <param name="tableRowStart">Table Row Start to insert table</param>
/// <param name="withHeader">if you want to insert table using header</param>
/// <param name="styling">for style </param>
/// <returns></returns>
public static MemoryStream CreateExcel(List <KeyValuePair <DataTable, string> > dtSourceList, ExcelPackage packageExist, int tableColStart, int tableRowStart, bool withHeader, OfficeOpenXml.Table.TableStyles styles)
{
foreach (KeyValuePair <DataTable, string> item in dtSourceList)
{
var sheet = packageExist.Workbook.Worksheets.Add(item.Value);
sheet.Cells[tableRowStart, tableColStart].LoadFromDataTable(item.Key, true, styles);
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
}
MemoryStream stream = new MemoryStream();
packageExist.SaveAs(stream);
return(stream);
}
void DataExporter(string FileName, string Path, string DataType, DataTable dt, OfficeOpenXml.Table.TableStyles ReportingTableDesign, string SheetName = "Report")
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var ep = new ExcelPackage(new FileInfo(Path + "\\" + FileName + ".xlsx")))
{
ExcelWorksheet ew = ep.Workbook.Worksheets.Add(SheetName);
ew.Cells["A1"].LoadFromDataTable(dt, true, ReportingTableDesign);
ew.Cells.AutoFitColumns(40);
ep.Workbook.Properties.Application = Application.ProductName;
ep.Workbook.Properties.Author = Environment.UserName;
ep.Workbook.Properties.Title = DataType + " Export";
ep.Workbook.Properties.Company = "Goosetuv";
ep.Workbook.Properties.Comments = "https://github.com/goosetuv";
ep.Save();
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheets"></param>
/// <param name="name"></param>
/// <param name="list"></param>
/// <param name="tableStyles"></param>
public static void AddDataToWorkSheet <T>(this ExcelWorksheets worksheets, String name, IList <T> list, OfficeOpenXml.Table.TableStyles tableStyles = OfficeOpenXml.Table.TableStyles.Light8)
{
ExcelWorksheet worksheet = worksheets.Add(name);
var dataRange = worksheet.Cells["A1"].LoadFromCollection(
list,
true, tableStyles);
dataRange.AutoFitColumns();
return;
}
/// <summary>
/// 添加摄像机信息表单
/// </summary>
/// <param name="worksheets">表单列表集合</param>
/// <param name="list">摄像机列表</param>
/// <param name="tableStyles">表单样式</param>
public static void AddDeviceWorkSheet(this ExcelWorksheets worksheets, IList <DeviceExcelData> list, OfficeOpenXml.Table.TableStyles tableStyles = OfficeOpenXml.Table.TableStyles.Light8)
{
ExcelWorksheet worksheet = worksheets.Add("设备信息");
var dataRange = worksheet.Cells["A1"].LoadFromCollection(
list.OrderBy(x => x.Name).OrderBy(x => x.No),
true, tableStyles);
worksheet.DataValidations.AddUniqueValidation("A2:A65536");
worksheet.DataValidations.AddListValidation("C2:C65536", DeviceTypeConverter.GetValues());
worksheet.DataValidations.AddListValidation("K2:K65536", Boolean2ChineseConverter.GetValues());
worksheet.DataValidations.AddUniqueValidation("E2:E65536");
worksheet.DataValidations.AddUniqueValidation("N2:N65536");
//worksheet.Column(0).Style.Locked = true;
//worksheet.Column(13).Style.Locked = true;
//worksheet.Column(14).Style.Locked = true;
//worksheet.Column(15).Style.Locked = true;
//worksheet.Column(16).Style.Locked = true;
//worksheet.Column(17).Style.Locked = true;
//worksheet.Protection.IsProtected = true;
dataRange.AutoFitColumns();
}
public bool Csv2Excel(string csvFilePath, string worksheetsName, bool bheader, string Loaction, OfficeOpenXml.Table.TableStyles TableStyle = OfficeOpenXml.Table.TableStyles.None)
{
try
{
bool firstRowIsHeader = false;
var excelTextFormat = new ExcelTextFormat();
excelTextFormat.Delimiter = ',';
excelTextFormat.EOL = "\r";
var csvFileInfo = new FileInfo(csvFilePath);
ExcelWorksheet worksheet = ep.Workbook.Worksheets.Add(worksheetsName);
worksheet.Cells["A1"].LoadFromText(csvFileInfo, excelTextFormat, TableStyle, firstRowIsHeader);
}
catch
{
return(false);
}
return(true);
}