winform DataTable导出为Excel

oo00gg99 2009-11-27 01:29:39

如题希望效率高点能设置保存路径

...全文

2203 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

快乐大法师 2011-04-16

打赏
举报

http://blog.sina.com.cn/s/blog_4e6dffee0100jktg.html

这个就行一点点遍历效率不是很快
要是想效率快就直接复制到内存中然后粘贴到excel

快乐大法师 2011-04-16

打赏
举报

public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
return;
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Workbook xlBook = xlApp.Workbooks.Add(true);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}
//xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
xlBook.SaveCopyAs(strFileName);
}

yaoxuefeng 2011-04-13

打赏
举报

看内容啊

CNBeing 2009-11-27

打赏
举报

做了这么久导出，性能和速度上觉得还是用报表比较好。楼主可以尝试下。大数据量下操作Excel是无法比拟。

qqiuzaihui 2009-11-27

打赏
举报

//======================================================================

//

//        Copyright : ...............

//        All rights reserved

//

//        Filename : HmExcelAssist

//        Description : Excel操作类, 将DataTable中的数据保存至Excel

//

//        created by 枫中玫瑰 at  2009-10-09 16:00:09

//

//======================================================================



using System;

using System.Text;

using System.Data;

using System.Threading;

using System.Windows.Forms;

using System.Collections.Generic;

using Excel = Microsoft.Office.Interop.Excel;



namespace HmDataPrinter

{

    /// <summary>

    /// Excel操作类

    /// </summary>

    public class HmExcelAssist

    {

        public static string saveFileName = string.Empty;           // 用于保存EXCEL文件的文档名

        public static DataTable dtDataSource = new DataTable();     // Excel数据源

        /// <summary>

        /// 实现DataGridView向DataTable的转换

        /// </summary>

        /// <param name="dvSource">DataGridView</param>

        public static void GridViewToTable(DataGridView dvSource)

        {

            dtDataSource = new DataTable();



            DataColumn col;                     // 设置列

            for (int i = 0; i < dvSource.Columns.Count; i++)

            {

                col = new DataColumn();

                col.ColumnName = dvSource.Columns[i].HeaderText;

                dtDataSource.Columns.Add(col);

            }



            DataRow dr;                         // 设置行

            for (int i = 0; i < dvSource.Rows.Count; i++)

            {

                dr = dtDataSource.NewRow();

                for (int j = 0; j < dvSource.Columns.Count; j++)

                {

                    dr[j] = 1 == j ? "'" + dvSource.Rows[i].Cells[j].Value.ToString() :

                        dvSource.Rows[i].Cells[j].Value.ToString();

                }

                dtDataSource.Rows.Add(dr);

            }

        }        

        /// <summary>

        /// 保存记录至Excel

        /// </summary>

        public static void SaveRecordToExcel()

        {

            saveFileName = string.Format("{0}{1}记录", DateTime.Now.Date.ToString("yyyyMMdd"),

                saveFileName);                                                  // 设置默认的保存文件名



            SaveFileDialog saveDialog = new SaveFileDialog();                   // 保存文件对话框

            saveDialog.DefaultExt = "xls";

            saveDialog.Filter = "Excel文件|*.xls";

            saveDialog.FileName = saveFileName;



            if (saveDialog.ShowDialog().Equals(DialogResult.Cancel))            // 单击了'取消'按钮,则返回

                return;



            saveFileName = saveDialog.FileName;                                 // 设置新的保存文件名

            Thread tdSaveRecord = new Thread(new ThreadStart(SaveRecord));      // 启用线程保存数据

            tdSaveRecord.Start();

        }



        #region Excel操作中私有的方法

        /// <summary>

        /// 读取信息并保存记录

        /// </summary>

        private static void SaveRecord()

        {

            string strMsg = string.Empty;                                       // 提示信息

            MessageBoxIcon msgIcon = MessageBoxIcon.Information;                // 设置消息框的图标



            if (dtDataSource.Rows.Count == 0)

            {

                MessageBox.Show("没有要保存的数据!", "操作提示", MessageBoxButtons.OK, msgIcon);

                return;

            }



            if (ExportExcel())                                                  // 保存数据至Excel文件中

            {

                strMsg = "记录导出完毕!";

            }

            else

            {

                strMsg = "记录导出出错,请重试!";

                msgIcon = MessageBoxIcon.Error;

            }

            MessageBox.Show(strMsg, "操作提示", MessageBoxButtons.OK, msgIcon);

        }

        /// <summary>

        /// 将 DataTable 数据保存至 Excel 文件中

        /// </summary>

        private static bool ExportExcel()

