public static DataTable GetDataTableFromExcel(string path)
{
using (var pck = new ExcelPackage())
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets[0];
var tbl = new DataTable();
var cells = ws.Cells[1, 1, 1, ws.Dimension.End.Column];
for (var i = cells.Start.Column; i <= cells.End.Column; i++)
{
tbl.Columns.Add(cells[1, i].Value.ToString());
}
for (var rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
var row = tbl.NewRow();
for (var cellIndex = 1; cellIndex <= ws.Dimension.End.Column; cellIndex++)
{
row[cellIndex - 1] = wsRow[rowNum, cellIndex].Value;
}
tbl.Rows.Add(row);
}
return tbl;
}
}
protected override void BeginProcessing()
{
base.BeginProcessing();
_ExcelPck = new ExcelPackage();
var finalPath = this.CompileFinalPath();
using (var stream = new FileStream(finalPath, FileMode.Open))
{
_ExcelPck.Load(stream);
}
}
public JsonResult GetVendorUploadDetails(string fileName, string type,string file)
{
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
string path = Path.Combine(Server.MapPath("~/VendorReportsExcelTemplate"), fileName);
//var existingFile = new FileInfo(path);
DataTable dtVendor = new DataTable();
DataTable dtErrorRec = new DataTable();
Vendor ObjVd = new Vendor();
var VendorInfo = new VendorInfo();
using (var pck = new OfficeOpenXml.ExcelPackage())
{
try
{
var workBook = pck.Workbook;
if (workBook != null)
{
using (var stream = System.IO.File.OpenRead(path))
{
pck.Load(stream);
}
var currentWorksheet = pck.Workbook.Worksheets.First();
int Count=0; DataSet ds = new DataSet();
Count = ValidateExcel(type, currentWorksheet, Count, ds);
if (Count > 0)
{
string data = JsonConvert.SerializeObject(ds, Formatting.Indented);
return Json(data);
}
dtVendor = CreateVendorDataTable();
dtErrorRec = CreateErrorDataTable();
for (int rowNumber = ExcelStartRow + 1; rowNumber <= currentWorksheet.Dimension.End.Row; rowNumber++)
// read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
{
//Column 4 : VendorRefNo And Column 5 : Amount ARE MANDITORY
if (currentWorksheet.Cells[rowNumber, 4].Value != null && currentWorksheet.Cells[rowNumber, 5].Value != null)
{
try
{
DataRow dr = dtVendor.NewRow();
currentWorksheet.Cells[rowNumber, 1].Style.Numberformat.Format = "mm/dd/yyyy";
dr["Date"] = (currentWorksheet.Cells[rowNumber, 1].Value == null ? DBNull.Value.ToString() : currentWorksheet.Cells[rowNumber, 1].Text);
dr["UsedBy"] = (currentWorksheet.Cells[rowNumber, 2].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 2].Value);
dr["Description"] = (currentWorksheet.Cells[rowNumber, 3].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 3].Value);
dr["ReferenceNo"] = (currentWorksheet.Cells[rowNumber, 4].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 4].Value);
dr["Amount"] = (currentWorksheet.Cells[rowNumber, 5].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 5].Value);
dtVendor.Rows.Add(dr);
}
catch (Exception)
{
DataRow dr = dtErrorRec.NewRow();
currentWorksheet.Cells[rowNumber, 1].Style.Numberformat.Format = "mm/dd/yyyy";
dr["Date"] = (currentWorksheet.Cells[rowNumber, 1].Value == null ? DBNull.Value.ToString() : currentWorksheet.Cells[rowNumber, 1].Text);
dr["UsedBy"] = (currentWorksheet.Cells[rowNumber, 2].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 2].Value);
dr["Description"] = (currentWorksheet.Cells[rowNumber, 3].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 3].Value);
dr["ReferenceNo"] = (currentWorksheet.Cells[rowNumber, 4].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 4].Value);
dr["Amount"] = (currentWorksheet.Cells[rowNumber, 5].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 5].Value);
dtErrorRec.Rows.Add(dr);
}
}
}
}
ObjVd.dtvendor = dtVendor;
ObjVd.dtErrorRec = dtErrorRec;
ObjVd.RecordCount = dtVendor.Rows.Count;
ObjVd.Type = type;
ObjVd.Name = file;
string UserIDSession = Convert.ToString(System.Web.HttpContext.Current.Session["UserID"]);
bool checkForEmptyExcel = (dtVendor.Rows.Count == 0 && dtErrorRec.Rows.Count == 0) ? false : true;
if (!string.IsNullOrEmpty(UserIDSession))
{
if (checkForEmptyExcel)
{
ObjVd.UploadedBy = Convert.ToInt32(UserIDSession);
VendorInfo = ObjVd.InsertVendorRecords(ObjVd);
}
}
}
catch (Exception ex)
{
log.Error("Error: " + ex);
VendorInfo.ExceptionMessage = "exception";
}
finally
{
log.Debug("End: " + methodBase.Name);
if (System.IO.File.Exists(path))
System.IO.File.Delete(path);
}
}
return Json(VendorInfo);
}
//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);
}
}
//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);
}
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 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);
}
}
public static ExcelWorkbook LoadWorkbookFromBytes(ExcelPackage package, byte[] bytes)
{
using (var stream = new MemoryStream(bytes)) {
package.Load(stream);
return package.Workbook;
}
}
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 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
}
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);
}
}
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 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 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));
}
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 static void GetDataTableFromExcel(Stream fileStream, ApplicationDbContext context, bool hasHeader = true)
{
try
{
List <ExcelExam.Data.DatabaseModel.City> c = new List <ExcelExam.Data.DatabaseModel.City>();
List <ExcelExam.Data.DatabaseModel.Sale> s = new List <ExcelExam.Data.DatabaseModel.Sale>();
using (var pck = new OfficeOpenXml.ExcelPackage())
{
// using (var stream = File.OpenRead(path))
using (var stream = fileStream)
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First();
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];
c.Add(new ExcelExam.Data.DatabaseModel.City()
{
CityName = wsRow[rowNum, ws.Dimension.End.Column].Text,
});
}
context.Cities.AddRange(c);
context.SaveChanges();
c = null;
ws = pck.Workbook.Worksheets[1];
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];
var ss = new ExcelExam.Data.DatabaseModel.Sale();
ss.Price = float.Parse(wsRow[rowNum, ws.Dimension.End.Column].Text);
ss.PersonFullName = wsRow[rowNum, ws.Dimension.End.Column - 1].Text;
ss.ProductCode = wsRow[rowNum, ws.Dimension.End.Column - 2].Text;
ss.NameProduct = wsRow[rowNum, ws.Dimension.End.Column - 3].Text;
ss.CityName = wsRow[rowNum, ws.Dimension.End.Column - 4].Text;
ss.Id = int.Parse(wsRow[rowNum, ws.Dimension.End.Column - 5].Text);
s.Add(ss);
}
context.Sales.AddRange(s);
context.SaveChanges();
s = null;
}
}
catch (Exception ex)
{
Console.WriteLine("***********************************************************************");
Console.WriteLine(ex.ToString());
Console.WriteLine("***********************************************************************");
}
}
public async Task <bool> GetDataFromExcel(FileEntity fileEntity, bool hasHeader = true)
{
var pck = new OfficeOpenXml.ExcelPackage();
pck.Load(fileEntity.File.OpenReadStream());
using (var transaction = context.Database.BeginTransaction())
{
try
{
var generalWs = pck.Workbook.Worksheets.First();
List <ExcelWorksheet> fullDataWs = new List <ExcelWorksheet>();;
var Result = InsertExcelRowToGeneralBilling(generalWs.Cells, fileEntity.CustomerId, fileEntity.SupplierId);
var generalInsertResult = Result.summary;
var invoiceNumber = Result.invoiceNumber;
if (generalInsertResult == null)
{
return(false);
}
for (int i = 0; i < pck.Workbook.Worksheets.Count; i++)
{
if (pck.Workbook.Worksheets[i].Name == "חיובים וזיכויים" || pck.Workbook.Worksheets[i].Name == "סכומים מחשבוניות קודמות")
{
fullDataWs.Add(pck.Workbook.Worksheets[i]);
}
}
if (fullDataWs == null)
{
transaction.Rollback();
return(false);
}
var startRow = hasHeader ? 2 : 1;
var result = await disassembleExcel(fullDataWs, hasHeader, generalInsertResult, invoiceNumber);
await context.BezekFileInfo.AddRangeAsync(result);
await context.SaveChangesAsync();
transaction.Commit();
pck.Dispose();
return(true);
}
catch (Exception e)
{
transaction.Rollback();
pck.Dispose();
throw e;
}
}
}
public static DataTable getDataFromExcelFileMM(string filePath, bool hasHeader)
{
try
{
if (!File.Exists(filePath))
{
throw new Exception("File not found!");
}
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(filePath))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First();
var result = new DataTable();
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
result.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++)
{
DataRow row = result.NewRow();
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
int i = 0;
foreach (var cell in (IEnumerable)wsRow.Value)
{
row[i] = cell != null ? cell : "";
i++;
}
if (row.ItemArray.Any(item => item != ""))
{
result.Rows.Add(row);
}
}
return(result);
}
}
catch (Exception ex)
{
throw new Exception("File not found, changing extension", ex);
}
}
public Dictionary <string, DataTable> GetDataTableFromAllWorksheets(string path, int workSheet = 0, bool hasHeader = true)
{
var dts = new Dictionary <string, DataTable>();
try {
var watchGlobal = Stopwatch.StartNew();
var fileInfo = new FileInfo(path);
ExcelReaderEventHub.OnOpenStart(null, new ExcelOpenStartEventArgs(path));
OnOpenStart(null, new ExcelOpenStartEventArgs(path));
using (var pck = new OfficeOpenXml.ExcelPackage()) {
using (var stream = File.OpenRead(path)) {
pck.Load(stream);
}
var worksheets = pck.Workbook.Worksheets;
foreach (var ws in worksheets)
{
dts[ws.Name.Trim()] = CreateDataTable(path, hasHeader, fileInfo, ws);
}
}
watchGlobal.Stop();
var elapsedMsGlobal = watchGlobal.ElapsedMilliseconds;
var tGlobal = TimeSpan.FromMilliseconds(elapsedMsGlobal);
string finalTime = string.Format("{0:D2}h:{1:D2}m:{2:D2}s:{3:D3}ms",
tGlobal.Hours,
tGlobal.Minutes,
tGlobal.Seconds,
tGlobal.Milliseconds);
ExcelReaderEventHub.OnOpenEnd(null, new ExcelOpenEndEventArgs(path, finalTime));
OnOpenEnd(null, new ExcelOpenEndEventArgs(path, finalTime));
} catch (Exception e) {
Console.WriteLine(e.Message);
ExcelReaderEventHub.OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace));
OnOpenError(null, new ExcelOpenErrorEventArgs(path, e.Message, e.StackTrace));
}
return(dts);
}
public async Task <BudgetEntity[]> ConvertFileToEntites(IFormFile file)
{
var pck = new OfficeOpenXml.ExcelPackage();
pck.Load(file.OpenReadStream());
OfficeOpenXml.ExcelWorksheet excel = pck.Workbook.Worksheets[0];
return(await Task.Run(() =>
{
List <BudgetEntity> budgets = new List <BudgetEntity>();
for (int rowNum = 2; rowNum <= excel.Dimension.End.Row; rowNum++)
{
var result = ConvertExcelRowToBudget(excel, rowNum);
if (result != null)
{
budgets.Add(result);
}
}
return budgets.ToArray();
}));
}
private DataTable GautiDataTable(string path)
{
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])
{
if (firstRowCell.Text == "Vieta" || firstRowCell.Text == "Taškai")
{
tbl.Columns.Add(firstRowCell.Text);
}
else
{
tbl.Columns.Add(firstRowCell.Text);
}
}
var startRow = 2;
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)
{
try
{
row[cell.Start.Column - 1] = cell.Text;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
return(tbl);
}
}
private static void 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();
Console.WriteLine(ws.Dimension.End.Row);
List <Dados> dados = new List <Dados>();
for (int i = 2; i < ws.Dimension.End.Row; i++)
{
try
{
Dados linha = new Dados();
linha.Bloco = int.Parse(ws.Cells[i, 1].Text);
linha.Tratamentos = int.Parse(ws.Cells[i, 2].Text);
linha.TemperaturaSolo = decimal.Parse(ws.Cells[i, 3].Text);
linha.TemperaturaSuperficieSolo = decimal.Parse(ws.Cells[i, 4].Text);
linha.TemperaturaCopa = decimal.Parse(ws.Cells[i, 5].Text);
linha.DiametroColeto = decimal.Parse(ws.Cells[i, 6].Text);
linha.AlturaPlanta = decimal.Parse(ws.Cells[i, 7].Text);
linha.NumeroFolhas = decimal.Parse(ws.Cells[i, 8].Text);
linha.Data = DateTime.Parse(ws.Cells[i, 9].Text);
linha.Codigo = int.Parse(ws.Cells[i, 10].Text);
dados.Add(linha);
}
catch (Exception ex)
{
Console.WriteLine("Erro Linha " + ws.Cells[i, 1].Address + " Exception " + ex.Message + " " + ex.InnerException);
}
}
Contexto db = new Contexto();
db.DADOS.AddRange(dados);
//db.SaveChanges();
}
}
public 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();
int i = 0;
foreach (var cell in wsRow)
{
i++;
if (i > tbl.Columns.Count)
{
break;
}
row[cell.Start.Column - 1] = cell.Text;
}
}
DataColumn newDataColumn = new DataColumn("SheetName", typeof(System.String));
newDataColumn.DefaultValue = ws.Name;
tbl.Columns.Add(newDataColumn);
return(tbl);
}
}
public static DataTable getDataTableFromExcel(string path) //添加了健壮性检验
{
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();
if (ws.Cells[1, 1].Value.ToString().Equals("ISBN") &&
ws.Cells[1, 2].Value.ToString().Equals("Book Name") &&
ws.Cells[1, 3].Value.ToString().Equals("Author") &&
ws.Cells[1, 4].Value.ToString().Equals("Publish") &&
ws.Cells[1, 5].Value.ToString().Equals("Store"))
{
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);
}
return(tbl);
}
else
{
MessageBox.Show("Sorry you have to check your excel's header and obey our naming conventions");
return(null);
}
}
}
public static List <T> getClassFromExcel <T>(string path) 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();
bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
var fielddic = new Dictionary <string, int>();
int idx = 0;
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
string field = (hasHeader ? firstRowCell.Text : string.Format("Column{0}", firstRowCell.Start.Column));
fielddic.Add(field, idx++);
}
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];
T objT = Activator.CreateInstance <T>();
Type myType = typeof(T);
PropertyInfo[] myProp = myType.GetProperties();
for (int i = 0; i < myProp.Count(); i++)
{
int colidx = fielddic[myProp[i].Name];
myProp[i].SetValue(objT, wsRow[rowNum, colidx + 1].Text);
}
retList.Add(objT);
}
return(retList);
}
}
/// <summary>
/// Retrieves data from excel file in form of Dictionary, where key is name of the excel sheet and the data is list of strings
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static Dictionary <string, List <string> > getExcelDataICS(string filePath)
{
if (!File.Exists(filePath))
{
throw new Exception("File not found!");
}
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(filePath))
{
pck.Load(stream);
}
return(pck.Workbook.Worksheets.Select(w =>
{
return new
{
key = w.Name,
values = w.Cells.Select(c => c.Text).ToList()
};
}).ToDictionary(t => t.key, t => t.values));
}
}
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
excelImportPath = openFileDialog1.FileName;
if (System.IO.File.Exists(excelImportPath))
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(excelImportPath))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First();
var startRow = 2; // EPPlus dòng đầu tiên tính từ 1 => dòng đầu là Hearder, dòng 2 bắt đầu dữ liệu cần import
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) // ws.Dimension.End.Row => đọc đến row cuối cùng có định dạng
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; // ws.Dimension.End.Column => đọc đến col cuối cùng có định dạng
var maHang = wsRow[rowNum, 2].Text;
if (!string.IsNullOrEmpty(maHang))
{
var tenHang = wsRow[rowNum, 3].Text;
var dVT = wsRow[rowNum, 4].Text;
dtImport.Rows.Add(rowNum - 1, maHang, tenHang, dVT);
}
}
dataGridView1.DataSource = dtImport;
}
}
}
private void SurasytiIExcel(DataTable table)
{
string path = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
path = System.IO.Path.Combine(path, "Leaderboard.xlsx");
FileInfo ExistingFile = new FileInfo(path);
using (var pck = new OfficeOpenXml.ExcelPackage(ExistingFile))
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets[1];
ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Column(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(3).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A1"].LoadFromDataTable(table, true);
pck.Save();
}
}
public static DataTable getDataFromExcelFile(string filePath, bool hasHeader)
{
if (!File.Exists(filePath))
{
throw new Exception("File is not found!");
}
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(filePath))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First();
var reuslt = new DataTable();
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
reuslt.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++)
{
DataRow row = reuslt.NewRow();
var p = row as IDictionary <String, object>;
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
int i = 0;
foreach (var cell in wsRow)
{
row[i] = cell.Text;
i++;
}
reuslt.Rows.Add(row);
}
return(reuslt);
}
}
public static DataTable getDataTableFromExcel(string path, string sheetName)
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets[sheetName];
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)
{
try
{
row[cell.Start.Column - 1] = String.IsNullOrEmpty(cell.Text) == true ? String.Empty : cell.Text;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
tbl.Rows.Add(row);
}
return(tbl);
}
}
public void SaveToFile(string file)
{
Stream fileStream;
ExcelPackage excel = new ExcelPackage();
if (File.Exists(file))
{
fileStream = File.OpenRead(file);
excel.Load(fileStream);
fileStream.Close();
File.Delete(file);
}
ExcelWorksheet sheet = excel.Workbook.Worksheets[m_WORKSHEET_NAME];
if (sheet != null)
excel.Workbook.Worksheets.Delete(m_WORKSHEET_NAME);
sheet = excel.Workbook.Worksheets.Add(m_WORKSHEET_NAME);
int rowIdx = 1;
sheet.Cells[rowIdx, (int)SheetColumnIdx.LISTING_ID].Value = "Listing ID";
sheet.Cells[rowIdx, (int)SheetColumnIdx.ITEM_ID].Value = "Item ID";
sheet.Cells[rowIdx, (int)SheetColumnIdx.ITEM_NAME].Value = "Item Name";
sheet.Cells[rowIdx, (int)SheetColumnIdx.ITEM_LEVEL].Value = "Item Level";
sheet.Cells[rowIdx, (int)SheetColumnIdx.ITEM_RARITY].Value = "Item Rarity";
sheet.Cells[rowIdx, (int)SheetColumnIdx.QUANTITY].Value = "Quantity";
sheet.Cells[rowIdx, (int)SheetColumnIdx.PRICE].Value = "Price";
sheet.Cells[rowIdx, (int)SheetColumnIdx.LISTING_TIME].Value = "Listing Time";
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED_TIME].Value = "Fulfilled Time";
sheet.Cells[rowIdx, (int)SheetColumnIdx.TYPE].Value = "Type";
sheet.Cells[rowIdx, (int)SheetColumnIdx.CANCELLED].Value = "Cancelled";
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED].Value = "Fulfilled";
sheet.Cells[rowIdx, (int)SheetColumnIdx.INVESTMENT_LIQUID].Value = "Investment (Liquid)";
sheet.Cells[rowIdx, (int)SheetColumnIdx.INVESTMENT_SOLID].Value = "Investment (Solid)";
sheet.Cells[rowIdx, (int)SheetColumnIdx.INVESTMENT].Value = "Investment";
sheet.Cells[rowIdx, (int)SheetColumnIdx.REVENUE_UNREALIZED].Value = "Revenue (Unrealized)";
sheet.Cells[rowIdx, (int)SheetColumnIdx.REVENUE_REALIZED].Value = "Revenue (Realized)";
// get a sorted list of the elements in the dictionary
List<ListingInfo> listOfListings = Listings.Values.ToList();
listOfListings.Sort((a, b) =>
{
return -a.ListingTime.CompareTo(b.ListingTime);
});
foreach (ListingInfo listing in listOfListings)
{
rowIdx++;
// data columns
sheet.Cells[rowIdx, (int)SheetColumnIdx.LISTING_ID].Value = listing.ListingId;
sheet.Cells[rowIdx, (int)SheetColumnIdx.ITEM_ID].Value = listing.ItemId;
sheet.Cells[rowIdx, (int)SheetColumnIdx.ITEM_NAME].Value = listing.ItemName;
sheet.Cells[rowIdx, (int)SheetColumnIdx.ITEM_LEVEL].Value = listing.ItemLevel;
sheet.Cells[rowIdx, (int)SheetColumnIdx.ITEM_RARITY].Value = listing.ItemRarity;
sheet.Cells[rowIdx, (int)SheetColumnIdx.QUANTITY].Value = listing.Quantity;
sheet.Cells[rowIdx, (int)SheetColumnIdx.PRICE].Value = listing.Price;
sheet.Cells[rowIdx, (int)SheetColumnIdx.LISTING_TIME].Value = listing.ListingTime.ToOADate();
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED_TIME].Value = (listing.FulfilledTime == null ? null : (object)listing.FulfilledTime.Value.ToOADate());
sheet.Cells[rowIdx, (int)SheetColumnIdx.TYPE].Value = listing.Type.ToString();
sheet.Cells[rowIdx, (int)SheetColumnIdx.CANCELLED].Value = listing.Cancelled;
// formula columns
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED].Formula = string.Format("IF({0} <> \"\", {1}, {2})",
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED_TIME].Address, true.ToString(), false.ToString());
sheet.Cells[rowIdx, (int)SheetColumnIdx.INVESTMENT_LIQUID].Formula = string.Format("IF(AND({0} = \"{1}\", {2} = {3}, {4} = {5}), {6} * {7}, 0)",
sheet.Cells[rowIdx, (int)SheetColumnIdx.TYPE].Address, ListingInfo.ListingType.BUY.ToString(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED].Address, false.ToString().ToUpper(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.CANCELLED].Address, false.ToString().ToUpper(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.QUANTITY].Address, sheet.Cells[rowIdx, (int)SheetColumnIdx.PRICE].Address);
sheet.Cells[rowIdx, (int)SheetColumnIdx.INVESTMENT_SOLID].Formula = string.Format("IF({0} = \"{1}\", CEILING({2} * {3} * {4}, 1), IF(AND({5} = \"{6}\", {7} = {8}), {9} * {10}, 0))",
sheet.Cells[rowIdx, (int)SheetColumnIdx.TYPE].Address, ListingInfo.ListingType.SELL.ToString(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.QUANTITY].Address, sheet.Cells[rowIdx, (int)SheetColumnIdx.PRICE].Address, ListingInfo.LISTING_FEE_PERCENT,
sheet.Cells[rowIdx, (int)SheetColumnIdx.TYPE].Address, ListingInfo.ListingType.BUY.ToString(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED].Address, true.ToString().ToUpper(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.QUANTITY].Address, sheet.Cells[rowIdx, (int)SheetColumnIdx.PRICE].Address);
sheet.Cells[rowIdx, (int)SheetColumnIdx.INVESTMENT].Formula = string.Format("{0} + {1}",
sheet.Cells[rowIdx, (int)SheetColumnIdx.INVESTMENT_LIQUID].Address, sheet.Cells[rowIdx, (int)SheetColumnIdx.INVESTMENT_SOLID].Address);
sheet.Cells[rowIdx, (int)SheetColumnIdx.REVENUE_UNREALIZED].Formula = string.Format("IF(AND({0} = \"{1}\", {2} = {3}, {4} = {5}), FLOOR({6} * {7} * (1.0 - {8}), 1), 0)",
sheet.Cells[rowIdx, (int)SheetColumnIdx.TYPE].Address, ListingInfo.ListingType.SELL.ToString(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED].Address, false.ToString().ToUpper(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.CANCELLED].Address, false.ToString().ToUpper(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.QUANTITY].Address, sheet.Cells[rowIdx, (int)SheetColumnIdx.PRICE].Address, ListingInfo.SALE_FEE_PERCENT);
sheet.Cells[rowIdx, (int)SheetColumnIdx.REVENUE_REALIZED].Formula = string.Format("IF(AND({0} = \"{1}\", {2} = {3}), FLOOR({4} * {5} * (1.0 - {6}), 1), 0)",
sheet.Cells[rowIdx, (int)SheetColumnIdx.TYPE].Address, ListingInfo.ListingType.SELL.ToString(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.FULFILLED].Address, true.ToString().ToUpper(),
sheet.Cells[rowIdx, (int)SheetColumnIdx.QUANTITY].Address, sheet.Cells[rowIdx, (int)SheetColumnIdx.PRICE].Address, ListingInfo.SALE_FEE_PERCENT);
}
// format dates properly
sheet.Column((int)SheetColumnIdx.LISTING_TIME).Style.Numberformat.Format = m_WORKSHEET_DATE_FORMAT;
sheet.Column((int)SheetColumnIdx.FULFILLED_TIME).Style.Numberformat.Format = m_WORKSHEET_DATE_FORMAT;
// format amounts properly
sheet.Column((int)SheetColumnIdx.PRICE).Style.Numberformat.Format = m_WORKSHEET_AMOUNT_FORMAT;
sheet.Column((int)SheetColumnIdx.INVESTMENT_LIQUID).Style.Numberformat.Format = m_WORKSHEET_AMOUNT_FORMAT;
sheet.Column((int)SheetColumnIdx.INVESTMENT_SOLID).Style.Numberformat.Format = m_WORKSHEET_AMOUNT_FORMAT;
sheet.Column((int)SheetColumnIdx.INVESTMENT).Style.Numberformat.Format = m_WORKSHEET_AMOUNT_FORMAT;
sheet.Column((int)SheetColumnIdx.REVENUE_UNREALIZED).Style.Numberformat.Format = m_WORKSHEET_AMOUNT_FORMAT;
sheet.Column((int)SheetColumnIdx.REVENUE_REALIZED).Style.Numberformat.Format = m_WORKSHEET_AMOUNT_FORMAT;
// make a pretty table
ExcelRange tableRange = sheet.Cells[1, 1, sheet.Dimension.End.Row, sheet.Dimension.End.Column];
ExcelTable table = sheet.Tables.Add(tableRange, m_WORKSHEET_TABLE_NAME);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium20;
table.ShowTotal = true;
table.Columns[(int)SheetColumnIdx.INVESTMENT_LIQUID - 1].TotalsRowFunction = RowFunctions.Sum;
table.Columns[(int)SheetColumnIdx.INVESTMENT_SOLID - 1].TotalsRowFunction = RowFunctions.Sum;
table.Columns[(int)SheetColumnIdx.INVESTMENT - 1].TotalsRowFunction = RowFunctions.Sum;
table.Columns[(int)SheetColumnIdx.REVENUE_UNREALIZED - 1].TotalsRowFunction = RowFunctions.Sum;
table.Columns[(int)SheetColumnIdx.REVENUE_REALIZED - 1].TotalsRowFunction = RowFunctions.Sum;
// set all the column widths
for (int colIdx = 1; colIdx <= sheet.Dimension.End.Column; colIdx++)
{
// clear formatting on the header cells
sheet.Cells[1, colIdx].Style.Numberformat.Format = string.Empty;
// auto-fit that column
sheet.Column(colIdx).AutoFit();
}
// save the new file
fileStream = File.OpenWrite(file);
excel.SaveAs(fileStream);
fileStream.Close();
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
if (Extension == ".xls")
{
//string notxlsx = ("This is not an xlsx file");
//ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true);
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//Counts the number of cells in a row and determines the columns from that.
int counter = sheet.GetRow(0).Cells.Count;
// J < number of columns needs to be exact at this moment
for (int j = 0; j < counter; j++)
{
// set each column to a - ** letters
// dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//Get first row and set the headers for each cell
//dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString());
//Get each cell value in row 0 and return its string for a column name.
dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue);
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
//Hackish way to remove the bad first row made by getting column names
dt.Rows.RemoveAt(0);
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
//Bind the data
GridView1.DataBind();
sheet.Dispose();
hssfworkbook.Dispose();
}
else
{
//Create a new epplus package using openxml
var pck = new OfficeOpenXml.ExcelPackage();
//load the package with the filepath I got from my fileuploader above on the button
//pck.Load(new System.IO.FileInfo(FilePath).OpenRead());
//stream the package
FileStream stream = new FileStream(FilePath, FileMode.Open);
pck.Load(stream);
//So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause.
//Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all.
var ws = pck.Workbook.Worksheets[1];
//This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"];
if (ws == null)
{
ws = pck.Workbook.Worksheets.Add("Sheet1");
// Obiviously I didn't add anything to the sheet so probably can count on it being blank.
}
//I created this datatable for below.
DataTable tbl = new DataTable();
//My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx
var hdr = bool.Parse(isHDR);
Console.WriteLine(hdr);
//Set the bool value for from above.
var hasHeader = hdr;
//Setup the table based on the value from my bool
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);
}
//Bind Data to GridView
//I have all my info in the tbl dataTable so the datasource for the Gridview1 is set to tbl
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = tbl;
//Bind the data
GridView1.DataBind();
pck.Save();
pck.Dispose();
stream.Close();
// string pathD = FilePath;
FilePath = null;
stream = null;
// var fileToDelete = new FileInfo(pathD);
// fileToDelete.Delete();
}
}
public void NetworkdaysShouldReturnNumberOfDaysWithHolidayRange()
{
using (MemoryStream ms = new MemoryStream())
{
// do something...
using (var package = new ExcelPackage())
{
package.Load(ms);
}
}
using (var package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("test");
ws.Cells["A1"].Formula = "NETWORKDAYS(DATE(2016,1,1), DATE(2016,1,20),B1)";
ws.Cells["B1"].Formula = "DATE(2016,1,15)";
ws.Calculate();
Assert.AreEqual(13, ws.Cells["A1"].Value);
}
}
private ExcelPackage OpenExcelFromByteArray()
{
try
{
ExcelPackage pck = new ExcelPackage();
using (Stream stream = new MemoryStream(excelPackegInBytes))
{
pck.Load(stream);
return pck;
}
}
catch (Exception ex)
{
throw ex;
}
}
protected override List<ExcelProject> GetListRows(string fileName)
{
var stream = File.OpenRead(fileName);
var excelPack = new ExcelPackage();
excelPack.Load(stream);
stream.Close();
var listRows = new List<ExcelProject>();
for (int i = 1; i <= excelPack.Workbook.Worksheets[1].Dimension.Rows; i++)
{
var structure = new ExcelProject();
var dataRow = excelPack.Workbook.Worksheets[1].Cells[i, 1, i, 18];
structure.Row = new object[18];
for (int j = 0; j < 18; j++)
{
structure.Row[j] = ((object[,])dataRow.Value)[0, j];
}
listRows.Add(structure);
}
return listRows;
}
protected virtual List<ExcelProject> GetListRows(string fileName)
{
var stream = File.OpenRead(fileName);
var excelPack = new ExcelPackage();
excelPack.Load(stream);
stream.Close();
var header = excelPack.Workbook.Worksheets[1].Cells[2, 1, 2, 18];
var listRows = new List<ExcelProject>();
var listCaption = new List<string>();
for (int i = 0; i < 18; i++)
{
listCaption.Add(((object[,])header.Value)[0, i].ToString());
}
for (int i = 3; i <= excelPack.Workbook.Worksheets[1].Dimension.Rows; i++)
{
var structure = new ExcelProject();
var dataRow = excelPack.Workbook.Worksheets[1].Cells[i, 1, i, 18];
structure.Row = new object[18];
for (int j = 0; j < 18; j++)
{
structure.Row[j] = ((object[,])dataRow.Value)[0, j];
}
structure.ColumnsCaptions = listCaption;
structure.FileNames = _docsFileName.GetFilesByNameDrawing(structure.GetField(3)?.ToString());
listRows.Add(structure);
}
return listRows;
}
public void DeleteColumnAfterRangeLimitThrowsArgumentException()
{
// Arrange
ExcelPackage pck = new ExcelPackage();
using (
Stream file =
Assembly.GetExecutingAssembly()
.GetManifestResourceStream("EPPlusTest.TestWorkbooks.PreDeleteColumn.xls"))
{
pck.Load(file);
}
var wsData = pck.Workbook.Worksheets[1];
// Act
wsData.DeleteColumn(16385);
// Assert
Assert.Fail();
}
public string FillSheet(TimeSheetExcel ts, string templatepath, string timesheetpath)
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(templatepath))
{
int RowNum = 8;
string fileName = "";
pck.Load(stream);
List <ExcelWorksheet> wsList = new List <ExcelWorksheet>();
var ws1 = pck.Workbook.Worksheets.First();
wsList.Add(ws1);
int sheetindex = 0;
var ws = wsList[sheetindex];
wsList[sheetindex].Cells["C2:N2"].Value = ts.EmployeeName;
wsList[sheetindex].Cells["C5:F5"].Value = ts.Year;
wsList[sheetindex].Cells["L5:S5"].Value = ts.FromDay;
wsList[sheetindex].Cells["Z5:AF5"].Value = ts.ToDay;
if (ts.LiveInEmployee)
{
wsList[sheetindex].Cells["S28"].Value = "X";
}
else
{
wsList[sheetindex].Cells["W28"].Value = "X";
}
double cnt = (double)ts.TimeRecordsLst.Count / (double)15;
int count = 0;
if ((cnt % 1) > 0)
{
count = (int)cnt;
count++;
}
else
{
count = (int)cnt;
}
for (int i = 1; i < count; i++)
{
wsList.Add(pck.Workbook.Worksheets.Add("Sheet (" + i.ToString() + ")", ws));
}
double tot = 0;
foreach (TimeRecordExcel tr in ts.TimeRecordsLst)
{
wsList[sheetindex].Cells["A" + RowNum.ToString()].Value = tr.MonthNumber.ToString("00");
wsList[sheetindex].Cells["B" + RowNum.ToString()].Value = tr.Day.ToString("00");
wsList[sheetindex].Cells["C" + RowNum.ToString()].Value = tr.ServiceCode;
wsList[sheetindex].Cells["D" + RowNum.ToString() + ":F" + RowNum.ToString()].Value = tr.EnterPlan.ToUpper();
wsList[sheetindex].Cells["G" + RowNum.ToString()].Value = tr.Backup.ToString().ToUpper();
wsList[sheetindex].Cells["H" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn1.Hours.ToString("00"))[0];
wsList[sheetindex].Cells["I" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn1.Hours.ToString("00"))[1];
wsList[sheetindex].Cells["J" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn1.Mins.ToString("00"))[0];
wsList[sheetindex].Cells["K" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn1.Mins.ToString("00"))[1];
wsList[sheetindex].Cells["L" + RowNum.ToString() + ":M" + RowNum.ToString()].Value = tr.TimeIn1.AmOrPm.ToUpper();
wsList[sheetindex].Cells["N" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut1.Hours.ToString("00"))[0];
wsList[sheetindex].Cells["O" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut1.Hours.ToString("00"))[1];
wsList[sheetindex].Cells["P" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut1.Mins.ToString("00"))[0];
wsList[sheetindex].Cells["Q" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut1.Mins.ToString("00"))[1];
wsList[sheetindex].Cells["R" + RowNum.ToString() + ":S" + RowNum.ToString()].Value = tr.TimeOut1.AmOrPm.ToUpper();
var calculatedHours = CalculateTotalWorkingHours(tr.TimeIn1.Hours, tr.TimeIn1.Mins, tr.TimeIn1.AmOrPm
, tr.TimeOut1.Hours, tr.TimeOut1.Mins, tr.TimeOut1.AmOrPm);
if (calculatedHours.TotalHours < 0)
{
tr.TotalWorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
tr.TotalWorkedHours += calculatedHours;
}
if (tr.TimeIn2.Hours != 0)
{
wsList[sheetindex].Cells["T" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn2.Hours.ToString("00"))[0];
wsList[sheetindex].Cells["U" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn2.Hours.ToString("00"))[1];
wsList[sheetindex].Cells["V" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn2.Mins.ToString("00"))[0];
wsList[sheetindex].Cells["W" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn2.Mins.ToString("00"))[1];
wsList[sheetindex].Cells["X" + RowNum.ToString() + ":Y" + RowNum.ToString()].Value = tr.TimeIn2.AmOrPm.ToUpper();
wsList[sheetindex].Cells["Z" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut2.Hours.ToString("00"))[0];
wsList[sheetindex].Cells["AA" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut2.Hours.ToString("00"))[1];
wsList[sheetindex].Cells["AB" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut2.Mins.ToString("00"))[0];
wsList[sheetindex].Cells["AC" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut2.Mins.ToString("00"))[1];
wsList[sheetindex].Cells["AD" + RowNum.ToString() + ":AE" + RowNum.ToString()].Value = tr.TimeOut2.AmOrPm.ToUpper();
calculatedHours = CalculateTotalWorkingHours(tr.TimeIn2.Hours, tr.TimeIn2.Mins, tr.TimeIn2.AmOrPm
, tr.TimeOut2.Hours, tr.TimeOut2.Mins, tr.TimeOut2.AmOrPm);
if (calculatedHours.TotalHours < 0)
{
tr.TotalWorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
tr.TotalWorkedHours += calculatedHours;
}
if (tr.ServiceCode.Equals("032"))
{
if (calculatedHours.TotalHours <= 0)
{
ts.Total032WorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
ts.Total032WorkedHours += calculatedHours;
}
}
else if (tr.ServiceCode.Equals("011"))
{
if (calculatedHours.TotalHours <= 0)
{
ts.Total011WorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
ts.Total011WorkedHours += calculatedHours;
}
}
}
calculatedHours = CalculateTotalWorkingHours(tr.TimeIn1.Hours, tr.TimeIn1.Mins, tr.TimeIn1.AmOrPm
, tr.TimeOut1.Hours, tr.TimeOut1.Mins, tr.TimeOut1.AmOrPm);
// Calculate total working hours per each Service Code
if (tr.ServiceCode.Equals("032"))
{
if (calculatedHours.TotalHours < 0)
{
ts.Total032WorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
ts.Total032WorkedHours += calculatedHours;
}
}
else if (tr.ServiceCode.Equals("011"))
{
if (calculatedHours.TotalHours < 0)
{
ts.Total011WorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
ts.Total011WorkedHours += calculatedHours;
}
}
wsList[sheetindex].Cells["AF" + RowNum.ToString()].Value = Math.Abs(tr.TotalWorkedHours.TotalHours);
tot += tr.TotalWorkedHours.TotalHours;
RowNum++;
if (RowNum > 22)
{
RowNum = 8;
// save before go to the next sheet the total working hours per each Service Code
if (ts.Total032WorkedHours.TotalHours != 0)
{
wsList[sheetindex].Cells["F24:G24"].Value = "032";
wsList[sheetindex].Cells["H24:J24"].Value = Math.Abs(ts.Total032WorkedHours.TotalHours);
}
if (ts.Total011WorkedHours.TotalHours != 0)
{
wsList[sheetindex].Cells["F26:G26"].Value = "011";
wsList[sheetindex].Cells["H26:J26"].Value = Math.Abs(ts.Total011WorkedHours.TotalHours);
}
wsList[sheetindex].Cells["AF26"].Value = tot;
wsList[sheetindex].Cells["AA26:AD26"].Value = tot;
tot = 0;
ts.Total011WorkedHours = new TimeSpan();
ts.Total032WorkedHours = new TimeSpan();
sheetindex++;
// ws = wsList[sheetindex];
}
}
if (RowNum <= 22)
{
// save in the last sheet the total working hours per each Service Code
if (ts.Total032WorkedHours.TotalHours != 0)
{
wsList[sheetindex].Cells["F24:G24"].Value = "032";
wsList[sheetindex].Cells["H24:J24"].Value = Math.Abs(ts.Total032WorkedHours.TotalHours);
}
if (ts.Total011WorkedHours.TotalHours != 0)
{
wsList[sheetindex].Cells["F26:G26"].Value = "011";
wsList[sheetindex].Cells["H26:J26"].Value = Math.Abs(ts.Total011WorkedHours.TotalHours);
}
wsList[sheetindex].Cells["AF26"].Value = tot;
wsList[sheetindex].Cells["AA26:AD26"].Value = tot;
tot = 0;
ts.Total011WorkedHours = new TimeSpan();
ts.Total032WorkedHours = new TimeSpan();
}
string dirPath = Path.Combine(timesheetpath + @"/" + ts.EmployeeName + "_" + ts.FromDay.Replace("/", "-") + "_" + ts.ToDay.Replace("/", "-"));
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
fileName = dirPath + @"/" + ts.EmployeeName + "_" + ts.FromDay.Replace("/", "-") + "_" + ts.ToDay.Replace("/", "-") + ".xlsx";
// oXL.DisplayAlerts = false;
byte[] data = pck.GetAsByteArray();
File.WriteAllBytes(fileName, data);
//oWB.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
// false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//oWB.Close();
}
}
// oXL.ActiveWorkbook.Sheets[1].Activate();
// oXL.UserControl = false;
return("");
}
public ActionResult InvoiceFromCSS2(string command)
{
try
{
if (command == "Sent To ACCPAC")
{
#region Download
DataSet dsInvoice = Billing.GetCABPrepareInvoiceFromCSS2();
if (dsInvoice.Tables[0].Rows.Count > 0)
{
var fileName = "INVOICE_FROM_CSS2_" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";
//var outputDir = HttpContext.Server.MapPath("~/CSS2ACCPACINTEGRATION/FromCSS2/");
//System.IO.FileInfo file = new FileInfo(outputDir + fileName);
string path = ConfigurationManager.AppSettings["AccpacFolderPath"].ToString() + "FromCSS2/";
System.IO.FileInfo file = new FileInfo(path + fileName);
using (var excely = new ExcelPackage(file))
{
#region WorkSheet 1
ExcelWorksheet worksheet = excely.Workbook.Worksheets.Add("INVOICE_FROM_CSS2");
//for Columns Names
worksheet.Cells[1, 1].LoadFromDataTable(dsInvoice.Tables[0], true);
//For Data
for (int i = 0; i < dsInvoice.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < dsInvoice.Tables[0].Columns.Count; j++)
{
string cellvalue = dsInvoice.Tables[0].Rows[i][j].ToString();
worksheet.Cells[i + 2, j + 1].Value = cellvalue;
}
}
#endregion
#region Work Sheet 2
ExcelWorksheet worksheet1 = excely.Workbook.Worksheets.Add("INVOICE_DETAILS_FROM_CSS2");
//for Columns Names
worksheet1.Cells[1, 1].LoadFromDataTable(dsInvoice.Tables[1], true);
//For Data
for (int i = 0; i < dsInvoice.Tables[1].Rows.Count; i++)
{
for (int j = 0; j < dsInvoice.Tables[1].Columns.Count; j++)
{
string cellvalue = dsInvoice.Tables[1].Rows[i][j].ToString();
worksheet1.Cells[i + 2, j + 1].Value = cellvalue;
}
}
#endregion
#region Update SentStatus
string CABMasterIDs = string.Empty;
foreach (DataRow dr in dsInvoice.Tables[0].Rows)
{
CABMasterIDs += dr["CNTITEM"].ToString() + ",";
}
Billing.CABUpdateSentStatus(CABMasterIDs.TrimEnd(','));
#endregion
#region Invoice Log
string Direction = "InvoiceFromCSS2";
int InvoiceCount = dsInvoice.Tables[0].Rows.Count;
int DetailsCount = dsInvoice.Tables[1].Rows.Count;
int Result = Billing.InsertCABInvoiceLog(Direction, InvoiceCount, DetailsCount, fileName);
#endregion
excely.Save();
worksheet.Protection.IsProtected = true;
worksheet1.Protection.IsProtected = true;
}
}
#endregion
}
else if (command == "Read From ACCPAC")
{
#region Read Excel
//var ExcelFiles = Directory.EnumerateFiles(HttpContext.Server.MapPath("~/CSS2ACCPACINTEGRATION/FromACCPAC/"), "*.xlsx");
string path = ConfigurationManager.AppSettings["AccpacFolderPath"].ToString() + "FromACCPAC/";
var ExcelFiles = Directory.EnumerateFiles(path, "*.xlsx");
foreach (string currentFile in ExcelFiles)
{
using (var pck = new ExcelPackage())
{
using (var stream = System.IO.File.OpenRead(currentFile))
{
pck.Load(stream);
}
int StartRow = 2;
int sheetCount = 1;
DataSet ds = new DataSet();
foreach (var currentWorksheet in pck.Workbook.Worksheets)
{
DataTable dtSheet = new DataTable(currentWorksheet.Name);
#region Sheet 1
if (sheetCount == 1)
{
dtSheet.Columns.Add("CNTITEM");
dtSheet.Columns.Add("INVOICENO");
dtSheet.Columns.Add("HSCODE_FROMACCPAC");
for (int rowNumber = StartRow; rowNumber <= currentWorksheet.Dimension.End.Row; rowNumber++)
{
if (currentWorksheet.Cells[rowNumber, 1].Value != null)
{
DataRow dr = dtSheet.NewRow();
dr["CNTITEM"] = currentWorksheet.Cells[rowNumber, 1].Value;
dr["INVOICENO"] = currentWorksheet.Cells[rowNumber, 2].Value;
dr["HSCODE_FROMACCPAC"] = currentWorksheet.Cells[rowNumber, 3].Value;
dtSheet.Rows.Add(dr);
}
}
}
#endregion
#region Sheet 2
else if (sheetCount == 2)
{
dtSheet.Columns.Add("CNTITEM");
dtSheet.Columns.Add("CNTSOURCE");
dtSheet.Columns.Add("CNTLINE");
dtSheet.Columns.Add("HSCODE_FROMACCPAC");
for (int rowNumber = StartRow; rowNumber <= currentWorksheet.Dimension.End.Row; rowNumber++)
{
DataRow dr = dtSheet.NewRow();
dr["CNTITEM"] = currentWorksheet.Cells[rowNumber, 1].Value;
dr["CNTSOURCE"] = currentWorksheet.Cells[rowNumber, 2].Value;
dr["CNTLINE"] = currentWorksheet.Cells[rowNumber, 3].Value;
dr["HSCODE_FROMACCPAC"] = currentWorksheet.Cells[rowNumber, 4].Value;
dtSheet.Rows.Add(dr);
}
}
#endregion
ds.Tables.Add(dtSheet);
sheetCount++;
}
#region Update ReceiveStatus
int ReceiveStatus = Billing.CABUpdateReceiveStatus(ds.Tables[0], ds.Tables[1]);
#endregion
#region Invoice Log
string Direction = "InvoiceFromACCPAC";
int InvoiceCount = ds.Tables[0].Rows.Count;
int DetailsCount = ds.Tables[1].Rows.Count;
int Result = Billing.InsertCABInvoiceLog(Direction, InvoiceCount, DetailsCount, System.IO.Path.GetFileName(currentFile));
#endregion
}
#region Move Files To Archive
string Destination = currentFile;
Destination = Destination.Replace("FromACCPAC", @"FromACCPAC\Archive\");
System.IO.File.Move(currentFile, Destination);
#endregion
}
#endregion
}
//else if (command == "CSS2 to CSS1 Integration")
//{
// try
// {
// var InvoicePreviewData = Billing.InsertCSS2toCC1Data();
// HelperClasses.InsertScheduleHistory("Run from UI", "ALL", "CSS2 to CSS1 Integration", false);
// return View();
// }
// catch (Exception ex)
// {
// log.Error("Error: " + ex);
// return View();// return Json("");
// }
//}
}
catch
{
}
return View();
}
public static DataTable GetDataTableFromExcel_EPPlus(string path, bool hasHeaders, out string error_str)
{
error_str = "";
try
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
//var ws = pck.Workbook.Worksheets.First();
var ws = pck.Workbook.Worksheets[1]; // 무조건 첫번째 탭을 읽어오도록 한다(index는 1부터 시작함)
DataTable tbl = new DataTable(ws.Name);
if (ws.Dimension == null)
{
return(tbl);
}
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
string col_name = (hasHeaders ? firstRowCell.Text : "");
if (col_name.Length == 0)
{
col_name = string.Format("Column {0}", firstRowCell.Start.Column);
}
tbl.Columns.Add(col_name);
}
var startRow = hasHeaders ? 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;
int col_index = cell.Start.Column - 1;
if (col_index >= tbl.Columns.Count)
{
break;
}
row[col_index] = cell.Value;
}
}
return(tbl);
}
}
catch (Exception ex1)
{
error_str = ex1.Message;
return(null);
}
}
/// <summary>
/// Load an xlsx Excel file into a datatable
/// </summary>
public static DataTable ParseExcelStream2DataTable(MemoryStream AStream,
bool AHasHeader = false,
int AWorksheetID = 0,
List <string>AColumnsToImport = null)
{
ExcelPackage pck = new ExcelPackage();
pck.Load(AStream);
int countWorksheets = 0;
ExcelWorksheet worksheet = null;
foreach (ExcelWorksheet worksheetLoop in pck.Workbook.Worksheets)
{
if (countWorksheets == AWorksheetID)
{
worksheet = worksheetLoop;
}
countWorksheets++;
}
DataTable result = new DataTable();
if (worksheet == null)
{
return result;
}
List <string>ColumnNames = new List <string>();
foreach (ExcelRangeBase firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
{
string ColumnName = (AHasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
ColumnNames.Add(ColumnName);
if ((AColumnsToImport != null) && !AColumnsToImport.Contains(ColumnName))
{
continue;
}
result.Columns.Add(ColumnName);
}
int firstDataRow = AHasHeader ? 2 : 1;
for (int countRow = firstDataRow; countRow <= worksheet.Dimension.End.Row; countRow++)
{
ExcelRangeBase ExcelRow = worksheet.Cells[countRow, 1, countRow, worksheet.Dimension.End.Column];
DataRow NewRow = result.NewRow();
foreach (ExcelRangeBase cell in ExcelRow)
{
if ((AColumnsToImport != null) && !AColumnsToImport.Contains(ColumnNames[cell.Start.Column - 1]))
{
continue;
}
NewRow[ColumnNames[cell.Start.Column - 1]] = cell.Value;
}
result.Rows.Add(NewRow);
}
return result;
}
public void DeleteFirstTwoColumnsFromRangeColumnsShouldBeDeleted()
{
// Arrange
ExcelPackage pck = new ExcelPackage();
using (
Stream file =
Assembly.GetExecutingAssembly()
.GetManifestResourceStream("EPPlusTest.TestWorkbooks.PreDeleteColumn.xls"))
{
pck.Load(file);
}
var wsData = pck.Workbook.Worksheets[1];
// Act
wsData.DeleteColumn(1, 2);
pck.SaveAs(new FileInfo(OutputDirectory + "AfterDeleteColumn.xlsx"));
// Assert
Assert.AreEqual("First Name", wsData.Cells["A1"].Text);
Assert.AreEqual("Family Name", wsData.Cells["B1"].Text);
}
private void Read_2007or2010()
{
try
{
FileStream stream;
try
{
stream = new FileStream(txtTenFile.Text, FileMode.Open);
}
catch (Exception)
{
MessageBox.Show(FormResource.msgKiemTraFile, FormResource.MsgCaption, MessageBoxButtons.OK,
MessageBoxIcon.Information);
ResultValue = null;
return;
}
var excelPkg = new ExcelPackage();
excelPkg.Load(stream);
stream.Close();
var oSheet = excelPkg.Workbook.Worksheets[1];
var startRows = oSheet.Dimension.Start.Row + ViTriHeader;
var endRows = oSheet.Dimension.End.Row;
var maximum = (endRows - startRows + 1) > 100 ? (endRows - startRows + 1) : 200;
upsbLoading.SetPropertyThreadSafe(p => p.Maximum, maximum);
var donvi = (endRows - startRows + 1) == 0 ? maximum : maximum / (endRows - startRows + 1);
for (var i = startRows; i <= endRows; i++)
{
_result.Rows.Add(
oSheet.Cells[i, 1].GetValue<string>(),
oSheet.Cells[i, 2].GetValue<string>(),
oSheet.Cells[i, 3].GetValue<string>(),
oSheet.Cells[i, 4].GetValue<string>(),
oSheet.Cells[i, 5].GetValue<string>()
);
upsbLoading.SetPropertyThreadSafe(c => c.Value, (i - startRows + 1) * donvi);
}
upsbLoading.SetPropertyThreadSafe(c => c.Value, maximum);
ResultValue = _result;
}
catch (Exception ex)
{
Log2File.LogExceptionToFile(ex);
ResultValue = null;
}
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
throw new ArgumentOutOfRangeException("filename");
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var p = ProductRow.Fields;
var s = SupplierRow.Fields;
var c = CategoryRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List<string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var productName = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
if (productName.IsTrimmedEmpty())
continue;
var product = uow.Connection.TryFirst<ProductRow>(q => q
.Select(p.ProductID)
.Where(p.ProductName == productName));
if (product == null)
product = new ProductRow
{
ProductName = productName
};
else
{
// avoid assignment errors
product.TrackWithChecks = false;
}
var supplierName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(supplierName))
{
var supplier = uow.Connection.TryFirst<SupplierRow>(q => q
.Select(s.SupplierID)
.Where(s.CompanyName == supplierName));
if (supplier == null)
{
response.ErrorList.Add("Error On Row " + row + ": Supplier with name '" +
supplierName + "' is not found!");
continue;
}
product.SupplierID = supplier.SupplierID.Value;
}
else
product.SupplierID = null;
var categoryName = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
if (!string.IsNullOrWhiteSpace(categoryName))
{
var category = uow.Connection.TryFirst<CategoryRow>(q => q
.Select(c.CategoryID)
.Where(c.CategoryName == categoryName));
if (category == null)
{
response.ErrorList.Add("Error On Row " + row + ": Category with name '" +
categoryName + "' is not found!");
continue;
}
product.CategoryID = category.CategoryID.Value;
}
else
product.CategoryID = null;
product.QuantityPerUnit = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
product.UnitPrice = Convert.ToDecimal(worksheet.Cells[row, 5].Value ?? 0);
product.UnitsInStock = Convert.ToInt16(worksheet.Cells[row, 6].Value ?? 0);
product.UnitsOnOrder = Convert.ToInt16(worksheet.Cells[row, 7].Value ?? 0);
product.ReorderLevel = Convert.ToInt16(worksheet.Cells[row, 8].Value ?? 0);
if (product.ProductID == null)
{
new ProductRepository().Create(uow, new SaveWithLocalizationRequest<MyRow>
{
Entity = product
});
response.Inserted = response.Inserted + 1;
}
else
{
new ProductRepository().Update(uow, new SaveWithLocalizationRequest<MyRow>
{
Entity = product,
EntityId = product.ProductID.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return response;
}
// This function will parse all data from a DB report worksheet to a DataTable object
public static DataTable ParseDataWorkSheet(string Entry_id)
{
string fileName = null;
using (spark1Entities db = new spark1Entities())
{
ReportEntries entry = db
.ReportEntries
.Where(re => re.EntryId == Entry_id)
.FirstOrDefault();
fileName = Path.Combine(
HttpContext.Current.Server.MapPath("~/xls_reports"),
entry.EntryUser,
entry.EntryId + ".xlsx");
}
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(fileName))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets["Data"];
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);
}
return tbl;
}
}
protected void Page_Load(object sender, EventArgs e)
{
string projectID = (Page.RouteData.Values["project_id"] as string);
List <string> repsIdsToCreate = (Page.RouteData.Values["reports"] as string).Split(',').ToList();
List <Rep> repsToCreate = new List <Rep>();
foreach (string repID in repsIdsToCreate)
{
repsToCreate.Add(new RepBLL().GetRepByRepID(Convert.ToInt32(repID)));
}
bool isFullHeader = Convert.ToBoolean(Page.RouteData.Values["headeroneachreport"] as string);// cbHeaderOnEachReport.Checked;
Eisk.BusinessEntities.Project project = new ProjectBLL().GetProjectByProjectID(Convert.ToInt32(projectID));
Eisk.BusinessEntities.User user = new UserBLL().GetUserByUserName((HttpContext.Current.User.Identity).Name);
Eisk.BusinessEntities.UserInfo userInfo = user.UserInfoes.First(instance => instance.UserID == user.UserID);
DateTime time = DateTime.Now;
string name = Reports.Translate(project.ProjectInfoes.First().ProjectName);
string path = System.Web.HttpContext.Current.Server.MapPath(@System.Configuration.ConfigurationManager.AppSettings["ProjectsRoot"]) + projectID.ToString();
// check folder exists
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
System.IO.File.WriteAllText(path + "/" + name + ".xlsx", "");
}
FileInfo newFile = new FileInfo(path + @"\" + name + ".xlsx");
File.Delete(path + @"\" + name + ".xlsx");
using (ExcelPackage pck = new ExcelPackage(newFile))
{
ProjectInfo projectInfo = new ProjectInfoBLL().GetProjectInfoesByProjectID(Convert.ToInt32(projectID)).First();
List <Project_Organisms> project_Organisms = new Project_OrganismsBLL().GetProject_OrganismsByProjectID(Convert.ToInt32(projectID));
int totalPages = 0;
int currentPageNumber = 0;
int maxLines = Convert.ToInt32(@System.Configuration.ConfigurationManager.AppSettings["reportsMaxLines"]);
List <TreeDetail> treeDetails = new List <TreeDetail>();
List <TreeDetail> treeComentaries = new List <TreeDetail>();
List <TreesSummary> actionProposedID0Items = new List <TreesSummary>();
List <TreesSummary> actionProposedID1Items = new List <TreesSummary>();
List <TreesSummary> actionProposedID2Items = new List <TreesSummary>();
List <TreesSummary> actionProposedID3Items = new List <TreesSummary>();
List <TreesSummary> actionProposedID4Items = new List <TreesSummary>();
int treeDetailsCount = 0;
int actionProposedID0ItemsCount = 0;
int actionProposedID1ItemsCount = 0;
int actionProposedID2ItemsCount = 0;
int actionProposedID3ItemsCount = 0;
int actionProposedID4ItemsCount = 0;
List <Index> idexes = new List <Index>();
if (repsToCreate.Select(instance => instance.RepID).Contains(1))
{
idexes.Add(
new Index(
repsToCreate.Where(instance => instance.RepID == 1).Select(instance => instance.RepName).First(),
1,
totalPages + 1,
totalPages + 1
));
totalPages += 1;
}
if (repsToCreate.Select(instance => instance.RepID).Contains(2))
{
treeDetails = new TreeDetailBLL().GetTreeDetailsByProjectID(Convert.ToInt32(projectID));
treeDetailsCount += Reports.GetPageCountOrDefault(maxLines, treeDetails.Count);
idexes.Add(
new Index(
repsToCreate.Where(instance => instance.RepID == 2).Select(instance => instance.RepName).First(),
2,
totalPages + 1,
totalPages + treeDetailsCount
));
totalPages += treeDetailsCount;
}
if (repsToCreate.Select(instance => instance.RepID).Contains(3))
{
actionProposedID0Items = new TreesSummaryBLL().GetItems(project_Organisms, 0, true);
actionProposedID0ItemsCount = Reports.GetPageCountOrDefault(maxLines, actionProposedID0Items.Count);
;
idexes.Add(
new Index(
repsToCreate.Where(instance => instance.RepID == 3).Select(instance => instance.RepName).First(),
3,
totalPages + 1,
totalPages + actionProposedID0ItemsCount
));
totalPages += actionProposedID0ItemsCount;
}
if (repsToCreate.Select(instance => instance.RepID).Contains(4))
{
actionProposedID1Items = new TreesSummaryBLL().GetItems(project_Organisms, 1, true);
actionProposedID1ItemsCount += Reports.GetPageCountOrDefault(maxLines, actionProposedID1Items.Count);
idexes.Add(
new Index(
repsToCreate.Where(instance => instance.RepID == 4).Select(instance => instance.RepName).First(),
4,
totalPages + 1,
totalPages + actionProposedID1ItemsCount
));
totalPages += actionProposedID1ItemsCount;
}
if (repsToCreate.Select(instance => instance.RepID).Contains(5))
{
actionProposedID2Items = new TreesSummaryBLL().GetItems(project_Organisms, 2, true);
actionProposedID2ItemsCount += Reports.GetPageCountOrDefault(maxLines, actionProposedID2Items.Count);
idexes.Add(
new Index(
repsToCreate.Where(instance => instance.RepID == 5).Select(instance => instance.RepName).First(),
5,
totalPages + 1,
totalPages + actionProposedID2ItemsCount
));
totalPages += actionProposedID2ItemsCount;
}
if (repsToCreate.Select(instance => instance.RepID).Contains(6))
{
actionProposedID3Items = new TreesSummaryBLL().GetItems(project_Organisms, 3, true);
actionProposedID3ItemsCount += Reports.GetPageCountOrDefault(maxLines, actionProposedID3Items.Count);
idexes.Add(
new Index(
repsToCreate.Where(instance => instance.RepID == 6).Select(instance => instance.RepName).First(),
6,
totalPages + 1,
totalPages + actionProposedID3ItemsCount
));
totalPages += actionProposedID3ItemsCount;
}
if (repsToCreate.Select(instance => instance.RepID).Contains(7))
{
actionProposedID4Items = new TreesSummaryBLL().GetItems(project_Organisms, 4, true);
actionProposedID4ItemsCount += Reports.GetPageCountOrDefault(maxLines, actionProposedID4Items.Count);
idexes.Add(
new Index(
repsToCreate.Where(instance => instance.RepID == 7).Select(instance => instance.RepName).First(),
7,
totalPages + 1,
totalPages + actionProposedID4ItemsCount
));
totalPages += actionProposedID4ItemsCount;
}
if (repsToCreate.Select(instance => instance.RepID).Contains(8))
{
idexes.Add(
new Index(
repsToCreate.Where(instance => instance.RepID == 8).Select(instance => instance.RepName).First(),
1,
totalPages + 1,
totalPages + 1
));
totalPages += 1;
}
if (repsToCreate.Select(instance => instance.RepID).Contains(2)) // Se repite el ID de inventario de arboles para anejar comentarios muy largos
{
treeComentaries = treeDetails.AsQueryable().DynamicOrderBy("Number").Where(instance => instance.Commentary.Trim().Length > 100).ToList();
int totalTreeDetailsLines = 0;
int pageCount = 0;
foreach (var treeDetail in treeComentaries)
{
int lines = (int)Math.Ceiling((double)treeDetail.Commentary.Length / 200D);
if (totalTreeDetailsLines + lines > maxLines * pageCount)
{
pageCount++;
}
totalTreeDetailsLines += lines;
}
if (treeComentaries.Count > 0)
{
idexes.Add(
new Index(
"Comentarios (Continuación)",
0,
totalPages + 1,
totalPages + pageCount
));
}
//int pageCount = (int)Math.Ceiling((double)totalTreeDetailsLines / (double)maxLines);
totalPages += pageCount;
}
bool hasIndex = Convert.ToBoolean(Page.RouteData.Values["createindex"] as string);
if (hasIndex)//cbCreateIndex.Checked)
{
Reports.Index(isFullHeader, currentPageNumber, totalPages, "Tabla de Contenido", project, userInfo, idexes, time, pck);
}
foreach (Int32 reportID in repsToCreate.Select(instance => instance.RepID))
{
switch (reportID)
{
case 1:
{
Reports.ProjectInfo(isFullHeader, hasIndex, currentPageNumber, totalPages, project, userInfo, time, pck);
currentPageNumber += 1;
}
break;
case 2:
{
Reports.TreeInventory(isFullHeader, hasIndex, currentPageNumber, totalPages, project, userInfo, treeDetails.AsQueryable().DynamicOrderBy("Number").ToList(), time, pck, maxLines);
currentPageNumber += treeDetailsCount;
}
break;
case 3:
{ // actionProposedID = 0; ALL
Reports.TreesSummary(isFullHeader, hasIndex, currentPageNumber, totalPages, project, userInfo, actionProposedID0Items, time, pck, maxLines);
currentPageNumber += actionProposedID0ItemsCount;
}
break;
case 4:
{ // actionProposedID = 1; Corte y Remoción
Reports.ActionProposedSummary(isFullHeader, hasIndex, currentPageNumber, totalPages, "Resumen de Corte y Remoción", project, userInfo, actionProposedID1Items, time, pck, maxLines);
currentPageNumber += actionProposedID1ItemsCount;
}
break;
case 5:
{ // actionProposedID = 2; Protección
Reports.ActionProposedSummary(isFullHeader, hasIndex, currentPageNumber, totalPages, "Resumen de Protección", project, userInfo, actionProposedID2Items, time, pck, maxLines);
currentPageNumber += actionProposedID2ItemsCount;
}
break;
case 6:
{ // actionProposedID = 3; Poda
Reports.ActionProposedSummary(isFullHeader, hasIndex, currentPageNumber, totalPages, "Resumen de Poda", project, userInfo, actionProposedID3Items, time, pck, maxLines);
currentPageNumber += actionProposedID3ItemsCount;
}
break;
case 7:
{ // actionProposedID = 4; Transplante
Reports.ActionProposedSummary(isFullHeader, hasIndex, currentPageNumber, totalPages, "Resumen de Transplante", project, userInfo, actionProposedID4Items, time, pck, maxLines);
currentPageNumber += actionProposedID4ItemsCount;
}
break;
case 8:
{
using (ExcelPackage pck2 = new OfficeOpenXml.ExcelPackage())
{
ExcelWorksheet wsTemplate = null;
pck2.Load(File.OpenRead(System.Web.HttpContext.Current.Server.MapPath(@"~\App_Resources\Excel\Totales.xlsx")));
wsTemplate = pck2.Workbook.Worksheets.First();
Reports.ProjectResults(isFullHeader, hasIndex, currentPageNumber, totalPages, project_Organisms, project, userInfo, time, pck, wsTemplate);
currentPageNumber += 1;
}
}
break;
default:
break;
}
}
if (treeComentaries.Count > 0 && treeDetailsCount > 0)
{
Reports.Comentaries(isFullHeader, hasIndex, currentPageNumber, totalPages, "Comentarios (Continuación)", project, userInfo, treeComentaries, time, pck, maxLines);
}
pck.Save();
pck.Dispose();
}
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
response.ClearContent();
response.Clear();
response.ContentType = "application/ms-excel";
response.AddHeader("Content-Disposition", "attachment; filename=" + name + ".xlsx");
response.WriteFile(path + @"/" + name + ".xlsx");
response.End();
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
if (Extension == ".xls")
{
//string notxlsx = ("This is not an xlsx file");
//ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true);
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//Counts the number of cells in a row and determines the columns from that.
int counter = sheet.GetRow(0).Cells.Count;
// J < number of columns needs to be exact at this moment
for (int j = 0; j < counter; j++)
{
// set each column to a - ** letters
// dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//Get first row and set the headers for each cell
//dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString());
//Get each cell value in row 0 and return its string for a column name.
dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue);
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
//Hackish way to remove the bad first row made by getting column names
dt.Rows.RemoveAt(0);
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
//Bind the data
GridView1.DataBind();
sheet.Dispose();
hssfworkbook.Dispose();
}
else
{
//Create a new epplus package using openxml
var pck = new OfficeOpenXml.ExcelPackage();
//load the package with the filepath I got from my fileuploader above on the button
//pck.Load(new System.IO.FileInfo(FilePath).OpenRead());
//stream the package
FileStream stream = new FileStream(FilePath, FileMode.Open);
pck.Load(stream);
//So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause.
//Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all.
var ws = pck.Workbook.Worksheets[1];
//This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"];
if (ws == null)
{
ws = pck.Workbook.Worksheets.Add("Sheet1");
// Obiviously I didn't add anything to the sheet so probably can count on it being blank.
}
//I created this datatable for below.
DataTable tbl = new DataTable();
//My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx
var hdr = bool.Parse(isHDR);
Console.WriteLine(hdr);
//Set the bool value for from above.
var hasHeader = hdr;
//Setup the table based on the value from my bool
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);
}
//Bind Data to GridView
//I have all my info in the tbl dataTable so the datasource for the Gridview1 is set to tbl
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = tbl;
//Bind the data
GridView1.DataBind();
pck.Save();
pck.Dispose();
stream.Close();
// string pathD = FilePath;
FilePath = null;
stream = null;
// var fileToDelete = new FileInfo(pathD);
// fileToDelete.Delete();
}
}
/// <summary>
/// 指定されたExcelを読み込み、Databaseに登録する
/// </summary>
/// <param name="path"></param>
/// <param name="sheetName"></param>
/// <param name="start_row"></param>
/// <returns>Excel読み込みデータ</returns>
public List<XlsData> LoadExcel(string path, string sheetName, int start_row)
{
log.Debug(string.Format("target file= {0}; sheet= {1}", path, sheetName));
_start_row = start_row; //開始位置 (ログ出力に使用)
int rowIndex = start_row;
List<XlsData> records = new List<XlsData>();
try
{
using (ExcelPackage xls = new ExcelPackage())
{
using (FileStream fs = new FileStream(path, FileMode.Open))
{
xls.Load(fs);
ExcelWorksheet sheet = xls.Workbook.Worksheets[sheetName];
while (true)
{
XlsData data = new XlsData();
data.id = rowIndex - start_row;
// read row data
data.drcode = Utils.ParseString(sheet.Cells[rowIndex, 1].Value);
data.drname = Utils.ParseString(sheet.Cells[rowIndex, 2].Value);
if (string.IsNullOrEmpty(data.drname))
{
break; //Dr名が無ければ終了
}
data.ncc_cd = Utils.ParseString(sheet.Cells[rowIndex, 3].Value);
data.ncc_name = Utils.ParseString(sheet.Cells[rowIndex, 4].Value);
data.ncc_dept = Utils.ParseString(sheet.Cells[rowIndex, 5].Value);
data.title = Utils.ParseString(sheet.Cells[rowIndex, 6].Value);
data.category = Utils.ParseString(sheet.Cells[rowIndex, 7].Value);
data.kingaku = Utils.ParseLong(sheet.Cells[rowIndex, 8].Value);
data.kaisu = Utils.ParseInt(sheet.Cells[rowIndex, 9].Value);
// 頭 '0' 埋め
data.drcode = data.drcode.PadLeft(6, '0');
if (data.drcode == "000000")
{
data.drcode = string.Empty;
}
records.Add(data);
rowIndex++;
}
}
}
//読み込んだデータをDatabaseに登録
int registed = BulkInsert(records);
log.Info(string.Format("登録件数= {0}", registed));
}
catch (Exception exp)
{
log.Error(string.Format(@"Fatal Error.: {0}", rowIndex), exp);
}
return records;
}
// считывание шаблонов структур из файла Excel
private void ReadStructuresFromExcel()
{
if (!File.Exists(Settings.Default.StructureExcelFile))
{
MessageBox.Show(string.Format("Не найден файл шаблонов структур - {0}", Settings.Default.StructureExcelFile));
Log.Error("Не найден файл шаблонов структур - {0}", Settings.Default.StructureExcelFile);
return;
}
// Открытие файла Excel
using (ExcelPackage excelStructure = new ExcelPackage())
{
using (var stream = File.OpenRead(Settings.Default.StructureExcelFile))
{
excelStructure.Load(stream);
}
var wbStructure = excelStructure.Workbook;
foreach (var ws in wbStructure.Worksheets)
{
if (ws.Name.StartsWith("{"))
{
var structure = new StructureTemplate(ws, this);
StructureTemplates.Add(structure);
}
}
List<StructureTemplate> errorStructures = new List<StructureTemplate>();
foreach (var structure in StructureTemplates)
{
try
{
structure.ReadSheet();
}
catch (Exception ex)
{
Log.Error(ex, "Ошибка чтения лтиста шаблона структуры {0} из файла {1}", structure.Name, Settings.Default.StructureExcelFile);
errorStructures.Add(structure);
}
}
errorStructures.ForEach(s => StructureTemplates.Remove(s));
// проверка структур, подстановка вложенных структур
foreach (var structure in StructureTemplates)
{
((FolderItemTemplate)structure.Root).CheckInnerStructure();
}
}
if (Inspector.HasError)
{
Inspector.Show();
Inspector.Clear();
}
}
public async Task TestExcelDataExporter()
{
var resultViewer = InitializeResultViewer();
var tempFileName = await GenerateExportFile(resultViewer, new ExcelDataExporter());
var package = new ExcelPackage();
var stream = new MemoryStream(File.ReadAllBytes(tempFileName));
File.Delete(tempFileName);
package.Load(stream);
package.Workbook.Worksheets.Count.ShouldBe(1);
var worksheet = package.Workbook.Worksheets[1];
worksheet.Cells[1, 1].Value.ShouldBe("DUMMY1");
worksheet.Cells[1, 2].Value.ShouldBe("DUMMY_WITH_UNDERSCORES");
worksheet.Cells[2, 1].Value.ShouldBe("Value \"1\" '2' <3>");
worksheet.Cells[2, 2].Value.ShouldBe("08/16/2014 22:25:34");
worksheet.Cells[3, 1].Value.ShouldBe("\"2.\"Value");
worksheet.Cells[3, 2].Value.ShouldBe("08/16/2014 00:00:00");
}