c#导出标准execl

如题，导出标准的execl文档（因为需要导入），要在客户端生成的，最好有源码，原来没有写过这样的。没有用过execl.dll
,所以最好给出详细的注释说明。谢谢！！

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

   1. string date1 = this.txtStartTime.Text.Trim();
   2.             string date2 = this.txtEndTime.Text.Trim();
   3.             string strDate = string.Empty;
   4.             string titledate = string.Empty;
   5.             if (date1 != "" && date2 != "")
   6.             {
   7.                 strDate = " and (senddate between '" + date1 + "' and Dateadd(d,1,'" + date2 + "') ) ";
   8.                 titledate = "(" + date1 + "至" + date2 + ")";
   9.             }
  10.             string tmpdepid = e.CommandArgument.ToString();
  11.             string tmpsql = "select a.tf,s.senddate,s.isly from tb_send s,tb_article a where s.sendArticleId=a.qwbs and s.senddepid='" + tmpdepid + "' " + strDate + " order by senddate desc";
  12.             DataTable dt = CHWEB.DBUtility.DbHelperSQL.Query(tmpsql).Tables[0];
  13.
  14.             if (dt != null && dt.Rows.Count > 0)
  15.             {
  16.                 Excel.Application excel = new Excel.Application();
  17.                 if (excel == null)
  18.                 {
  19.                     return;
  20.                 }
  21.                 Excel.Workbooks workbooks = excel.Workbooks;
  22.                 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
  23.                 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;
  24.                 Excel.Range range = worksheet.Cells;
  25.                 //设置表格样式
  26.                 Excel.PageSetup ps = worksheet.PageSetup;
  27.                 //ps.Orientation = Excel.XlPageOrientation.xlLandscape
  28.                 ps.CenterHorizontally = true;
  29.                 ps.LeftMargin = ps.RightMargin = excel.Application.InchesToPoints(0.551181102362205);
  30.                 ps.TopMargin =ps.BottomMargin= excel.Application.InchesToPoints(0.590551181102362);
  31.                 ps.HeaderMargin = ps.FooterMargin = excel.Application.InchesToPoints(0.511811023622047);
  32.                 ps.CenterFooter = "第 &P 页，共 &N 页";
  33.
  34.
  35.                 //导出内容如下--------------
  36.                 Excel.Range r1 = worksheet.get_Range(range[1, 1], range[1, 4]) as Excel.Range;
  37.                 r1.Merge(true);
  38.
  39.
  40.                 r1.Value2 = getDepShortName(tmpdepid) + " 的报送数据" + titledate;
  41.                 r1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
  42.                 r1.Font.Name = "黑体";
  43.                 r1.RowHeight = 50;
  44.                 r1.Font.Size = 20;
  45.                 r1.Font.Bold = true;
  46.
  47.                 string[] titles = new string[] { "序号", "报送标题", "报送日期", "状态" };
  48.                 for (int i = 0; i < titles.Length; i++)
  49.                 {
  50.                     Excel.Range r = (Excel.Range)worksheet.Cells[2, i+1];
  51.                     r.Value2 = titles[i];
  52.                     r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
  53.                     r.RowHeight = 20;
  54.                     r.Font.Bold = true;
  55.                     switch(i.ToString())
  56.                     {
  57.                         case "0":
  58.                             r.ColumnWidth = 8;
  59.                             break;
  60.                         case "2":
  61.                             r.ColumnWidth = 18;
  62.                             break;
  63.                         case "3":
  64.                             r.ColumnWidth = 8;
  65.                             break;
  66.                         default:
  67.                             r.ColumnWidth = 48;
  68.                             break;
  69.                     }
  70.                 }
  71.
  72.                 int rowindex = 3;
  73.                 for (int i = 0; i < dt.Rows.Count; i++)
  74.                 {
  75.                     DataRow dr = dt.Rows[i];
  76.                     Excel.Range rr1 ;
  77.                     rr1 = (Excel.Range)worksheet.Cells[rowindex, 1];
  78.                     rr1.Value2 = (i + 1).ToString();
  79.                     rr1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
  80.
  81.                     Excel.Range rr2;
  82.                     rr2 = (Excel.Range)worksheet.Cells[rowindex, 2];
  83.                     rr2.Value2 = dr[0].ToString();
  84.                     rr2.WrapText = true;//自动换行
  85.                     if (dr[2].ToString() == "1")
  86.                     {
  87.                         rr2.Font.ColorIndex = 5;
  88.
  89.                     }
  90.
  91.                     Excel.Range rr3;
  92.                     rr3 = (Excel.Range)worksheet.Cells[rowindex, 3];
  93.                     rr3.Value2 = dr[1].ToString();
  94.                     rr3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
  95.
  96.                     Excel.Range rr4;
  97.                     rr4 = (Excel.Range)worksheet.Cells[rowindex, 4];
  98.                     rr4.Value2 = getInfoStatus(dr[2].ToString());
  99.                     rr4.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
100.
101.                     rowindex++;
102.                 }
103.
104.                 Excel.Range rAll = (Excel.Range)worksheet.get_Range(range[2, 1], range[rowindex-1, 4]);
105.                 rAll.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
106.
107.                 //合计行
108.                 Excel.Range rFooter = (Excel.Range)worksheet.get_Range(range[rowindex, 1], range[rowindex, 4]);
109.                 rFooter.Merge(true);
110.                 rFooter.Value2 = "合计：报送总量" + dt.Rows.Count.ToString() + "，被采用量" + getDepUse(tmpdepid).ToString();
111.                 rFooter.RowHeight = 40;
112.                 rFooter.Font.Bold = true;
113.
114.
115.
116.                 //-------------------------------
117.                 string filepath = Server.MapPath("export.xls");
118.
119.
120.                 excel.Visible=false;
121.                 workbook.SaveCopyAs(filepath);
122.                 workbook.Close(false, null, null);
123.                 excel.Quit();
124.
125.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
126.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
127.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
128.                 workbook = null;
129.                 excel = null;
130.                 worksheet = null;
131.
132.                 GC.Collect();
133.
134.                 System.IO.FileInfo file = new System.IO.FileInfo(filepath);
135.                 Response.Clear();
136.                 Response.Charset = "GB2312";
137.                 Response.ContentEncoding = System.Text.Encoding.UTF8;
138.                 // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
139.                 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(getDepShortName(tmpdepid)+".xls"));
140.                 // 添加头信息，指定文件大小，让浏览器能够显示下载进度
141.                 Response.AddHeader("Content-Length", file.Length.ToString());
142.
143.                 // 指定返回的是一个不能被客户端读取的流，必须被下载
144.                 Response.ContentType = "application/ms-excel";
145.
146.                 // 把文件流发送到客户端
147.                 //Response.WriteFile(file.FullName);
148.                 Response.TransmitFile(filepath);
149.                 // 停止页面的执行
150.
151.                 Response.End();
下面这段代码是DataGridview导出excel；
//导出方法 public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "Export Excel File";
            saveFileDialog.ShowDialog();
            if (saveFileDialog.FileName == "")
                return true;
            Stream myStream;
            myStream = saveFileDialog.OpenFile();
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
            string str = "";
            try
            {
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    if (i > 0)
                    {
                        str += "\t";
                    }
                    str += dataGridView1.Columns[i].HeaderText;
                }
                sw.WriteLine(str);
                for (int j = 0; j < dataGridView1.Rows.Count; j++)
                {
                    string tempStr = "";
                    for (int k = 0; k < dataGridView1.Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            tempStr += "\t";
                        }
                        tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString();
                    }
                    sw.WriteLine(tempStr);
                }
                sw.Close();
                myStream.Close();
                MessageBox.Show("导出Excel成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sw.Close();
                myStream.Close();
            }
            return true;
        }//调用
