Limitazione quando esport i dati in un foglio di calcolo di Excel

So che questa domanda esiste, perché è la mia e ho messo 500 punti bounty su di esso:

Esportzione di report C # in Excel quando ci sono più di 5K righe

La risposta mi ha portto al di sopra (a un certo punto) ma siamo al punto in cui accettiamo semplicemente che i set di dati anormalmente non possono essere esportti tramite il nostro frontale ASP, quindi spediamo queste richieste al nostro SQL DB del server, che quindi eseguono le procedure memorizzate appropriate e copia / incolla nei fogli di calcolo di Excel.

La mia domanda qui è; può qualcuno rispondere definitivamente se è assolutamente imansible esportre un grande set di dati in un foglio di calcolo Excel tramite un frontale ASP? Una volta che un particolare rapporto colpisce circa 8K record o qualcosa, non può sembrare che sia fatto. Sto solo cercando di determinare se è ansible fare altre modifiche potenziali, o se quei dati sono solo più di ASP in grado di gestire?

Beh … da quando ho trasmesso gigabyte di dati direttamente da ASP.NET, sono abbastanza sicuro che stai facendo qualcosa di sbagliato. Cercare di isolare il problema innanzitutto – è nel mettere i dati nella session, è i limiti di richiesta / risposta, è richiesta timeout? Scopri where è il problema e poi vai avanti e risolvi! 🙂

In termini generali, non c'è motivo per cui dovresti prima mettere i dati in un DataSet . Utilizza invece un SqlDataReader e scrivi i dati da submit in blocchi. In questo modo si evita di avere l'insieme di dati in memory; allo stesso modo, è ansible scrivere direttamente sul stream di output, senza bufferare l'HTML generato in memory. Perché tenete i dati in session? Non sarebbe meglio tenere i parametri necessari per recuperarla dal DB, se necessario, utilizzando il DataReader ?

Se hai problemi con i timeout, esegui una guida periodica. Questo aiuta anche a ridurre l'impronta di memory sul lato ASP.NET.

Salvare i dati di output in un file sul server aiuta innanzitutto e ti permette di colbind anche file di file parziali – assicuratevi di avere spazio sufficiente sull'unità.

EDIT :

Ok, quindi hai un SqlCommand . Invece di utilizzarlo in un SqlDataAdapter , puoi fare qualcosa di simile ( cmd come istanza SqlCommand ):

 HtmlTextWriter wr = new HtmlTextWriter(Response.Output); using (var rdr = cmd.ExecuteReader()) { int index = 0; wr.WriteBeginTag("table"); wr.WriteLine("<tr><td>Column 1</td><td>Column 2</td></tr>"); while (rdr.Read()) { wr.WriteBeginTag("tr"); wr.WriteBeginTag("td"); wr.Write(rdr["Column1"]); wr.WriteEndTag("td"); wr.WriteBeginTag("td"); wr.Write(rdr["Column2"]); wr.WriteEndTag("td"); wr.WriteEndTag("tr"); if (index++ % 1000 == 0) Response.Flush(); } wr.WriteEndTag("table"); } indice int = 0; HtmlTextWriter wr = new HtmlTextWriter(Response.Output); using (var rdr = cmd.ExecuteReader()) { int index = 0; wr.WriteBeginTag("table"); wr.WriteLine("<tr><td>Column 1</td><td>Column 2</td></tr>"); while (rdr.Read()) { wr.WriteBeginTag("tr"); wr.WriteBeginTag("td"); wr.Write(rdr["Column1"]); wr.WriteEndTag("td"); wr.WriteBeginTag("td"); wr.Write(rdr["Column2"]); wr.WriteEndTag("td"); wr.WriteEndTag("tr"); if (index++ % 1000 == 0) Response.Flush(); } wr.WriteEndTag("table"); } 

Non l'ho provata, quindi potrebbe essere necessario una modifica, ma l'idea dovrebbe essere abbastanza evidente.

