winform程序如何将DataTable数据导入到Excel文件中

winform程序如何将DataTable数据导入到Excel文件中,用文件流的形式可以吗？
能给个代码参考下么 谢谢了。

10 个解决方案

#1

/// <summary>
   /// 读取Excel文档
  /// </summary>
   /// <param name="Path">文件名称</param>
   /// <returns>返回一个数据集</returns>
   public DataSet ExcelToDS(string Path)
   {
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
    OleDbConnection conn = new OleDbConnection(strConn);
    conn.Open();  
    string strExcel = "";   
    OleDbDataAdapter myCommand = null;
    DataSet ds = null;
    strExcel="select * from [sheet1$]";
    myCommand = new OleDbDataAdapter(strExcel, strConn);
    ds = new DataSet();
    myCommand.Fill(ds,"table1");   
    return ds;
   }


/// <summary>
   /// 写入Excel文档
  /// </summary>
   /// <param name="Path">文件名称</param>
   public bool SaveFP2toExcel(string Path)
   {
    try
    {
     string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
     OleDbConnection conn = new OleDbConnection(strConn);
     conn.Open();  
     System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
     cmd.Connection =conn;
     //cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
     //cmd.ExecuteNonQuery ();
     for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
     {
      if(fp2.Sheets [0].Cells[i,0].Text!="")
      {
       cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
        fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
        "','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
       cmd.ExecuteNonQuery ();
      }
     }
     conn.Close ();
     return true;
    }
    catch(System.Data.OleDb.OleDbException ex)
    {
     System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
    }
    return false;
   }

#2

上面的方法不是很苟同，因为如果是大数据量的话，非常的慢，目前我也没找到针对ADO.NET用EXCEL快速输出数据的方法，求高人指点。。。。

#3

引用 楼主 wuzida110 的回复:

winform程序如何将DataTable数据导入到Excel文件中,用文件流的形式可以吗？
能给个代码参考下么 谢谢了。

用流可以的
http://blog.csdn.net/happy09li/article/details/7431967

#4

有个插件吧 nopi 

#5