        {

            if (saveFileName.IndexOf(":") < 0) return false; //被点了取消



            Excel.Application xlApp = new Excel.Application();

            object missing = System.Reflection.Missing.Value;



            if (xlApp == null)

            {

                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");

                return false;

            }

            Excel.Workbooks workbooks = xlApp.Workbooks;

            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];    //取得sheet1

            Excel.Range range;



            string strCaption = saveFileName.Remove(0, saveFileName.LastIndexOf('\\') + 1);

            strCaption = strCaption.Remove(strCaption.Length - 4, 4);



            long totalCount = dtDataSource.Rows.Count;

            long rowRead = 0;

            float percent = 0;



            range = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1],

                worksheet.Cells[2, dtDataSource.Columns.Count]);                    //标题占用前两行

            range.Merge(missing);                                                   //合并

            range.Font.Bold = true;                                                 //粗体设置

            range.Font.Size = 16;                                                   //字体大小设置 

            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;              //水平对齐设置 

            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;                //垂直对齐设置

            //range.FormulaR1C1 = 公式;             //公式设置

            //range.ColumnWidth = 宽度;             //列宽设置

            //range.RowHeight = 行高;               //行高  

            worksheet.Cells[1, 1] = strCaption;



            //写入字段

            for (int i = 0; i < dtDataSource.Columns.Count; i++)

            {

                worksheet.Cells[4, i + 1] = dtDataSource.Columns[i].ColumnName;

                range = (Excel.Range)worksheet.Cells[4, i + 1];

                range.Interior.ColorIndex = 15;

                range.Font.Bold = true;

            }



            //写入数值

            for (int r = 0; r < dtDataSource.Rows.Count; r++)

            {

                for (int i = 0; i < dtDataSource.Columns.Count; i++)

                {

                    worksheet.Cells[r + 5, i + 1] = dtDataSource.Rows[r][i];

                }

                rowRead++;

                percent = ((float)(100 * rowRead)) / totalCount;

                Application.DoEvents();

            }

            worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);



            range = worksheet.get_Range(worksheet.Cells[4, 1],

                worksheet.Cells[dtDataSource.Rows.Count + 4, dtDataSource.Columns.Count]);

            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);



            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;



            if (dtDataSource.Columns.Count > 1)

            {

                range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            }

            workbook.Close(missing, missing, missing);

            xlApp.Quit();



            return true;

        }

        #endregion

    }

}

测试代码：

                    if (this.datagridMain.Rows.Count == 0)

                    {

                        MessageBox.Show("不存在要保存的数据, 请重试!\t\n", "操作提示",

                            MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

                        return;

                    }

                    HmExcelAssist.saveFileName = strFileName;

                    HmExcelAssist.GridViewToTable(this.datagridMain);       // 保存数据

                    HmExcelAssist.SaveRecordToExcel();

bdmh 2009-11-27

打赏
举报

另一种，逐行写入



        private void WriteExcel_Step(string filename)

        {

            Application excel = new ApplicationClass();

            _Workbook xBk;

            xBk = excel.Workbooks.Add(true);

            excel.Visible = true;

            progressBar1.Visible = true;

            label4.Visible = true;

            progressBar1.Maximum = dt.Rows.Count;

            progressBar1.Value = 0;

            progressBar1.Step = 1;

            for (int i=0;i<dt.Columns.Count;i++)

            {

                excel.Cells[1, i + 1] = dt.Columns[i].Caption;

            }

            int j = 2;

            string s= "";

            foreach (DataRow r in dt.Rows)

            {

                for (int i = 0; i < r.ItemArray.Length; i++)

                {

                    try

                    {

                        s = "";

                        if (dt.Columns[i].DataType.Name == "String")

                        {

                            s = "'";

                        }

                        if (dt.Columns[i].Caption == "交易日期")

                        {

                            excel.Cells[j, i + 1] = s + ConvertDate(r.ItemArray.GetValue(i).ToString());

                        }

                        else if (dt.Columns[i].Caption == "交易时间")

                        {

                            excel.Cells[j, i + 1] = s + ConvertTime(r.ItemArray.GetValue(i).ToString());

                        }

                        else

                            excel.Cells[j, i + 1] = s + r.ItemArray.GetValue(i).ToString();

                    }

                    catch

                    {

                        continue;

                    }

                }

                j++;

            }

            progressBar1.Value = 0;

            progressBar1.Visible = false;

            label4.Visible = false;

        }

十八道胡同 2009-11-27

打赏
举报

参考

using System;   

using System.Collections.Generic;   

using System.Text;   

using System.Data;   

using System.Data.OleDb;   

using System.Data.SqlClient;   

using System.IO;   

using Microsoft.Office.Interop.Excel;   

namespace TestAccess   

{   

    class Program   

    {   

        static void Main(string[] args)   

        {   

  

            string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";   

            strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";   

            strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";   

            OleDbConnection objConnection = new OleDbConnection(strConnection);   

            objConnection.Open();   

            OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);   

            DataSet ds = new DataSet();   

            myCommandd.Fill(ds, "[Sheet1$]");   

            System.Data.DataTable dt = ds.Tables["[Sheet1$]"];   

            Console.WriteLine(dt.Columns[0].ToString());   

            Console.WriteLine(dt.Columns[1].ToString());   

            DataRow drDisplay = dt.Rows[0];   

            int[] num = new int[dt.Columns.Count];   

            for (int j = 0; ; )   

            {   

                for (int i = 0; i < dt.Columns.Count; i++)   

                {   

  

                    if (drDisplay[i] is DBNull) ;   

                    else  

                        num[i] += Convert.ToInt32(drDisplay[i]);   

  

                }   

                if (++j >= dt.Rows.Count) break;   

                drDisplay = dt.Rows[j];   

            }   

            objConnection.Close();   

            object MissingValue = Type.Missing;   

            Microsoft.Office.Interop.Excel.Application app = new Application();   

            Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,   

MissingValue, MissingValue, MissingValue,   

MissingValue, MissingValue, MissingValue,   

MissingValue, MissingValue, MissingValue,   

MissingValue, MissingValue, MissingValue,   

MissingValue);   

            Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;   

            for (int i = 0; i < dt.Columns.Count; i++)   

            {   

                //注意下面是i+1,,excel小标默认从1开始   

                wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();   

            }   

  

            wbook.Save();   

            wbook.Close(true, null, null);   

            System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);   

            System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);   

            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);   

            wsheet = null;   

            wbook = null;   

            app = null;   

            GC.Collect();   

        }   

    }   

}  





