c# Excel

读取Excel:

 

 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + ViewState["file"];
            string sql = "SELECT * FROM [Sheet1$]";
            DataTable _table = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
            da.Fill(_table);
            ViewState["table"] = _table;
            lblMsg.Text = "总共: "+_table.Rows.Count+" 条记录";
            _table.Dispose();

 

导出Excel方法，使用Response

 

  #region 最简单的创建Excel
        public void CreateExcel(DataTable _table, string FileName)
        {
            //FileName = Server.UrlEncode(FileName);
            HttpResponse response = Page.Response;
            response.Clear();
            response.Buffer = true;
            response.Charset = "GB2312";
            response.HeaderEncoding = System.Text.Encoding.GetEncoding("GB2312");
            response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
            response.AddHeader("Content-Type", "application/octet-stream;charset=GB2312");
            response.ContentType = "application/ms-excel";
            string ls_item = "";

            ls_item = "编号\t险种\t保单号\t手续费\t手续费率\t佣金\t备注\t分组序号\n";
            response.Write(ls_item);
            ls_item = "";
            int i = 1;
            foreach (DataRow row in _table.Rows)
            {
                ls_item = i.ToString() + "\t" + row[3] + "\t" + row[4] + "\t" + row[7] + "\t" + row[8] + "\t" + row[9] + "\t" + row[10] + "\t" + i.ToString() + "\n";
                response.Write(ls_item);
                i++;
            }
            //写缓冲区中的数据到HTTP头文件中 
            response.Flush();
            response.Clear();
            response.End();
        }
        #endregion

 