public static bool ExportExcelWithXML(System.Data.DataTable dt, string path)
        {
            bool succeed = false;
            if (dt == null)
            {
                // 导出为XML格式的Excel文件，需要事先准备好XML格式的Excel文件作为模版
                try
                {
                    XmlDocument doc = new XmlDocument();
                    doc.Load(System.Windows.Forms.Application.StartupPath + @"\XLS\ExportXML.xls");
                    XmlNode root = doc.DocumentElement;
                    XmlNodeList xnlist = root.ChildNodes;
                    XmlElement sheet = null;
                    XmlElement documentPro = null;
                    XmlElement styles = null;
                    foreach (XmlNode xn in xnlist)
                    {
                        XmlElement xe = (XmlElement)xn;
                        if (xe.Name == "DocumentProperties")
                        {
                            documentPro = xe;
                        }
                        else if (xe.Name == "Worksheet")
                        {
                            sheet = xe;
                        }
                        else if (xe.Name == "Styles")
                        {
                            styles = xe;
                        }
                    }

                    if (documentPro == null || sheet == null || styles == null)
                    {
                        return false;
                    }

                    // 写入Sheet名
                    sheet.SetAttribute("Name", ssNameSpace, dt.TableName);

                    // 添加Style
                    XmlElement styleColumnName = doc.CreateElement("Style", ssNameSpace);
                    styleColumnName.SetAttribute("ID", ssNameSpace, "s16");
                    XmlElement fontColumnName = doc.CreateElement("Font", ssNameSpace);
                    fontColumnName.SetAttribute("FontName", ssNameSpace, "Arial");
                    fontColumnName.SetAttribute("Family", xNameSpace, "Swiss");
                    fontColumnName.SetAttribute("Color", ssNameSpace, "#000000");
                    fontColumnName.SetAttribute("Bold", ssNameSpace, "1");
                    styleColumnName.AppendChild(fontColumnName);
                    styles.AppendChild(styleColumnName);

                    XmlElement styleRow = doc.CreateElement("Style", ssNameSpace);
                    styleRow.SetAttribute("ID", ssNameSpace, "s17");
                    XmlElement fontRow = doc.CreateElement("Font", ssNameSpace);
                    fontRow.SetAttribute("FontName", ssNameSpace, "Arial");
                    fontRow.SetAttribute("Family", xNameSpace, "Swiss");
                    fontRow.SetAttribute("Color", ssNameSpace, "#000000");
                    styleRow.AppendChild(fontRow);
                    styles.AppendChild(styleRow);

                    // 写入表格内容
                    XmlNode table = sheet.FirstChild;

                    // 写入行列个数
                    ((XmlElement)table).SetAttribute("ExpandedColumnCount", ssNameSpace, dt.Columns.Count.ToString());
                    ((XmlElement)table).SetAttribute("ExpandedRowCount", ssNameSpace, (dt.Rows.Count + 2).ToString());

                    // 添加列宽
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        XmlElement column = doc.CreateElement("Column", ssNameSpace);
                        column.SetAttribute("Width", ssNameSpace, "100");
                        column.SetAttribute("AutoFitWidth", ssNameSpace, "1");
                        table.AppendChild(column);
                    }

                    // 添加列名
                    XmlElement columnName = doc.CreateElement("Row", ssNameSpace);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        XmlElement columnCell = doc.CreateElement("Cell", ssNameSpace);
                        columnCell.SetAttribute("StyleID", ssNameSpace, "s16");

                        XmlElement data = doc.CreateElement("ss:Data", ssNameSpace);
                        data.SetAttribute("Type", ssNameSpace, "String");
                        data.InnerText = dt.Columns[i].ToString();

                        columnCell.AppendChild(data);
                        columnName.AppendChild(columnCell);
                    }
                    table.AppendChild(columnName);

                    // 添加行
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        XmlElement row = doc.CreateElement("Row", ssNameSpace);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            XmlElement cell = doc.CreateElement("Cell", ssNameSpace);
                            cell.SetAttribute("StyleID", ssNameSpace, "s17");

                            XmlElement data = doc.CreateElement("Data", ssNameSpace);
                            data.SetAttribute("Type", ssNameSpace, "String");
                            data.InnerText = dt.Rows[i][j].ToString();

                            cell.AppendChild(data);
                            row.AppendChild(cell);
                        }
                        table.AppendChild(row);
                    }

                    DateTime now = DateTime.Now;
                    string timeString = string.Format("{0}T{1}Z", now.ToShortDateString(), now.ToLongTimeString());
                    XmlNodeList docProNodeList = documentPro.ChildNodes;
                    foreach (XmlNode xn in docProNodeList)
                    {
                        if (xn.Name == "Author" || xn.Name == "LastAuthor")
                        {
                            // 写入作者和修改者
                            xn.InnerText = Environment.UserName;
                        }
                        else if (xn.Name == "Created" || xn.Name == "LastSaved")
                        {
                            // 写入创建时间和修改时间
                            xn.InnerText = timeString;
                        }
                        else if (xn.Name == "Company")
                        {
                            // 写入公司名
                            xn.InnerText = System.Windows.Forms.Application.CompanyName;
                        }
                    }

                    doc.Save(path);
                    succeed = true;
                }
                catch (Exception e)
                {
                    succeed = false;
                }
            }

            return succeed;
        }

#6

