第一种比较实用，Insert添加数据
/// <summary> /// 导出数据到Excel并保存 /// </summary> /// <param name="ds">DataSet</param> /// <param name="filePath">文件路径</param> /// <param name="columName">excel列名 逗号分割的字符串</param> /// <param name="tableName">数据库列名 逗号分割字符串 </param> /// <returns></returns> public static bool ExportReportEmail(DataSet ds, string filePath,string columName, string tableName) { if (ds != null && ds.Tables.Count > 0) { System.Data.DataTable sourceTable = ds.Tables[0]; // 使用OleDb驱动程序连接到副本 OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"); using (conn) { conn.Open();// if (sourceTable != null && sourceTable.Rows.Count > 0) { string[] colums = columName.Split(','); string[] tables = tableName.Split(','); for (int i = 0; i < sourceTable.Rows.Count; i++) { // 增加记录 OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$](" + columName + ") VALUES(" + tableName + ")", conn);
//循环给列 指定参数 for (int j = 0; j < colums.Length;j++ ) { cmd.Parameters.AddWithValue(tables[j].Trim(), sourceTable.Rows[i][colums[j].Trim()].ToString()); // } cmd.ExecuteNonQuery(); } } } return true; }else return false; }
第二种 写入文件
//这种写入的文件，其实后缀是xls的 文本文档
try { long rowRead = 0; StreamWriter sw = new StreamWriter(Pathname, false, Encoding.GetEncoding("gb2312")); StringBuilder sb = new StringBuilder(); for (int k = 0; k < sourceTable.Columns.Count; k++) { sb.Append(sourceTable.Columns[k].ColumnName.ToString() + " "); } sb.Append(Environment.NewLine); for (int i = 0; i < sourceTable.Rows.Count; i++) { rowRead++; for (int j = 0; j < sourceTable.Columns.Count; j++) { sb.Append(sourceTable.Rows[i][j].ToString() + " "); } sb.Append(Environment.NewLine); } sw.Write(sb.ToString()); sw.Flush(); sw.Close(); return true; } catch (Exception ex) { return false; }
第三种 HSSFWorkbook
using Microsoft.Office.Interop.Excel; using NPOI.HSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.IO; using System.Linq; using System.Reflection; using System.Text; using System.Web; namespace JW.Shop.Common { public class NopiHelper { #region 导出到Excel(仅支持Excel1997-2003) /// <summary> /// 由DataTable导出Excel /// </summary> /// <param name="sourceTable">要导出的数据源</param> /// <param name="sheetName">工作表名</param> /// <param name="columnNames">工作表列名</param> /// <returns>Excel工作表</returns> private static Stream ExportDataTableToExcel(System.Data.DataTable sourceTable, string sheetName, string columnNames) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = workbook.CreateSheet(sheetName) as HSSFSheet; HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; // handling header. if (!string.IsNullOrEmpty(columnNames)) { string[] cols = columnNames.Split(','); for (int i = 0; i < sourceTable.Columns.Count; i++) { headerRow.CreateCell(sourceTable.Columns[i].Ordinal).SetCellValue(cols[i]); } } else { foreach (DataColumn column in sourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } // handling value. int rowIndex = 1; foreach (DataRow row in sourceTable.Rows) { HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return ms; } /// <summary> /// 导出数据到Excel并下载(仅支持Excel1997-2003) /// </summary> /// <param name="sourceTable">要导出的数据源</param> /// <param name="sheetName">Excel的工作表名</param> /// <param name="columnNames">工作表列名(逗号分隔)</param> /// <param name="fileName">下载保存Excel的文件名</param> /// <returns>Excel工作表</returns> public static bool ExportDownloadExcel(System.Data.DataTable sourceTable, string sheetName, string columnNames, string fileName) { try { MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName, columnNames) as MemoryStream; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8)); HttpContext.Current.Response.ContentType = "application/ms-excel"; // application/octet-stream HttpContext.Current.Response.HeaderEncoding = Encoding.UTF8;// System.Text.Encoding.GetEncoding("GB2312"); HttpContext.Current.Response.ContentEncoding = Encoding.UTF8; HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.Flush(); //HttpContext.Current.Response.End(); ms.Close(); ms = null; return true; } catch (Exception) { return false; } } /// <summary> /// 由DataSet导出Excel /// </summary> /// <param name="sourceDs">要导出的数据源</param> /// <param name="sheetName">工作表名</param> /// <param name="columnNames">工作表列名</param> /// <returns>Excel工作表</returns> private static Stream ExportDataSetToExcel(DataSet sourceDs, string[] sheetNames, string[] columnNames) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); //string[] sheetNames = sheetName.Split(','); for (int i = 0; i < sheetNames.Length; i++) { HSSFSheet sheet = workbook.CreateSheet(sheetNames[i]) as HSSFSheet; HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; // handling header. if (columnNames.Length > 0 && (!string.IsNullOrEmpty(columnNames[i]))) { string[] cols = columnNames[i].Split(','); for (int j = 0; j < cols.Length; j++) { headerRow.CreateCell(sourceDs.Tables[i].Columns[j].Ordinal).SetCellValue(cols[j]); } } else { foreach (DataColumn column in sourceDs.Tables[i].Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } // handling value. int rowIndex = 1; foreach (DataRow row in sourceDs.Tables[i].Rows) { HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; if (columnNames.Length > 0 && (!string.IsNullOrEmpty(columnNames[i]))) { string[] cols = columnNames[i].Split(','); for (int j = 0; j < cols.Length; j++) { dataRow.CreateCell(sourceDs.Tables[i].Columns[j].Ordinal).SetCellValue(row[j].ToString()); } } else { foreach (DataColumn column in sourceDs.Tables[i].Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } } rowIndex++; } } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; return ms; } /// <summary> /// 导出数据到Excel并下载(仅支持Excel1997-2003) /// </summary> /// <param name="sourceDs">要导出数据的DataTable</param> /// <param name="sheetName">工作表名</param> /// <param name="columnNames">工作表列名</param> /// <param name="fileName">指定Excel工作表名称</param> /// <returns>Excel工作表</returns> public static bool ExportDownloadExcel(DataSet sourceDs, string[] sheetName, string[] columnNames, string fileName) { try { MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName, columnNames) as MemoryStream; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8)); HttpContext.Current.Response.ContentType = "application/ms-excel"; // application/octet-stream HttpContext.Current.Response.HeaderEncoding = Encoding.UTF8;// System.Text.Encoding.GetEncoding("GB2312"); HttpContext.Current.Response.ContentEncoding = Encoding.UTF8; HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.Flush(); //HttpContext.Current.Response.End(); ms.Close(); ms = null; return true; } catch (Exception) { return false; } } #endregion } }