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