/// <summary>
        /// 把DataTable导出到EXCEL不是科学计算法
        /// </summary>
        /// <param name="dt">DataTable数据源表</param>
        /// <param name="saveFileName">EXCEL全路径文件名</param>
        /// <returns>导出是否成功</returns>
        public bool ExportExcel_NoScientificCalc(System.Data.DataTable dt, string saveFileName)
        {
            if (dt == null)
            {
                MessageBoxHepler.ShowErrorMsgBox("要到出的数据集为空!");
                return false;
            }
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            if (xlApp == null)
            {
                MessageBoxHepler.ShowErrorMsgBox("不能创建EXCEL文件,请检查您的机器是否正确安装过EXCEL!");
                return false;
            }
            //xlApp.Visible = true; //打开EXCEL 可以清楚的看到导入过程,这样省掉了进度条的功能但是加长的导出时间

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得第一个SHEET
            worksheet.Name = dt.TableName;
            worksheet.Cells.Font.Size = 10; //字体大小
            Microsoft.Office.Interop.Excel.Range range;

            worksheet.get_Range("A1", "D1").Merge((worksheet.get_Range("A1", "D1").MergeCells));
            worksheet.get_Range("E1", "G1").Merge((worksheet.get_Range("E1", "G1").MergeCells));


            worksheet.Cells[1, 1] = txtConsignor.Text;
            worksheet.Cells[1, 5] = ghdtc.DtpStartDate.ToString("yyyy-MM-dd") + " - " + ghdtc.DtpEndDate.ToString("yyyy-MM-dd");
            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.Interior.ColorIndex = 34;
            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 5];
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.Interior.ColorIndex = 34;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Rows["1:1", System.Type.Missing]).RowHeight = 22;

            //写入列头字段
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
                if (i == 5)
                {
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i];
                    range.NumberFormatLocal = "@";
                }
            }

            //写入数值
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName == "报关" || dt.Columns[i].ColumnName == "三检" || dt.Columns[i].ColumnName == "其他" || dt.Columns[i].ColumnName == "合计")
                    {
                        //((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 3, i + 1]).NumberFormatLocal = "$#,##0.00";//更改格式为文本格式
                        worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
                        range = worksheet.get_Range("O"+(r+3).ToString(), System.Type.Missing);
                        range.Formula = "=SUM(L"+(r+3).ToString()+":N"+(r+3).ToString()+")";
                        range.Calculate();

                        range = worksheet.get_Range("L" + (dt.Rows.Count - 1 + 3).ToString(), System.Type.Missing);
                        range.Formula = "=SUM(L3:L" + (dt.Rows.Count - 2 + 3).ToString() + ")";
                        range.Calculate();

                        range = worksheet.get_Range("M" + (dt.Rows.Count - 1 + 3).ToString(), System.Type.Missing);
                        range.Formula = "=SUM(M3:M" + (dt.Rows.Count - 2 + 3).ToString() + ")";
                        range.Calculate();

                        range = worksheet.get_Range("N" + (dt.Rows.Count - 1 + 3).ToString(), System.Type.Missing);
                        range.Formula = "=SUM(N3:N" + (dt.Rows.Count - 2 + 3).ToString() + ")";
                        range.Calculate();
                    }
                    else
                    {
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 3, i + 1]).NumberFormatLocal = "@";//更改格式为文本格式
                        worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
                    }
                }
            }
            
            range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
            range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
            if (dt.Rows.Count > 0)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            }
            if (dt.Columns.Count > 1)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            }
            range.EntireColumn.AutoFit();
            try
            {
                workbook.Saved = true;
                workbook.SaveCopyAs(saveFileName);//保存文件
                return true;
            }
            catch (Exception ex)
            {
                MessageBoxHepler.ShowErrorMsgBox("保存文件时出错,要保存的文件名错误或者文件可能正被打开!\r\n" + ex.Message);
                return false;
            }
            finally
            {
                //释放Excel对应的对象
                if (range != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                    range = null;
                }
                if (worksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                    worksheet = null;
                }
                if (workbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    workbook = null;
                }
                if (workbooks != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                    workbooks = null;
                }

                // System.Diagnostics.Process excel = System.Diagnostics.Process.GetCurrentProcess();

                xlApp.Application.Workbooks.Close();
                xlApp.Quit();
                if (xlApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    KillSpecialExcel(xlApp);
                    xlApp = null;
                }

                GC.Collect();
            }
        }

#7

引用 3 楼 happy09li 的回复:

Quote: 引用 楼主 wuzida110 的回复:

winform程序如何将DataTable数据导入到Excel文件中,用文件流的形式可以吗？
能给个代码参考下么 谢谢了。

用流可以的
http://blog.csdn.net/happy09li/article/details/7431967

+1我写的代码 就是 利用 你的 
高手

#8

引用 3 楼 happy09li 的回复:

Quote: 引用 楼主 wuzida110 的回复:

winform程序如何将DataTable数据导入到Excel文件中,用文件流的形式可以吗？
能给个代码参考下么 谢谢了。

用流可以的
http://blog.csdn.net/happy09li/article/details/7431967

我想问一下“ for(int i=0;i< fp2.Sheets [0].RowCount -1;i++)  ”
的“fp2”是在哪来的？找不到啊！

#9

引用 8 楼 wuzida110 的回复:

Quote: 引用 3 楼 happy09li 的回复:

Quote: 引用 楼主 wuzida110 的回复:

winform程序如何将DataTable数据导入到Excel文件中,用文件流的形式可以吗？
能给个代码参考下么 谢谢了。

用流可以的
http://blog.csdn.net/happy09li/article/details/7431967

我想问一下“ for(int i=0;i< fp2.Sheets [0].RowCount -1;i++)  ”
的“fp2”是在哪来的？找不到啊！

 Excel.Workbook book

#10

  public static void DataToExcel(System.Data.DataTable dataTable)
        {
            try
            {
                object missingValue = System.Reflection.Missing.Value;
                Application excel = new Application();
                excel.Visible = false;
                _Workbook workBook = excel.Workbooks.Add(missingValue);
                int excelRow = 0;
                int excelColumn = 0;
                for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
                {
                    for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        excelRow = rowIndex + 1;
                        excelColumn = columnIndex + 1;
                        excel.Cells[excelRow, excelColumn] = dataTable.Rows[rowIndex][columnIndex];
                    }
                }
                workBook.SaveAs(@"D:\test.xls", missingValue, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null);
                workBook.Close(false, missingValue, missingValue);
                excel.Quit();
              
            }
            catch (Exception exception)
            {
              
            }
        }