wake-up-neo.com

Exportieren Sie SQL-Abfragedaten nach Excel

Ich habe eine Abfrage, die einen sehr großen Datensatz zurückgibt. Ich kann es nicht kopieren und in Excel einfügen, was ich normalerweise mache. Ich habe einige Nachforschungen darüber angestellt, wie direkt in eine Excel-Tabelle exportiert werden kann. Ich verwende SQL SERVER 2008 auf einem Server, auf dem Microsoft Server 2003 ausgeführt wird. Ich versuche, den Datenprovider Microsoft.Jet.OLEDB.4.0 und Excel 2007 zu verwenden. Ich habe ein kleines Stück Code zusammengefügt, das wie folgt aussieht hab in beispielen gesehen.

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Working\Book1.xlsx;Extended Properties=Excel 12.0;HDR=YES')
SELECT productid, price FROM dbo.product

Dies funktioniert jedoch nicht, ich erhalte eine Fehlermeldung 

Msgstr "Falsche Syntax in der Nähe des Schlüsselworts 'SELECT'".

Hat jemand eine Idee, wie man dies oder möglicherweise einen besseren Ansatz macht?

23
JBone

Ich weiß nicht, ob Sie danach suchen, aber Sie können die Ergebnisse wie folgt nach Excel exportieren:

Klicken Sie im Ergebnisbereich auf die Zelle oben links, um alle Datensätze zu markieren, klicken Sie mit der rechten Maustaste auf die Zelle oben links und klicken Sie auf "Ergebnisse speichern unter". Eine der Exportoptionen ist CSV.

Sie könnten dies auch versuchen:

INSERT INTO OPENROWSET 
   ('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product')

Schließlich können Sie den Datenexport mithilfe von SSIS (ersetztes DTS) prüfen. Hier ist ein Link zu einem Tutorial:

http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm

45
James Johnson

Wenn Sie nur nach Excel exportieren möchten, können Sie den Assistenten zum Exportieren von Daten verwenden. Klicken Sie mit der rechten Maustaste auf die Datenbank, Aufgaben-> Daten exportieren. 

15
brian

Ich hatte ein ähnliches Problem, aber mit einem gewissen Unterschied - die oben aufgeführten Lösungen funktionierten, wenn das Resultset aus einer Abfrage stammte. In meiner Situation hatte ich jedoch mehrere individuelle Auswahlabfragen, deren Ergebnisse nach Excel exportiert werden mussten. Im Folgenden soll nur ein Beispiel veranschaulicht werden, obwohl ich eine name in-Klausel verwenden könnte ...

select a,b from Table_A where name = 'x'
select a,b from Table_A where name = 'y'
select a,b from Table_A where name = 'z'

Der Assistent ließ mich das Ergebnis aus einer Abfrage nach Excel exportieren, in diesem Fall jedoch nicht alle Ergebnisse aus verschiedenen Abfragen. 

Bei meiner Recherche stellte ich fest, dass wir die Ergebnisse für das Raster deaktivieren und die Ergebnisse für Text aktivieren können. Drücken Sie also Strg + T und führen Sie dann alle Anweisungen aus. Dies sollte die Ergebnisse als Textdatei im Ausgabefenster anzeigen. Sie können den Text in ein durch Tabulatoren getrenntes Format umwandeln, damit Sie ihn in Excel importieren können. 

Sie können auch die Tastenkombination Strg + Umschalt + F drücken, um die Ergebnisse in eine Datei zu exportieren. Sie wird als .rpt-Datei exportiert, die mit einem Texteditor geöffnet und für den Excel-Import bearbeitet werden kann.

Ich hoffe, das hilft allen anderen, die ein ähnliches Problem haben.

1
Prashanth

Für jeden, der hierher kommt und nach sucht, wie ich dies in C # tun kann, habe ich die folgende Methode ausprobiert und hatte Erfolg in dotnet core 2.0.3 und entity framework core 2.0.3

Erstellen Sie zuerst Ihre Modellklasse.

public class User
{  
    public string Name { get; set; }  
    public int Address { get; set; }  
    public int Zip { get; set; }  
    public string Gender { get; set; }  
} 

Dann installieren Sie das EPPlus Nuget-Paket . (Ich habe Version 4.0.5 verwendet, wird wahrscheinlich auch für andere Versionen funktionieren.)

Install-Package EPPlus -Version 4.0.5

Die create ExcelExportHelper-Klasse, die die Logik zum Konvertieren der Datenmenge in Excel-Zeilen enthält. Diese Klasse hat keine Abhängigkeit von mit Ihrer Modellklasse oder Ihrem Datenbestand. 

public class ExcelExportHelper
    {
        public static string ExcelContentType
        {
            get
            { 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();

            for (int i = 0; i < 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)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = properties[i].GetValue(item);
                }

                dataTable.Rows.Add(values);
            }
            return dataTable;
        }

        public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
        {

            byte[] result = null;
            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 the content into the Excel file  
                workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);

                // autofit width of cells with small content  
                int columnIndex = 1;
                foreach (DataColumn column in dataTable.Columns)
                {
                    int maxLength;
                    ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
                    try
                    {
                        maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
                    }
                    catch (Exception) //nishanc
                    {
                        maxLength = columnCells.Max(cell => (cell.Value +"").ToString().Length);
                    }

                    //workSheet.Column(columnIndex).AutoFit();
                    if (maxLength < 150)
                    {
                        //workSheet.Column(columnIndex).AutoFit();
                    }


                    columnIndex++;
                }

                // 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(Color.Brown);
                }

                // 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  
                for (int i = dataTable.Columns.Count - 1; i >= 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 = 10;
                }

                result = package.GetAsByteArray();
            }

            return result;
        }

        public static byte[] ExportExcel<T>(List<T> data, string Heading = "", bool showSlno = false, params string[] ColumnsToTake)
        {
            return ExportExcel(ListToDataTable<T>(data), Heading, showSlno, ColumnsToTake);
        }
    }

Fügen Sie nun diese Methode dort hinzu, wo Sie die Excel-Datei generieren möchten, wahrscheinlich für eine Methode im Controller. Sie können auch Parameter für Ihre gespeicherte Prozedur übergeben. Beachten Sie, dass der Rückgabetyp der Methode FileContentResult ist. Unabhängig von der Abfrage, die Sie ausführen, ist es wichtig, dass Sie die Ergebnisse in einer List angeben müssen.

[HttpPost]
public async Task<FileContentResult> Create([Bind("Id,StartDate,EndDate")] GetReport getReport)
{
    DateTime startDate = getReport.StartDate;
    DateTime endDate = getReport.EndDate;

    // call the stored procedure and store dataset in a List.
    List<User> users = _context.Reports.FromSql("exec dbo.SP_GetEmpReport @start={0}, @end={1}", startDate, endDate).ToList();
    //set custome column names
    string[] columns = { "Name", "Address", "Zip", "Gender"};
    byte[] filecontent = ExcelExportHelper.ExportExcel(users, "Users", true, columns);
    // set file name.
    return File(filecontent, ExcelExportHelper.ExcelContentType, "Report.xlsx"); 
}

Weitere Details finden Sie hier

0