private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) { FileInfo newFile = new FileInfo(fileName); if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(fileName); } var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None); using (ExcelPackage package = new ExcelPackage(newFile)) { ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model"); FormatModelSheet(modelWorksheet, solution, formulaParts); ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset"); WriteDatasetToExcel(datasetWorksheet, solution.ProblemData); ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs"); WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset); if (solution is IRegressionSolution) { ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values"); WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution); ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts"); AddCharts(chartsWorksheet); } package.Workbook.Properties.Title = "Excel Export"; package.Workbook.Properties.Author = "HEAL"; package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab"; package.Save(); } }
public void AverageCellReferences() { // In the case of cell references, Average DOES NOT parse and include numeric strings, date strings, bools, unparsable strings, etc. ExcelPackage package = new ExcelPackage(); var worksheet = package.Workbook.Worksheets.Add("Test"); ExcelRange range1 = worksheet.Cells[1, 1]; range1.Formula = "\"1000\""; range1.Calculate(); var range2 = worksheet.Cells[1, 2]; range2.Value = 2000; var range3 = worksheet.Cells[1, 3]; range3.Formula = $"\"{new DateTime(2013, 1, 5).ToString("d")}\""; range3.Calculate(); var range4 = worksheet.Cells[1, 4]; range4.Value = true; var range5 = worksheet.Cells[1, 5]; range5.Value = new DateTime(2013, 1, 5); var range6 = worksheet.Cells[1, 6]; range6.Value = "Test"; Average average = new Average(); var rangeInfo1 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 1, 1, 3); var rangeInfo2 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 4, 1, 4); var rangeInfo3 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 5, 1, 6); var context = ParsingContext.Create(); var address = new OfficeOpenXml.FormulaParsing.ExcelUtilities.RangeAddress(); address.FromRow = address.ToRow = address.FromCol = address.ToCol = 2; context.Scopes.NewScope(address); var result = average.Execute(new FunctionArgument[] { new FunctionArgument(rangeInfo1), new FunctionArgument(rangeInfo2), new FunctionArgument(rangeInfo3) }, context); Assert.AreEqual((2000 + new DateTime(2013, 1, 5).ToOADate()) / 2, result.Result); }
/// <summary> /// 保存excel文件,覆盖相同文件名的文件 /// </summary> public static void SaveExcel(string FileName, string sql, string SheetName) { FileInfo newFile = new FileInfo(FileName); if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(FileName); } using (ExcelPackage package = new ExcelPackage(newFile)) { try { ExcelWorksheet ws = package.Workbook.Worksheets.Add(SheetName); IDataReader reader = DBConfig.db.DBProvider.ExecuteReader(sql); ws.Cells["A1"].LoadFromDataReader(reader, true); } catch (Exception ex) { throw ex; } package.Save(); } }
/// <summary> /// Экспортирует массив данных в XLSX формат с учетом выбранной локали /// </summary> /// <param name="path">Путь к файлу, в который нужно сохранить данные</param> /// <param name="localisation">Локализация</param> /// <returns>Успешное завершение операции</returns> public override bool Export(String path, Localisation localisation) { try { if (!path.EndsWith(".xlsx")) path += ".xlsx"; log.Info(String.Format("Export to .xlsx file to: {0}", path)); var timer = new Stopwatch(); timer.Start(); var file = new FileInfo(path); using (var pck = new ExcelPackage(file)) { ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1"); ws.Cells["A1"].LoadFromDataTable(dataTable, true); ws.Cells.AutoFitColumns(); pck.Save(); } timer.Stop(); log.Info(String.Format("Export complete! Elapsed time: {0} ms", timer.Elapsed.Milliseconds)); return true; } catch (Exception ex) { log.Error("Can't export to .xlsx file!", ex); return false; } }
public void GetDataTableFromExcel(string path, bool hasHeader) { using (var pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { pck.Load(stream); } var ws = pck.Workbook.Worksheets[DropDownList1.SelectedItem.ToString()]; tbl = new DataTable(); Session.Add("tblTable", tbl); foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; DataRow row = tbl.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } } GridView1.Caption = Path.GetFileName(path); GridView1.DataSource = tbl; GridView1.DataBind(); } }
public System.IO.Stream Serialize(T data) { var ms = new System.IO.MemoryStream(); using (var pkg = new OfficeOpenXml.ExcelPackage(ms)) { if (settings.Template != null) { using (var tmp = new OfficeOpenXml.ExcelPackage(settings.Template.Open())) { foreach (var sheet in tmp.Workbook.Worksheets) { pkg.Workbook.Worksheets.Add(sheet.Name, sheet); } } } if (!pkg.Workbook.Worksheets.Any()) { pkg.Workbook.Worksheets.Add("Export"); } foreach (var p in settings.Processes) { p.Run(pkg, data); } pkg.Save(); } ms.Position = 0; return(ms); }
private static void GetDataTableFromExcel(string filePath) { using (var pck = new OfficeOpenXml.ExcelPackage()) { //using (var stream = File.OpenRead(path)) //{ // pck.Load(stream); //} //var ws = pck.Workbook.Worksheets[1]; //DataTable tbl = new DataTable(); //foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) //{ // tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); //} //var startRow = hasHeader ? 2 : 1; //for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) //{ // var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; // DataRow row = tbl.Rows.Add(); // foreach (var cell in wsRow) // { // row[cell.Start.Column - 1] = cell.Text; // } //} //return tbl; } }
public void CalculateDateMath() { using (ExcelPackage package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Test"); var dateCell = worksheet.Cells[2, 2]; var date = new DateTime(2013, 1, 1); dateCell.Value = date; var quotedDateCell = worksheet.Cells[2, 3]; quotedDateCell.Formula = $"\"{date.ToString("d")}\""; var dateFormula = "B2"; var dateFormulaWithMath = "B2+1"; var quotedDateFormulaWithMath = $"\"{date.ToString("d")}\"+1"; var quotedDateReferenceFormulaWithMath = "C2+1"; var expectedDate = 41275.0; // January 1, 2013 var expectedDateWithMath = 41276.0; // January 2, 2013 Assert.AreEqual(expectedDate, worksheet.Calculate(dateFormula)); Assert.AreEqual(expectedDateWithMath, worksheet.Calculate(dateFormulaWithMath)); Assert.AreEqual(expectedDateWithMath, worksheet.Calculate(quotedDateFormulaWithMath)); Assert.AreEqual(expectedDateWithMath, worksheet.Calculate(quotedDateReferenceFormulaWithMath)); var formulaCell = worksheet.Cells[2, 4]; formulaCell.Formula = dateFormulaWithMath; formulaCell.Calculate(); Assert.AreEqual(expectedDateWithMath, formulaCell.Value); formulaCell.Formula = quotedDateReferenceFormulaWithMath; formulaCell.Calculate(); Assert.AreEqual(expectedDateWithMath, formulaCell.Value); } }
public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true) { using (var pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = File.OpenRead(path)) { pck.Load(stream); } var ws = pck.Workbook.Worksheets.First(); DataTable tbl = new DataTable(); foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; DataRow row = tbl.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } } return(tbl); } }
//Read excel data to Datatable using EPPlus public static DataTable ExcelToDataTable(string path, string sheetName) { var pck = new OfficeOpenXml.ExcelPackage(); pck.Load(File.OpenRead(path)); var ws = pck.Workbook.Worksheets[sheetName]; DataTable tbl = new DataTable(); bool hasHeader = true; foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; var row = tbl.NewRow(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } tbl.Rows.Add(row); } pck.Dispose(); return(tbl); }
private void CreateExcelFile(string fileName, IList<SqliteExcelFormat> data) { if (File.Exists(fileName)) File.Delete(fileName); using (var excel = new ExcelPackage(new FileInfo(fileName))) { var ws = excel.Workbook.Worksheets.Add("Sheet1"); ws.Cells[1, 1].Value = "Vendor"; ws.Cells[1, 2].Value = "Incomes"; ws.Cells[1, 3].Value = "Expenses"; ws.Cells[1, 4].Value = "Taxes"; ws.Cells[1, 5].Value = "Financial Result"; for (int i = 0; i < data.Count; i++) { var tax = (data[i].TaxPercentage / 100) * data[i].Incomes; ws.Cells[i + 2, 1].Value = data[i].Vendor; ws.Cells[i + 2, 2].Value = data[i].Incomes; ws.Cells[i + 2, 3].Value = data[i].Expenses; ws.Cells[i + 2, 4].Value = Math.Round((decimal)tax, 2); ws.Cells[i + 2, 5].Value = Math.Round((decimal)(data[i].Incomes - data[i].Expenses - tax), 2); } ws.Column(1).AutoFit(); ws.Column(2).AutoFit(); ws.Column(3).AutoFit(); ws.Column(4).AutoFit(); ws.Column(5).AutoFit(); excel.Save(); } }
public ActionResult UnitFiscalOfficerExport(string id) { var authorizedDepartments = GetAuthorizedDepartments(id); if (authorizedDepartments.Count > 0) { var salaries = DbContext.Salaries .Include("Person") .Include("Person.Employments") .Include("Person.StatusType") .Include("RankType") .Include("AppointmentType") .Where(s => s.Person.Employments.Any(e => e.Department.UnitId == id)) .OrderBy(s => s.RankType.SequenceValue) .ThenBy(s => s.Person.LastName) .ToList(); var package = new ExcelPackage(); var report = new FiscalOfficerExport(package, authorizedDepartments, salaries, true); return File(report.Package.GetAsByteArray(), report.FileType, report.FileName); } return new HttpUnauthorizedResult(); }
public void Import(ExcelPackage Package, bool ClearDatabase) { Clr clr = new Clr(); if (ClearDatabase) { Cleaner cleander = new Cleaner(); cleander.Clear(); } Dictionary<ulong, object> objects = new Dictionary<ulong, object>(); List<ImporterRelation> relations = new List<ImporterRelation>(); Db.Transact(() => { for (int i = 2; i <= Package.Workbook.Worksheets.Count; i++) { ExcelWorksheet sheet = Package.Workbook.Worksheets[i]; this.ExportTable(sheet, objects, relations); } foreach (ImporterRelation r in relations) { this.ApplyRelation(objects, r); } }); }
public void Write(int rows, int cols) { String file = "testWs.xlsx"; if (File.Exists(file)) { File.Delete(file); } TimeSpan start; // Write Console.WriteLine(); Console.WriteLine("Test: " + rows + " x " + cols); Console.WriteLine("======="); Console.WriteLine("Writing"); start = Process.GetCurrentProcess().TotalProcessorTime; using (ExcelPackage package = new ExcelPackage(new FileInfo(file))) { int div = Math.Max(1, rows / 20); ExcelWorksheet ws = package.Workbook.Worksheets.Add("Stress", rows, cols); TimeRestart("Create", ref start); for (int row = 0; row < rows; row++) { for (int col = 0; col < cols; col++) { ws.Cell(row + 1, col + 1).Value = GetVal(row, col); } if (row % div == 0) { Console.Write("*"); } } Console.WriteLine("done"); TimeRestart("Write", ref start); package.Save(); } TimeRestart("Save", ref start); // Read Console.WriteLine("Reading"); start = Process.GetCurrentProcess().TotalProcessorTime; using (ExcelPackage package = new ExcelPackage(new FileInfo(file))) { int div = Math.Max(1, rows / 20); ExcelWorksheet ws = package.Workbook.Worksheets["Stress"]; TimeRestart("Open", ref start); for (int row = 0; row < rows; row++) { for (int col = 0; col < cols; col++) { ExcelCell cell = ws.Cell(row + 1, col + 1); String val = cell.Value; Assert.AreEqual(GetVal(row, col), cell.Value, "@" + (row + 1) + ", " + (col + 1)); } if (row % div == 0) { Console.Write("*"); } } Console.WriteLine("done"); TimeRestart("Read", ref start); } Console.WriteLine(" "); // skip }
private static void LoadFile1(ExcelPackage package) { //Create the Worksheet var sheet = package.Workbook.Worksheets.Add("Csv1"); //Create the format object to describe the text file var format = new ExcelTextFormat(); format.TextQualifier = '"'; format.SkipLinesBeginning = 2; format.SkipLinesEnd = 1; //Now read the file into the sheet. Start from cell A1. Create a table with style 27. First row contains the header. Console.WriteLine("Load the text file..."); var range = sheet.Cells["A1"].LoadFromText(new FileInfo("..\\..\\csv\\Sample9-1.txt"), format, TableStyles.Medium27, true); Console.WriteLine("Format the table..."); //Tables don't support custom styling at this stage(you can of course format the cells), but we can create a Namedstyle for a column... var dateStyle = package.Workbook.Styles.CreateNamedStyle("TableDate"); dateStyle.Style.Numberformat.Format = "YYYY-MM"; var numStyle = package.Workbook.Styles.CreateNamedStyle("TableNumber"); numStyle.Style.Numberformat.Format = "#,##0.0"; //Now format the table... var tbl = sheet.Tables[0]; tbl.ShowTotal = true; tbl.Columns[0].TotalsRowLabel = "Total"; tbl.Columns[0].DataCellStyleName = "TableDate"; tbl.Columns[1].TotalsRowFunction = RowFunctions.Sum; tbl.Columns[1].DataCellStyleName = "TableNumber"; tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum; tbl.Columns[2].DataCellStyleName = "TableNumber"; tbl.Columns[3].TotalsRowFunction = RowFunctions.Sum; tbl.Columns[3].DataCellStyleName = "TableNumber"; tbl.Columns[4].TotalsRowFunction = RowFunctions.Sum; tbl.Columns[4].DataCellStyleName = "TableNumber"; tbl.Columns[5].TotalsRowFunction = RowFunctions.Sum; tbl.Columns[5].DataCellStyleName = "TableNumber"; tbl.Columns[6].TotalsRowFunction = RowFunctions.Sum; tbl.Columns[6].DataCellStyleName = "TableNumber"; Console.WriteLine("Create the chart..."); //Now add a stacked areachart... var chart = sheet.Drawings.AddChart("chart1", eChartType.AreaStacked); chart.SetPosition(0, 630); chart.SetSize(800, 600); //Create one series for each column... for (int col = 1; col < 7; col++) { var ser = chart.Series.Add(range.Offset(1, col, range.End.Row - 1, 1), range.Offset(1, 0, range.End.Row - 1, 1)); ser.HeaderAddress = range.Offset(0, col, 1, 1); } //Set the style to 27. chart.Style = eChartStyle.Style27; sheet.View.ShowGridLines = false; sheet.Cells[sheet.Dimension.Address].AutoFitColumns(); }
public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true) { try { using (ExcelPackage pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = File.OpenRead(path)) { pck.Load(stream); } ExcelWorksheet ws = pck.Workbook.Worksheets.First(); DataTable tbl = new DataTable(); foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; DataRow row = tbl.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Value; } } return(tbl); } } catch (Exception ex) { Functions.SendErrorEmail(ex, "GLOBAL007 GetDataTableFromExcel [path:" + path + "]"); return(null); } }
public static List <T> getClassFromExcel <T>(string path, int fromRow, int fromColumn, int toColumn = 0) where T : class { using (var pck = new OfficeOpenXml.ExcelPackage()) { List <T> retList = new List <T>(); using (var stream = File.OpenRead(path)) { pck.Load(stream); } var ws = pck.Workbook.Worksheets.First(); toColumn = toColumn == 0 ? typeof(T).GetProperties().Count() : toColumn; for (var rowNum = fromRow; rowNum <= ws.Dimension.End.Row; rowNum++) { T objT = Activator.CreateInstance <T>(); Type myType = typeof(T); PropertyInfo[] myProp = myType.GetProperties(); var wsRow = ws.Cells[rowNum, fromColumn, rowNum, toColumn]; for (int i = 0; i < myProp.Count(); i++) { myProp[i].SetValue(objT, wsRow[rowNum, fromColumn + i].Text); } retList.Add(objT); } return(retList); } }
private DataTable read_excel_file(bool hasHeader = true) { using (var pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = File.OpenRead(excel_path)) { pck.Load(stream); } var ws = pck.Workbook.Worksheets[0]; DataTable tbl = new DataTable(); foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; DataRow row = tbl.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } } return(tbl); } //sourced online -> https://stackoverflow.com/questions/13396604/excel-to-datatable-using-epplus-excel-locked-for-editing }
/// <summary> /// Converts Excel into Json. This function first converts stream into ExcelPackage and passes overloaded Convert function. /// </summary> /// <param name="stream">Stream represents the Excel file</param> public JObject Compile(Stream stream) { using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(stream)) { return(Compile(excelPackage)); } }
private void OpenAndExportWFTDAStatsReport() { try { FileInfo newFile = new FileInfo(openFileName); ExcelPackage p = new ExcelPackage(newFile); //Add the Content sheet if (GameViewModel.Instance != null) { PopulateWftdaReport pop = new PopulateWftdaReport(p); pop.PopulateReport(); } FileInfo fi = new FileInfo(this.fileName); p.SaveAs(fi); System.Diagnostics.Process.Start(this.fileName); } catch (Exception exception) { ErrorViewModel.Save(exception, exception.GetType()); } }
public ActionResult Import1() { SqlConnection Con = new SqlConnection(); string Path = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; Con.ConnectionString = Path; DataTable DtNew = new DataTable(); SqlDataAdapter Adp = new SqlDataAdapter("select * from OrganizationCurators", Con); Adp.Fill(DtNew); if (DtNew.Rows.Count > 0) { string filepath = Server.MapPath("~/Content/ExcelExportfile1.xlsx"); FileInfo Files = new FileInfo(filepath); OfficeOpenXml.ExcelPackage excel = new OfficeOpenXml.ExcelPackage(Files); var sheetcreate = excel.Workbook.Worksheets.Add("OrganizationCuratorDat"); for (int i = 0; i < DtNew.Columns.Count; i++) { sheetcreate.Cells[1, i + 1].Value = DtNew.Columns[i].ColumnName.ToString(); } for (int i = 0; i < DtNew.Rows.Count; i++) { for (int j = 0; j < DtNew.Columns.Count; j++) { sheetcreate.Cells[i + 2, j + 1].Value = DtNew.Rows[i][j].ToString(); } } excel.Save(); } return(View()); }
public static DataTable ToDataTable(string path) { var app = new OfficeOpenXml.ExcelPackage(); app.Load(File.OpenRead(path)); var worksheet = app.Workbook.Worksheets.First(); DataTable datatable = new DataTable(); bool hasHeader = true; foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column]) { datatable.Columns.Add(hasHeader ? firstRowCell.Text.Trim() : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (var rowNum = startRow; rowNum <= worksheet.Dimension.End.Row; rowNum++) { var wsRow = worksheet.Cells[rowNum, 1, rowNum, worksheet.Dimension.End.Column]; var row = datatable.NewRow(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } datatable.Rows.Add(row); } app.Dispose(); return(datatable); }
public void ReadStreamWithTemplateWorkSheet() { FileStream instream = new FileStream(@"Test\Worksheet.xlsx", FileMode.Open, FileAccess.Read); MemoryStream stream = new MemoryStream(); using (ExcelPackage pck = new ExcelPackage(stream, instream)) { var ws = pck.Workbook.Worksheets["Perf"]; Assert.AreEqual(ws.Cells["H6"].Formula, "B5+B6"); var wsHF=pck.Workbook.Worksheets["Header footer"]; Assert.AreEqual(wsHF.HeaderFooter.firstFooter.LeftAlignedText, "First Left"); Assert.AreEqual(wsHF.HeaderFooter.firstFooter.RightAlignedText, "First Right"); Assert.AreEqual(wsHF.HeaderFooter.evenFooter.CenteredText, "even Centered"); Assert.AreEqual(wsHF.HeaderFooter.oddFooter.LeftAlignedText, "odd Left"); Assert.AreEqual(wsHF.HeaderFooter.oddFooter.CenteredText,"odd Centered"); Assert.AreEqual(wsHF.HeaderFooter.oddFooter.RightAlignedText, "odd Right"); foreach (var cell in pck.Workbook.Names["Data"]) { Assert.IsNotNull(cell); } pck.SaveAs(new FileInfo(@"Test\Worksheet2.xlsx")); } instream.Close(); }
//EPPlus /// <summary> /// 将指定的Excel的文件转换成DataTable /// </summary> /// <param name="path"></param> /// <param name="dtName"></param> /// <param name="hasHeader"></param> /// <returns></returns> public static DataTable excelToDtByEpplus(string path, string dtName, bool hasHeader = true) { using (var pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = File.OpenRead(path)) { pck.Load(stream); } var ws = pck.Workbook.Worksheets.First(); DataTable dt = new DataTable(dtName); foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { dt.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; DataRow row = dt.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } } pck.Dispose(); return(dt); } }
/// <summary> /// constructor /// </summary> /// <param name="dt">To export DataTable</param> /// <param name="tableStyle">Styling for entire table</param> /// <param name="headerStyle">Styling for header</param> /// <param name="itemStyle">Styling for the individual cells</param> public ExcelFileResult(DataTable dt, TableStyle tableStyle, TableItemStyle headerStyle, TableItemStyle itemStyle) : base("application/ms-excel") { this.dt = dt; Title = "Attendance Report "; Footer = "Powered By: Hasib, IT Department"; TitleExportDate = "Export Date: {0}"; this.tableStyle = tableStyle; this.headerStyle = headerStyle; this.itemStyle = itemStyle; ExcelPackage EXPackage = new ExcelPackage(); // provide defaults if (this.tableStyle == null) { this.tableStyle = new TableStyle(); this.tableStyle.BorderStyle = BorderStyle.Solid; this.tableStyle.BorderColor = Color.Black; this.tableStyle.BorderWidth = Unit.Parse("2px"); //this.tableStyle.BackColor = Color.LightGray; this.tableStyle.BackColor = Color.Azure; //this.tableStyle.BackImageUrl = Path.GetFullPath("D:/HOP/BOK.jpg"); //exPackage.Workbook.Properties.Author = "Hasib"; //exPackage.Workbook.Properties.Comments = "HopLunIT"; //exPackage.Workbook.Properties.Title = "HopLun (Bangladesh) Ltd. Reports"; } if (this.headerStyle == null) { this.headerStyle = new TableItemStyle(); this.headerStyle.BackColor = Color.LightGray; } }
public void Convert(Bitmap image, string savePath) { FileInfo newFile = new FileInfo(savePath); Bitmap zoomImage = Zoom(image);//縮放 if (LowQuality) {//低色彩品質 zoomImage = BitmapColorConvert(zoomImage); } var data = BitmapToColorArray(zoomImage); using (ExcelPackage excelPackage = new ExcelPackage(newFile)) { ExcelWorkbook workbook = excelPackage.Workbook; ExcelWorksheet worksheet = workbook.Worksheets.Add("pixelArt"); worksheet.DefaultColWidth = 10.0 / 70.0 * PixelSize;//10 = 70px for (int row = 0; row < data[0].Length; row++) { worksheet.Row(row + 1).Height = 10.0 / 13.0 * PixelSize;//10 = 13px for (int col = 0; col < data.Length; col++) { if(data[col][row].A == 0)continue;//透明背景 worksheet.Cells[row + 1, col + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + 1, col + 1].Style.Fill.BackgroundColor.SetColor(data[col][row]); } Console.WriteLine($"完成進度: {Math.Round(row / (double)data[0].Length * 100)}%"); } worksheet.Cells[data[0].Length + 1, 1].Value= "由ExcelPixelArt繪製,https://github.com/XuPeiYao/ExcelPixelArt/"; worksheet.Row(data[0].Length + 1).Height = 20; Console.WriteLine("檔案儲存中"); excelPackage.Save(); } }
public void WriteVBA() { var package = new ExcelPackage(); package.Workbook.Worksheets.Add("Sheet1"); package.Workbook.CreateVBAProject(); package.Workbook.VbaProject.Modules["Sheet1"].Code += "\r\nPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)\r\nMsgBox(\"Test of the VBA Feature!\")\r\nEnd Sub\r\n"; package.Workbook.VbaProject.Modules["Sheet1"].Name = "Blad1"; package.Workbook.CodeModule.Name = "DenHärArbetsboken"; package.Workbook.Worksheets[1].Name = "FirstSheet"; package.Workbook.CodeModule.Code += "\r\nPrivate Sub Workbook_Open()\r\nBlad1.Cells(1,1).Value = \"VBA test\"\r\nMsgBox \"VBA is running!\"\r\nEnd Sub"; //X509Store store = new X509Store(StoreLocation.CurrentUser); //store.Open(OpenFlags.ReadOnly); //package.Workbook.VbaProject.Signature.Certificate = store.Certificates[11]; var m = package.Workbook.VbaProject.Modules.AddModule("Module1"); m.Code += "Public Sub Test(param1 as string)\r\n\r\nEnd sub\r\nPublic Function functest() As String\r\n\r\nEnd Function\r\n"; var c = package.Workbook.VbaProject.Modules.AddClass("Class1", false); c.Code += "Private Sub Class_Initialize()\r\n\r\nEnd Sub\r\nPrivate Sub Class_Terminate()\r\n\r\nEnd Sub"; var c2 = package.Workbook.VbaProject.Modules.AddClass("Class2", true); c2.Code += "Private Sub Class_Initialize()\r\n\r\nEnd Sub\r\nPrivate Sub Class_Terminate()\r\n\r\nEnd Sub"; package.Workbook.VbaProject.Protection.SetPassword("EPPlus"); package.SaveAs(new FileInfo(@"c:\temp\vbaWrite.xlsm")); }
public ListaHojasExcel(string pathExcel) : this() { var archivo = new FileInfo(pathExcel); Excel = new ExcelPackage(archivo); foreach (var hoja in Hojas) { var indice = hoja.Key; var hojaActual = Excel.Workbook.Worksheets[indice]; var filaActual = 1; while (String.IsNullOrEmpty(hojaActual.GetValue<string>(filaActual, 2))) { filaActual++; } filaActual+=3; while (!String.IsNullOrEmpty(hojaActual.GetValue<string>(filaActual, 2))) { var oportunidad = Oportunidad.CrearOportunidad(indice); oportunidad.CargarDatos(hojaActual, filaActual); //if (oportunidad.Codigo == 13124) && otraOportunidad.Codigo == this.Codigo // Console.WriteLine("this"); hoja.Value.Add(oportunidad); filaActual++; } } }
public static DataTable GetDataTableFromExcel(Stream filestream) { using (var pck = new OfficeOpenXml.ExcelPackage()) { pck.Load(filestream); var ws = pck.Workbook.Worksheets.First(); DataTable tbl = new DataTable(); bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach) foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; var row = tbl.NewRow(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } tbl.Rows.Add(row); } return(tbl); } }
private void AddOrderLines(ExcelPackage excelFile, OrderExportQuery exportQuery) { ExcelWorksheet ordersWorksheet = excelFile.Workbook.Worksheets.Add("Order Lines"); IList<OrderLine> orderLines = GetOrderLines(exportQuery); Dictionary<string, Func<OrderLine, object>> columns = GetOrderLineColumns(); List<string> keys = columns.Keys.ToList(); for (int index = 0; index < keys.Count; index++) { string key = keys[index]; var cell = ordersWorksheet.Cells[1, index + 1]; cell.Value = _stringResourceProvider.GetValue("Excel Order Line Export - " + key, key); cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } for (int i = 0; i < orderLines.Count; i++) { var orderLine = orderLines[i]; for (int index = 0; index < keys.Count; index++) { string key = keys[index]; var row = i + 2; // +1 for the non-zero-based index and +1 for the header row var cell = ordersWorksheet.Cells[row, index + 1]; var value = columns[key](orderLine); cell.Value = value; if (value is DateTime) { cell.Style.Numberformat.Format = "dd/mm/yyyy hh:mm:ss"; } } } ordersWorksheet.Cells.AutoFitColumns(); }
public static Excel LoadExcel(string path) { FileInfo file = new FileInfo(path); ExcelPackage ep = new ExcelPackage(file); Excel xls = new Excel(ep.Workbook); return xls; }
public Stream Convert(Bitmap image) { var convertImage = image; if (LowQuality) {//低色彩品質 convertImage = BitmapColorConvert(convertImage); } var data = BitmapToColorArray(convertImage); using (ExcelPackage excelPackage = new ExcelPackage()) { ExcelWorkbook workbook = excelPackage.Workbook; ExcelWorksheet worksheet = workbook.Worksheets.Add("pixelArt"); worksheet.DefaultColWidth = 10.0 / 70.0 * PixelSize;//10 = 70px for (int row = 0; row < data[0].Length; row++) { worksheet.Row(row + 1).Height = 10.0 / 13.0 * PixelSize;//10 = 13px for (int col = 0; col < data.Length; col++) { if (data[col][row].A == 0) continue;//透明背景 worksheet.Cells[row + 1, col + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + 1, col + 1].Style.Fill.BackgroundColor.SetColor(data[col][row]); } } worksheet.Cells[data[0].Length + 1, 1].Value = "由ExcelPixelArt繪製,https://github.com/XuPeiYao/ExcelPixelArt/"; worksheet.Row(data[0].Length + 1).Height = 20; return new MemoryStream(excelPackage.GetAsByteArray()); } }
private List <ImportModel.rawImport> ReadInputFile() { List <ImportModel.rawImport> recordsIn = new List <ImportModel.rawImport>(); var fred = TempData["path"].ToString(); //var filesData = Directory.GetFiles(@fred); string path = Server.MapPath("~/App_Data/" + fred); //string path = Server.MapPath(fred.ToString()); var package = new OfficeOpenXml.ExcelPackage(new FileInfo(path)); OfficeOpenXml.ExcelWorksheet workSheet = package.Workbook.Worksheets[1]; for (int row = workSheet.Dimension.Start.Row; row <= workSheet.Dimension.End.Row; row++) { if (HasData(workSheet, row)) { decimal price = 0.0m; ImportModel.rawImport obj = new ImportModel.rawImport(); obj.Sku = GetPBSKU(workSheet, row); obj.FormSizeCode = GetPBFSCOde(workSheet, row); obj.Name = GetName(workSheet, row); obj.FormSize = GetFSDecription(workSheet, row); price = GetPrice(workSheet, row); obj.Price = price * 100; obj.Location = "PB"; recordsIn.Add(obj); } } return(recordsIn); }
public CMixExcel(Stream s) { if (IsUpperVer2003) _excelPackage = new ExcelPackage(s); else _HSSFWorkbook = new HSSFWorkbook(s); }
private static void VBASample2(DirectoryInfo outputDir) { //Open Sample 1 again ExcelPackage pck = new ExcelPackage(new FileInfo(outputDir.FullName + @"\sample1.xlsx")); //Create a vba project pck.Workbook.CreateVBAProject(); //Now add some code that creates a bubble chart... var sb = new StringBuilder(); sb.AppendLine("Public Sub CreateBubbleChart()"); sb.AppendLine("Dim co As ChartObject"); sb.AppendLine("Set co = Inventory.ChartObjects.Add(10, 100, 400, 200)"); sb.AppendLine("co.Chart.SetSourceData Source:=Range(\"'Inventory'!$B$1:$E$5\")"); sb.AppendLine("co.Chart.ChartType = xlBubble3DEffect 'Add a bubblechart"); sb.AppendLine("End Sub"); //Create a new module and set the code var module = pck.Workbook.VbaProject.Modules.AddModule("EPPlusGeneratedCode"); module.Code = sb.ToString(); //Call the newly created sub from the workbook open event pck.Workbook.CodeModule.Code = "Private Sub Workbook_Open()\r\nCreateBubbleChart\r\nEnd Sub"; //Optionally, Sign the code with your company certificate. /* X509Store store = new X509Store(StoreLocation.CurrentUser); store.Open(OpenFlags.ReadOnly); pck.Workbook.VbaProject.Signature.Certificate = store.Certificates[0]; */ //And Save as xlsm pck.SaveAs(new FileInfo(outputDir.FullName + @"\sample15-2.xlsm")); }
public void AverateIfsShouldCaluclateResult() { using (var pck = new ExcelPackage()) { var sheet = pck.Workbook.Worksheets.Add("test"); sheet.Cells["F4"].Value = 1; sheet.Cells["F5"].Value = 2; sheet.Cells["F6"].Formula = "2 + 2"; sheet.Cells["F7"].Value = 4; sheet.Cells["F8"].Value = 5; sheet.Cells["H4"].Value = 3; sheet.Cells["H5"].Value = 3; sheet.Cells["H6"].Formula = "2 + 2"; sheet.Cells["H7"].Value = 4; sheet.Cells["H8"].Value = 5; sheet.Cells["I4"].Value = 2; sheet.Cells["I5"].Value = 3; sheet.Cells["I6"].Formula = "2 + 2"; sheet.Cells["I7"].Value = 5; sheet.Cells["I8"].Value = 1; sheet.Cells["H9"].Formula = "AVERAGEIFS(F4:F8;H4:H8;\">3\";I4:I8;\"<5\")"; sheet.Calculate(); Assert.AreEqual(4.5d, sheet.Cells["H9"].Value); } }
protected void btn_download_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.TableName = "All_Contacts"; using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dlgf"].ConnectionString)) { SqlCommand cmd = new SqlCommand("[CTX].[sp_Select_All_List]", conn); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dt); conn.Close(); } string dir = Server.MapPath("All_List_output"); string milli = DateTime.Now.Millisecond.ToString(); FileInfo newFile = new FileInfo(dir + @"\CTX_"+ milli + ".xlsx"); if (newFile.Exists) { newFile.Delete(); // ensures we create a new workbook newFile = new FileInfo(dir + @"\CTX_" + milli + ".xlsx"); } using (ExcelPackage pack = new ExcelPackage(newFile)) { ExcelWorksheet ws = pack.Workbook.Worksheets.Add(dt.TableName.ToString()); ws.Cells["A1"].LoadFromDataTable(dt, true); pack.Save(); } string path = dir + @"\" + newFile.Name; Response.ContentType = "application/excel"; Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + newFile.Name + "\""); //file name must be in double quotes to allow spaces in the filename Response.TransmitFile(path); Response.End(); }
public void export() { SqlConnection conn = new SqlConnection(); string DatabaseEntities = ConfigurationManager.ConnectionStrings["Database"].ConnectionString; conn.ConnectionString = DatabaseEntities; conn.Open(); DataTable DtNew = new DataTable(); SqlDataAdapter adp = new SqlDataAdapter("Select * from Student", conn); adp.Fill(DtNew); if (DtNew.Rows.Count > 0) { ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial; var filename = new FileInfo("excel"); using (var excel = new OfficeOpenXml.ExcelPackage(filename)) { var sheetcreate = excel.Workbook.Worksheets.Add("Data"); for (int i = 0; i < DtNew.Columns.Count; i++) { sheetcreate.Cells[1, i + 1].Value = DtNew.Columns[i].ColumnName.ToString(); } for (int i = 0; i < DtNew.Rows.Count; i++) { for (int j = 0; j < DtNew.Columns.Count; j++) { sheetcreate.Cells[i + 2, j + 1].Value = DtNew.Rows[i][j].ToString(); } } Session["DownloadExcel_FileManager"] = excel.GetAsByteArray(); } } conn.Close(); }
/// <summary> /// Call-back handler that builds simple PivotTable in Excel /// </summary> public void CreatePivotTable(OfficeOpenXml.ExcelPackage excelPackage, ExcelWorksheet worksheet, string pivotRangeName) { string pivotWorksheetName = "Pivot-" + worksheet.Name.Replace(" ", ""); var wsPivot = excelPackage.Workbook.Worksheets.Add(pivotWorksheetName); excelPackage.Workbook.Worksheets.MoveBefore(pivotWorksheetName, worksheet.Name); ExcelRange dataRange = worksheet.Cells["A1:" + worksheet.Dimension.End.Address]; var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells[1, 1], dataRange, pivotWorksheetName); pivotTable.ShowHeaders = true; pivotTable.UseAutoFormatting = true; pivotTable.ApplyWidthHeightFormats = true; pivotTable.ShowDrill = true; pivotTable.FirstHeaderRow = 1; // first row has headers pivotTable.FirstDataCol = 1; // first col of data pivotTable.FirstDataRow = 2; // first row of data foreach (string row in groupByColumns) { var field = pivotTable.Fields[row]; pivotTable.RowFields.Add(field); field.Sort = eSortType.Ascending; } foreach (string column in summaryColumns) { var field = pivotTable.Fields[column]; ExcelPivotTableDataField result = pivotTable.DataFields.Add(field); } pivotTable.DataOnRows = false; }
public void WriteListToExcel(string fileName, SharepointList list) { var newFile = new FileInfo(fileName); if (newFile.Exists) { newFile.Delete(); newFile = new FileInfo(fileName); } using (ExcelPackage package = new ExcelPackage(newFile)) { // add a new worksheet to the empty workbook ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory"); //Add the headers for (var i = 0; i < list.Columns.Count; i++) { var column = list.Columns[i]; worksheet.Cells[1, i+1].Value = column.Name; } //Add the items for (var i = 0; i < list.Rows.Count; i++) { var row = list.Rows[i]; for (var j = 0; j < row.Values.Count; j++) { var value = row.Values[j] != null ? row.Values[j].ToString() : row.Values[j]; worksheet.Cells[i+2, j+1].Value = value; } } package.Save(); } }
public static void RunSample2(string FilePath) { Console.WriteLine("Reading column 2 of {0}", FilePath); Console.WriteLine(); FileInfo existingFile = new FileInfo(FilePath); using (ExcelPackage xlPackage = new ExcelPackage(existingFile)) { // get the first worksheet in the workbook ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1]; int iCol = 2; // the column to read // output the data in column 2 for (int iRow = 1; iRow < 5; iRow++) Console.WriteLine("\tCell({0},{1}).Value={2}", iRow, iCol, worksheet.Cells[iRow, iCol].Value); // output the formula in row 6 Console.WriteLine("\tCell({0},{1}).Formula={2}", 5, iCol, worksheet.Cells[5, iCol].Formula); Console.WriteLine("\tCell({0},{1}).FormulaR1C1={2}", 5, iCol, worksheet.Cells[5, iCol].FormulaR1C1); } // the using statement automatically calls Dispose() which closes the package. Console.WriteLine(); Console.WriteLine("Sample 2 complete"); Console.WriteLine(); }
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 (((dynamic)dr[j - 1])["isColor"] == true) { Color color = 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 NarrativeReport(IEnumerable<Area> areas, IEnumerable<Fund> funds) { Funds = funds; ExcelPackage package = new ExcelPackage(); ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Funding Request Report"); #region Table Labels Row++; sheet = WriteTableLabels(sheet); #endregion #region Area Data foreach (Area area in areas) { sheet = WriteAreaData(sheet, area); } #endregion sheet = PerformFinalFormatting(sheet); this.BinaryData = package.GetAsByteArray(); this.FileType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.FileName = "FoundationPortal_" + System.DateTime.Now.ToShortDateString() + ".xlsx"; }
public byte[] GetShirtsFile(List<GuestShirtGetDto> notOrderedShirts) { using (var pck = new ExcelPackage()) { //Create the worksheet var ws = pck.Workbook.Worksheets.Add($"NotOrderedShirts-{DateTime.Today.ToShortDateString()}"); if (notOrderedShirts.Count > 0) { var properties = notOrderedShirts.First().GetType().GetProperties(); for (var i = 0; i < properties.Length; i++) { ws.Cells[1, i + 1].Value = properties[i].Name; } ws.Row(1).Style.Font.Bold = true; for (var j = 0; j < notOrderedShirts.Count; j++) { var item = notOrderedShirts[j]; for (var i = 0; i < properties.Length; i++) { ws.Cells[j + 2, i + 1].Value = item.GetType().GetProperty(properties[i].Name).GetValue(item); } } } return pck.GetAsByteArray(); } }
public static void Main(string[] args) { Encoding utf8 = Encoding.GetEncoding("UTF-8"); string filePath = args[0]; string feedType = args[1]; Console.WriteLine(filePath); Console.WriteLine(feedType); StreamWriter w = null; try{ w = new StreamWriter(@"./pla.txt", true, utf8); //ファイルパスのチェック if (!File.Exists(filePath)) { Console.WriteLine("指定されたファイルが存在しません。"); return; } //Feedのタイプをチェック if (feedType != Pla.PLA) { Console.WriteLine("指定されたFeedのタイプが無効です。"); return; } Excel.ExcelPackage exApp = new Excel.ExcelPackage(new FileInfo(filePath)); Excel.ExcelWorksheet ws = exApp.Workbook.Worksheets[1]; int getRow = 0; while (true) { Feed.Feed f = null; switch (feedType) { case Feed.Pla.PLA: f = new Feed.Pla(); break; } string pid = ws.Cells[f.StartRow + getRow, f.PidCol].Text; string title = ws.Cells[f.StartRow + getRow, f.TitleCol].Text; if (title.Equals("")) { break; } f.Pid = pid; f.Title = title; w.WriteLine(string.Format(f.OutputFormat, f.Pid, f.Title)); getRow = getRow + 1; } } catch (Exception e) { Console.WriteLine(e.Message); } finally { w.Close(); } }
protected override bool CloseInternal() { if (_obj != null) { _obj.Dispose(); _obj = null; return(true); } return(false); }
public static DataTable TransformWorksheetToDataTable(System.IO.Stream excelSpreadsheetStream, string worksheetName, bool firstRowIsHeader = false, Model.ColumnNameType columnType = Model.ColumnNameType.CellValue) { lib.global.setup(); using (OfficeOpenXml.ExcelPackage pkg = new OfficeOpenXml.ExcelPackage(excelSpreadsheetStream)) { DataTable table = TransformWorksheetToDataTable(pkg, worksheetName, firstRowIsHeader, columnType); return(table); } }
private void Close() { application.Dispose(); workBook.Dispose(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(application); application = null; workBook = null; workSheet = null; //System.GC.Collect(); }
public async Task <(bool, string)> ImportCustomersFromExcel(string path, int startColumn = 1) { var customers = new List <Customer>(); using (var excelPackage = new OfficeOpenXml.ExcelPackage()) { using (var fileStream = File.OpenRead(path)) { excelPackage.Load(fileStream); } var workSheet = excelPackage.Workbook.Worksheets.First(); for (int rowNum = startColumn; rowNum <= workSheet.Dimension.End.Row; rowNum++) { var customer = new Customer() { FirstName = workSheet.Cells[rowNum, 1].Value?.ToString() ?? string.Empty, LastName = workSheet.Cells[rowNum, 2].Value?.ToString() ?? string.Empty, CompanyName = workSheet.Cells[rowNum, 3].Value?.ToString() ?? string.Empty, Address = workSheet.Cells[rowNum, 4].Value?.ToString() ?? string.Empty, City = workSheet.Cells[rowNum, 5].Value?.ToString() ?? string.Empty, State = workSheet.Cells[rowNum, 6].Value?.ToString() ?? string.Empty, Post = workSheet.Cells[rowNum, 7].Value?.ToString() ?? string.Empty, Phone1 = workSheet.Cells[rowNum, 8].Value?.ToString() ?? string.Empty, Phone2 = workSheet.Cells[rowNum, 9].Value?.ToString() ?? string.Empty, Email = workSheet.Cells[rowNum, 10].Value?.ToString() ?? string.Empty, Web = workSheet.Cells[rowNum, 11].Value?.ToString() ?? string.Empty }; if (!string.IsNullOrEmpty(customer.Email)) { var existedCustomer = await _customerRepository.GetCustomerByEmail(customer.Email); if (existedCustomer != null) { if (customers.Any(uc => uc.Email == customer.Email)) { return(false, string.Format("Email {0} has duplicated records. Please check the data again.", customer.Email)); } else { existedCustomer = UpdatePropertyCustomer(existedCustomer, customer); customers.Add(existedCustomer); } } else { customers.Add(customer); } } } } return(customers.Any() ? (await _customerRepository.BulkImportCustomerData(customers), string.Empty) : (true, string.Empty)); }
private static void DuplicateExcelSheetByClosedXML(string baseDir, string bookPath) { var outPath = Path.Combine(baseDir, "Book1 - EPPlus.xlsx"); using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(bookPath))) using (var book = package.Workbook) { book.Worksheets.Add("Sheet2", book.Worksheets["Sheet1"]); package.SaveAs(new FileInfo(outPath)); } }
public void Issue15159() { var fs = new FileStream(@"C:\temp\bug\DeleteColFormula\FormulasIssue\demo.xlsx", FileMode.OpenOrCreate); using (var package = new OfficeOpenXml.ExcelPackage(fs)) { package.Save(); } fs.Seek(0, SeekOrigin.Begin); var fs2 = fs; }
public void ValidateCaseInsensitiveCustomProperties() { using (var p = new OfficeOpenXml.ExcelPackage()) { p.Workbook.Worksheets.Add("CustomProperties"); p.Workbook.Properties.SetCustomPropertyValue("Foo", "Bar"); p.Workbook.Properties.SetCustomPropertyValue("fOO", "bAR"); Assert.AreEqual("bAR", p.Workbook.Properties.GetCustomPropertyValue("fOo")); } }
public static DataTable ToDataTable(System.IO.Stream stream) { DataTable dt = null; using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(stream)) { dt = ToDataTable(package); } return(dt); }
public ExcelImportResult <T> ImportTo <T>(string path) { var results = new ExcelImportResult <T>(); using (var pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = System.IO.File.OpenRead(path)) { pck.Load(stream); } var workSheet = pck.Workbook.Worksheets.First(); var columnsCount = workSheet.Dimension.End.Column; var rowCount = workSheet.Dimension.End.Row; //var headerRow = workSheet.Cells[1, 1, 1, columnsCount]; //var headList = headerRow.Select(cell => cell).ToList(); var rowModel = new GeneralRowModel <T>(workSheet); results = new ExcelImportResult <T>(rowModel); for (int rowNum = 1; rowNum <= rowCount; rowNum++) { var errors = new List <Error>(); if (rowModel.HeaderRow != rowNum) { var row = workSheet.Cells[rowNum, 1, rowNum, columnsCount]; var values = Activator.CreateInstance <T>(); foreach (var generalCell in rowModel) { var cell = row[rowNum, generalCell.ForColumn]; try { SetValue(generalCell, values, cell); } catch (Exception ex) { var error = new Error(rowNum, generalCell.ForColumn, generalCell.Name, ex); errors.Add(error); } } var newRow = new Row <T>(rowNum, values, errors); results.Rows.Add(newRow); } } } return(results); }
public void Issue13492() { using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\Bug13492.xlsx"))) { ExcelWorkbook workBook = package.Workbook; var worksheet = workBook.Worksheets[1]; var rt = worksheet.Cells["K31"].RichText.Text; package.Save(); } }
public void Issue15198() { using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\Output.xlsx"), new FileInfo(@"c:\temp\bug\demo.xlsx"))) { ExcelWorkbook workBook = package.Workbook; var worksheet = workBook.Worksheets[1]; worksheet.DeleteRow(12); package.Save(); } }
public void Issue14788() { using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\i15195_Save.xlsx"), new FileInfo(@"c:\temp\bug\GetWorkSheetXmlBad.xlsx"))) { ExcelWorkbook workBook = package.Workbook; var worksheet = workBook.Worksheets[1]; worksheet.InsertColumn(8, 2); package.Save(); } }
public void Issue15179() { using (var package = new OfficeOpenXml.ExcelPackage()) { var ws = package.Workbook.Worksheets.Add("MergeDeleteBug"); ws.Cells["E3:F3"].Merge = true; ws.Cells["E3:F3"].Merge = false; ws.DeleteRow(2, 6); ws.Cells["A1"].Value = 0; var s = ws.Cells["A1"].Value.ToString(); } }
public ActionResult DepartmentFiscalOfficerExport(string id) { if (User.IsInRole("manage-all") || User.IsInRole("read-" + id)) { var department = DbContext.Departments .Include("Unit") .Where(d => d.Id == id) .ToList()[0]; var salaries = DbContext.Salaries .Include("Person") .Include("Person.Employments") .Include("Person.StatusType") .Include("RankType") .Include("AppointmentType") .Where(s => s.Person.Employments.Any(e => e.DepartmentId == id)) .OrderBy(s => s.RankType.SequenceValue) .ThenBy(s => s.Person.LastName) .ToList(); var package = new ExcelPackage(); var departments = new List<Department>() { department }; var report = new FiscalOfficerExport(package, departments, salaries, false); return File(report.Package.GetAsByteArray(), report.FileType, report.FileName); } return new HttpUnauthorizedResult(); }
public byte[] Create() { //using (var package = new ExcelPackage(new FileInfo(@"c:\temp\ttc-excels\testy-" + DateTime.Now.ToString("yyyy-M-d HH.mm.ss") + ".xlsx"))) using (var package = new ExcelPackage()) { var allPlayerSheet = package.Workbook.Worksheets.Add(ExcelExportResources.SheetAllPlayers); ExcelHelper.SetHeader( allPlayerSheet, ExcelExportResources.PlayerName, ExcelExportResources.PlayerAddress, ExcelExportResources.PlayerCity, ExcelExportResources.PlayerPhone, ExcelExportResources.PlayerEmail ); SetPlayersSheet(allPlayerSheet); allPlayerSheet.Cells.AutoFitColumns(); allPlayerSheet.Column(5).Width = 50; var vttlSheet = package.Workbook.Worksheets.Add(ExcelExportResources.SheetVttl); SetVttlSheet(vttlSheet); var sportaSheet = package.Workbook.Worksheets.Add(ExcelExportResources.SheetSporta); SetSportaSheet(sportaSheet); //package.Save(); // save to file return package.GetAsByteArray(); } }