GetExcelData.cs

当前位置 : 首页 > 网页制作 > CSS > GetExcelData.cs

GetExcelData.cs

来源: 作者: 时间:2016-01-25 10:02
using System;using System Collections Generic; Listusing System Data;using System Data OleDb;using System Diagnostics; Processusing System IO;using System Reflection; Missing
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;
	}
	
}

Tag:
网友评论

<