C#中datatabel导出excel(三种方法)

发布网友 发布时间：2023-01-05 16:16

我来回答

共1个回答

热心网友 时间：2023-10-16 04:50

　　方法一 (拷贝直接可以使用 适合大批量资料 上万笔)Microsoft Office Interop Excel Application appexcel = new Microsoft Office Interop Excel Application();SaveFileDialog savefiledialog = new SaveFileDialog();System Reflection Missing miss = System Reflection Missing Value;appexcel = new Microsoft Office Interop Excel Application();Microsoft Office Interop Excel Workbook workbookdata;Microsoft Office Interop Excel Worksheet worksheetdata;Microsoft Office Interop Excel Range rangedata;//设置对象不可见appexcel Visible = false;System Globalization CultureInfo currentci = System Threading Thread CurrentThread CurrentCulture;System Threading Thread CurrentThread CurrentCulture = new System Globalization CultureInfo( en us );workbookdata = appexcel Workbooks Add(miss);worksheetdata = (Microsoft Office Interop Excel Worksheet)workbookdata Worksheets Add(miss miss miss miss);//给工作表赋名称worksheetdata Name = saved ;for (int i = ; i < dt Columns Count; i++){    worksheetdata Cells[ i + ] = dt Columns[i] ColumnName ToString();}//因为第一行已经写了表头 所以所有数据都应该从a 开始rangedata = worksheetdata get_Range( a miss);Microsoft Office Interop Excel Range xlrang = null;//irowcount为实际行数 最大行int irowcount = dt Rows Count;int iparstedrow = icurrsize = ;//ieachsize为每次写行的数值 可以自己设置int ieachsize = ;//icolumnaccount为实际列数 最大列数int icolumnaccount = dt Columns Count;//在内存中声明一个ieachsize×icolumnaccount的数组 ieachsize是每次最大存储的行数 icolumnaccount就是存储的实际列数object[ ] objval = new object[ieachsize icolumnaccount];icurrsize = ieachsize;

　　while (iparstedrow < irowcount){    if ((irowcount iparstedrow) < ieachsize)        icurrsize = irowcount iparstedrow;    //用for循环给数组赋值    for (int i = ; i < icurrsize; i++)    {        for (int j = ; j < icolumnaccount; j++)            objval[i j] = dt Rows[i + iparstedrow][j] ToString();        System Windows Forms Application DoEvents();    }    string X = A + ((int)(iparstedrow + )) ToString();    string col = ;    if (icolumnaccount <= )    {        col = ((char)( A + icolumnaccount )) ToString() + ((int)(iparstedrow + icurrsize + )) ToString();    }    else    {        col = ((char)( A + (icolumnaccount / ))) ToString() + ((char)( A + (icolumnaccount % ))) ToString() + ((int)(iparstedrow + icurrsize + )) ToString();    }    xlrang = worksheetdata get_Range(X col);    // 调用range的value 属性 把内存中的值赋给excel    xlrang Value = objval;    iparstedrow = iparstedrow + icurrsize;}//保存工作表System Runtime InteropServices Marshal ReleaseComObject(xlrang);xlrang = null;//调用方法关闭excel进程appexcel Visible = true;

　　方法二 (自己建函数 适合大批量资料 上万笔)using System IO;private void dataTableToCsv(DataTable table string file){    string title = ;    FileStream fs = new FileStream(file FileMode OpenOrCreate);    //FileStream fs = File Open(file FileMode Open FileAccess Read);    StreamWriter sw = new StreamWriter(new BufferedStream(fs) System Text Encoding Default);    for (int i = ; i < table Columns Count; i++)    {        title += table Columns[i] ColumnName + \t ; //栏位 自动跳到下一单元格    }    title = title Substring( title Length ) + \n ;    sw Write(title);    foreach (DataRow row in table Rows)    {        string line = ;        for (int i = ; i < table Columns Count; i++)        {            line += row[i] ToString() Trim() + \t ; //内容 自动跳到下一单元格        }        line = line Substring( line Length ) + \n ;        sw Write(line);    }    sw Close();    fs Close();}dataTableToCsv(dt @ c:\ xls ); //调用函数System Diagnostics Process Start(@ c:\ xls );  //打开excel文件