using System;
using System.Collections.Generic;// List
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;// Process
using System.IO;
using System.Reflection;// Missing.Value
using System.Text.RegularExpressions;// Regex
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ExcelCOM = Microsoft.Office.Interop.Excel;// 启用 Excel 组件
/*/--------------------------------------------------------------------------------//
// GetExcelData 的摘要说明
//--------------------------------------------------------------------------------/*/
public class GetExcelData : System.Web.UI.Page
{
// 构造函数
public GetExcelData()
{
killExcel();// 结束所有 Excel 进程
}
// 结束进程
public bool killExcel()
{
try
{
Process[] myProcesses;
myProcesses = Process.GetProcessesByName("Excel");
// 结束所有 Excel 进程
foreach(Process myProcess in myProcesses)
{
myProcess.Kill();
}
return true;
}
catch
{
return false;
}
}
// 索引字符转数字
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 null;
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 conn = new OleDbConnection(strConnect);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + f_SheetName + "$]", strConnect);
conn.Open();
oada.Fill(ds,f_SheetName + "$");
conn.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 conn = new OleDbConnection(strConnect);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + f_SheetName + "$]", strConnect);
conn.Open();
oada.Fill(dt);
conn.Close();
}
catch
{
}
return dt;
}
// 获取指定编号表名
public string getSheetName(string f_FilePath,int f_SheetNum)
{
System.Data.DataTable dt = new System.Data.DataTable();
string sheetName = null;
string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + f_FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
try
{
OleDbConnection conn = new OleDbConnection(strConnect);
conn.Open();
dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
sheetName = dt.Rows[f_SheetNum][2].ToString();
}
catch
{
}
return sheetName.Substring(0,sheetName.Length-1);
}
// 统计表数
public int countSheet(string f_FilePath)
{
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 conn = new OleDbConnection(strConnect);
conn.Open();
dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
}
catch
{
}
return dt.Rows.Count;
}
// 打开Excel
public ExcelCOM.Worksheet openExcel(ExcelCOM.Application ExcelApp,string f_FilePath,string f_SheetName)
{
ExcelApp.Visible = false;// 后台执行
ExcelApp.DisplayAlerts = false;// 禁止弹出询问提示框
ExcelApp.AlertBeforeOverwriting = false;// 覆盖不提示
try
{
object oMissing = Missing.Value;
ExcelCOM.Workbook myBook = ExcelApp.Workbooks.Open(
f_FilePath,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing
);
ExcelCOM.Worksheet mySheet = myBook.Sheets[f_SheetName] as ExcelCOM.Worksheet;
return mySheet;
}
catch
{
closeExcel(ExcelApp,false);
return null;
}
}
// 关闭Excel
public bool closeExcel(ExcelCOM.Application ExcelApp,bool isSave)
{
if (isSave) ExcelApp.Save();
ExcelApp.Quit();
System.GC.Collect(System.GC.GetGeneration(ExcelApp));
ExcelApp = null;
if(!killExcel())
{
return false;
}
return true;
}
// 读取单元格,行列从1开始
public string getCell(ExcelCOM.Worksheet f_Worksheet,int row,int col)
{
if ((row<=0)||(col<=0)){return null;}
ExcelCOM.Range rangeCell = f_Worksheet.Cells[row,col] as ExcelCOM.Range;
return rangeCell.Text.ToString();
}
// 写入单元格,行列从1开始
public bool setCell(ExcelCOM.Worksheet f_Worksheet,int row,int col,string val)
{
if ((row<=0)||(col<=0)){return false;}
ExcelCOM.Range rangeCell = f_Worksheet.Cells[row,col] as ExcelCOM.Range;
// 判断合并单元格
int rowStart = (bool)rangeCell.MergeCells ? rangeCell.MergeArea.Row : row;
int colStart = (bool)rangeCell.MergeCells ? rangeCell.MergeArea.Column : col;
rangeCell = f_Worksheet.Cells[rowStart,colStart] as ExcelCOM.Range;
rangeCell.Value = val;
return true;
}
// 判断是否为合并单元格
public bool isMergeCell(ExcelCOM.Worksheet f_Worksheet,int row,int col)
{
if ((row<=0)||(col<=0)){return false;}
ExcelCOM.Range rangeCell = (ExcelCOM.Range)f_Worksheet.Cells[row,col];
return (bool)rangeCell.MergeCells;
}
// 获取合并单元格跨行数
public int getMergeRow(ExcelCOM.Worksheet f_Worksheet,int row,int col)
{
if ((row<=0)||(col<=0)){return 0;}
ExcelCOM.Range rangeCell = (ExcelCOM.Range)f_Worksheet.Cells[row,col];
return rangeCell.MergeArea.Rows.Count;
}
// 获取合并单元格跨列数
public int getMergeCol(ExcelCOM.Worksheet f_Worksheet,int row,int col)
{
if ((row<=0)||(col<=0)){return 0;}
ExcelCOM.Range rangeCell = (ExcelCOM.Range)f_Worksheet.Cells[row,col];
return rangeCell.MergeArea.Columns.Count;
}
}
