NPOI操作代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】 NPOI操作
作者:Dezai.CN / 发布于2013/5/13/ 708
利用NPOI开源的读写Excel、WORD等微软OLE2组件读写execl，控制样式或单元格

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Web;
	using System.Web.UI;
	using System.Web.UI.WebControls;
	//add
	using 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
	}

	

	

	

	

	

	/// <summary>
	 ///传入ds直接生成excel在服务器目录上
	 /// </summary>
	 /// <param name="ds">ds包含多个DataTable</param>
	 /// <param name="strPath"></param>
	 /// <param name="strFileName"></param>
	 /// <returns></returns>
	 protected static bool ExportExcelByDataSet(DataSet ds, string strPath, string strFileName, string ReportHeader = "")
	 {
	 //NPOI
	 HSSFWorkbook hssfworkbook2 = new HSSFWorkbook();
	 #region 开始循环DS中的Table
	 for (int p = 0; p < ds.Tables.Count; p++)
	 {
	 #region 创建一个sheet
	 HSSFSheet sheet = (HSSFSheet)hssfworkbook2.CreateSheet("sheet" + (p + 1));
	 //定义字体 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
	 }
	 #endregion

	 //获取用户选择路径
	 string ReportPath = strPath + strFileName;
	 //创建excel
	 System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create);
	 hssfworkbook2.Write(file3);
	 file3.Close();
	 return true;
	 }
试试其它关键字
　NPOI　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)