excel导出

第一种导出

 

     private void ExportExcel(DataTable exceldt)
        {
            string FileName = "";
            FileName = "投诉建议" + DateTime.Now.ToString("MMddHHmm");
            string bname = GetClientBrowserVersions().ToLower();
            if (bname.Contains("firefox"))
            {
            }
            else if (bname.Contains("ie"))
            {
                FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
            }
            else
            {
                FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
            }

            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
            string colHeaders = "", ls_item = "";
            DataRow[] myRow = exceldt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int cl = exceldt.Columns.Count;
            for (int i = 0; i < 5; i++)
            {
                if (i == 0)
                {
                    colHeaders += "姓名\t";
                }
                else if (i == 1)
                {
                    colHeaders += "域账号\t";
                }
                else if (i == 2)
                {
                    colHeaders += "部门\t";
                }
                else if (i == 3)
                {
                    colHeaders += "录入时间\t";
                }
                else if (i == 4)
                {
                    colHeaders += "建议内容\n";
                }
            }
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息 
            //逐行处理数据　　
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据　　 
                for (int i = 0; i < 5; i++)
                {
                    if (i == 0)
                    {
                        ls_item += row["name"] + "\t";
                    }
                    else if (i == 1)
                    {
                        ls_item += row["account"] + "\t";
                    }
                    else if (i == 2)
                    {
                        ls_item += row["department"] + "\t";
                    }
                    else if (i == 3)
                    {
                        ls_item += row["add_time"] + "\t";
                    }
                    else if (i == 4)
                    {
                        ls_item += Utils.DropHTML(row["suggestion"].ToString()) + "\n";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";
            }
            resp.End();
        }

 第二种方法

  public static bool ExportExcelWithAspose(DataTable dt, string path)
        {
            bool succeed = false;
            if (dt != null)
            {
                try
                {
                    Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                    Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
                    //为单元格添加样式      
                    Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
                    //设置居中  
                    style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                    //设置背景颜色  
                    style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
                    style.Pattern = BackgroundType.Solid;
                    style.Font.IsBold = true;
                    int rowIndex = 0;
                    int colIndex = 0;
                    int colCount = dt.Columns.Count;
                    int rowCount = dt.Rows.Count;
                    //列名的处理  
                    Aspose.Cells.Style cellstyle = new Aspose.Cells.Style();
                    cellstyle.Font.IsBold = true;
                    cellstyle.Font.Name = "宋体";
                    for (int i = 0; i < colCount; i++)
                    {
                        cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
                        cellSheet.Cells[rowIndex, colIndex].SetStyle(cellstyle);
                        colIndex++;
                    }
                    rowIndex++;
                    for (int i = 0; i < rowCount; i++)
                    {
                        colIndex = 0;
                        for (int j = 0; j < colCount; j++)
                        {
                            cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
                            colIndex++;
                        }
                        rowIndex++;
                    }
                    cellSheet.AutoFitColumns();
                    path = Path.GetFullPath(path);
                    workbook.Save(path);
                    succeed = true;
                }
                catch (Exception ex)
                {
                    succeed = false;
                }
            }
            return succeed;
        }