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;
}
}
}
protected void ImgExportToExcell_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
long RecordCount;
var kids = KidsUser_DataProvider.GetGeustUser(out RecordCount,
Name: txtSearchName.Text,
Family: txtSearchFamily.Text,
MelliCode: txtSearchMelliCode.Text,
EmailAddress: txtSearchEmailAddress.Text,
MobileNumber: txtSearchMobileNumber.Text,
SelectDistinct: true,
PageSize: 100000
);
string templatefilePath = Server.MapPath("~/AdminCP/Files/GeustUser/GeustUserReport.xlsx");
FileInfo templateFileInfo = new FileInfo(templatefilePath);
string NewfilePath = string.Format("~/AdminCP/Files/GeustUser/Temp/GeustUserReport_{0}.xlsx", PersianDateTime.Now.ToLongDateTimeString().Replace("/", "-").Replace(":", "-"));
FileInfo NewfileInfo = new FileInfo(Server.MapPath(NewfilePath));
ExcelPackage xlPackage = new ExcelPackage(templateFileInfo, true);
ExcelWorksheet workSheetGeustUser = xlPackage.Workbook.Worksheets["GeustUser"];
int i = 2;
foreach (var user in kids)
{
FillExcellRow(user, workSheetGeustUser, i);
i++;
}
xlPackage.SaveAs(NewfileInfo);
xlPackage.Dispose();
ClientRedirect(NewfilePath, 2000);
}
private static void CreateDeletePackage(int Sheets, int rows)
{
List<object> row = new List<object>();
row.Add(1);
row.Add("Some text");
row.Add(12.0);
row.Add("Some larger text that has completely no meaning. How much wood can a wood chuck chuck if a wood chuck could chuck wood. A wood chuck could chuck as much wood as a wood chuck could chuck wood.");
FileInfo LocalFullFileName = new FileInfo(Path.GetTempFileName());
LocalFullFileName.Delete();
package = new ExcelPackage(LocalFullFileName);
try
{
for (int ca = 0; ca < Sheets; ca++)
{
CreateWorksheet("Sheet" + (ca+1), row, rows);
}
package.Save();
}
finally
{
LocalFullFileName.Refresh();
if (LocalFullFileName.Exists)
{
LocalFullFileName.Delete();
}
package.Dispose();
package = null;
GC.Collect();
}
}
//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);
}
private void writeHeader(string filename)
{
ExcelPackage ep;
ep = new ExcelPackage(new FileInfo(filename));
var workbook = ep.Workbook;
ExcelWorksheet akWorksheet = null;
foreach (ExcelWorksheet worksheet in workbook.Worksheets)
{
if (worksheet.Name == TableName)
akWorksheet = worksheet;
}
if (akWorksheet == null)
{
akWorksheet = workbook.Worksheets.Add(TableName);
}
int n = 1;
foreach (DatasetConfigRow myFeld in datasetConfig.DatasetConfigRows)
{
akWorksheet.Cells[1, n].Value = myFeld.DatabaseField;
n++;
}
ep.Save();
ep.Dispose();
}
//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);
}
}
void Handle(Input.Import Action)
{
FileInfo fi = new FileInfo(this.ImportPath);
ExcelPackage package = new ExcelPackage(fi);
Importer importer = new Importer();
importer.Import(package, true);
this.Message = "The file has been successfully imported!";
package.Dispose();
}
private void Close()
{
application.Dispose();
workBook.Dispose();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
application = null;
workBook = null;
workSheet = null;
//System.GC.Collect();
}
protected override bool CloseInternal()
{
if (_obj != null)
{
_obj.Dispose();
_obj = null;
return(true);
}
return(false);
}
public void AllDrawingsInsideMarkupCompatibility()
{
string workbooksDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\workbooks");
// This is codeplex issue 15028: Making an unrelated change to an Excel file that contains drawings that ALL exist
// inside MarkupCompatibility/Choice nodes causes the drawings.xml file to be incorrectly garbage collected
// when an unrelated change is made.
string path = Path.Combine(workbooksDir, "AllDrawingsInsideMarkupCompatibility.xlsm");
// Load example document.
_pck = new ExcelPackage(new FileInfo(path));
// Verify the drawing part exists:
Uri partUri = new Uri("/xl/drawings/drawing1.xml", UriKind.Relative);
Assert.IsTrue(_pck.Package.PartExists(partUri));
// The Excel Drawings NamespaceManager from ExcelDrawing.CreateNSM:
NameTable nt = new NameTable();
var xmlNsm = new XmlNamespaceManager(nt);
xmlNsm.AddNamespace("a", ExcelPackage.schemaDrawings);
xmlNsm.AddNamespace("xdr", ExcelPackage.schemaSheetDrawings);
xmlNsm.AddNamespace("c", ExcelPackage.schemaChart);
xmlNsm.AddNamespace("r", ExcelPackage.schemaRelationships);
xmlNsm.AddNamespace("mc", ExcelPackage.schemaMarkupCompatibility);
XmlDocument drawingsXml = new XmlDocument();
drawingsXml.PreserveWhitespace = false;
XmlHelper.LoadXmlSafe(drawingsXml, _pck.Package.GetPart(partUri).GetStream());
// Verify that there are the correct # of drawings:
Assert.AreEqual(drawingsXml.SelectNodes("//*[self::xdr:twoCellAnchor or self::xdr:oneCellAnchor or self::xdr:absoluteAnchor]", xmlNsm).Count, 5);
// Make unrelated change. (in this case a useless additional worksheet)
_pck.Workbook.Worksheets.Add("NewWorksheet");
// Save it out.
string savedPath = Path.Combine(workbooksDir, "AllDrawingsInsideMarkupCompatibility2.xlsm");
_pck.SaveAs(new FileInfo(savedPath));
_pck.Dispose();
// Reload the new saved file.
_pck = new ExcelPackage(new FileInfo(savedPath));
// Verify the drawing part still exists.
Assert.IsTrue(_pck.Package.PartExists(new Uri("/xl/drawings/drawing1.xml", UriKind.Relative)));
drawingsXml = new XmlDocument();
drawingsXml.PreserveWhitespace = false;
XmlHelper.LoadXmlSafe(drawingsXml, _pck.Package.GetPart(partUri).GetStream());
// Verify that there are the correct # of drawings:
Assert.AreEqual(drawingsXml.SelectNodes("//*[self::xdr:twoCellAnchor or self::xdr:oneCellAnchor or self::xdr:absoluteAnchor]", xmlNsm).Count, 5);
// Verify that the new worksheet exists:
Assert.IsNotNull(_pck.Workbook.Worksheets["NewWorksheet"]);
// Cleanup:
File.Delete(savedPath);
}
public void ValidateCaseInsensitiveCustomProperties_Loading()
{
var p = new OfficeOpenXml.ExcelPackage();
p.Workbook.Worksheets.Add("CustomProperties");
p.Workbook.Properties.SetCustomPropertyValue("fOO", "bAR");
p.Workbook.Properties.SetCustomPropertyValue("Foo", "Bar");
p.Save();
var p2 = new OfficeOpenXml.ExcelPackage(p.Stream);
Assert.AreEqual("Bar", p2.Workbook.Properties.GetCustomPropertyValue("fOo"));
p.Dispose();
p2.Dispose();
}
public void CreateWorkbook(string output, DataTable dt)
{
if (File.Exists(output)) File.Delete(output);
FileInfo newFile = new FileInfo(output);
ExcelPackage xlPackage = new ExcelPackage(newFile);
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Data");
ExcelRange header = worksheet.Cells[1, 1, 1, dt.Columns.Count];
worksheet.View.FreezePanes(2, 1);
header.Style.Fill.PatternType = ExcelFillStyle.Solid;
header.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
header.Style.Font.Bold = true;
for (int i = 1; i <= dt.Columns.Count; ++i)
header[1, i].Value = dt.Columns[i - 1].ColumnName;
header.Dispose();
ExcelRange data = worksheet.Cells[2, 1, dt.Rows.Count + 1, dt.Columns.Count];
for (int i = 2; i <= dt.Rows.Count + 1; ++i)
{
for (int j = 1; j <= dt.Columns.Count; ++j)
{
data[i, j].Value = dt.Rows[i - 2][j - 1];
}
}
data.Dispose();
for (int i = 1; i <= dt.Columns.Count; ++i)
{
var format = "@";
if (dt.Columns[i - 1].DataType == typeof(DateTime))
{
format = "[$-409]d/m/yy h:mm AM/PM;@";
}
else if (dt.Columns[i - 1].DataType == typeof(int))
{
format = "#,##0";
}
worksheet.Cells[2, i, dt.Rows.Count + 1, i].Style.Numberformat.Format = format;
if (format != "@") worksheet.Column(i).AutoFit();
}
xlPackage.Save();
xlPackage.Dispose();
}
private void PrintForm()
{
if (Request["uid"].IsInt64())
{
var user = KidsUser_DataProvider.GetKidsUser(Request["uid"].ToLong()).FirstOrDefault();
if (user == null)
return;
string templatefilePath = Server.MapPath("~/AdminCP/Files/KidsAccForm/528_2.xlsx");
FileInfo templateFileInfo = new FileInfo(templatefilePath);
string NewfilePath = string.Format("~/AdminCP/Files/KidsAccForm/Temp/528_2_{0}.xlsx", PersianDateTime.Now.ToLongDateTimeString().Replace("/", "-").Replace(":", "-"));
FileInfo NewfileInfo = new FileInfo(Server.MapPath(NewfilePath));
ExcelPackage xlPackage = new ExcelPackage(templateFileInfo, true);
ExcelWorksheet workSheetReal_1 = xlPackage.Workbook.Worksheets["حقيقي-1"];
FillExcellRow(user, workSheetReal_1);
ExcelWorksheet workSheetReal_2 = xlPackage.Workbook.Worksheets["حقيقي-2"];
FillExcellRow2(user, workSheetReal_2);
ExcelWorksheet w_MelliCard = xlPackage.Workbook.Worksheets["کارت ملی"];
ExcelWorksheet w_Identity = xlPackage.Workbook.Worksheets["شناسنامه"];
var w = JpegImage.CmToPx(20).ToInt32();
var h = JpegImage.CmToPx(30).ToInt32();
CreateImage(w_Identity, JpegImage.GetPicFromDB(user, JpegImage.ImageActType.ChildIdentityPic, false, w, h), 0, 0);
w = JpegImage.CmToPx(10).ToInt32();
h = JpegImage.CmToPx(20).ToInt32();
CreateImage(w_MelliCard, JpegImage.GetPicFromDB(user, JpegImage.ImageActType.ChildNationalCardFaceUPPic, false, w, h), 0, 0);
CreateImage(w_MelliCard, JpegImage.GetPicFromDB(user, JpegImage.ImageActType.ChildNationalCardFaceDownPic, false, w, h), 17, 0);
xlPackage.SaveAs(NewfileInfo);
xlPackage.Dispose();
ClientRedirect(NewfilePath, 2000);
}
}
public void FileLockedProblem()
{
using (ExcelPackage pck = new ExcelPackage(new FileInfo(@"c:\temp\url.xlsx")))
{
pck.Workbook.Worksheets[1].DeleteRow(1, 1);
pck.Save();
pck.Dispose();
}
}
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();
}
}
private List<String> GetAllColumnsName(ExcelPackage packeg)
{
ExcelWorksheet ws = packeg.Workbook.Worksheets.First();
List<String> listFile = new List<String>();
DataTable dt = new DataTable(ws.Name);
int totalCols = ws.Dimension.End.Column;
int totalRows = ws.Dimension.End.Row;
foreach (var firstRowCell in ws.Cells[1, 1, 1, totalCols])
{
listFile.Add(firstRowCell.Text);
}
packeg.Dispose();
return listFile;
}
/// <summary>Standard Dispose method</summary>
public override void Dispose()
{
_package.Dispose();
}
public void VbaError()
{
DirectoryInfo workingDir = new DirectoryInfo(@"C:\epplusExample\folder");
if (!workingDir.Exists) workingDir.Create();
FileInfo f = new FileInfo(workingDir.FullName + "//" + "temp.xlsx");
if (f.Exists) f.Delete();
ExcelPackage myPackage = new ExcelPackage(f);
myPackage.Workbook.CreateVBAProject();
ExcelWorksheet excelWorksheet = myPackage.Workbook.Worksheets.Add("Sheet1");
ExcelWorksheet excelWorksheet2 = myPackage.Workbook.Worksheets.Add("Sheet2");
ExcelWorksheet excelWorksheet3 = myPackage.Workbook.Worksheets.Add("Sheet3");
FileInfo f2 = new FileInfo(workingDir.FullName + "//" + "newfile.xlsm");
ExcelVBAModule excelVbaModule = myPackage.Workbook.VbaProject.Modules.AddModule("Module1");
StringBuilder mybuilder = new StringBuilder(); mybuilder.AppendLine("Sub Jiminy()");
mybuilder.AppendLine("Range(\"D6\").Select");
mybuilder.AppendLine("ActiveCell.FormulaR1C1 = \"Jiminy\"");
mybuilder.AppendLine("End Sub");
excelVbaModule.Code = mybuilder.ToString();
myPackage.SaveAs(f2);
myPackage.Dispose();
}
public void Issue15109()
{
System.IO.FileInfo newFile = new System.IO.FileInfo(@"C:\Temp\bug\test01.xlsx");
ExcelPackage excelP = new ExcelPackage(newFile);
ExcelWorksheet ws = excelP.Workbook.Worksheets[1];
Assert.AreEqual("A1:Z75",ws.Dimension.Address);
excelP.Dispose();
newFile = new System.IO.FileInfo(@"C:\Temp\bug\test02.xlsx");
excelP = new ExcelPackage(newFile);
ws = excelP.Workbook.Worksheets[1];
Assert.AreEqual("A1:AF501", ws.Dimension.Address);
excelP.Dispose();
newFile = new System.IO.FileInfo(@"C:\Temp\bug\test03.xlsx");
excelP = new ExcelPackage(newFile);
ws = excelP.Workbook.Worksheets[1];
Assert.AreEqual("A1:AD406", ws.Dimension.Address);
excelP.Dispose();
}
private void BuildPivotTable2(FileInfo MyFile)
{
using (ExcelPackage ep = new ExcelPackage(MyFile))
{
var wsData = ep.Workbook.Worksheets["Data"];
var totalRows = wsData.Dimension.Address;
ExcelRange data = wsData.Cells[totalRows];
var wsAuditPivot = ep.Workbook.Worksheets.Add("Pivot2");
var pivotTable1 = wsAuditPivot.PivotTables.Add(wsAuditPivot.Cells["A7:C30"], data, "PivotAudit2");
pivotTable1.ColumGrandTotals = true;
var rowField = pivotTable1.RowFields.Add(pivotTable1.Fields["INVOICE_DATE"]);
rowField.AddDateGrouping(eDateGroupBy.Years);
var yearField = pivotTable1.Fields.GetDateGroupField(eDateGroupBy.Years);
yearField.Name = "Year";
var rowField2 = pivotTable1.RowFields.Add(pivotTable1.Fields["AUDIT_LINE_STATUS"]);
var TotalSpend = pivotTable1.DataFields.Add(pivotTable1.Fields["TOTAL_INVOICE_PRICE"]);
TotalSpend.Name = "Total Spend";
TotalSpend.Format = "$##,##0";
var CountInvoicePrice = pivotTable1.DataFields.Add(pivotTable1.Fields["COUNT"]);
CountInvoicePrice.Name = "Total Lines";
CountInvoicePrice.Format = "##,##0";
pivotTable1.DataOnRows = false;
ep.Save();
ep.Dispose();
}
}
private void writeToExcel(string path, string name, string val)
{
ExcelPackage p = new ExcelPackage(new FileInfo(path));
ExcelWorksheet worksheet = p.Workbook.Worksheets[2];
for (var i = 1; i < 10000000; i++)
{
var range = worksheet.Cells[i, 1];
if (range.Value == null)
{
worksheet.Cells[i, 1].Value = name;
worksheet.Cells[i, 2].Value = val;
break;
}
}
p.Save();
p.Dispose();
}
private static void SaveToExcel(string outputFilename, LoaderCSV load) {
FileInfo file = new FileInfo(outputFilename);
if (file.Exists) {
file.Delete();
file = new FileInfo(outputFilename);
}
using (ExcelPackage package = new ExcelPackage(file)) {
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("CSVToExcel");
int i = 2;
int j = 1;
foreach (Dictionary<string, string> row in load.Rows) {
foreach (KeyValuePair<string, string> keyValuePair in row) {
worksheet.Cells[1, j].Value = keyValuePair.Key;
worksheet.Cells[i, j].Value = keyValuePair.Value;
j++;
}
j = 1;
i++;
}
package.Save();
package.Dispose();
}
}
private void refreshExcel()
{
FileInfo excelFileInfo = new FileInfo(Program.excelFile);
ExcelPackage package = new ExcelPackage(new FileInfo(Program.excelFile));
foreach (ExcelWorksheet sheet in package.Workbook.Worksheets)
{
foreach (var cell in sheet.Cells)
{
if (cell.Start.Column == 2)
{
cell.Style.Numberformat.Format = "hh:mm:ss";
}
}
}
if (package.Workbook.Worksheets.Count() == 0)
{
package.Workbook.Worksheets.Add("Times");
}
package.Save();
package.Dispose();
package.Stream.Close();
}
public void ExportXLS(string filename, bool openfile)
{
var newFile = new FileInfo(filename);
var pck = new ExcelPackage(newFile);
int maxx = 1, maxy = 1;
foreach (var p in pages)
{
SetWorkbookCells(pck, p, ref maxx, ref maxy);
}
ColourFormatDocument(pck, maxx, maxy);
SetHeaderColours(pck, maxx, maxy);
AutoFit(pck);
pck.Save();
pck.Dispose();
if (openfile)
Process.Start(filename);
}
public ActionResult ReporteExcel(string stado, string id, string etiqueta, string tarea, string label, string FechaIncio, string FechaFin, int numPag, int allReg, int Cant)
{
try
{
//Ruta de la plantilla
FileInfo fTemplateFile = new FileInfo(Server.MapPath("/") + "Reporte/Venta/excel/" + "Tarea.xlsx");
var Usuario = Authentication.UserLogued.Usuario;
//Ruta del nuevo documento
FileInfo fNewFile = new FileInfo(Server.MapPath("/") + "Reporte/Venta/excel/" + "Tarea" + "_" + Usuario + ".xlsx");
List <ETareas> ListaCompra;
ListaCompra = General.ListaTarea(stado, id, etiqueta, tarea, label, FechaIncio, FechaFin, numPag, allReg, Cant);
ExcelPackage pck = new ExcelPackage(fNewFile, fTemplateFile);
var ws = pck.Workbook.Worksheets[1];
////** aqui
DateTime today = DateTime.Now;
ws.Cells[6, 2].Value = today;
ws.Cells[7, 2].Value = Usuario;
int iFilaDetIni = 10;
int starRow = 1;
foreach (ETareas Detalle in ListaCompra)
{
ws.Cells[iFilaDetIni + starRow, 1].Value = Detalle.item;
ws.Cells[iFilaDetIni + starRow, 2].Value = Detalle.stado;
ws.Cells[iFilaDetIni + starRow, 3].Value = Detalle.etiqueta;
ws.Cells[iFilaDetIni + starRow, 4].Value = Detalle.tarea;
ws.Cells[iFilaDetIni + starRow, 5].Value = Detalle.label + "-" + Detalle.descripcion;
ws.Cells[iFilaDetIni + starRow, 6].Value = Detalle.id;
ws.Cells[iFilaDetIni + starRow, 7].Value = Detalle.direcion;
ws.Cells[iFilaDetIni + starRow, 8].Value = Detalle.inicio;
ws.Cells[iFilaDetIni + starRow, 9].Value = Detalle.fin;
ws.Cells[iFilaDetIni + starRow, 10].Value = Detalle.llegada;
ws.Cells[iFilaDetIni + starRow, 11].Value = Detalle.duracion;
starRow++;
}
int Count = starRow + 1;
string modelRange = "A" + System.Convert.ToString(iFilaDetIni) + ":K" + (iFilaDetIni + Count - 1).ToString();
var modelTable = ws.Cells[modelRange];
modelTable.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
//Guardando Archivo...
pck.Save();
// Liberando...
pck.Dispose();
OfficeOpenXml.ExcelPackage pcks = new OfficeOpenXml.ExcelPackage(fNewFile, false);
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + "ReporteTarea" + "_" + Usuario + ".xlsx");
MemoryStream memoryStream = new MemoryStream();
pcks.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.End();
pcks.Dispose();
System.IO.File.Delete(fNewFile.FullName);
return(View());
}
catch (Exception e)
{
throw e;
}
}
public bool exportFromExcel(DataGridView data, string pathToExcelFile)
{
if (File.Exists(pathToExcelFile)) File.Delete(pathToExcelFile);
FileInfo excelFile = new FileInfo(pathToExcelFile);
ExcelPackage excelPackage = new ExcelPackage(excelFile);
try
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cell(1, 1).Value = "Mã MH";
worksheet.Cell(1, 2).Value = "Tên MH";
worksheet.Cell(1, 3).Value = "Số TC";
//MessageBox.Show(data.Rows[6].Cells[2].Value.ToString());
//return false;
int rowCount = data.Rows.Count;
for (int r = 0; r < rowCount; r++)
for (int c = 0; c < 3; c++)
worksheet.Cell(r + 2, c + 1).Value = data.Rows[r].Cells[c].Value.ToString();
excelPackage.Save();
return true;
}
catch
{
return false;
}
finally
{
excelPackage.Dispose();
}
}
public static void dispose(ExcelPackage oExcel)
{
oExcel.Dispose();
}
//[TestMethod]
//[Ignore]
public void ReadDocument()
{
var fi=new FileInfo(_worksheetPath + "drawing.xlsx");
if (!fi.Exists)
{
Assert.Inconclusive("Drawing.xlsx is not created. Skippng");
}
var pck = new ExcelPackage(fi, true);
foreach(var ws in pck.Workbook.Worksheets)
{
foreach(ExcelDrawing d in pck.Workbook.Worksheets[1].Drawings)
{
if (d is ExcelChart)
{
TestContext.WriteLine(((ExcelChart)d).ChartType.ToString());
}
}
}
pck.Dispose();
}
/// <summary>
/// Export data to Excel
/// </summary>
/// <param name="excelFileName"></param>
public void Export(string excelFileName)
{
//FileStream fs = new FileStream(excelFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
//載入Excel檔案
ExcelPackage ep = new ExcelPackage();
ExcelWorksheet sheet = ep.Workbook.Worksheets.Add("Sheet1");//取得Sheet1
int startRowNumber = 1;
sheet.Cells[startRowNumber, 1].Value = "Customer_Code";
sheet.Cells[startRowNumber, 2].Value = "Customer_Name";
sheet.Cells[startRowNumber, 3].Value = "New_Code";
//寫入標題文字
//sheet.Cells[1, 11].Value = "緯度";
//sheet.Cells[1, 12].Value = "經度";
foreach (CustomerInfo item in this.CurrentData)
{
startRowNumber += 1;
//寫值
sheet.Cells[startRowNumber, 1].Value = item.Customer_Code;
sheet.Cells[startRowNumber, 2].Value = item.Customer_Name;
sheet.Cells[startRowNumber, 3].Value = item.New_Code;
}
//fs.Close();
//建立檔案
FileStream fs = new FileStream(excelFileName, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
ep.SaveAs(fs);//存檔
fs.Close();
//關閉資源
ep.Dispose();
ep = null;
return;
}
public void TestHeaderaddress()
{
_pck = new ExcelPackage();
var ws = _pck.Workbook.Worksheets.Add("Draw");
var chart = ws.Drawings.AddChart("NewChart1",eChartType.Area) as ExcelChart;
var ser1 = chart.Series.Add("A1:A2", "B1:B2");
ser1.HeaderAddress = new ExcelAddress("A1:A2");
ser1.HeaderAddress = new ExcelAddress("A1:B1");
ser1.HeaderAddress = new ExcelAddress("A1");
_pck.Dispose();
_pck = null;
}
/// <summary>
/// 讀取 Excel 檔案作為初始化資料
/// </summary>
/// <param name="excelFileName"></param>
public void Import(string excelFileName)
{
DB = new CustomerDB();
List<CustomerInfo> result = DB.Items;
//開檔
FileStream fs = new FileStream(excelFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
//載入Excel檔案
ExcelPackage ep = new ExcelPackage(fs);
ExcelWorksheet sheet = ep.Workbook.Worksheets["Sheet1"];//取得Sheet1
int startRowNumber = sheet.Dimension.Start.Row;//起始列編號，從1算起
int endRowNumber = sheet.Dimension.End.Row;//結束列編號，從1算起
bool isHeader = true;
if (isHeader)//有包含標題
{
startRowNumber += 1;
}
//寫入標題文字
//sheet.Cells[1, 11].Value = "緯度";
//sheet.Cells[1, 12].Value = "經度";
for (int i = startRowNumber; i <= endRowNumber; i++)
{
//讀值
string cellValue1 = sheet.Cells[i, 1].Value.ToString();
string cellValue2 = sheet.Cells[i, 2].Value.ToString();
result.Add(new CustomerInfo()
{
Customer_Code = cellValue1
, Customer_Name=cellValue2
}
);
//寫值
//sheet.Cells[i, 1].Value = cellValue + "test";
}
fs.Close();
////建立檔案
//fs = new FileStream(@"D:\目標檔案.xlsx", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
//ep.SaveAs(fs);//存檔
//fs.Close();
//關閉資源
ep.Dispose();
ep = null;
return ;
}
public void IssueMergedCells()
{
var p = new ExcelPackage();
var ws = p.Workbook.Worksheets.Add("t");
ws.Cells["A1:A5,C1:C8"].Merge = true;
ws.Cells["C1:C8"].Merge = false;
ws.Cells["A1:A8"].Merge = false;
p.Dispose();
}
private static void WriteToXLSX(DataCell[][] cells, string filename)
{
if (File.Exists(filename))
{
File.Delete(filename);
}
var package = new ExcelPackage(new FileInfo(filename));
var sheet = package.Workbook.Worksheets.Add("Content");
foreach (var cell in new ExcelCellEnumerator(cells.GetLength(0), cells.Max(x => x.Length)))
{
var dataRow = cells[cell.Row - 1];
if (cell.Column >= dataRow.Length) continue;
var data = dataRow[cell.Column];
var excelCell = sheet.Cells[cell.Value];
excelCell.Value = data.Content;
switch (data.Type)
{
case DataCellType.HeadingBig:
excelCell.Style.Font.Bold = true;
excelCell.Style.Font.Size = 18;
sheet.Row(cell.Row).Merged = true;
break;
case DataCellType.Heading:
excelCell.Style.Font.Bold = true;
excelCell.Style.Font.Size = 12;
break;
case DataCellType.ResultGood:
XLSXDataSerializer.SetResultCell(excelCell, Color.FromArgb(0xBC, 0xED, 0x91));
break;
case DataCellType.ResultNeutral:
XLSXDataSerializer.SetResultCell(excelCell, Color.FromArgb(0xFF, 0xEC, 0xB3));
break;
case DataCellType.ResultBad:
XLSXDataSerializer.SetResultCell(excelCell, Color.FromArgb(0xFF, 0x8A, 0x65));
break;
case DataCellType.Number:
excelCell.Value = double.Parse(excelCell.Value.ToString().Replace(",", "."),
CultureInfo.InvariantCulture);
break;
}
if (data.Outline.HasFlag(DataCellOutline.Top))
{
excelCell.Style.Border.Top.Style = ExcelBorderStyle.Medium;
excelCell.Style.Border.Top.Color.SetColor(Color.Black);
}
if (data.Outline.HasFlag(DataCellOutline.Bottom))
{
excelCell.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
excelCell.Style.Border.Bottom.Color.SetColor(Color.Black);
}
if (data.Outline.HasFlag(DataCellOutline.Left))
{
excelCell.Style.Border.Left.Style = ExcelBorderStyle.Medium;
excelCell.Style.Border.Left.Color.SetColor(Color.Black);
}
if (data.Outline.HasFlag(DataCellOutline.Right))
{
excelCell.Style.Border.Right.Style = ExcelBorderStyle.Medium;
excelCell.Style.Border.Right.Color.SetColor(Color.Black);
}
}
for (var i = 1; i < cells.Max(x => x.Length); i++)
{
sheet.Cells[2, i, sheet.Dimension.Rows, i].AutoFitColumns(10.71);
// 10.71 represents the default excel cell width, we do not want to go below that
}
package.Save();
package.Dispose();
}
public static void taoexcelfilesv(DataGridView data, int sodong, string excelFilePath)
{
if (File.Exists(excelFilePath)) File.Delete(excelFilePath);
FileInfo newFile = new FileInfo(excelFilePath);
// FileInfo newFile_template = new FileInfo(@"C:\template\mauimportsv.xlsx");
ExcelPackage xlPackage = new ExcelPackage(newFile);//
try
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cell(1, 1).Value = "Ma";
worksheet.Cell(1, 2).Value = "Hoten";
worksheet.Cell(1, 3).Value = "Malop";
worksheet.Cell(1, 4).Value = "Bacdaotao";
worksheet.Cell(1, 5).Value = "Khoahoc";
worksheet.Cell(1, 6).Value = "Khoa";
worksheet.Cell(1, 7).Value = "Cmnd";
worksheet.Cell(1, 8).Value = "Ngaysinh";
worksheet.Cell(1, 9).Value = "Gioitinh";
worksheet.Cell(1, 10).Value = "Diachi";
worksheet.Cell(1, 11).Value = "Dienthoai";
for (int j = 0; j < sodong; j++)
{
//for (int i = 0; i < 11; i++)
worksheet.Cell(j + 2, 1).Value = data.Rows[j].Cells["ma"].Value.ToString();
worksheet.Cell(j + 2, 2).Value = data.Rows[j].Cells["hoten"].Value.ToString();
worksheet.Cell(j + 2, 3).Value = data.Rows[j].Cells["malopd"].Value.ToString();
worksheet.Cell(j + 2, 4).Value = data.Rows[j].Cells["bacdaotaod"].Value.ToString();
worksheet.Cell(j + 2, 5).Value = data.Rows[j].Cells["Khoahocd"].Value.ToString();
worksheet.Cell(j + 2, 6).Value = data.Rows[j].Cells["khoad"].Value.ToString();
worksheet.Cell(j + 2, 7).Value = data.Rows[j].Cells["cmndd"].Value.ToString();
worksheet.Cell(j + 2, 8).Value = data.Rows[j].Cells["ngaysinhd"].Value.ToString();
worksheet.Cell(j + 2, 9).Value = data.Rows[j].Cells["gioitinhd"].Value.ToString();
worksheet.Cell(j + 2, 10).Value = data.Rows[j].Cells["diachid"].Value.ToString();
worksheet.Cell(j + 2, 11).Value = data.Rows[j].Cells["dienthoaid"].Value.ToString();
}
xlPackage.Save();
}
catch
{
MessageBox.Show("bạn đã tao file Sinhvien.xlsx trong ổ C:/ rồi ,muốn tạo lại bạn hãy xóa nó và thực hiện lại");
}
finally
{
xlPackage.Dispose();
}
}
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>
/// 依据参数，选择生成SQL语言的方法。
/// </summary>
/// <param name="filesPath">文件完整的路径名,如D:\\test.xls</param>
/// <param name="filesTypes">文件类型，如*.xls将会引用NPOI组件</param>
/// <param name="sqlLangTypes">SQL语言类别，如Insert、Update、Delete、Up-Only</param>
/// <returns></returns>
public static void npoiPrintSQLLangTypesAndMethods(string filesPath, int filesTypes, int sqlLangTypes)
{
if(filesTypes == 2003)
{
#region //xls文件的处理
try
{
FileStream fs = new FileStream(filesPath, FileMode.Open);
HSSFWorkbook HBook = new HSSFWorkbook(fs);
ISheet isheet = HBook.GetSheetAt(FormMain.defaultTables);
#region //回传当前读取的Sheet表名！
FormMain.selectTableName = isheet.SheetName;
#endregion
switch (sqlLangTypes)
{
case 1:
npoiPrintSQLLangInsertMulti(isheet);
break;
case 2:
npoiPrintSQLLangDelete(isheet);
break;
case 3:
npoiPrintSQLLangUpdate(isheet);
break;
case 4:
npoiPrintSQLLangUpdateOnly(isheet);
break;
case 5:
npoiPrintSQLLangInsertEachLineASentence(isheet);
break;
default:
break;
}
//释放过程中使用的资源！
HBook.Close();
fs.Close();
FormMain.isSqlLangCreatedSuccessful = true;
}
catch (Exception ex)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
#endregion
}
else if (filesTypes == 2007) //*.XLSX
{
try
{
ExcelPackage excelPackage;
FileInfo newFile = new FileInfo(filesPath);
excelPackage = new ExcelPackage(newFile);
ExcelWorkbook myWorkbook = excelPackage.Workbook;
ExcelWorksheet myWorksheet = myWorkbook.Worksheets[FormMain.defaultTables + 1];
#region //回传当前读取的Sheet表名！
FormMain.selectTableName = myWorksheet.Name;
#endregion
switch (sqlLangTypes)
{
case 1:
excelPackagePrintSQLLangInsertMulti(myWorksheet);
break;
case 2:
excelPackagePrintSQLLangDelete(myWorksheet);
break;
case 3:
excelPackagePrintSQLLangUpdate(myWorksheet);
break;
case 4:
excelPackagePrintSQLLangUpdateOnly(myWorksheet);
break;
case 5:
excelPackagePrintSQLLangInsertEachLineASentence(myWorksheet);
break;
default:
break;
}
//貌似很有必要释放内存，不然没法连续执行，不关掉程序文档打不开。
excelPackage.Dispose();
FormMain.isSqlLangCreatedSuccessful = true;
}
catch (Exception ex)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
public void Getvw_AnnenbergSymposiumExcel(string query)
{
DataTable myDataTable = getdatatable();
var table =from m in db.vw_AnnenbergSymposiumExcel
select m;
foreach (var element in table)
{
var row = myDataTable.NewRow();
row["Reg_ID"] = element.Reg_ID;
row["Ev_ID"] = element.Ev_ID;
row["Research_Title"] = element.Research_Title;
row["Research_Abstract"] = element.Research_Abstract;
row["Presentation_Type"] = element.Presentation_Type;
row["ASP_ID"] = element.ASP_ID;
row["Email"] = element.Email;
row["Firstname"] = element.Firstname;
row["Lastname"] = element.Lastname;
row["Adv_Email"] = element.Adv_Email;
row["Adv_Firstname"] = element.Adv_Firstname;
row["Adv_Lastname"] = element.Adv_Lastname;
myDataTable.Rows.Add(row);
}
DataTable dtExcel = new DataTable();
foreach (DataColumn col in myDataTable.Columns)
{
dtExcel.Columns.Add(col.ColumnName);
}
foreach (DataRow row in myDataTable.Rows)
{
DataRow dr = dtExcel.NewRow();
foreach (DataColumn col in myDataTable.Columns)
{
string data = row[col.ColumnName].ToString();
dr[col.ColumnName] = data;
}
dtExcel.Rows.Add(dr);
}
if (dtExcel != null)
{
String _tempFolder = ConfigurationManager.AppSettings["TempFolder"].ToString();
String _templatePath = "~/excel_templates/confidential.xlsx";
String _OutputFile = _tempFolder + "Excel_" + Guid.NewGuid().ToString() + ".xlsx";
string destFileName = HttpContext.Current.Server.MapPath(_OutputFile);
string templateFileName = HttpContext.Current.Server.MapPath(_templatePath);
ExcelPackage excelPackage = new ExcelPackage(new FileInfo(destFileName), new FileInfo(templateFileName));
ExcelWorksheet ws = excelPackage.Workbook.Worksheets[1];
ws.Cells["A1"].LoadFromDataTable(dtExcel, true);
int colCount = dtExcel.Columns.Count;
using (ExcelRange rng = ws.Cells[1, 1, 1, colCount])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));
rng.Style.Font.Color.SetColor(Color.White);
}
excelPackage.Save();
excelPackage.Dispose();
String generatedExcelFile = _OutputFile;
DownloadFile(generatedExcelFile, "AnnenbergApplications.xlsx");
}
return;
}
public bool exportFromExcel(DataGridView data, string pathToExcelFile)
{
if (File.Exists(pathToExcelFile)) File.Delete(pathToExcelFile);
FileInfo excelFile = new FileInfo(pathToExcelFile);
ExcelPackage excelPackage = new ExcelPackage(excelFile);
try
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cell(1, 1).Value = "Mã";
worksheet.Cell(1, 2).Value = "Họ tên";
worksheet.Cell(1, 3).Value = "Ngày sinh";
worksheet.Cell(1, 4).Value = "Giới tính";
worksheet.Cell(1, 5).Value = "Địa chỉ";
worksheet.Cell(1, 6).Value = "Điện thoại";
worksheet.Cell(1, 7).Value = "Email";
worksheet.Cell(1, 8).Value = "Mã khoa";
worksheet.Cell(1, 9).Value = "Trình độ";
worksheet.Cell(1, 10).Value = "Phân loại";
worksheet.Cell(1, 11).Value = "Quốc tịch";
worksheet.Cell(1, 12).Value = "Năng khiếu";
//MessageBox.Show(data.Rows[6].Cells[2].Value.ToString());
//return false;
int rowCount = data.Rows.Count;
for (int r = 0; r < rowCount; r++)
for (int c = 0; c < 12; c++)
worksheet.Cell(r + 2, c + 1).Value = data.Rows[r].Cells[c].Value.ToString();
excelPackage.Save();
return true;
}
catch
{
return false;
}
finally
{
excelPackage.Dispose();
}
}
