利用NPOI开源的读写Excel、WORD等微软OLE2组件读写execl，控制样式或单元格
来源：互联网 发布：哑铃锻炼软件下载 编辑：程序博客网 时间：2024/06/05 06:48
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;//addusing System.Data;using System.IO;using NPOI;using NPOI.HSSF.UserModel;public partial class ExeclOperation : System.Web.UI.Page{ #region 页面加载 protected void Page_Load(object sender, EventArgs e) { DataTable dt = ReadExcelToDataTable("~/xls/demo001.xls", 0, 0); ViewState["dtview"] = dt; GridView1.DataSource = dt; GridView1.DataBind(); } #endregion #region DS直接生成Execl protected void btnExport_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); DataTable dt = ViewState["dtview"] as DataTable; ds.Tables.Add(dt); bool success = ExportExcelByDataSet(ds, "~/xls/", "demo.xls", "这是测试数据"); if (success) { ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "alert1", "alert('生成execl文件成功')", true); } else { ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "alert2", "alert('生成execl文件失败')", true); } } #endregion #region GridView自动列适应，不换行 protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow || e.Row.RowType == DataControlRowType.Header) { TableCellCollection cells1 = e.Row.Cells; for (int i = 0; i < cells1.Count; i++) { cells1[i].Wrap = false; } } } #endregion #region DataSet与Execl互转 /// <summary> /// 传入ds直接生成excel文件 /// </summary> /// <param name="ds">DataSet</param> /// <param name="strPath">文件路径</param> /// <param name="strFileName">文件名</param> /// <param name="ReportHeader">execl表头</param> /// <returns></returns> public static bool ExportExcelByDataSet(DataSet ds, string strPath, string strFileName, string ReportHeader = "") { //NPOI HSSFWorkbook hssfworkbook2 = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)hssfworkbook2.CreateSheet("sheet1"); //定义字体 font 设置字体类型和大小 HSSFFont font = (HSSFFont)hssfworkbook2.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 11; //定义单元格格式；单元格格式style1 为font的格式 HSSFCellStyle style1 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle(); style1.SetFont(font); style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; HSSFCellStyle style2 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle(); style2.SetFont(font); style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; //style2.WrapText = true; //设置大标题行 int RowCount = 0; int arrFlag = 0; string TileName1 = ""; string TileName2 = ""; string s = ReportHeader; string[] sArray = s.Split('|'); if (ReportHeader != "") { foreach (string i in sArray) { string str1 = i.ToString(); string[] subArray = str1.Split('@'); foreach (string k in subArray) { Console.WriteLine(k.ToString()); if (arrFlag == 0) { TileName1 = k.ToString(); } else { TileName2 = k.ToString(); } arrFlag = arrFlag + 1; } HSSFRow row0 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列 row0.CreateCell(0).SetCellValue(TileName1); row0.CreateCell(1).SetCellValue(TileName2); RowCount = RowCount + 1; arrFlag = 0; } } //设置全局列宽和行高 sheet.DefaultColumnWidth = 14;//全局列宽 sheet.DefaultRowHeightInPoints = 15;//全局行高 //设置标题行数据 int a = 0; string mColumnName = ""; HSSFRow row1 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列 for (int k = 0; k < ds.Tables[0].Columns.Count; k++) { mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString(); row1.CreateCell(a).SetCellValue(mColumnName); row1.Cells[a].CellStyle = style2; a++; } //填写ds数据进excel for (int i = 0; i < ds.Tables[0].Rows.Count; i++)//写6行数据 { HSSFRow row2 = (HSSFRow)sheet.CreateRow(i + RowCount + 1); int b = 0; for (int j = 0; j < ds.Tables[0].Columns.Count; j++) { string DgvValue = ""; DgvValue = ds.Tables[0].Rows[i][j].ToString(); ; row2.CreateCell(b).SetCellValue(DgvValue); b++; } } //获取用户选择路径 string ReportPath = HttpContext.Current.Server.MapPath(strPath + strFileName); //创建excel System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create); hssfworkbook2.Write(file3); file3.Close(); return true; } /// <summary> /// 用NPOI直接读取excel返回DataTable /// </summary> /// <param name="ExcelFileStream">文件流</param> /// <param name="SheetIndex">Sheet序号</param> /// <param name="StartRowIndex">开始行号</param> /// <returns></returns> public static DataTable ReadExcelToDataTable(Stream ExcelFileStream, int SheetIndex, int StartRowIndex) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex); DataTable table = new DataTable(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(StartRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; for (int i = (StartRowIndex + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } table.Rows.Add(dataRow); } ExcelFileStream.Close(); workbook = null; sheet = null; return table; } /// <summary> /// 用NPOI直接读取excel返回DataTable /// </summary> /// <param name="FilePath">文件路径</param> /// <param name="SheetIndex">Sheet序号</param> /// <param name="StartRowIndex">开始行号</param> /// <returns></returns> public static DataTable ReadExcelToDataTable(string FilePath, int SheetIndex, int StartRowIndex) { DataSet ds = new DataSet(); FileStream fs = File.Open(HttpContext.Current.Server.MapPath(FilePath), FileMode.Open); DataTable dt = ReadExcelToDataTable(fs, SheetIndex, StartRowIndex); return dt; } #endregion }
#region DataSet与Execl互转 /// <summary> /// 传入ds直接生成excel文件 /// </summary> /// <param name="ds">DataSet</param> /// <param name="strPath">文件路径</param> /// <param name="ReportHeader">execl表头</param> /// <returns></returns> public static bool ExportExcelByDataSet(DataSet ds, string strPath, string ReportHeader = "") { //NPOI HSSFWorkbook hssfworkbook2 = new HSSFWorkbook(); #region 循环开始 for (int p = 0; p < ds.Tables.Count; p++) { int t = p + 1; HSSFSheet sheet = (HSSFSheet)hssfworkbook2.CreateSheet("page" + t); //定义字体 font 设置字体类型和大小 HSSFFont font = (HSSFFont)hssfworkbook2.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 11; //定义单元格格式；单元格格式style1 为font的格式 HSSFCellStyle style1 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle(); style1.SetFont(font); style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; HSSFCellStyle style2 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle(); style2.SetFont(font); style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; //style2.WrapText = true; //设置大标题行 int RowCount = 0; int arrFlag = 0; string TileName1 = ""; string TileName2 = ""; string s = ReportHeader; string[] sArray = s.Split('|'); if (ReportHeader != "") { foreach (string i in sArray) { string str1 = i.ToString(); string[] subArray = str1.Split('@'); foreach (string k in subArray) { Console.WriteLine(k.ToString()); if (arrFlag == 0) { TileName1 = k.ToString(); } else { TileName2 = k.ToString(); } arrFlag = arrFlag + 1; } HSSFRow row0 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列 row0.CreateCell(0).SetCellValue(TileName1); row0.CreateCell(1).SetCellValue(TileName2); RowCount = RowCount + 1; arrFlag = 0; } } //设置全局列宽和行高 sheet.DefaultColumnWidth = 14;//全局列宽 sheet.DefaultRowHeightInPoints = 15;//全局行高 //设置标题行数据 int a = 0; string mColumnName = ""; HSSFRow row1 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列 for (int k = 0; k < ds.Tables[p].Columns.Count; k++) { mColumnName = ds.Tables[p].Columns[k].ColumnName.ToString(); row1.CreateCell(a).SetCellValue(mColumnName); row1.Cells[a].CellStyle = style2; a++; } //填写ds数据进excel for (int i = 0; i < ds.Tables[p].Rows.Count; i++)//写6行数据 { HSSFRow row2 = (HSSFRow)sheet.CreateRow(i + RowCount + 1); int b = 0; for (int j = 0; j < ds.Tables[p].Columns.Count; j++) { string DgvValue = ""; DgvValue = ds.Tables[p].Rows[i][j].ToString(); ; row2.CreateCell(b).SetCellValue(DgvValue); b++; } } } #endregion //获取用户选择路径 string ReportPath = (strPath); //创建excel System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create); hssfworkbook2.Write(file3); file3.Close(); return true; } /// <summary> /// 用NPOI直接读取excel返回DataSet /// </summary> /// <param name="ExcelFileStream">FileStream fs = File.Open(dlg.FileName, FileMode.Open);</param> /// <param name="SheetCount">sheet数量</param> /// <returns></returns> public static DataSet ReadExcelToDataSet(Stream ExcelFileStream,int SheetCount) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); DataSet ds = new DataSet(); for (int k = 0; k < SheetCount; k++) { HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(k); DataTable table = new DataTable("table" + k); HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { string columnName = headerRow.GetCell(i).StringCellValue; DataColumn column = new DataColumn(columnName); if (!table.Columns.Contains(columnName)) { table.Columns.Add(column); } } int rowCount = sheet.LastRowNum; for (int i = (0 + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j); } table.Rows.Add(dataRow); } sheet = null; ds.Tables.Add(table); } workbook = null; ExcelFileStream.Close(); return ds; } #endregion