private void button1_Click(object sender, EventArgs e)
        {
            DataGridviewShowToExcel(dataGridView1, true);
        }
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=gbk"/>
    <title>WEB页面导出为EXCEL文档的方法</title>
</head>
<body>
<table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0">
    <tr>
        <td colspan="5" align="center">WEB页面导出为EXCEL文档的方法</td>
    </tr>
    <tr>
        <td>列标题1</td>
        <td>列标题2</td>
        <td>列标题3</td>
        <td>列标题4</td>
        <td>列标题5</td>
    </tr>
    <tr>
        <td>aaa</td>
        <td>bbb</td>
        <td>ccc</td>
        <td>ddd</td>
        <td>eee</td>
    </tr>
    <tr>
        <td><div>AAA</div></td>
        <td>BBB</td>
        <td>CCC</td>
        <td>DDD</td>
        <td>EEE</td>
    </tr>
    <tr>
        <td>FFF</td>
        <td>GGG</td>
        <td>HHH</td>
        <td>III</td>
        <td>JJJ</td>
    </tr>
</table>
<input type="button" onclick="javascript:method1('tableExcel');" value="导入到EXCEL">
<SCRIPT LANGUAGE="javascript">
var idTmr ;
function method1(tableid) {//整个表格拷贝到EXCEL中     var curTbl = document.getElementById(tableid);
    var oXL = new ActiveXObject("Excel.Application");
    //创建AX对象excel
    var oWB = oXL.Workbooks.Add();
    //获取workbook对象
    var xlsheet = oWB.Worksheets(1);
    //激活当前sheet
    var sel = document.body.createTextRange();
    sel.moveToElementText(curTbl);
    //把表格中的内容移到TextRange中
    sel.select();
    //全选TextRange中内容
    sel.execCommand("Copy");
    //复制TextRange中内容
    xlsheet.Paste();
    //粘贴到活动的EXCEL中
    oXL.Visible = true;
    //设置excel可见属性 try{
var fname = oXL.Application.GetSaveAsFilename("save.xls", "Excel Spreadsheets (*.xls), *.xls");
if(fname){
oWB.SaveAs(fname);
}

}catch(e){
print("Nested catch caught " + e);
}finally{

oWB.Close(savechanges=false);
oXL.Quit();
oXL=null;
//结束excel进程，退出完成
idTmr = window.setInterval("Cleanup();",1);
}
}
function Cleanup() {
    window.clearInterval(idTmr);    CollectGarbage();
  }
