GetExcelData.cs - html/css语言栏目：html.css - 自学php

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; } }