WinForm中导出Excel的方法

WinForm中导出Excel的方法  

2011-03-28 17:05:37|  分类： 软件编程 |  标签：excel  missing  xls  false  cells   |举报 |字号 订阅

二、winForm中导出Excel的方法：

1、方法1：

SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]); 
    SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn); 
    DataSet ds=new DataSet(); 
    da.Fill(ds,"table1"); 
    DataTable dt=ds.Tables["table1"]; 
    string name=System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";//存放到web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数 
    FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write); 
    StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312")); 
    sw.WriteLine("自动编号,姓名,年龄"); 
    foreach(DataRow dr in dt.Rows) 
    { 
     sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]); 
    } 
    sw.Close(); 
    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name)); 
    Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载 
    Response.WriteFile(name); // 把文件流发送到客户端 
    Response.End();


public void Out2Excel(string sTableName,string url)
{
Excel.Application oExcel=new Excel.Application();
Workbooks oBooks;
Workbook oBook;
Sheets oSheets;
Worksheet oSheet;
Range oCells;
string sFile="",sTemplate="";
//
System.Data.DataTable dt=TableOut(sTableName).Tables[0];

sFile=url+"myExcel.xls";
sTemplate=url+"MyTemplate.xls";
//
oExcel.Visible=false;
oExcel.DisplayAlerts=false;
//定义一个新的工作簿
oBooks=oExcel.Workbooks;
oBooks.Open(sTemplate,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing, Type.Missing);
oBook=oBooks.get_Item(1);
oSheets=oBook.Worksheets;
oSheet=(Worksheet)oSheets.get_Item(1);
//命名该sheet
oSheet.Name="Sheet1";

oCells=oSheet.Cells;
//调用dumpdata过程，将数据导入到Excel中去
DumpData(dt,oCells);
//保存
oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
oBook.Close(false, Type.Missing,Type.Missing);
//退出Excel，并且释放调用的COM资源
oExcel.Quit();

GC.Collect();
KillProcess("Excel");
}

private void KillProcess(string processName)
{
System.Diagnostics.Process myproc= new System.Diagnostics.Process();
//得到所有打开的进程
try
{
foreach (Process thisproc in Process.GetProcessesByName(processName))
{
if(!thisproc.CloseMainWindow())
{
thisproc.Kill();
}
}
}
catch(Exception Exc)
{
throw new Exception("",Exc);
}
}

2、方法2：

protected void ExportExcel()
   {
    gridbind(); 
    if(ds1==null) return; 

    string saveFileName="";
//    bool fileSaved=false;
    SaveFileDialog saveDialog=new SaveFileDialog();
    saveDialog.DefaultExt ="xls";
    saveDialog.Filter="Excel文件|*.xls";
    saveDialog.FileName ="Sheet1";
    saveDialog.ShowDialog();
    saveFileName=saveDialog.FileName;
    if(saveFileName.IndexOf(":")<0) return; //被点了取消
//    excelapp.Workbooks.Open   (App.path & 工程进度表.xls) 
   
    Excel.Application xlApp=new Excel.Application();
    object missing=System.Reflection.Missing.Value; 


    if(xlApp==null)
    {
     MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
     return;
    }
    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 oldCaption=Title_label .Text.Trim ();
    long totalCount=ds1.Tables[0].Rows.Count;
    long rowRead=0;
    float percent=0; 

    worksheet.Cells[1,1]=Title_label .Text.Trim ();
    //写入字段
    for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
    {
     worksheet.Cells[2,i+1]=ds1.Tables[0].Columns.ColumnName; 
     range=(Excel.Range)worksheet.Cells[2,i+1];
     range.Interior.ColorIndex = 15;
     range.Font.Bold = true;

    }
    //写入数值
    Caption .Visible = true;
    for(int r=0;r<ds1.Tables[0].Rows.Count;r++)
    {
     for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
     {
      worksheet.Cells[r+3,i+1]=ds1.Tables[0].Rows[r];     
     }
     rowRead++;
     percent=((float)(100*rowRead))/totalCount;    
     this.Caption.Text= "正在导出数据["+ percent.ToString("0.00") +"%]";
     Application.DoEvents();
    }
    worksheet.SaveAs(saveFileName,missing,missing,missing,missing,missing,missing,missing,missing);
   
    this.Caption.Visible= false;
    this.Caption.Text= oldCaption; 

    range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds1.Tables[0].Rows.Count+2,ds1.Tables[0].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(ds1.Tables[0].Columns.Count>1)
    {
     range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Excel.XlColorIndex.xlColorIndexAutomatic;
     }
    workbook.Close(missing,missing,missing);
    xlApp.Quit();
   }

3.从DataGridView里导出

 /// <summary>   
        /// 常用方法，列之间加\t开。   
        /// </summary>   
        /// <remarks>   
        /// using System.IO;   
        /// </remarks>   
        /// <param name="dgv"></param>   
        private void DataGridViewToExcel(DataGridView dgv)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.CheckFileExists = false;
            dlg.CheckPathExists = false;
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = "";
                try
                {
                    //写入列标题   
                    for (int i = 0; i < dgv.ColumnCount; i++)
                    {
                        if (i > 0)
                        {
                            columnTitle += "\t";
                        }
                        columnTitle += dgv.Columns[i].HeaderText;
                    }
                    sw.WriteLine(columnTitle);

                    //写入列内容   
                    for (int j = 0; j < dgv.Rows.Count; j++)
                    {
                        string columnValue = "";
                        for (int k = 0; k < dgv.Columns.Count; k++)
                        {
                            if (k > 0)
                            {
                                columnValue += "\t";
                            }
                            if (dgv.Rows[j].Cells[k].Value == null)
                                columnValue += "";
                            else
                                columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }

 

4.把Excel数据读到DataSet里

代码

 OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.CheckFileExists = false;
            dlg.CheckPathExists = false;
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.Title = "将Excel文件数据导入到DataSet";
            dlg.ShowDialog();

            DataSet ds = new DataSet();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dlg.FileName.Trim() + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
            using (OleDbConnection OleConn = new OleDbConnection(strConn))
            {
                OleConn.Open();
                String sql = "SELECT * FROM [Sheet1$]";
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                OleDaExcel.Fill(ds);
                OleConn.Close();
            }