本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/LCL_data/archive/2009/05/06/4154784.aspx

bdmh 2009-11-27

打赏
举报

这个方法是用excel直接根据sql读取数据的例子，需引入Microsoft.Office.Interop.Excel



        /// <summary>

        /// 写Excel文件

        /// </summary>

        /// <param name="filename"></param>

        private void WriteExcel(string filename,string sql)

        {

            Application excel;

            _Workbook xBk;

            _Worksheet xSt;

            _QueryTable xQt;

            excel = new ApplicationClass();

            if (excel == null)

            {

                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");

                return;

            } 

            xBk = excel.Workbooks.Add(true);

            xSt = (_Worksheet)xBk.ActiveSheet;

            string Conn = "ODBC;DRIVER=SQL Server;SERVER=" + host + ";UID=sa;PWD=58325245;DATABASE=CMSChina";

            xQt = xSt.QueryTables.Add(Conn, xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]), sql);

            xQt.Name = "导出示例";

            xQt.FieldNames = true;

            xQt.RowNumbers = false;

            xQt.FillAdjacentFormulas = false;

            xQt.PreserveFormatting = false;

            xQt.BackgroundQuery = true;

            xQt.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;

            xQt.AdjustColumnWidth = true;

            xQt.RefreshPeriod = 0;

            xQt.PreserveColumnInfo = true;

            xQt.Refresh(xQt.BackgroundQuery);

            excel.Visible = true;

        }

kk_timo 2009-11-27

打赏
举报

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Reflection;

namespace Console.ExcelExport
{
public class ExcelExport
{
public void printAll(System.Data.DataTable dt)
{

//导出到execl
try
{
//没有数据的话就不往下执行
if (dt.Rows.Count == 0)

//实例化一个Excel.Application对象
MessageBox.Show("当前没有数据！");

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

if (excel == null)
{
MessageBox.Show("无法创建Excel对象，可能未安装Excel");
return;
}

//新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
excel.Application.Workbooks.Add(true);

//让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
excel.Visible = false;
//生成Excel中列头名称
for (int i = 0; i < dt.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dt.Columns[i].ColumnName;//输出DataGridView列头名
}

//把DataGridView当前页的数据保存在Excel中
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完
{
for (int j = 0; j < dt.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完
{
string str = dt.Rows[i][j].ToString();
excel.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制
}
}
}
//设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;

//保存工作簿，值为false会报错
excel.Application.Workbooks.Add(true).Save();
//保存excel文件
excel.Save("D:" + "\\KKHMD.xls");

//确保Excel进程关闭
excel.Quit();
excel = null;

}
catch (Exception)
{
//KillProcess("EXCEL");//杀死进程EXCEL
//int generation = System.GC.GetGeneration(excel);
//excelApp = null;
System.GC.Collect();

//MessageBox.Show(ex.Message, "错误提示");

}
}
}
}

oo00gg99 2009-11-27