Экспортируйте большой запрос данных (60k + rows) в Excel

Ответ 1

Я бы попытался использовать displaytag, чтобы отобразить результаты. Вы можете настроить его для отображения определенного номера на странице, что должно решить проблему с перегрузкой. Затем вы можете установить displaytag для экспорта Excel.

Ответ 2

Обычно мы обрабатываем это с помощью кнопки команды "Экспорт", которая подключается к методу на стороне сервера, чтобы захватить набор данных и преобразовать его в CSV. Затем мы настраиваем заголовки ответов, и браузер будет рассматривать их как загрузку. Я знаю, что это решение на стороне сервера, но вы можете подумать об этом, так как у вас будут проблемы с тайм-аутом и браузером, пока вы не выполните пейджинг на стороне сервера.

Ответ 3

Почти полтора года с тех пор, как я начал эту проблему, мне наконец-то удалось в какой-то степени работать. Я буду временно ждать ответа, чтобы узнать, есть ли у кого-нибудь более эффективный, лучший метод "лучших практик".

Создав строку JSON, я развелся с JavaScript из GridView. JSON генерируется в коде позади, когда данные заполняются:

    protected static string ConstructReportJSON(ref DataTable dtResults)
    {
        StringBuilder sb = new StringBuilder();
        for (int r = 0; r < dtResults.Rows.Count; r++)
        {
            sb.Append("{");
            for (int c = 0; c < dtResults.Columns.Count; c++)
            {
                sb.AppendFormat("\"{0}\":\"{1}\",", dtResults.Columns[c].ColumnName, dtResults.Rows[r][c].ToString());
            }
            sb.Remove(sb.Length - 1, 1); //Truncate the trailing comma
            sb.Append("},");
        }
        sb.Remove(sb.Length - 1, 1);
        return String.Format("[{0}]", sb.ToString());
    }

Возвращает строку данных, например

[{ "Caller": "John Doe", "Office": "5555", "Type": "Incoming" и т.д.),

{ "Caller": "Jane Doe", "Office": "7777", "Тип": "Исходящие" и т.д.}, {etc}]

Я скрыл эту строку, назначив текст в Literal в UpdatePanel, используя:

    <div id="div_JSON" style="display: none;">
            <asp:Literal id="lit_JSON" runat="server" /> 
    </div>

И JavaScript анализирует вывод, считывая содержимое div:

function exportToExcel_Pivot(sMyJSON, sTitleOfReport, sReportPop) {
     //sMyJSON = the name, supplied as a text, of the hidden element that houses the JSON array.
     //sTitleOfReport = Will be used as the page header if the spreadsheet is printed.
     //sReportPop = Determines which business logic to create a pivot table for.

var sJSON = document.getElementById(sMyJSON).innerHTML;
var oJSON = eval("(" + sJSON + ")");

 //    DEBUG Example Test Code
 //    for (x = 0; x < oJSON.length; x++) {
 //        for (y in oJSON[x])
 //            alert(oJSON[x][y]); //DEBUG, returns field value
 //            alert(y); //DEBUG, returns column name
 //    }


//If no data is in the JSON object array, display alert.
if (oJSON == null)
    alert('No data for report');
else {
    var oExcel = new ActiveXObject("Excel.Application");
    var oBook = oExcel.Workbooks.Add;
    var oSheet = oBook.Worksheets(1);
    var oSheet2 = oBook.Worksheets(2);
    var iRow = 0;
    var iCol = 0;

        //Take the column names of the JSON object and prepare them in Excel
        for (header in oJSON[0])
        {
            oSheet.Cells(iRow + 1, iCol + 1) = header;
            iCol++;
        }

        iRow++;

        //Export all rows of the JSON object to excel
        for (var r = 0; r < oJSON.length; r++)
        {
            iCol = 0;
            for (c in oJSON[r]) 
                    {
                        oSheet.Cells(iRow + 1, iCol + 1) = oJSON[r][c];
                        iCol++;
                    } //End column loop
            iRow++;
        } //End row

Вывод строки и анализ JavaScript eval работают очень быстро, но цикл через объект JSON немного медленнее, чем хотелось бы.

Я считаю, что этот метод будет ограничен примерно 1 миллиардом символов данных - может быть, меньше зависит от того, как работает тестирование памяти. (Я подсчитал, что я, вероятно, буду смотреть максимум на 1 миллион символов в день, так что это должно быть хорошо, в течение одного года после отчета.)

Ответ 4

Легко и эффективно писать CSV файлы. Однако , если вам нужен Excel, он также может быть выполнен достаточно эффективным способом, который может обрабатывать более 60 000 строк с помощью открытого XML-писателя Microsoft Open XML SDK.

• Установите Microsoft Open SDK, если у вас его нет (google "загрузить microsoft open xml sdk" )
• Создать консольное приложение
• Добавить ссылку на DocumentFormat.OpenXml
• Добавить ссылку на WindowsBase
• Попробуйте запустить некоторый тестовый код, как показано ниже (потребуется несколько вариантов использования)

Просто ознакомьтесь с решением Vincent Tan на http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/ (Ниже я немного очистил его пример, чтобы помочь новым пользователям.)

В моем собственном использовании я нашел это довольно прямолинейно с регулярными данными, но мне пришлось вырезать символы "\ 0" из моих реальных данных.

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

...

        using (var workbook = SpreadsheetDocument.Create("SomeLargeFile.xlsx", SpreadsheetDocumentType.Workbook))
        {
            List<OpenXmlAttribute> attributeList;
            OpenXmlWriter writer;

            workbook.AddWorkbookPart();
            WorksheetPart workSheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();

            writer = OpenXmlWriter.Create(workSheetPart);
            writer.WriteStartElement(new Worksheet());
            writer.WriteStartElement(new SheetData());

            for (int i = 1; i <= 50000; ++i)
            {
                attributeList = new List<OpenXmlAttribute>();
                // this is the row index
                attributeList.Add(new OpenXmlAttribute("r", null, i.ToString()));

                writer.WriteStartElement(new Row(), attributeList);

                for (int j = 1; j <= 100; ++j)
                {
                    attributeList = new List<OpenXmlAttribute>();
                    // this is the data type ("t"), with CellValues.String ("str")
                    attributeList.Add(new OpenXmlAttribute("t", null, "str"));

                    // it suggested you also have the cell reference, but
                    // you'll have to calculate the correct cell reference yourself.
                    // Here an example:
                    //attributeList.Add(new OpenXmlAttribute("r", null, "A1"));

                    writer.WriteStartElement(new Cell(), attributeList);

                    writer.WriteElement(new CellValue(string.Format("R{0}C{1}", i, j)));

                    // this is for Cell
                    writer.WriteEndElement();
                }

                // this is for Row
                writer.WriteEndElement();
            }

            // this is for SheetData
            writer.WriteEndElement();
            // this is for Worksheet
            writer.WriteEndElement();
            writer.Close();

            writer = OpenXmlWriter.Create(workbook.WorkbookPart);
            writer.WriteStartElement(new Workbook());
            writer.WriteStartElement(new Sheets());

            // you can use object initialisers like this only when the properties
            // are actual properties. SDK classes sometimes have property-like properties
            // but are actually classes. For example, the Cell class has the CellValue
            // "property" but is actually a child class internally.
            // If the properties correspond to actual XML attributes, then you're fine.
            writer.WriteElement(new Sheet()
            {
                Name = "Sheet1",
                SheetId = 1,
                Id = workbook.WorkbookPart.GetIdOfPart(workSheetPart)
            });

            writer.WriteEndElement(); // Write end for WorkSheet Element
            writer.WriteEndElement(); // Write end for WorkBook Element
            writer.Close();

            workbook.Close();
        }

Если вы просмотрите этот код, вы заметите две основные записи: сначала лист, а затем книгу, содержащую лист. Часть книги - это скучная часть в конце, более ранняя часть листа содержит все строки и столбцы.

В вашей собственной адаптации вы можете записывать реальные значения строк в ячейки из ваших собственных данных. Вместо этого мы просто используем нумерацию строк и столбцов.

writer.WriteElement(new CellValue("SomeValue"));

Стоит отметить, что нумерация строк в Excel начинается с 1, а не 0. Запуск строк, пронумерованных из индекса нуля, приведет к сообщениям об ошибках "Коррумпированный файл".

Наконец, если вы работаете с очень большими наборами данных, никогда не вызывать ToList(). Используйте метод чтения данных стиль потоковой передачи данных. Например, у вас может быть IQueryable и использовать его в для каждого. Вы никогда не захотите полагаться на одновременное использование всех данных в памяти, или вы столкнетесь с нехваткой памяти и/или высокой загрузкой памяти.