Cara menggunakan convert vnd to excel

Saya memiliki DataTable dengan 30+ kolom dan 6500+ baris. Saya perlu membuang seluruh nilai DataTable ke dalam file Excel. Adakah yang bisa membantu dengan kode C #. Saya perlu setiap nilai kolom berada di dalam sel. Lebih tepatnya, Saya membutuhkan salinan DataTable yang tampak persis dalam File Excel. Tolong bantu.

Terima kasih, Vix

gunakan kode ini ...

    dt = city.GetAllCity();//your datatable
    string attachment = "attachment; filename=city.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/vnd.ms-Excel";
    string tab = "";
    foreach (DataColumn dc in dt.Columns)
    {
        Response.Write(tab + dc.ColumnName);
        tab = "\t";
    }
    Response.Write("\n");
    int i;
    foreach (DataRow dr in dt.Rows)
    {
        tab = "";
        for (i = 0; i < dt.Columns.Count; i++)
        {
            Response.Write(tab + dr[i].ToString());
            tab = "\t";
        }
        Response.Write("\n");
    }
    Response.End();

Cuplikan ini bisa lebih cepat diterapkan:

// Example data
DataTable table = new DataTable();
table.Columns.AddRange(new[]{ new DataColumn("Key"), new DataColumn("Value") });
foreach (string name in Request.ServerVariables)
    table.Rows.Add(name, Request.ServerVariables[name]);

// This actually makes your HTML output to be downloaded as .xls file
Response.Clear();
Response.ClearContent();
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xls");

// Create a dynamic control, populate and render it
GridView Excel = new GridView();
Excel.DataSource = table;
Excel.DataBind();
Excel.RenderControl(new HtmlTextWriter(Response.Output));

Response.Flush();
Response.End();

Tautan di bawah ini digunakan untuk mengekspor dataat ke Excel dalam C # Code.

