求导出excel的经典代码

[zsdl00]

求导出excel的经典代码
在网上找了找，有不少代码，比较乱，试一下，也总报错。

所以我想求大家发个经典一些的源码。

我能想到的有三种方式。
1.导出csv，这个比较好实现，用不到excel组件，因为就是导出纯文本文件。
2.导出excel，导出时给定文件名，看不到excel程序打开，直接生成文件。
3.导出excel的同时，启动excel程序，然后结果呈现在用户眼下，让用户自已保存。

我不知以上哪种方式更好些。分别如何实现呢？

[andy1118]

http://topic.csdn.net/u/20100908/11/9a7b4ae1-5750-4a74-b338-e0276bb6998c.html

[qqrto]

我这里有个导出excel的类，很好用，你可以试试

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Data;

namespace test1
{
class ExcelManager
{
public static String no = "";
//引用命名空间 using Microsoft.Office.Interop.Excel;
//DataGridView 导出到Excel
public static void SaveAs(DataGridView gridView)
{
//导出到execl
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
//saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出客户资料";
saveFileDialog.ShowDialog();
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
//toolStripProgressBar1.Visible = true;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true); ;
excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面
if (excel == null)
{
MessageBox.Show("EXCEL无法启动！");
return;
}
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "CallCenter";

int m = 0, n = 0;
//生成列名称 这里i是从1开始的 因为我第0列是个隐藏列ID 没必要写进去
for (int i = 1; i < gridView.ColumnCount-4; i++)
{

excel.Cells[1, i] = gridView.Columns[i+1].HeaderText.ToString();

}

//填充数据
for (int i = 0; i < gridView.RowCount; i++)
{

//j也是从1开始 原因如上 每个人需求不一样
for (int j = 1; j < gridView.ColumnCount-4; j++)
{

if (gridView[j + 1, i].Value.GetType() == typeof(string))
{
excel.Cells[i+2 , j] = "'" + gridView[j+1, i].Value.ToString();
}
else if (gridView[j + 1, i].Value.GetType() == typeof(DateTime))
{
DateTime dt = (DateTime)gridView[j + 1, i].Value;
excel.Cells[i + 2, j] = dt.ToString("yyyy-MM-dd");
}
else
{
excel.Cells[i+2, j] = gridView[j+1, i].Value.ToString();
}



}
//toolStripProgressBar1.Value += 100 / gridView.RowCount;
}

sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

GC.Collect();
MessageBox.Show("数据已经成功导出!");
//toolStripProgressBar1.Value = 0;


System.Diagnostics.Process.Start(strName);
}
}

catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

}



//获得当前你选择的Excel Sheet的所有名字



public static string[] GetExcelSheetNames(string filePath)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, 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);
excelApp.Visible = false;
int count = wb.Worksheets.Count;
string[] names = new string[count];
for (int i = 1; i <= count; i++)
{
names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
}
wb.Close(false, miss, miss);
wbs.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

GC.Collect();
//wb.Close(false, miss, miss);
return names;
}



//Excel导入到数据库Access中



//filePath 你的Excel文件路径

public static bool Import(string filePath,DataSet set,OleDbDataAdapter adapter)
{
try
{

//Excel就好比一个数据源一般使用

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";

string[] names = GetExcelSheetNames(filePath);
OleDbConnection con = new OleDbConnection(strConn);
List<String> savelist = new List<string>();
con.Open();

if (names.Length > 0)
{
////set.Clear();
foreach (string name in names)
{
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = string.Format(" select * from [{0}$]", name);//[sheetName$]要如此格式
OleDbDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
for(int step=0;step<odr.FieldCount;step++)
{
savelist.Add(odr[step].ToString());
}
Add(savelist,set);
savelist = new List<string>();
}
odr.Close();
}
no = "";
}
con.Close();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}

}

[qqrto]

这个是可以用的，包含了导入导出excel表，表名和保存路径由用户自己选择定义，保存完成后，自动打开，你可以根据你的需求稍微修改一下就可以

[qqrto]

最后需要特别说明一下，它用到了Microsoft.Office.Interop.Excel这个dll文件，需要给office excel 2003打个安装补丁，补丁的安装程序名字叫做O2003PIA.MSI，你要是找不到，把邮箱给我，我给你发过去

[wuyq11]

导出总结

[macrottian]

