c# - I am trying to export an excel and make it password protected. My code is given below.But i am getting error -
i trying export excel , make password protected.
my code given below.
but getting error:
excel completed file level validation , repair.
some parts of workbook may have been repaired or discarded.
i don't know doing wrong .
in-case without save line package error doesn't appear.
in controller:
[httpget] public filestreamresult exporttoexcel() { _objservice = new servicebal(); list<reconcilationentity> objmodel = new list<reconcilationentity>(); objmodel = _objservice.getcreditsudharleads(); string url = string.empty; if (!directory.exists(server.mappath("~/tempexcel"))) { system.io.directory.createdirectory(server.mappath("~/tempexcel")); } string filepath = server.mappath("~/tempexcel"); string date = datetime.now.toshortdatestring().replace("/", "_") + "_" + datetime.now.toshorttimestring().replace(" ", "_").replace(":", "_").trim(); string filename = "creditsudhar_" + date + ".xlsx"; filepath = filepath + "\\" + filename; string[] columns = { "affname", "affphone", "affemail", "productname", "contactname", "status", "createdon", "commission", "iscommissionpaid", "accountname", "accountnumber", "bankname", "bankbranch", "ifsccode", "pannumber" }; var file = excelexporthelper.exportexcel(excelexporthelper.listtodatatable(objmodel), filepath, "creditsudhar reconcillation sheet " + datetime.now.toshortdatestring(), true, columns); var memstream = new memorystream(file); return this.file(memstream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); } public static string excelcontenttype { { return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; } } public static datatable listtodatatable<t>(list<t> data) { propertydescriptorcollection properties = typedescriptor.getproperties(typeof(t)); datatable datatable = new datatable(); (int = 0; < properties.count; i++) { propertydescriptor property = properties[i]; datatable.columns.add(property.name, nullable.getunderlyingtype(property.propertytype) ?? property.propertytype); } object[] values = new object[properties.count]; foreach (t item in data) { (int = 0; < values.length; i++) { values[i] = properties[i].getvalue(item); } datatable.rows.add(values); } return datatable; } public static byte[] exportexcel(datatable datatable, string filepath, string heading = "", bool showsrno = false, params string[] columnstotake) { string fullpath = string.empty; byte[] ret; deleteuploadedfile(filepath); string result = string.empty; using (excelpackage package = new excelpackage()) { excelworksheet worksheet = package.workbook.worksheets.add(string.format("{0} data", heading)); int startrowfrom = string.isnullorempty(heading) ? 1 : 3; if (showsrno) { datacolumn datacolumn = datatable.columns.add("#", typeof(int)); datacolumn.setordinal(0); int index = 1; foreach (datarow item in datatable.rows) { item[0] = index; index++; } } // add content excel file worksheet.cells["a" + startrowfrom].loadfromdatatable(datatable, true); // autofit width of cells small content int columnindex = 1; foreach (datacolumn column in datatable.columns) { try { excelrange columncells = worksheet.cells[worksheet.dimension.start.row, columnindex, worksheet.dimension.end.row, columnindex]; int maxlength = columncells.max(cell => cell.value.tostring().count()); if (maxlength < 150) { worksheet.column(columnindex).autofit(); } columnindex++; } catch (exception ex) { if (!(ex system.threading.threadabortexception)) { //log other errors here } } } // format header - bold, yellow on black using (excelrange r = worksheet.cells[startrowfrom, 1, startrowfrom, datatable.columns.count]) { r.style.font.color.setcolor(system.drawing.color.white); r.style.font.bold = true; r.style.fill.patterntype = officeopenxml.style.excelfillstyle.solid; r.style.fill.backgroundcolor.setcolor(system.drawing.colortranslator.fromhtml("#1fb5ad")); } // format cells - add borders using (excelrange r = worksheet.cells[startrowfrom + 1, 1, startrowfrom + datatable.rows.count, datatable.columns.count]) { r.style.border.top.style = excelborderstyle.thin; r.style.border.bottom.style = excelborderstyle.thin; r.style.border.left.style = excelborderstyle.thin; r.style.border.right.style = excelborderstyle.thin; r.style.border.top.color.setcolor(system.drawing.color.black); r.style.border.bottom.color.setcolor(system.drawing.color.black); r.style.border.left.color.setcolor(system.drawing.color.black); r.style.border.right.color.setcolor(system.drawing.color.black); } // removed ignored columns (int = datatable.columns.count - 1; >= 0; i--) { if (i == 0 && showsrno) { continue; } if (!columnstotake.contains(datatable.columns[i].columnname)) { worksheet.deletecolumn(i + 1); } } if (!string.isnullorempty(heading)) { worksheet.cells["a1"].value = heading; worksheet.cells["a1"].style.font.size = 20; worksheet.insertcolumn(1, 1); worksheet.insertrow(1, 1); worksheet.column(1).width = 5; } system.io.fileinfo fileinfo2 = new system.io.fileinfo(filepath); deleteuploadedfile(filepath); worksheet.protection.setpassword("mypassword"); worksheet.protection.isprotected = true; worksheet.protection.allowselectunlockedcells = false; worksheet.protection.allowselectlockedcells = false; package.saveas(fileinfo2, "mypassword"); ret = package.getasbytearray(); return ret; } } public static void deleteuploadedfile(string filepath) { try { if (system.io.file.exists(filepath)) { system.io.file.delete(filepath); } } catch (exception ex) { } } public static byte[] exportexcel<t>(list<t> data, string filepath, string heading = "", bool showslno = false, params string[] columnstotake) { return exportexcel(listtodatatable<t>(data), filepath, heading, showslno, columnstotake); }
an answer mentioned saveas close package, correct steps returning saved file array instead of using getasbytearray afterwards. or use getasbytearray(passwords) without saveas.
Comments
Post a Comment