http://royalarun.blogspot.in/2012/01/export-datatable-to-Excel-in-c-windows.html

  using System;      
   using System.Data;  
   using System.IO;  
   using System.Windows.Forms;  

    namespace ExportExcel  
    {      
        public partial class ExportDatatabletoExcel : Form  
        {  
            public ExportDatatabletoExcel()  
            {  
                InitializeComponent();  
            }  

            private void Form1_Load(object sender, EventArgs e)
            {

                DataTable dt = new DataTable();

                //Add Datacolumn
                DataColumn workCol = dt.Columns.Add("FirstName", typeof(String));

                dt.Columns.Add("LastName", typeof(String));
                dt.Columns.Add("Blog", typeof(String));
                dt.Columns.Add("City", typeof(String));
                dt.Columns.Add("Country", typeof(String));

                //Add in the datarow
                DataRow newRow = dt.NewRow();

                newRow["firstname"] = "Arun";
                newRow["lastname"] = "Prakash";
                newRow["Blog"] = "http://royalarun.blogspot.com/";
                newRow["city"] = "Coimbatore";
                newRow["country"] = "India";

                dt.Rows.Add(newRow);

                //open file
                StreamWriter wr = new StreamWriter(@"D:\\Book1.xls");

                try
                {

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
                    }

                    wr.WriteLine();

                    //write rows to Excel file
                    for (int i = 0; i < (dt.Rows.Count); i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (dt.Rows[i][j] != null)
                            {
                                wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
                            }
                            else
                            {
                                wr.Write("\t");
                            }
                        }
                        //go to next line
                        wr.WriteLine();
                    }
                    //close file
                    wr.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
    }

Saya menggunakan ini di halaman.`

 public void DTToExcel(DataTable dt)
{
    // dosya isimleri ileride aynı anda birden fazla kullanıcı aynı dosya üzerinde işlem yapmak ister düşüncesiyle guid yapıldı. 
    string FileName = Guid.NewGuid().ToString();

    FileInfo f = new FileInfo(Server.MapPath("Downloads") + string.Format("\\{0}.xlsx", FileName));
    if (f.Exists)
        f.Delete(); // delete the file if it already exist.

    HttpResponse response = HttpContext.Current.Response;
    response.Clear();
    response.ClearHeaders();
    response.ClearContent();
    response.Charset = Encoding.UTF8.WebName;
    response.AddHeader("content-disposition", "attachment; filename=" + FileName + ".xls");
    response.AddHeader("Content-Type", "application/Excel");
    response.ContentType = "application/vnd.xlsx";
    //response.AddHeader("Content-Length", file.Length.ToString());


    // create a string writer
    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter htw = new HtmlTextWriter(sw)) //datatable'a aldığımız sorguyu bir datagrid'e atayıp html'e çevir.
        {
            // instantiate a datagrid
            DataGrid dg = new DataGrid();
            dg.DataSource = dt;
            dg.DataBind();
            dg.RenderControl(htw);
            response.Write(sw.ToString());
            dg.Dispose();
            dt.Dispose();
            response.End();
        }
    }
}

Jika Anda ingin mengekspor dataat ke Excel dengan teks header yang diformat, coba seperti ini.

public void ExportFullDetails()
    {
        Int16 id = Convert.ToInt16(Session["id"]);
        DataTable registeredpeople = new DataTable();
        registeredpeople = this.dataAccess.ExportDetails(eventid);

        string attachment = "attachment; filename=Details.xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/vnd.ms-Excel";
        string tab = "";


        registeredpeople.Columns["Reg_id"].ColumnName = "Reg. ID";
        registeredpeople.Columns["Name"].ColumnName = "Name";
        registeredpeople.Columns["Reg_country"].ColumnName = "Country";
        registeredpeople.Columns["Reg_city"].ColumnName = "City";
        registeredpeople.Columns["Reg_email"].ColumnName = "Email";
        registeredpeople.Columns["Reg_business_phone"].ColumnName = "Business Phone";
        registeredpeople.Columns["Reg_mobile"].ColumnName = "Mobile";
        registeredpeople.Columns["PositionRole"].ColumnName = "Position";
        registeredpeople.Columns["Reg_work_type"].ColumnName = "Work Type";

        foreach (DataColumn dc in registeredpeople.Columns)
        {
            Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }

        Response.Write("\n");
        int i;
        foreach (DataRow dr in registeredpeople.Rows)
        {
            tab = "";
            for (i = 0; i < registeredpeople.Columns.Count; i++)
            {
                Response.Write(tab + dr[i].ToString());
                tab = "\t";
            }
            Response.Write("\n");
        }
        Response.End();

    }

Meskipun bukan implementasi .NET, Anda mungkin menemukan bahwa plug-in TableTools mungkin sangat efektif tergantung pada audiens Anda. Itu bergantung pada flash yang seharusnya tidak menjadi masalah bagi kebanyakan kasus yang membutuhkan untuk benar-benar bekerja secara mendalam dan kemudian ingin merekam informasi tabular.

Versi terbaru muncul untuk mendukung penyalinan ke clipboard, ke CSV, ".XLS" (benar-benar hanya file yang dibatasi-tab dengan nama .xls), ke PDF, atau membuat versi halaman ramah-printer dengan semua baris ditampilkan dan sisanya dari konten halaman Anda disembunyikan.

Saya menemukan ekstensi di situs DataTables di sini: http://datatables.net/extras/tabletools/

Unduhan tersedia di halaman plug-in (ekstra) di sini: http://datatables.net/extras/

Seharusnya itu diunduh sebagai bagian dari DataTables (karenanya frasa "Ekstra termasuk dalam paket DataTables") tetapi saya tidak menemukannya dalam unduhan yang telah saya gunakan. Tampaknya bekerja dengan sangat baik!

Saya telah melakukan konversi DataTable ke Excel dengan kode berikut. Semoga ini sangat mudah, tidak perlu mengubah lagi cukup salin & hama kode ganti variabel Anda dengan variabel Anda, dan itu akan berfungsi dengan baik.

Pertama buat folder di solusi Anda Document , dan buat file Excel MyTemplate.xlsx . Anda dapat mengubah nama itu sesuai dengan kebutuhan Anda. Tetapi ingat untuk itu Anda harus mengubah nama dalam kode juga.

Temukan kode berikut ...

    protected void GetExcel_Click(object sender, EventArgs e)
    {            

        ManageTicketBS objManageTicket = new ManageTicketBS();
        DataTable DT = objManageTicket.GetAlldataByDate();   //this function will bring the data in DataTable format, you can use your function instate of that.  

        string DownloadFileName;
        string FolderPath;
        string FileName = "MyTemplate.xlsx";
        DownloadFileName = Path.GetFileNameWithoutExtension(FileName) + new Random().Next(10000, 99999) + Path.GetExtension(FileName);
        FolderPath = ".\\" + DownloadFileName;

        GetParents(Server.MapPath("~/Document/" + FileName), Server.MapPath("~/Document/" + DownloadFileName), DT);

        string path = Server.MapPath("~/Document/" + FolderPath);
        FileInfo file = new FileInfo(path);
        if (file.Exists)
        {
            try
            {
                HttpResponse response = HttpContext.Current.Response;
                response.Clear();
                response.ClearContent();
                response.ClearHeaders();
                response.Buffer = true;
                response.ContentType = MimeType(Path.GetExtension(FolderPath));
                response.AddHeader("Content-Disposition", "attachment;filename=" + DownloadFileName);
                byte[] data = File.ReadAllBytes(path);
                response.BinaryWrite(data);
                HttpContext.Current.ApplicationInstance.CompleteRequest();
                response.End();
            }

            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                DeleteOrganisationtoSupplierTemplate(path);
            }
        }
    }
    public string GetParents(string FilePath, string TempFilePath, DataTable DTTBL)
    {
        File.Copy(Path.Combine(FilePath), Path.Combine(TempFilePath), true);
        FileInfo file = new FileInfo(TempFilePath);
        try
        {
            DatatableToExcel(DTTBL, TempFilePath, 0);

            return TempFilePath;

        }

        catch (Exception ex)
        {                
            return "";
        }

    }


    public static string MimeType(string Extension)
    {
        string mime = "application/octetstream";
        if (string.IsNullOrEmpty(Extension))
            return mime;
        string ext = Extension.ToLower();
        Microsoft.Win32.RegistryKey rk = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(ext);
        if (rk != null && rk.GetValue("Content Type") != null)
            mime = rk.GetValue("Content Type").ToString();
        return mime;
    }


    static bool DeleteOrganisationtoSupplierTemplate(string filePath)
    {
        try
        {                
            File.Delete(filePath);
            return true;
        }
        catch (IOException)
        {               
            return false;
        }
    }


    public void DatatableToExcel(DataTable dtable, string pFilePath, int excelSheetIndex=1)
    {

        try
        {
            if (dtable != null && dtable.Rows.Count > 0)
            {
                IWorkbook workbook = null;
                ISheet worksheet = null;

                using (FileStream stream = new FileStream(pFilePath, FileMode.Open, FileAccess.ReadWrite))
                {

                    workbook = WorkbookFactory.Create(stream);
                    worksheet = workbook.GetSheetAt(excelSheetIndex);

                    int iRow = 1;



                    foreach (DataRow row in dtable.Rows)
                    {
                        IRow file = worksheet.CreateRow(iRow);
                        int iCol = 0;
                        foreach (DataColumn column in dtable.Columns)
                        {
                            ICell cell = null;
                            object cellValue = row[iCol];

                            switch (column.DataType.ToString())
                            {
                                case "System.Boolean":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = file.CreateCell(iCol, CellType.Boolean);

                                        if (Convert.ToBoolean(cellValue)) { cell.SetCellFormula("TRUE()"); }
                                        else { cell.SetCellFormula("FALSE()"); }

                                        //cell.CellStyle = _boolCellStyle;
                                    }
                                    break;

                                case "System.String":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = file.CreateCell(iCol, CellType.String);
                                        cell.SetCellValue(Convert.ToString(cellValue));
                                    }
                                    break;

                                case "System.Int32":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = file.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToInt32(cellValue));
                                        //cell.CellStyle = _intCellStyle;
                                    }
                                    break;
                                case "System.Int64":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = file.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToInt64(cellValue));
                                        //cell.CellStyle = _intCellStyle;
                                    }
                                    break;
                                case "System.Decimal":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = file.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToDouble(cellValue));
                                        //cell.CellStyle = _doubleCellStyle;
                                    }
                                    break;
                                case "System.Double":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = file.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToDouble(cellValue));
                                        //cell.CellStyle = _doubleCellStyle;
                                    }
                                    break;

                                case "System.DateTime":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = file.CreateCell(iCol, CellType.String);
                                        DateTime dateTime = Convert.ToDateTime(cellValue);
                                        cell.SetCellValue(dateTime.ToString("dd/MM/yyyy"));

                                        DateTime cDate = Convert.ToDateTime(cellValue);
                                        if (cDate != null && cDate.Hour > 0)
                                        {
                                            //cell.CellStyle = _dateTimeCellStyle; 
                                        }
                                        else
                                        {
                                            // cell.CellStyle = _dateCellStyle; 
                                        }
                                    }
                                    break;
                                default:
                                    break;
                            }
                            iCol++;
                        }
                        iRow++;
                    }
                    using (var WritetoExcelfile = new FileStream(pFilePath, FileMode.Create, FileAccess.ReadWrite))
                    {
                        workbook.Write(WritetoExcelfile);
                        WritetoExcelfile.Close();
                        //workbook.Write(stream);
                        stream.Close();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Kode ini hanya perlu Anda salin & hama dalam skrip Anda dan tambahkan Namespace sebagai berikut, Juga ubah nama file Excel seperti yang dibahas sebelumnya.

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;

Sebagian besar jawaban sebenarnya menghasilkan CSV yang saya tidak selalu memiliki pengalaman yang baik, ketika dibuka di Excel.

Salah satu cara melakukannya adalah dengan ACE OLEDB Provider (lihat juga string koneksi untuk Excel ). Tentu saja Anda harus menginstal penyedia dan terdaftar. Anda benar-benar memilikinya, jika Anda telah menginstal Excel, tetapi ini adalah sesuatu yang harus Anda pertimbangkan ketika menggunakan (mis. Pada server web). 

Di bawah ini kode kelas pembantu Anda harus memanggil sesuatu seperti ExportHelper.CreateXlsFromDataTable(dataset.Tables[0], @"C:\tmp\export.xls");

public class ExportHelper
{
    private const string ExcelOleDbConnectionStringTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";";

    /// <summary>
    /// Creates the Excel file from items in DataTable and writes them to specified output file.
    /// </summary>
    public static void CreateXlsFromDataTable(DataTable dataTable, string fullFilePath)
    {
        string createTableWithHeaderScript = GenerateCreateTableCommand(dataTable);

        using (var conn = new OleDbConnection(String.Format(ExcelOleDbConnectionStringTemplate, fullFilePath)))
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            OleDbCommand cmd = new OleDbCommand(createTableWithHeaderScript, conn);
            cmd.ExecuteNonQuery();

            foreach (DataRow dataExportRow in dataTable.Rows)
            {
                AddNewRow(conn, dataExportRow);
            }
        }
    }

    private static void AddNewRow(OleDbConnection conn, DataRow dataRow)
    {
        string insertCmd = GenerateInsertRowCommand(dataRow);

        using (OleDbCommand cmd = new OleDbCommand(insertCmd, conn))
        {
            AddParametersWithValue(cmd, dataRow);
            cmd.ExecuteNonQuery();
        }
    }

    /// <summary>
    /// Generates the insert row command.
    /// </summary>
    private static string GenerateInsertRowCommand(DataRow dataRow)
    {
        var stringBuilder = new StringBuilder();
        var columns = dataRow.Table.Columns.Cast<DataColumn>().ToList();
        var columnNamesCommaSeparated = string.Join(",", columns.Select(x => x.Caption));
        var questionmarkCommaSeparated = string.Join(",", columns.Select(x => "?"));

        stringBuilder.AppendFormat("INSERT INTO [{0}] (", dataRow.Table.TableName);
        stringBuilder.Append(columnNamesCommaSeparated);
        stringBuilder.Append(") VALUES(");
        stringBuilder.Append(questionmarkCommaSeparated);
        stringBuilder.Append(")");
        return stringBuilder.ToString();
    }

    /// <summary>
    /// Adds the parameters with value.
    /// </summary>
    private static void AddParametersWithValue(OleDbCommand cmd, DataRow dataRow)
    {
        var paramNumber = 1;

        for (int i = 0; i <= dataRow.Table.Columns.Count - 1; i++)
        {
            if (!ReferenceEquals(dataRow.Table.Columns[i].DataType, typeof(int)) && !ReferenceEquals(dataRow.Table.Columns[i].DataType, typeof(decimal)))
            {
                cmd.Parameters.AddWithValue("@p" + paramNumber, dataRow[i].ToString().Replace("'", "''"));
            }
            else
            {
                object value = GetParameterValue(dataRow[i]);
                OleDbParameter parameter = cmd.Parameters.AddWithValue("@p" + paramNumber, value);
                if (value is decimal)
                {
                    parameter.OleDbType = OleDbType.Currency;
                }
            }

            paramNumber = paramNumber + 1;
        }
    }

    /// <summary>
    /// Gets the formatted value for the OleDbParameter.
    /// </summary>
    private static object GetParameterValue(object value)
    {
        if (value is string)
        {
            return value.ToString().Replace("'", "''");
        }
        return value;
    }

    private static string GenerateCreateTableCommand(DataTable tableDefination)
    {
        StringBuilder stringBuilder = new StringBuilder();
        bool firstcol = true;

        stringBuilder.AppendFormat("CREATE TABLE [{0}] (", tableDefination.TableName);

        foreach (DataColumn tableColumn in tableDefination.Columns)
        {
            if (!firstcol)
            {
                stringBuilder.Append(", ");
            }
            firstcol = false;

            string columnDataType = "CHAR(255)";

            switch (tableColumn.DataType.Name)
            {
                case "String":
                    columnDataType = "CHAR(255)";
                    break;
                case "Int32":
                    columnDataType = "INTEGER";
                    break;
                case "Decimal":
                    // Use currency instead of decimal because of bug described at 
                    // http://social.msdn.Microsoft.com/Forums/vstudio/en-US/5d6248a5-ef00-4f46-be9d-853207656bcc/localization-trouble-with-oledbparameter-and-decimal?forum=csharpgeneral
                    columnDataType = "CURRENCY";
                    break;
            }

            stringBuilder.AppendFormat("{0} {1}", tableColumn.ColumnName, columnDataType);
        }
        stringBuilder.Append(")");

        return stringBuilder.ToString();
    }
}

Silakan coba ini, ini akan mengekspor data tabel data Anda lebih cepat ke Excel. 

Catatan: Rentang "FW", yang saya punya kode keras karena saya memiliki 179 kolom.

public void UpdateExcelApplication(SqlDataTable dataTable)
    {
        var objects = new string[dataTable.Rows.Count, dataTable.Columns.Count];

        var rowIndex = 0;

        foreach (DataRow row in dataTable.Rows)
        {
            var colIndex = 0;

            foreach (DataColumn column in dataTable.Columns)
            {
                objects[rowIndex, colIndex++] = Convert.ToString(row[column]);
            }

            rowIndex++;
        }

        var range = this.workSheet.Range[$"A3:FW{dataTable.Rows.Count + 2}"];
        range.Value = objects;

        this.workSheet.Columns.AutoFit();
        this.workSheet.Rows.AutoFit();
    }

Kode kerja untuk Ekspor Excel

 try
        {
            DataTable dt = DS.Tables[0];
            string attachment = "attachment; filename=log.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/vnd.ms-Excel";
            string tab = "";
            foreach (DataColumn dc in dt.Columns)
            {
                Response.Write(tab + dc.ColumnName);
                tab = "\t";
            }
            Response.Write("\n");
            int i;
            foreach (DataRow dr in dt.Rows)
            {
                tab = "";
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    Response.Write(tab + dr[i].ToString());
                    tab = "\t";
                }
                Response.Write("\n");
            }
            Response.End();
        }
        catch (Exception Ex)
        { }