#region ExportToMultipleExcelSheets
        private static System.Text.StringBuilder SqlScript = new System.Text.StringBuilder();
        private static System.Text.StringBuilder SqlInsert = new System.Text.StringBuilder();
        private static bool mPredefineFile = false;

        public static void ImportToMultipleXLSheets(System.String SqlSelect,System.String samplePath, System.String mOutputFileName,System.Data.DataSet ds)
        {
            string FolderPath;
            FolderPath = mOutputFileName.Remove(mOutputFileName.LastIndexOf("\\"), mOutputFileName.Length - mOutputFileName.LastIndexOf("\\"));

            //File.Copy(FolderPath + "\\Sample.xls", mOutputFileName, true);
            File.Copy(samplePath, mOutputFileName, true);
            
            string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mOutputFileName + ";Extended Properties='Excel 8.0'";
            OleDbConnection xlConn = new OleDbConnection(connstr);

            try
            {
                xlConn.Open();
                
                PrepareScript(ds.Tables[0]);
                StartImport(ds.Tables[0], xlConn);

            }
            catch (Exception exp)
            {
                //throw new Exception("ImportToMultipleXLSheets", exp.InnerException);
                throw new Exception(exp.Message, exp);
            }
            finally
            {
                if (xlConn != null)
                {
                    if (xlConn.State == ConnectionState.Open) xlConn.Close();
                    xlConn.Dispose();
                }
            }
        }

        private static void CreateXLSheets(DataTable DTable, OleDbConnection xlConn, System.String XLSheetName)
        {
            // Create New Excel Sheet
            System.Text.StringBuilder SqlFinalScript = new System.Text.StringBuilder();
            OleDbCommand cmdXl = new OleDbCommand();
            try
            {

                SqlFinalScript.Length = 0;

                cmdXl.Connection = xlConn;

                SqlFinalScript.Append("CREATE TABLE [" + XLSheetName + "$] (");
                SqlFinalScript.Append(SqlScript.ToString());

                cmdXl.CommandText = SqlFinalScript.ToString();
                cmdXl.ExecuteNonQuery();

            }
            catch (Exception xlSheetExp)
            {
                //throw (new Exception("CreateXLSheetException", xlSheetExp.InnerException));
                throw new Exception(xlSheetExp.Message, xlSheetExp);
            }
            finally
            {
                cmdXl.Dispose();
            }
        }

        private static string PrepareScript(DataTable DTable)
        {
            // Prepare Scripts to create excel Sheet
            SqlInsert.Length = 0;
            SqlScript.Length = 0;
            for (int i = 0; i < DTable.Columns.Count; i++)
            {
                SqlInsert.Append("[" + DTable.Columns[i].ColumnName + "],");

                SqlScript.Append("[" + DTable.Columns[i].ColumnName.Replace("'", "''") + "]");

                if (DTable.Columns[i].DataType.ToString().ToLower().Contains("int") || DTable.Columns[i].DataType.ToString().ToLower().Contains("decimal"))
                    SqlScript.Append(" double");
                else
                    SqlScript.Append(" text");

                SqlScript.Append(", ");
            }
            SqlInsert.Remove(SqlInsert.Length - 1, 1);
            SqlScript.Remove(SqlScript.Length - 2, 1);
            SqlScript.Append(") ");
            return SqlScript.ToString();
        }
        private static void StartImport(DataTable DTable, OleDbConnection xlConn)
        {
            Int64 rowNo = 0, xlSheetIndex = 2, TotalNoOfRecords = 0;

            System.String NewXLSheetName = "Sheet";
            System.Text.StringBuilder strInsert = new System.Text.StringBuilder();
            TotalNoOfRecords = DTable.Rows.Count;
            OleDbCommand cmdXl = new OleDbCommand();
            cmdXl.Connection = xlConn;
            if (mPredefineFile) xlSheetIndex = 1;
            xlSheetIndex = 1;
            for (int count = 0; count < DTable.Rows.Count; count++)
            {
                strInsert.Length = 0;

                if (rowNo == 0 && !mPredefineFile)
                    CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex);
                rowNo += 1;

                // TotalNoOfRecords : Total no of records return by Sql Query, ideally should be set to 65535
                //rowNo : current Row no in the loop 
                if (TotalNoOfRecords > 5000 && rowNo > 5000)
                {
                    xlSheetIndex += 1;
                    if (!mPredefineFile) CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex);
                    rowNo = 1;
                }
                strInsert.Append("Insert Into [" + NewXLSheetName + xlSheetIndex.ToString() + "$](" + SqlInsert.ToString() + ") Values (");
                foreach (DataColumn dCol in DTable.Columns)
                {
                    if (dCol.DataType.ToString().ToLower().Contains("int"))
                    {
                        if (DTable.Rows[count][dCol.Ordinal].ToString() == "")
                            strInsert.Append("NULL");
                        else
                            strInsert.Append(DTable.Rows[count][dCol.Ordinal]);
                    }
                    else if (dCol.DataType.ToString().ToLower().ToLower().Contains("decimal"))
                    {
                        if (DTable.Rows[count][dCol.Ordinal].ToString() == "")
                            strInsert.Append("NULL");
                        else
                            strInsert.Append(DTable.Rows[count][dCol.Ordinal]);
                    }
                    else
                        strInsert.Append("\"" + DTable.Rows[count][dCol.Ordinal].ToString().Replace("'", "''") + "\"");

                    strInsert.Append(",");
                }
                strInsert.Remove(strInsert.Length - 1, 1);
                strInsert.Append(");");
                cmdXl.CommandText = strInsert.ToString();
                cmdXl.ExecuteNonQuery();
            }
        }
        #endregion

[macrottian]

using System.Data.OleDb;

在你的程序里新一个Sample.xls
优点：速度快、客户端不用安装Excel

[wubudang]

Response.Write我都是这么用的，效果还可以。