宇文辑录

#region 最简单的导出Excel        public void CreateExcel(DataTable _table, string FileName)        {            //FileName = Server.UrlEncode(FileName);  &nbsp;         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        #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

---

[code]/// <summary>        /// 将指定Datatable的数据导出到指定路径下的Excel表格        /// </summary>        /// <param name="table">要导出的数据表格</param>        /// <param name="path">保存的路径(包含文件名)</param>        /// <returns>是够导出成功</returns>        public static bool TableToExcel(System.Data.DataTable table, string path)        {            bool isSuccess = true;            object miss = Missing.Value;            Application excelApp = new Application();            excelApp.Workbooks.Add(miss);            try            {                Worksheet workSheet = (Worksheet)excelApp.Worksheets[1];                int rowCount = table.Rows.Count + 1;                int colCount = table.Columns.Count;                object[,] dataArray = new object[rowCount, colCount];                for (int i = 0; i < colCount; i++)                {                    dataArray[0, i] = table.Columns[i].ToString();                    for (int j = 0; j < table.Rows.Count; j++)                    {                        dataArray[(j + 1), i] = table.Rows[j][i].ToString();                    }                }                workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[rowCount, colCount]).Value2 = dataArray;                Workbook workBook = excelApp.Workbooks[1];                workBook.SaveAs(path, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);                workBook.Close(false, miss, miss);                workBook = null;            }            catch             {                isSuccess = false;            }            finally            {                excelApp.Quit();                excelApp = null;                GC.Collect();                KillExcelProcess();//结束excel进程            }            return isSuccess;        }        /// <summary>         /// 强制结束Excel进程        /// </summary>         public static void KillExcelProcess()        {            int ProceedingCount = 0;            try            {                System.Diagnostics.Process[] ProceddingCon = System.Diagnostics.Process.GetProcesses();                foreach (System.Diagnostics.Process IsProcedding in ProceddingCon)                {                    if (IsProcedding.ProcessName.ToUpper() == "EXCEL")                    {                        ProceedingCount += 1;                        IsProcedding.Kill();                    }                }            }            catch (System.Exception err)            {            }        }[/code]

[code]  //导出Excel表格        protected void Bt_Export_Click(object sender, EventArgs e)        {            DataTable table = (DataTable)Session["result"];                 //我是从session里取得table数据，你可以自己定制datatable            string excelPath =@"text.xlsx";//服务器缓存的路径            if (TableToExcel(table, excelPath))//调用方法写入Excel            {                              string fileName ="查询结果.xlsx";//客户端保存的文件名                      FileInfo fileInfo = new FileInfo(excelPath);                            Response.Clear();                Response.ClearContent();                Response.ClearHeaders();                Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(fileName));                Response.AddHeader("Content-Length", fileInfo.Length.ToString());                Response.AddHeader("Content-Transfer-Encoding", "binary");                Response.ContentType = "application/octet-stream";                Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");                Response.WriteFile(fileInfo.FullName);                Response.Flush();                Response.End();                   //下载完成后删除服务器端Excel表格                DeleteExcel(excelPath);            }            else            {                Response.Write("alert('导出失败！')");            }                           }[/code]由于客户端不一定能够装有office相应组件，所以我的策略是先在服务器端写入一个Excel到指定目录，给客户端下载之后，再把服务器文件删除
[code]/// <summary>        /// 删除指定路径的Excel        /// </summary>        /// <param name="deletePath">要删除文件的路径</param>        /// <returns>是否删除成功</returns>        public static bool DeleteExcel(string deletePath)        {            File.Delete(deletePath);            return true;        }


[/code]