导出Excel,使用组件OWC11

 

        #region 引用了OWC11组件，比较方便，好控制
        private void ExpoertExcel(DataTable _table)
        {
            //请在项目中引用OWC11(COM组件)
            OWC11.SpreadsheetClass xlsheet = new OWC11.SpreadsheetClass();
            ////合并单元格
            //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 8]).set_MergeCells(true);
            //xlsheet.ActiveSheet.Cells[1, 1] = "保险劳务费";
            ////字体加粗
            //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 8]).Font.set_Bold(true);
            ////单元格文本水平居中对齐
            //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 8]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            ////设置字体大小
            //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 8]).Font.set_Size(14);
            //设置列宽
            xlsheet.get_Range(xlsheet.Cells[1,1], xlsheet.Cells[1, 1]).set_ColumnWidth(5);
            xlsheet.get_Range(xlsheet.Cells[1, 2], xlsheet.Cells[1,2]).set_ColumnWidth(12);
            xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(25);
            xlsheet.get_Range(xlsheet.Cells[1, 4], xlsheet.Cells[1, 4]).set_ColumnWidth(15);
            xlsheet.get_Range(xlsheet.Cells[1, 5], xlsheet.Cells[1, 5]).set_ColumnWidth(10);
            xlsheet.get_Range(xlsheet.Cells[1, 6], xlsheet.Cells[1, 6]).set_ColumnWidth(15);
            xlsheet.get_Range(xlsheet.Cells[1, 7], xlsheet.Cells[1, 7]).set_ColumnWidth(10);
            xlsheet.get_Range(xlsheet.Cells[1, 8], xlsheet.Cells[1, 8]).set_ColumnWidth(8);
            //设置行高
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_table.Rows.Count, 8]).set_RowHeight(20);
            
            //画边框线
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_table.Rows.Count, 8]).Borders.set_LineStyle(OWC11.XlLineStyle.xlContinuous);
            //设置字体大小
            xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_table.Rows.Count, 8]).Font.set_Size(10);
            //写入数据 (这里可根据需要由DS生成)
            int i = 1,j=0;
            int groupID = 0;
            double t1 = 0.0, t2 = 0.0;
            foreach (DataRow row in _table.Select("佣金<1333 and 险种<>''", "佣金 desc")) //注意写入数据时,必须从第一行开始写EXCEL里没有第"0"行
            {
                if (j % 10 == 0)
                {
                    t1 = 0.0;
                    t2 = 0.0;
                    SetTitle(xlsheet, i);
                    i++; i++; groupID++;
                }
                xlsheet.ActiveSheet.Cells[i, 1] = j%10+1;
                xlsheet.ActiveSheet.Cells[i, 2] = row["险种"];
                xlsheet.ActiveSheet.Cells[i, 3] = row["保单号"].ToString()+" ";
                xlsheet.ActiveSheet.Cells[i, 4] = row["手续费"];
                xlsheet.ActiveSheet.Cells[i, 5] = row[8];//"手续费率"
                xlsheet.ActiveSheet.Cells[i, 6] = row["佣金"];
                xlsheet.ActiveSheet.Cells[i, 7] = row["备注"];
                xlsheet.ActiveSheet.Cells[i, 8] = groupID;//分组
                t1 += Convert.ToDouble(row["手续费"]);
                t2 += Convert.ToDouble(row["佣金"]);
                if (j % 10 == 9)
                {
                    SetFoot(xlsheet, ++i, t1, t2);
                    i++;                   
                }
                i++; j++;
            }
            try
            {
                //格式化 Selection.NumberFormatLocal = "0;[红色]0"
                xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[_table.Rows.Count, 3]).set_NumberFormat("0");
                xlsheet.get_Range(xlsheet.Cells[1, 4], xlsheet.Cells[_table.Rows.Count, 4]).set_NumberFormat("￥#,##0.00");
                xlsheet.get_Range(xlsheet.Cells[1, 5], xlsheet.Cells[_table.Rows.Count, 5]).set_NumberFormat("￥#,##0.00");
                xlsheet.get_Range(xlsheet.Cells[1, 6], xlsheet.Cells[_table.Rows.Count, 6]).set_NumberFormat("￥#,##0.00");
                if (_table != null) _table.Dispose();
                xlsheet.Export(System.AppDomain.CurrentDomain.BaseDirectory + "计算并导出后的Excel.xls", OWC11.SheetExportActionEnum.ssExportActionNone, OWC11.SheetExportFormat.ssExportXMLSpreadsheet);
                GC.Collect();
                lblMsg.Text = "导出成功!!!";
            }
            catch
            {
                lblMsg.Text = "请关闭Excel文件，再重试!!!";
                return;
            }
            Response.Redirect("计算并导出后的Excel.xls");           
        }
        private void SetTitle(Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet,int row)        
        {
            //合并单元格
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).set_MergeCells(true);
            xlsheet.ActiveSheet.Cells[row, 1] = "保险劳务费";
            //字体加粗
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).Font.set_Bold(true);
            //单元格文本水平居中对齐
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            //设置字体大小
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).Font.set_Size(14);
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 1]).set_RowHeight(35);

            row++;

            xlsheet.ActiveSheet.Cells[row , 1] = "编号";
            xlsheet.ActiveSheet.Cells[row , 2] = "险种";
            xlsheet.ActiveSheet.Cells[row , 3] = "保单号";
            xlsheet.ActiveSheet.Cells[row , 4] = "手续费";
            xlsheet.ActiveSheet.Cells[row, 5] = "手续费率";
            xlsheet.ActiveSheet.Cells[row, 6] = "佣金";
            xlsheet.ActiveSheet.Cells[row , 7] = "备注";
            xlsheet.ActiveSheet.Cells[row , 8] = "分组序号";//分组

            //字体加粗
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).Font.set_Bold(true);
            //单元格文本水平居中对齐
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            //设置字体大小8
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 8]).Font.set_Size(10);

        }
        private void SetFoot(Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet, int row,double t1,double t2)
        {
            row++;
            xlsheet.ActiveSheet.Cells[row, 1] = "合计";
            //字体加粗
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 1]).Font.set_Bold(true);
            //合并单元格
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 3]).set_MergeCells(true);
            //单元格文本水平居中对齐
            xlsheet.get_Range(xlsheet.Cells[row, 1], xlsheet.Cells[row, 3]).set_HorizontalAlignment(OWC11.XlHAlign.xlHAlignCenter);
            xlsheet.ActiveSheet.Cells[row, 4] = t1;
            xlsheet.ActiveSheet.Cells[row, 6] = t2;
        }
        #endregion

 

导出Excel,建一个SqlServer的数据源，利用Excel的外部数据源让Excel自己从数据库取数据

此方法出处:http://www.cnblogs.com/martinxj/archive/2004/08/21/35324.html

 

