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("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");
		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<string> chars = new List<string>();
		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;
		}
	}
	
}