Come esportre i dati JQgrid in Excel utilizzando c #?

Ho fatto alcune ricerche riguardo a questo ma la maggior parte delle soluzioni sono per MVC .. Sto usando solo Asp.net 3.5 Come potrei raggiungere questo clic sul button .. Dovrei includere qualsiasi libreria o qualsiasi altra cosa .. Aiutaci per favore ..

Il codice che ho inviato nella risposta può essere utilizzato praticamente senza alcuna modifica in alcun codice ASP.NET che sono scritti in C #. La class di assistente DataForExcel (vedere il file DataForExcel.cs ) ha il constructor

 public DataForExcel(string[] headers, List<string[]> data, string sheetName) 

o una versione più "avanzata"

 public DataForExcel(string[] headers, DataType[] colunmTypes, List<string[]> data, string sheetName) 

che consentono di specificare quali colonne hanno il tipo di dati numbersco. I parametri List<string[]> data sono i dati che devono essere esportti in Excel. Le string[] headers parametro string[] headers specificano i dati della prima row dell'output.

La class DataForExcel ha solo un metodo pubblico

 public void CreateXlsxAndFillData(Stream stream) 

che riempiono il stream con la rappresentazione binaria del file Excel .XLSX risultante.

Per restituire i dati binari dal metodo ASP.NET (ad esempio il gestore ASHX) è necessario fare quasi la stessa cosa esegue ExecuteResult dalla mia risposta:

  • creare stream di memory con l' using (var stream = new MemoryStream()) {...}
  • creare DataForExcel object DataForExcel necessario per l'esportzione in Excel var dataExcel = new DataForExcel (new []{"Id", "Votes", "Title"}, "Questions.xlsx", "Name or Sheet");
  • response.AddHeader ("content-disposition", "attachment; filename=Questions.xlsx");
  • response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
  • response.ContentEncoding = Encoding.UTF8;
  • stream.WriteTo (response.OutputStream);
  • response.Flush();

È tutto.

AGGIORNAMENTO : Ho modificato il codice che ho inviato prima per creare in modo molto semplice il file Excel (in formato .xlsx) dalla griglia. Se si utilizza OpenXML SDK 2.0, è ansible utilizzare .NET 3.5. OpenXML SDK 2.5 richiesto .NET 4.0 o superiore.

Il codice suggerito consente di convertire la string[][] dei dati in dati binari di Excel e scrivere i risultati in Stream. È ansible utilizzare MemoryStream come ho descritto prima per restituire Excel da qualsiasi applicazione ASP.NET.

Il codice suggerito contiene la class statica ExportToExcel con un metodo statico pubblico FillSpreadsheetDocument che può essere utilizzato nel modo seguente

 var data = new[] { new [] {"Tom", "30", "x", "", "1974-06-16"}, new [] {"Margarita", "34", "x", "x", "1978-10-02"}, new [] {"Bob", "7", "", "", "2005-06-26"}, new [] {"Oleg", "48", "x", "x", "1964-09-11"}, new [] {"Frank", "29", "", "x", "1983-01-28"} }; using (var stream = new FileStream("Test.xlsx", FileMode.Create)) { ExportToExcel.FillSpreadsheetDocument(stream, new[] { new ColumnModel { Type = DataType.String, Alignment = HorizontalAlignment.Left, Header = "Name" }, new ColumnModel { Type = DataType.Integer, Header = "Age" }, new ColumnModel { Type = DataType.String, Header = "Is Married", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true }, new ColumnModel { Type = DataType.String, Header = "Has Children", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true }, new ColumnModel { Type = DataType.Date, Header = "Birthday", Alignment = HorizontalAlignment.Left } }, data, "Friends"); } 

Produce il "Test.xlsx" con un foglio "Amici" che guarda

immettere qui la descrizione dell'immagine

La width delle colonne non sarà impostata, ma in due clic (select tutti e fare doppio click tra le colonne), l'utente può impostare la width delle colonne in width ottimale, come nella build precedente. Tutte le celle hanno dati formattati (nessun formato "generico"). Ho usato interi, date e stringhe pura. Si possono facilmente creare colonne con l'allineamento centrale oi testi di allineamento destro.

È ansible modificare facilmente il codice in modo da utilizzare più formati di text diversi. È solo un esempio di come produrre un vero documento Excel con le celle formattate.

