利用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;//demousing System.Data;using System.Data.OracleClient;using NPOI;using NPOI.HSSF.UserModel;using System.IO;namespace Util{ /// <summary> /// 利用NPOI组件读写Execl /// </summary> public class XmlExcelReport { /// <summary> ///传入ds直接生成excel在服务器目录上 /// </summary> /// <param name="dt"></param> /// <param name="strPath"></param> /// <param name="strFileName"></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; //设置大标题行 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 = 16;//全局列宽 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 = 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"></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; } }}