C# DataTable导出Excel

╰+哭是因爲堅強的太久メ 2022-08-27 12:58 161阅读 0赞

## [C\#中DataTabel导出excel(三种方法)][C_DataTabel_excel] ##

(拷贝直接可以使用，适合大批量资料, 上万笔)

/// <summary>  
        /// 导出Excel  
        /// </summary>  
        /// <param name="dt"></param>  
        public void SaveExcel(System.Data.DataTable dt)  
        \{  
              
            Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();

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;

//设置对象不可见

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 = 0; i < dt.Columns.Count; i++)  
            \{

worksheetdata.Cells\[1, i + 1\] = dt.Columns\[i\].ColumnName.ToString();

//因为第一行已经写了表头，所以所有数据都应该从a2开始

rangedata = worksheetdata.get\_Range("a2", miss);

Microsoft.Office.Interop.Excel.Range xlrang = null;

//irowcount为实际行数，最大行

int irowcount = dt.Rows.Count;

int iparstedrow = 0, icurrsize = 0;

//ieachsize为每次写行的数值，可以自己设置

int ieachsize = 1000;

//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 = 0; i < icurrsize; i++)  
                \{  
                    for (int j = 0; j < icolumnaccount; j++)  
                        objval\[i, j\] = dt.Rows\[i + iparstedrow\]\[j\].ToString();  
                \}

string X = "A" + ((int)(iparstedrow + 2)).ToString();

string col = "";

if (icolumnaccount <= 26)  
                \{

col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();  
                \}  
                else  
                \{  
                    col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();  
                \}

xlrang = worksheetdata.get\_Range(X, col);

// 调用range的value2属性，把内存中的值赋给excel

xlrang.Value2 = objval;

iparstedrow = iparstedrow + icurrsize;

\}  
            appexcel.DisplayAlerts = true;  
            appexcel.AlertBeforeOverwriting = true;  
            //保存工作表  
              
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);  
             
            xlrang = null;  
            //调用方法关闭excel进程  
        \}

方法二：(自己建函数，适合大批量资料, 上万笔)

using System.IO;

private void dataTableToCsv(DataTable table, string file)

string title = "";

FileStream fs = new FileStream(file, FileMode.OpenOrCreate);

//FileStream fs1 = File.Open(file, FileMode.Open, FileAccess.Read);

StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);

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

title += table.Columns\[i\].ColumnName + "\\t"; //栏位：自动跳到下一单元格

title = title.Substring(0, title.Length - 1) + "\\n";

sw.Write(title);

foreach (DataRow row in table.Rows)

string line = "";

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

line += row\[i\].ToString().Trim() + "\\t"; //内容：自动跳到下一单元格

line = line.Substring(0, line.Length - 1) + "\\n";

sw.Write(line);

sw.Close();

fs.Close();

dataTableToCsv(dt, @"c:\\1.xls"); //调用函数

System.Diagnostics.Process.Start(@"c:\\1.xls");  //打开excel文件

方法三：适合小批量数据

public bool DoExport(System.Data.DataTable dt)  
        \{  
            Application app = new ApplicationClass();  
            if (app == null)  
            \{  
                throw new Exception("Excel无法启动");  
            \}  
            app.Visible = true;   
            Workbooks wbs = app.Workbooks;   
            Workbook wb = wbs.Add(Missing.Value);   
            Worksheet ws = (Worksheet)wb.Worksheets\[1\];   
            int cnt = dt.Rows.Count;   
            int columncnt = dt.Columns.Count;  
            // \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*获取数据\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
            object\[,\] objData = new Object\[cnt + 1, columncnt\];  // 创建缓存数据

// 获取列标题  
            for (int i = 0; i < columncnt; i++)  
            \{  
                objData\[0, i\] = dt.Columns\[i\].ColumnName;  
            \}  
            // 获取具体数据  
            for (int i = 0; i < cnt; i++)  
            \{  
                System.Data.DataRow dr = dt.Rows\[i\];  
                for (int j = 0; j < columncnt; j++)  
                \{ objData\[i + 1, j\] = dr\[j\]; \}  
            \}

//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 写入Excel\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
            Range r = ws.get\_Range(app.Cells\[1, 1\], app.Cells\[cnt + 1, columncnt\]);  
            r.NumberFormat = "@";  
            //r = r.get\_Resize(cnt+1, columncnt);  
            r.Value2 = objData;  
            r.EntireColumn.AutoFit();  
            app = null;

return true;  
        \}

///方法四：使用IO写入Excel

private void dataTableToCsv(System.Data.DataTable table, string file)  
        \{  
            string title = "";

FileStream fs = new FileStream(file, FileMode.OpenOrCreate);

//FileStream fs1 = File.Open(file, FileMode.Open, FileAccess.Read);

StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);

for (int i = 0; i < table.Columns.Count; i++)  
            \{

title += table.Columns\[i\].ColumnName + "\\t"; //栏位：自动跳到下一单元格

title = title.Substring(0, title.Length - 1) + "\\n";

sw.Write(title);

foreach (System.Data.DataRow row in table.Rows)  
            \{  
                string line = "";  
                for (int i = 0; i < table.Columns.Count; i++)  
                \{  
                    line += row\[i\].ToString().Trim() + "\\t"; //内容：自动跳到下一单元格  
                \}  
                line = line.Substring(0, line.Length - 1) + "\\n";  
                sw.Write(line);  
            \}  
            sw.Close();  
            fs.Close();  
        \}

[C_DataTabel_excel]: http://www.cnblogs.com/wuhuisheng/archive/2012/07/10/2585140.html