public void ExportToExcel(string pstrSql)
        {
            Excel.Application pApplication;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;
            Excel._QueryTable xQt;
            string ExcelConn = "ODBC;DRIVER=SQL Server;SERVER=localhost;UID=sa;PWD=;APP=Microsoft(R) Windows (R) 2000 Operating System;WSID=me;DATABASE=pubs";
            pApplication = new Excel.ApplicationClass();
            xBk = pApplication.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;
            pApplication.Cells[2,2] = this.title;

            xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Bold = true;
            xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Name = "黑体";
            xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Size = 22;
            xQt = xSt.QueryTables.Add(ExcelConn,xSt.get_Range(pApplication.Cells[4,2],pApplication.Cells[4,2]),pstrSql);
            xQt.Name = "导出EXCEL";
            xQt.FieldNames = true;
            xQt.RowNumbers = false;
            xQt.FillAdjacentFormulas = false;
            xQt.PreserveFormatting = false;
            xQt.BackgroundQuery = true;
            xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
            xQt.AdjustColumnWidth = true;
            xQt.RefreshPeriod = 0;
            xQt.PreserveColumnInfo = true;
            xQt.Refresh(xQt.BackgroundQuery);
            pApplication.Visible = true;
        }

 

导出Excel,使用Microsoft.Office.Interop.Excel.dll，超慢！忽略淘汰！！！

导出Excel,这种方法正在筹划中...

如果遇到什么问题，建议在Excel中建一个宏，把你的操作记录在宏中，然后打开宏就可以看到代码，然后再模拟宏里面的代码在C#中写！

 

学的快，还不如抄的快,以下转自:http://www.cnblogs.com/renyu732/archive/2005/06/15/174866.html

DataGrid输出到Excel并进行格式化处理
用Xml2OleDb将XML文件插入到数据库
在C#中利用Excel做高级报表
在ASP.NET中将数据直接输出成Excel内容
在.NET环境下将报表导出Excel和Word
将DataGrid输出到Excel文件
把Excel文件中的数据读入到DataGrid中


Other

关于asp.net导出Excel
http://www.cnblogs.com/caizinet/archive/2005/05/11/152908.aspx

ASP.NET & Excel
http://www.cnblogs.com/birdshome/favorite/1666.html

如何使用 ASP.NET、ADO.NET 和 Visual C# .NET 查询和显示 Excel 数据
http://support.microsoft.com/default.aspx?scid=kb;zh-cn;306572

必学:将Asp.Net页面输出到EXCEL里去
http://blog.edu.cn/user1/18141/archives/2005/198690.shtml

ASP.NET中数据库数据导入Excel并打印
http://www.weste.net/2004/12-20/11440068743.html

Microsoft Excel for Data Analysis and Reporting in ASP.NET
http://www.asp101.com/articles/jayram/exceldotnet/default.asp

在ASP.NET下用Microsoft Excel进行数据分析与报表
http://dev.csdn.net/develop/article/20/20283.shtm

如何在ASP.NET中用OWC绘制图表
http://tech.ccidnet.com/pub/article/c1111_a65936_p1.html

Export data to Excel by using an HTTPHandler, XSLT, and ASP.NET
http://www.kbalertz.com/kb_871040.aspx

在asp.net访问Excel文件
http://www.chinacs.net/archives/2/2001/06/29/361.html

Using Office Web Components to Load Excel Workbooks and do Calculations
http://www.eggheadcafe.com/articles/20030725.asp

Link ASP.NET Data Grid to Microsoft Excel in the client side
http://www.codeproject.com/aspnet/DGExdel.asp

Create your ASP & ASP.NET-pages using Excel
http://www.exceleverywhere.com/excel-asp-net.htm

Exporting to Excel in Crystal Reports .NET
http://aspalliance.com/478

Create Dynamic ASP.NET Excel Workbooks In C#
http://www.eggheadcafe.com/articles/20021012.asp

ASP.NET中数据库数据导入Excel并打印
http://www.chinacs.net/archives/2/2005/01/28/195.html

将Asp.Net页面输出到EXCEL里去
http://www.pconline.com.cn/pcedu/empolder/net/0412/513965.html

关于asp.net导出Excel
http://www.cnblogs.com/caizinet/archive/2005/05/11/152908.aspx


特别推荐：纯VB.NET代码直接生成Excel文件（不需要Excel）
http://unruledboy.cnblogs.com/archive/2004/07/07/22093.html