#region 导出Excel/// <summary>/// 导出Excel/// </summary>/// <param name="page">请求的页面this</param>/// <param name="dataTable">导出的数据源</param>/// <param name="fileName">保存文件名称</param>/// <returns>布尔值</returns>public bool ExportExcel(Page page, DataTable dataTable, string fileName){try{HttpContext.Current.Response.Clear();HttpContext.Current.Response.Buffer = true;HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.numberformat:@";page.EnableViewState = false;HttpContext.Current.Response.Charset = "UTF-8";HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//设置输出流为简体中文HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");//输出列名for (int i = 0; i < dataTable.Columns.Count; i++)HttpContext.Current.Response.Write(dataTable.Columns[i].ColumnName + "\t");HttpContext.Current.Response.Write("\r\n");//输出数据for (int i = 0; i < dataTable.Rows.Count; i++){for (int j = 0; j < dataTable.Columns.Count; j++){HttpContext.Current.Response.Write(dataTable.Rows[i][j].ToString() + "\t");}HttpContext.Current.Response.Write("\r\n");}//输出当前缓存内容//HttpContext.Current.Response.Flush();HttpContext.Current.Response.End();return true;}catch{return false;}}#endregion
#region 导出Excel 自定义格式/// <summary>/// 导出Excel /// 1.文本：vnd.ms-excel.numberformat:@/// 2.日期：vnd.ms-excel.numberformat:yyyy/mm/dd/// 3.数字：vnd.ms-excel.numberformat:#,##0.00/// 4.货币：vnd.ms-excel.numberformat:￥#,##0.00/// 5.百分比：vnd.ms-excel.numberformat: #0.00%/// </summary>/// <param name="fileName"></param>/// <param name="dt"></param>/// <returns></returns>public bool Export(string fileName, DataTable dt){try{HttpResponse resp;resp = System.Web.HttpContext.Current.Response;resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");resp.AppendHeader("Content-Type", "application/ms-excel");StringBuilder colHeaders = new StringBuilder();StringBuilder ls_item = new StringBuilder();DataRow[] myRow = dt.Select();int cl = dt.Columns.Count;colHeaders.Append(" <html><head> \n ");colHeaders.Append(" <meta http-equiv='Content-Type' content='text/html; charset=gb2312' /> \n ");colHeaders.Append(" </head> \n ");colHeaders.Append(" <body> \n ");colHeaders.Append(" <table border='1'> ");colHeaders.Append(" <tr> ");//输出列名for (int i = 0; i < dt.Columns.Count; i++)colHeaders.Append("<td style='background-color:#CCCCCC'>" + dt.Columns[i].ColumnName + "</td>");colHeaders.Append("</tr> ");resp.Write(colHeaders.ToString());foreach (DataRow row in myRow){ls_item.Append("<tr>");for (int i = 0; i < cl; i++){if (i == (cl - 1)){ls_item.Append("<td style='vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>" + "\n");}else{ls_item.Append("<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>");}}ls_item.Append("</tr>");}ls_item.Append(" </table> \n ");ls_item.Append(" </body> \n ");ls_item.Append(" </html>");resp.Write(ls_item.ToString());resp.End();return true;}catch{return false;}}#endregion
/// <summary>/// Aspose插件导出Excel（无需安装office都可以导出）/// </summary>/// <param name="dataTable"></param>/// <param name="fileName"></param>/// <returns></returns>public static bool ExportExcelWithAspose(DataTable dataTable, string fileName){//string path = bine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, "SaveExcel");//if (!Directory.Exists(path))// Directory.CreateDirectory(path);//string fullPath = bine(path, fileName + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls");if (dataTable == null || dataTable.Rows.Count <= 0)return false;try{Aspose.Cells.License lic = new Aspose.Cells.License();Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];cellSheet.Name = dataTable.TableName;int rowIndex = 0;int colIndex = 0;int colCount = dataTable.Columns.Count;int rowCount = dataTable.Rows.Count;Style headStyle = new Style();headStyle.Font.IsBold = true;headStyle.Font.Name = "宋体";//列名的处理for (int i = 0; i < colCount; i++){cellSheet.Cells[rowIndex, colIndex].PutValue(dataTable.Columns[i].ColumnName);cellSheet.Cells[rowIndex, colIndex].SetStyle(headStyle);colIndex++;}Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];style.Font.Name = "Arial";style.Font.Size = 10;Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();cellSheet.Cells.ApplyStyle(style, styleFlag);rowIndex++;for (int i = 0; i < rowCount; i++){colIndex = 0;for (int j = 0; j < colCount; j++){cellSheet.Cells[rowIndex, colIndex].PutValue(dataTable.Rows[i][j].ToString());colIndex++;}rowIndex++;}cellSheet.AutoFitColumns();//服务端下载//fullPath = Path.GetFullPath(fullPath);//workbook.Save(fullPath);//网页端下载workbook.Save(HttpContext.Current.Response, HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)+DateTime.Now.ToString("yyyyMMddHHmm") + ".xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Auto));return true;}catch (Exception ex){return false;}}
#region 导入Excelpublic string ImportExcel(string[] list, string filePath){string isXls = System.IO.Path.GetExtension(filePath).ToLower();//System.IO.Path.GetExtension获得文件的扩展名if (isXls != ".xls")return "请选择Excel文件导入！";DataSet ds = ExecleDataSet(filePath);//调用自定义方法DataRow[] dr = ds.Tables[0].Select();//定义一个DataRow数组int rowsnum = ds.Tables[0].Rows.Count;if (ds.Tables[0].Rows.Count == 0)return "Excel无数据！";return "";}//OleDB连接读取Excel中数据public DataSet ExecleDataSet(string filePath){string OleDbConnection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";OleDbConnection conn = new OleDbConnection(OleDbConnection);conn.Open();DataSet ds = new DataSet();OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);odda.Fill(ds);conn.Close();return ds;}#endregion
