Ciao a tutti ho un problema a far uscire questo Export Excel.
Ho la seguente void
codice:
var db = new GMM.DAL.DBLocale();
var listaOperatori = db.Operatori.OrderBy(x => x.idOperatore).ToList();
DataTable myDT = new DataTable();
myDT= ExcelUtility.ListToDataTable(listaOperatori);
ExcelUtility.CreateExcelDocument(myDT, Server.MapPath("public/miofile.xlsx"));
Con cui richiamo le funzioni della classe: ExcelUtility
codice:
public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponse Response)
{
DataSet ds = new DataSet();
ds.Tables.Add(dt);
CreateExcelDocumentAsStream(ds, filename, Response);
ds.Tables.Remove(dt);
return true;
}
public static bool CreateExcelDocumentAsStream(DataSet ds, string filename, System.Web.HttpResponse Response)
{
try
{
System.IO.MemoryStream stream = new System.IO.MemoryStream();
SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true);
using (document)
{
WriteExcelFile(ds, document);
}
stream.Flush();
stream.Position = 0;
Response.ClearContent();
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
// NOTE: If you get an "HttpCacheability does not exist" error on the following line, make sure you have
// manually added System.Web to this project's References.
Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + filename);
byte[] data1 = new byte[stream.Length-1];
stream.Read(data1, 0, data1.Length);
stream.Close();
Response.BinaryWrite(data1);
Response.Flush();
}
catch (Exception ex)
{
log.Error("Errore CreateExcelDocumentAsStream" + ex.Message, ex);
return false;
}
finally
{
Response.End();
}
return true;
}
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Create the Excel file contents. This function is used when creating an Excel file either writing
// to a file, or writing to a MemoryStream.
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string workSheetID = "rId" + worksheetNumber.ToString();
string worksheetName = dt.TableName;
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
newWorksheetPart.Worksheet.Save();
// create the worksheet to workbook relation
if (worksheetNumber == 1)
spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = (uint)worksheetNumber,
Name = dt.TableName
});
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
spreadsheet.Close();
}
Il problema è che non mi apre nessun file alla fine. Non accade niente.
Dove sbaglio?