Il progetto Visual Studio 2008 in esecuzione è ansible scaricare da qui . Di seguito troverai il codice sorgente dal demo:

 using System; using System.Collections.Generic; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.Text; using System.IO; using System.Globalization; namespace ExportToExcel { public enum ExcelCellDataType { String, Integer, Date } public enum HorizontalAlignment { Left, Center, Right } public class ColumnModel { public ExcelCellDataType Type { set; get; } public HorizontalAlignment Alignment { set; get; } public string Header { set; get; } public bool IsRotatedHeader { set; get; } } public enum OutputCellFormat: uint { Text, Integer, Date, TextHeader, TextHeaderRotated, TextCenter, TextRight } public static class ExportToExcel { private static StringBuilder ConvertIntToColumnHeader (uint iCol) { var sb = new StringBuilder(); while (iCol > 0) { if (iCol <= 'Z' - 'A') // iCol=0 -> 'A', 25 -> 'Z' break; sb.Append(ConvertIntToColumnHeader(iCol / ('Z' - 'A' + 1) - 1)); iCol = iCol % ('Z' - 'A' + 1); } sb.Append((char)('A' + iCol)); return sb; } private static string GetCellReference (uint iRow, uint iCol) { return ConvertIntToColumnHeader(iCol).Append(iRow).ToString(); } private static Row CreateColumnHeaderRow (uint iRow, IList<ColumnModel> colunmModels) { var r = new Row { RowIndex = iRow }; for (var iCol = 0; iCol < colunmModels.Count; iCol++) { var styleIndex = colunmModels[iCol].IsRotatedHeader ? (UInt32Value)(uint)(OutputCellFormat.TextHeaderRotated + 1) : (UInt32Value)(uint)(OutputCellFormat.TextHeader + 1); r.Append(new OpenXmlElement[] { // create Cell with InlineString as a child, which has Text as a child new Cell(new InlineString(new Text { Text = colunmModels[iCol].Header })) { DataType = CellValues.InlineString, StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); } return r; } private static UInt32Value GetStyleIndexFromColumnModel (ColumnModel colunmModel) { switch (colunmModel.Type) { case ExcelCellDataType.Integer: return (uint)(OutputCellFormat.Integer) + 1; case ExcelCellDataType.Date: return (uint)(OutputCellFormat.Date) + 1; } switch (colunmModel.Alignment) { case HorizontalAlignment.Center: return (uint)(OutputCellFormat.TextCenter) + 1; case HorizontalAlignment.Right: return (uint)(OutputCellFormat.TextRight) + 1; default: return (uint)(OutputCellFormat.Text) + 1; } } private static string ConvertDateToString (string date) { DateTime dt; string text = date; // default results of conversion if (DateTime.TryParse(date, out dt)) text = dt.ToOADate().ToString(CultureInfo.InvariantCulture); return text; } private static Row CreateRow (UInt32 iRow, IList<string> data, IList<ColumnModel> colunmModels, IDictionary<string, int> sharedStrings) { var r = new Row { RowIndex = iRow }; for (var iCol = 0; iCol < data.Count; iCol++) { var styleIndex = (uint)(OutputCellFormat.Text) + 1; string text = data[iCol] ?? String.Empty; if (colunmModels != null && iCol < colunmModels.Count) { styleIndex = GetStyleIndexFromColumnModel(colunmModels[iCol]); switch (colunmModels[iCol].Type) { case ExcelCellDataType.Integer: r.Append(new OpenXmlElement[] { // create Cell with CellValue as a child, which has Text as a child new Cell(new CellValue { Text = text }) { StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); continue; case ExcelCellDataType.Date: r.Append(new OpenXmlElement[] { // create Cell with CellValue as a child, which has Text as a child new Cell(new CellValue { Text = ConvertDateToString(text) }) { StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); continue; } } // default format is text if (String.IsNullOrEmpty(text) || !sharedStrings.ContainsKey(text)) { // create Cell with InlineString as a child, which has Text as a child r.Append(new OpenXmlElement[] { new Cell(new InlineString(new Text { Text = text })) { DataType = CellValues.InlineString, StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); } else { r.Append(new OpenXmlElement[] { // create Cell with CellValue as a child, which has Text as a child new Cell(new CellValue { Text = sharedStrings[text].ToString(CultureInfo.InvariantCulture) }) { DataType = CellValues.SharedString, StyleIndex = styleIndex, CellReference = GetCellReference(iRow, (uint)iCol) } }); } } return r; } private static void FillSpreadsheetDocument (SpreadsheetDocument spreadsheetDocument, IList<ColumnModel> columnModels, IList<string[]> data, string sheetName) { if (columnModels == null) throw new ArgumentNullException("columnModels"); if (data == null) throw new ArgumentNullException("data"); // add empty workbook and worksheet to the SpreadsheetDocument var workbookPart = spreadsheetDocument.AddWorkbookPart(); var worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>(); // create styles for the header and columns workbookStylesPart.Stylesheet = new Stylesheet( new Fonts( // Index 0 - The default font. new Font( new FontSize { Val = 11 }, new Color { Rgb = new HexBinaryValue { Value = "00000000" } }, new FontName { Val = "Calibri" } ), // Index 1 - The bold font. new Font( new Bold(), new FontSize { Val = 11 }, new Color { Rgb = new HexBinaryValue { Value = "00000000" } }, new FontName { Val = "Calibri" } ) ), new Fills( // Index 0 - required, reserved by Excel - no pattern new Fill(new PatternFill { PatternType = PatternValues.None }), // Index 1 - required, reserved by Excel - fill of gray 125 new Fill(new PatternFill { PatternType = PatternValues.Gray125 }), // Index 2 - no pattern text on gray background new Fill(new PatternFill { PatternType = PatternValues.Solid, BackgroundColor = new BackgroundColor { Indexed = 64U }, ForegroundColor = new ForegroundColor { Rgb = "FFD9D9D9" } }) ), new Borders( // Index 0 - The default border. new Border( new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder() ), // Index 1 - Applies a Left, Right, Top, Bottom border to a cell new Border( new LeftBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new RightBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new TopBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new BottomBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new DiagonalBorder() ) ), new CellFormats( // Index 0 - The default cell style. If a cell does not have a style iCol applied it will use this style combination instead new CellFormat { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }, // Index 1 - Alignment Left, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Left }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 2 - Interger Number new CellFormat { NumberFormatId = (UInt32Value)1U, // "0" - integer format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true }, // Index 3 - Interger Date new CellFormat { NumberFormatId = (UInt32Value)14U, // "14" - date format mm-dd-yy - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true }, // Index 4 - Text for headers new CellFormat(new Alignment { Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Center }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 5 - Text for headers rotated new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Center, TextRotation = (UInt32Value)90U }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 6 - Alignment Center, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Center }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 7 - Alignment Right, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Right }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true } ) ); workbookStylesPart.Stylesheet.Save(); // create and fill SheetData var sheetData = new SheetData(); // first row is the header uint iRow = 1; sheetData.AppendChild(CreateColumnHeaderRow(iRow++, columnModels)); //iRow++; // skip one row for the filter // first of all collect all different strings var sst = new SharedStringTable(); var sharedStrings = new SortedDictionary<string, int>(); foreach (var dataRow in data) for (var iCol = 0; iCol < dataRow.Length; iCol++) if (iCol >= columnModels.Count || columnModels[iCol].Type != ExcelCellDataType.Integer) { string text = (columnModels[iCol].Type == ExcelCellDataType.Date ? dataRow[iCol] : ConvertDateToString(dataRow[iCol])) ?? String.Empty; if (!String.IsNullOrEmpty(text) && !sharedStrings.ContainsKey(text)) { sst.AppendChild(new SharedStringItem(new Text(text))); sharedStrings.Add(text, sharedStrings.Count); } } var shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>(); shareStringPart.SharedStringTable = sst; shareStringPart.SharedStringTable.Save(); foreach (var dataRow in data) sheetData.AppendChild(CreateRow(iRow++, dataRow, columnModels, sharedStrings)); // add sheet data to Worksheet worksheetPart.Worksheet = new Worksheet(sheetData); worksheetPart.Worksheet.Save(); // fill workbook with the Worksheet spreadsheetDocument.WorkbookPart.Workbook = new Workbook( new FileVersion { ApplicationName = "Microsoft Office Excel" }, new Sheets(new Sheet { Name = sheetName, SheetId = (UInt32Value)1U, Id = workbookPart.GetIdOfPart(worksheetPart) // generate the id for sheet }) ); spreadsheetDocument.WorkbookPart.Workbook.Save(); spreadsheetDocument.Close(); } public static void FillSpreadsheetDocument (Stream stream, IList<ColumnModel> columnModels, IList<string[]> data, string sheetName) { using (var spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { FillSpreadsheetDocument(spreadsheetDocument, columnModels, data, sheetName); } } } class Program { static void Main () { var data = new[] { new [] {"Tom", "30", "x", null, "1974-06-16"}, new [] {"Margarita", "34", "x", "x", null}, new [] {"Bob", "7", "", "", "2005-06-26"}, new [] {"Oleg", null, "x", "x", "1964-09-11"}, new [] {"Frank", "29", "", "x", "1983-01-28"} }; using (var stream = new FileStream("Test.xlsx", FileMode.Create)) { ExportToExcel.FillSpreadsheetDocument(stream, new[] { new ColumnModel { Type = ExcelCellDataType.String, Alignment = HorizontalAlignment.Left, Header = "Name" }, new ColumnModel { Type = ExcelCellDataType.Integer, Header = "Age" }, new ColumnModel { Type = ExcelCellDataType.String, Header = "Is Married", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true }, new ColumnModel { Type = ExcelCellDataType.String, Header = "Has Children", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true }, new ColumnModel { Type = ExcelCellDataType.Date, Header = "Birthday", Alignment = HorizontalAlignment.Left } }, data, "Friends"); } } } } 

php + jqgrid + esport in excel

Mentre è una soluzione PHP, il nucleo sembra essere nel Javascript se questo è di qualsiasi aiuto.

È ansible utilizzare il formato XML di Office (http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats). L'unico inconveniente è che l'output sarà un file XML piuttosto che un binario. Tuttavia, si apre e si comport esattamente lo stesso come il file .xls regolare.

Il modo in cui ho fatto prima è quello di invertire l'ingegnerizzazione di Excel nel formato che ne ho bisogno, quindi salvarlo come formato .xml, aprirlo in Notepad ++ e calcolare l'intestazione, la tabella (convertita dalla griglia) e, infine, il piè di pagina

Esempio:

Intestazione (può essere codificato rigido)

 <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author>phpGrid.com</Author> <Created></Created> <LastSaved></LastSaved> <Version></Version> </DocumentProperties> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>768</WindowHeight> <WindowWidth>1024</WindowWidth> <WindowTopX>0</WindowTopX> <WindowTopY>0</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom" /> <Borders/> <Font ss:FontName="Arial" ss:Size="8" /> <Interior/> <NumberFormat /> <Protection /> </Style> <Style ss:ID="sHyperlink" ss:Name="Hyperlink"> <Font ss:Color="#0000FF" ss:Underline="Single" /> </Style> <Style ss:ID="sDate"> <NumberFormat ss:Format="Short Date"/> </Style> <Style ss:ID="sNumber"> <NumberFormat/> </Style> <Style ss:ID="sHeader"> <Font ss:Family="Arial" ss:Bold="1" /> </Style> <Style ss:ID="sDecimal"> <NumberFormat ss:Format="Fixed"/> </Style> </Styles><Worksheet ss:Name="Sheet1"> 

Corpo (questo è il tavolo convertito da datagrid. Devi essere generato dynamicmente)

 <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="328" x:FullColumns="1" x:FullRows="1"><Row><Cell ss:StyleID="sHeader"><Data ss:Type="String">Order No.</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">Order Date</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">Shipped Date</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">status</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">comments</Data></Cell><Cell ss:StyleID="sHeader"><Data ss:Type="String">Customer No.</Data></Cell></Row> <Row><Cell ss:StyleID="sNumber"><Data ss:Type="Number">0</Data></Cell><Cell><Data ss:Type="String">2010-08-20 00:00:00</Data></Cell><Cell><Data ss:Type="String"></Data></Cell><Cell><Data ss:Type="String">Open</Data></Cell><Cell><Data ss:Type="String">foo</Data></Cell><Cell ss:StyleID="sNumber"><Data ss:Type="Number">123456</Data></Cell></Row> <Row><Cell ss:StyleID="sNumber"><Data ss:Type="Number">10100</Data></Cell><Cell><Data ss:Type="String">2003-01-06 00:00:00</Data></Cell><Cell><Data ss:Type="String">2003-01-10 00:00:00</Data></Cell><Cell><Data ss:Type="String">Shipped</Data></Cell><Cell><Data ss:Type="String">foo</Data></Cell><Cell ss:StyleID="sNumber"><Data ss:Type="Number">363</Data></Cell></Row> <Row><Cell ss:StyleID="sNumber"><Data ss:Type="Number">10101</Data></Cell><Cell><Data ss:Type="String">2003-01-18 00:00:00</Data></Cell><Cell><Data ss:Type="String">2003-01-22 00:00:00</Data></Cell><Cell><Data ss:Type="String">Shipped</Data></Cell><Cell><Data ss:Type="String">Check on availability.</Data></Cell><Cell ss:StyleID="sNumber"><Data ss:Type="Number">128</Data></Cell></Row></Table> 

Piè di pagina (può essere codificato rigido)

 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Print> <ValidPrinterInfo /> <HorizontalResolution>800</HorizontalResolution> <VerticalResolution>0</VerticalResolution> </Print> <Selected /> <Panes> <Pane> <Number>3</Number> <ActiveRow>1</ActiveRow> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook> 

È ansible semplicemente codificare l'intestazione e il footer XML, solo il corpo verrà generato in modo dinamico. Se hai bisogno di allineamento e di altri stili richiesti. Provare nuovamente lo stesso metodo di "reverse" ingegnerizzazione e capire cosa deve aggiungere all'XML.

Un vantaggio che ho trovato in questo modo è che non c'è bisogno di fare affidamento su API perché letteralmente tutto può essere seppellito in una string. Funziona anche attraverso diversi linguaggi di programmazione. Può non essere più flessibile o potente come l'API, ma per datagrid, solo con tabella, righe e colonne è sufficiente. Alless per me.