using System;
using System.Collections.Generic;// List
using System.Data;// DataTable
using System.Data.OleDb;// OleDbConnection,OleDbDataAdapter
using System.IO;// FileStream
using System.Text.RegularExpressions;// Regex
using System.Web;// HttpResponse
using System.Web.UI;// HtmlTextWriter
using System.Web.UI.WebControls;// GridView
using NPOI.HSSF.UserModel;// HSSFWorkbook,HSSFSheet,HSSFRow,HSSFCell
using NPOI.SS.UserModel;// IWorkbook,ISheet,IRow,ICell
using NPOI.SS.Util;// CellRangeAddress
/*/--------------------------------------------------------------------------------//
// GetExcelNpoi 的摘要说明
// 适用于 NPOI 2.0 版本
//--------------------------------------------------------------------------------/*/
public class GetExcelNpoi : System.Web.UI.Page
{
// 构造函数
public GetExcelNpoi() { }
// GridView 导出 Excel
// 注意: 使用此函数要定义事件 public override void VerifyRenderingInServerForm(Control control){}
public void exportExcel(HttpResponse pageResponse,GridView drawGridView,string f_FileName)
{
pageResponse.Clear();
pageResponse.Buffer = false;
pageResponse.Charset = "GB2312";
pageResponse.AppendHeader("Content-Disposition", "attachment;filename="+f_FileName+".xls");
pageResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
pageResponse.ContentType = "application/ms-excel";
pageResponse.Write("");
EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
drawGridView.RenderControl(oHtmlTextWriter);
pageResponse.Write(oStringWriter.ToString());
pageResponse.End();
}
// 索引字符转数字
public static int toIndex(string columnName)
{
int index = 0;
if (!Regex.IsMatch(columnName, @"^[A-Za-z]+$"))
{
return -1;
}
char[] chars = columnName.ToUpper().ToCharArray();
for (int i = 0; i < chars.Length; i++)
{
index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
}
return index - 1;
}
// 索引数字转字符
public static string toColName(int index)
{
if (index < 0) return "";
List chars = new List();
do
{
if (chars.Count > 0) index--;
chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
index = (int)((index - index % 26) / 26);
}
while (index > 0);
return String.Join(string.Empty, chars.ToArray());
}
// 从Excel导出到DataSet
public DataSet getDataSet(string f_FilePath,string f_SheetName)
{
DataSet ds = new DataSet();
string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + f_FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
try
{
OleDbConnection objConn = new OleDbConnection(strConnect);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + f_SheetName + "$]", strConnect);
objConn.Open();
objDA.Fill(ds, f_SheetName + "$");
objConn.Close();
}
catch
{
}
return ds;
}
// 从Excel导出到DataTable
public System.Data.DataTable getDataTable(string f_FilePath,string f_SheetName)
{
System.Data.DataTable dt = new System.Data.DataTable();
string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + f_FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
try
{
OleDbConnection objConn = new OleDbConnection(strConnect);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + f_SheetName + "$]", strConnect);
objConn.Open();
objDA.Fill(dt);
objConn.Close();
}
catch
{
}
return dt;
}
// 创建工作簿
public HSSFWorkbook createWorkbook()
{
HSSFWorkbook wb = new HSSFWorkbook();
return wb;
}
// 打开工作簿
public HSSFWorkbook openWorkbook(string f_FilePath)
{
FileStream fs = File.Open(f_FilePath,FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(fs);
fs.Close();
return wb;
}
// 保存工作簿
public void saveWorkbook(string f_FilePath,HSSFWorkbook f_Workbook)
{
FileStream fs = File.Open(f_FilePath,FileMode.OpenOrCreate);
f_Workbook.Write(fs);
fs.Close();
}
// 创建表
public ISheet createSheet(HSSFWorkbook f_Workbook,string f_SheetName)
{
return f_Workbook.CreateSheet(f_SheetName);
}
// 打开表,重载
public ISheet openSheet(HSSFWorkbook f_Workbook,string f_SheetName)
{
int index = f_Workbook.GetSheetIndex(f_SheetName);
return index>=0 ? f_Workbook.GetSheetAt(index) : null;
}
// 打开表,重载
public ISheet openSheet(HSSFWorkbook f_Workbook,int f_SheetIndex)
{
int index = f_SheetIndex;
return index>=0 ? f_Workbook.GetSheetAt(index) : null;
}
// 获取指定索引表名
public string getSheetName(HSSFWorkbook f_Workbook,int f_SheetIndex)
{
return f_Workbook.GetSheetName(f_SheetIndex);
}
// 获取指定表名索引
public int getSheetIndex(HSSFWorkbook f_Workbook,string f_SheetName)
{
return f_Workbook.GetSheetIndex(f_SheetName);
}
// 统计表数
public int countSheet(HSSFWorkbook f_Workbook)
{
return f_Workbook.NumberOfSheets;
}
// 统计行数
public int countRow(ISheet f_Sheet)
{
int result = f_Sheet.LastRowNum;
return result+1;
}
// 统计列数
public int countColumn(ISheet f_Sheet)
{
int result = 0;
int rowCount = f_Sheet.LastRowNum;
for (int i=0;i<=rowCount;i++)
{
try
{
int colCount = f_Sheet.GetRow(i).LastCellNum;
result = colCount>result ? colCount : result;
}
catch
{
}
}
return result;
}
// 读取单元格,行列从1开始
public string getCell(ISheet f_Sheet,int f_RowIndex,int f_ColIndex)
{
if ((f_RowIndex < 1) || (f_ColIndex < 1)) return "";
int rowIndex = f_RowIndex - 1;
int colIndex = f_ColIndex - 1;
ICell cell = f_Sheet.GetRow(rowIndex).GetCell(colIndex);
return cell.ToString();
}
// 写入单元格,行列从1开始
public bool setCell(ISheet f_Sheet,int f_RowIndex,int f_ColIndex,string f_Value)
{
bool result = true;
if ((f_RowIndex < 1) || (f_ColIndex < 1)) return false;
int rowIndex = f_RowIndex - 1;
int colIndex = f_ColIndex - 1;
int rowNum = f_Sheet.PhysicalNumberOfRows;
if (f_RowIndex > rowNum)
{
for (int i = rowNum;i < (f_RowIndex - rowNum);i++)
{
f_Sheet.CreateRow(i);
}
}
int colNum = f_Sheet.GetRow(rowIndex).PhysicalNumberOfCells;
if (f_ColIndex > colNum)
{
for (int i = colNum;i < (f_ColIndex - colNum);i++)
{
f_Sheet.GetRow(rowIndex).CreateCell(i);
}
}
try
{
ICell cell = f_Sheet.GetRow(rowIndex).GetCell(colIndex);
cell.SetCellValue(f_Value);
}
catch
{
result = false;
}
return result;
}
// 设定合并单元格,行列从1开始
public bool setMergeCell(ISheet f_Sheet,int f_sRow,int f_eRow,int f_sCol,int f_eCol)
{
bool result = false;
if (0 <= f_Sheet.AddMergedRegion(new CellRangeAddress(f_sRow - 1,f_eRow - 1,f_sCol - 1,f_eCol - 1)))
{
result = true;
}
return result;
}
// 获取单元格信息,行列从1开始
// 调用时要在输出变量前加 out
public void getCellInfo(ISheet f_Sheet,int f_RowIndex,int f_ColIndex,out int f_RowSpan,out int f_ColSpan,out bool f_isMergeCell)
{
f_RowSpan = 0;
f_ColSpan = 0;
f_isMergeCell = false;
if ((f_RowIndex < 1) || (f_ColIndex < 1)) return;
int rowIndex = f_RowIndex - 1;
int colIndex = f_ColIndex - 1;
int regionsCuont = f_Sheet.NumMergedRegions;
f_RowSpan = 1;
f_ColSpan = 1;
for (int i = 0; i < regionsCuont; i++)
{
CellRangeAddress range = f_Sheet.GetMergedRegion(i);
f_Sheet.IsMergedRegion(range);
if (range.FirstRow == rowIndex && range.FirstColumn == colIndex)
{
f_RowSpan = range.LastRow - range.FirstRow + 1;
f_ColSpan = range.LastColumn - range.FirstColumn + 1;
break;
}
}
try
{
f_isMergeCell = f_Sheet.GetRow(rowIndex).GetCell(colIndex).IsMergedCell;
}
catch
{
f_isMergeCell = false;
}
}
}
阅读(454) | 评论(0) | 转发(0) |