È ansible farlo in quanto ho appena finito un codice specifico per farlo come parte di un progetto di reporting che sto lavorando su where abbiamo in eccesso di record da 20K che devono essere tirati indietro e esportti in excel.

Tirerò fuori il codice e lo attaccherò a github per te a guardare.

Sto usando in realtà il pacchetto di elaborazione excel di NPOI e quindi utilizzando il mio codice personalizzato sono in grado di elaborare dynamicmente qualsiasi Elenco di classi in un set di dati e quindi scaricarlo nei fogli di lavoro.

Ho bisogno di ordinare il codice per te, ma dovrei avere qualcosa di pronto per te questa sera.

Questo codice funziona sia per le applicazioni desktop che per le applicazioni web.

Per darti un'idea, il mio codice è stato in grado di elaborare relativamente rapidamente un set di dati di oltre 30.000. Devo risolvere un problema con i set di dati oltre il limite di 65536 record prima di essere pronto per te.

La cosa bella con questa soluzione significa che non si basa su excel che viene installato sulla macchina che ospita la soluzione.

EDIT Ho caricato un progetto su github qui:

https://github.com/JellyMaster/ExcelHelper

ma qui è il bit principale che fa tutta l'elaborazione excel:

  public static MemoryStream CreateExcelSheet(DataSet dataToProcess) { MemoryStream stream = new MemoryStream(); if (dataToProcess != null) { var excelworkbook = new HSSFWorkbook(); foreach (DataTable table in dataToProcess.Tables) { var worksheet = excelworkbook.CreateSheet(); var headerRow = worksheet.CreateRow(0); foreach (DataColumn column in table.Columns) { headerRow.CreateCell(table.Columns.IndexOf(column)).SetCellValue(column.ColumnName); } //freeze top panel. worksheet.CreateFreezePane(0, 1, 0, 1); int rowNumber = 1; foreach (DataRow row in table.Rows) { var sheetRow = worksheet.CreateRow(rowNumber++); foreach (DataColumn column in table.Columns) { sheetRow.CreateCell(table.Columns.IndexOf(column)).SetCellValue(row[column].ToString()); } } } excelworkbook.Write(stream); } return stream; } public static DataSet CreateDataSetFromExcel(Stream streamToProcess, string fileExtentison = "xlsx") { DataSet model = new DataSet(); if (streamToProcess != null) { if (fileExtentison == "xlsx") { XSSFWorkbook workbook = new XSSFWorkbook(streamToProcess); model = ProcessXLSX(workbook); } else { HSSFWorkbook workbook = new HSSFWorkbook(streamToProcess); model = ProcessXLSX(workbook); } } return model; } private static DataSet ProcessXLSX(HSSFWorkbook workbook) { DataSet model = new DataSet(); for (int index = 0; index < workbook.NumberOfSheets; index++) { ISheet sheet = workbook.GetSheetAt(0); if (sheet != null) { DataTable table = GenerateTableData(sheet); model.Tables.Add(table); } } return model; } private static DataTable GenerateTableData(ISheet sheet) { DataTable table = new DataTable(sheet.SheetName); for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++) { //we will assume the first row are the column names IRow row = sheet.GetRow(rowIndex); //a completely empty row of data so break out of the process. if (row == null) { break; } if (rowIndex == 0) { for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++) { string value = row.GetCell(cellIndex).ToString(); if (string.IsNullOrEmpty(value)) { break; } else { table.Columns.Add(new DataColumn(value)); } } } else { //get the data and add to the collection //now we know the number of columns to iterate through lets get the data and fill up the table. DataRow datarow = table.NewRow(); object[] objectArray = new object[table.Columns.Count]; for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++) { try { ICell cell = row.GetCell(columnIndex); if (cell != null) { objectArray[columnIndex] = cell.ToString(); } else { objectArray[columnIndex] = string.Empty; } } catch (Exception error) { Debug.WriteLine(error.Message); Debug.WriteLine("Column Index" + columnIndex); Debug.WriteLine("Row Index" + row.RowNum); } } datarow.ItemArray = objectArray; table.Rows.Add(datarow); } } return table; } private static DataSet ProcessXLSX(XSSFWorkbook workbook) { DataSet model = new DataSet(); for (int index = 0; index < workbook.NumberOfSheets; index++) { ISheet sheet = workbook.GetSheetAt(index); if (sheet != null) { DataTable table = GenerateTableData(sheet); model.Tables.Add(table); } } return model; } } Foglio ISheet = workbook.GetSheetAt (0);  public static MemoryStream CreateExcelSheet(DataSet dataToProcess) { MemoryStream stream = new MemoryStream(); if (dataToProcess != null) { var excelworkbook = new HSSFWorkbook(); foreach (DataTable table in dataToProcess.Tables) { var worksheet = excelworkbook.CreateSheet(); var headerRow = worksheet.CreateRow(0); foreach (DataColumn column in table.Columns) { headerRow.CreateCell(table.Columns.IndexOf(column)).SetCellValue(column.ColumnName); } //freeze top panel. worksheet.CreateFreezePane(0, 1, 0, 1); int rowNumber = 1; foreach (DataRow row in table.Rows) { var sheetRow = worksheet.CreateRow(rowNumber++); foreach (DataColumn column in table.Columns) { sheetRow.CreateCell(table.Columns.IndexOf(column)).SetCellValue(row[column].ToString()); } } } excelworkbook.Write(stream); } return stream; } public static DataSet CreateDataSetFromExcel(Stream streamToProcess, string fileExtentison = "xlsx") { DataSet model = new DataSet(); if (streamToProcess != null) { if (fileExtentison == "xlsx") { XSSFWorkbook workbook = new XSSFWorkbook(streamToProcess); model = ProcessXLSX(workbook); } else { HSSFWorkbook workbook = new HSSFWorkbook(streamToProcess); model = ProcessXLSX(workbook); } } return model; } private static DataSet ProcessXLSX(HSSFWorkbook workbook) { DataSet model = new DataSet(); for (int index = 0; index < workbook.NumberOfSheets; index++) { ISheet sheet = workbook.GetSheetAt(0); if (sheet != null) { DataTable table = GenerateTableData(sheet); model.Tables.Add(table); } } return model; } private static DataTable GenerateTableData(ISheet sheet) { DataTable table = new DataTable(sheet.SheetName); for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++) { //we will assume the first row are the column names IRow row = sheet.GetRow(rowIndex); //a completely empty row of data so break out of the process. if (row == null) { break; } if (rowIndex == 0) { for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++) { string value = row.GetCell(cellIndex).ToString(); if (string.IsNullOrEmpty(value)) { break; } else { table.Columns.Add(new DataColumn(value)); } } } else { //get the data and add to the collection //now we know the number of columns to iterate through lets get the data and fill up the table. DataRow datarow = table.NewRow(); object[] objectArray = new object[table.Columns.Count]; for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++) { try { ICell cell = row.GetCell(columnIndex); if (cell != null) { objectArray[columnIndex] = cell.ToString(); } else { objectArray[columnIndex] = string.Empty; } } catch (Exception error) { Debug.WriteLine(error.Message); Debug.WriteLine("Column Index" + columnIndex); Debug.WriteLine("Row Index" + row.RowNum); } } datarow.ItemArray = objectArray; table.Rows.Add(datarow); } } return table; } private static DataSet ProcessXLSX(XSSFWorkbook workbook) { DataSet model = new DataSet(); for (int index = 0; index < workbook.NumberOfSheets; index++) { ISheet sheet = workbook.GetSheetAt(index); if (sheet != null) { DataTable table = GenerateTableData(sheet); model.Tables.Add(table); } } return model; } } 

Ciò richiede che il pacchetto NPOI nuget sia installato nel progetto.

Tutte le domande mi danno un grido. Il progetto github fa un po 'più, ma questo è sufficiente per farti andare sperando.