</SCRIPT>
</body>
</html>
注意：客户端导出，只能在IE中使用，并且客户端需要安装Excel。
客户端生成几乎是不太可能的，服务器端也是不需要生成文件，而只要输出流就可以的。建议你用NOPI组件
完全的源代码http://dotnet.aspx.cc/ArticleSearch.aspx?keyword=excel&filter=1
这个是dll 的下载地址 http://u.115.com/file/f8f096039c ExcelLibrary.dll  提取码f8f096039c添加引用后 using ExcelLibrary.Office.Excel;string file = HttpContext.Current.Server.MapPath("/manage/hr/upload/" + HttpUtility.UrlEncode(System.DateTime.Now.ToString()+".xls"));            Workbook workbook = new Workbook();            Worksheet worksheet = new Worksheet("Sheet1");            //for (int col = 0; col < tab.Columns.Count; col++)            //{                //worksheet.Cells[0, col] = new Cell(tab.Columns[col].ColumnName);                worksheet.Cells[0, 0] = new Cell("考勤ID");                worksheet.Cells[0, 1] = new Cell("人员编号");                worksheet.Cells[0, 2] = new Cell("姓名");                worksheet.Cells[0, 3] = new Cell("考勤日期");                worksheet.Cells[0, 4] = new Cell("早上打卡时间");                worksheet.Cells[0, 5] = new Cell("中午打卡时间");                worksheet.Cells[0, 6] = new Cell("下午打卡时间");                worksheet.Cells[0, 7] = new Cell("晚上加班打卡时间");                worksheet.Cells[0, 8] = new Cell("晚上下班打卡时间");                worksheet.Cells[0, 9] = new Cell("打卡方式");                worksheet.Cells[0, 10] = new Cell("登记编号");                worksheet.Cells[0, 11] = new Cell("考勤设备编号");                worksheet.Cells[0, 12] = new Cell("考勤标示");                worksheet.Cells[0, 13] = new Cell("操作员");                worksheet.Cells[0, 14] = new Cell("考勤备注");                worksheet.Cells[0, 15] = new Cell("考勤状态");                worksheet.Cells[0, 16] = new Cell("早上打卡状态");                worksheet.Cells[0, 17] = new Cell("中午打卡状态");                worksheet.Cells[0, 18] = new Cell("下午打卡状态");                worksheet.Cells[0, 19] = new Cell("晚上加班打卡状态");                worksheet.Cells[0, 20] = new Cell("晚上下班打卡状态");            //}            for (int rowIndex = 0; rowIndex < tab.Rows.Count; rowIndex++)            {                for (int colIndex = 0; colIndex < tab.Columns.Count; colIndex++)                {                    worksheet.Cells[rowIndex + 1, colIndex] = new Cell(tab.Rows[rowIndex][colIndex].ToString());                }            }            workbook.Worksheets.Add(worksheet);            workbook.Save(file);            FileInfo info = new FileInfo(file);            long size = 0;            if (info.Exists)                size = info.Length;            HttpContext.Current.Response.AddHeader("content-type", "application/x-msdownload;");            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(System.DateTime.Now.ToString() + ".xls"));            HttpContext.Current.Response.AddHeader("content-length", size.ToString());            HttpContext.Current.Response.WriteFile(file, 0, size);
刚才打错了，是NPOIhttp://msdn.microsoft.com/zh-tw/ee818993.aspxhttp://npoi.codeplex.com/一个开源软件，看一下例子就明白了，很简单
类似
          HttpContext.Current.Response.AddHeader("content-type", "application/x-msdownload;");            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(System.DateTime.Now.ToString() + ".xls"));            HttpContext.Current.Response.AddHeader("content-length", size.ToString());            HttpContext.Current.Response.WriteFile(file, 0, size);这样的代码就是直接输出流呀