excel操作类
代码
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
using System.Reflection;
namespace SJJSKY.Windows.Toolkit
{
public class ExcelHelper : IDisposable
{
private static readonly string ConnectionString_2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
private static readonly string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
public static object oMissing = Type.Missing;
private Excel.Application m_App = null; //Excel应用程序对象
private Excel.Workbook m_WorkBook = null; //工作簿
private Excel.Worksheet m_Sheet = null; //sheet对象
private Excel.Workbook m_CopyBook = null;
private Excel.Worksheet m_CopySheet = null;
private int flgCopyMove = 0;
public Excel.Application Application
{
get { return m_App; }
set { m_App = value; }
}
public Excel.Workbook WorkBook
{
get { return m_WorkBook; }
set { m_WorkBook = value; }
}
public Excel.Worksheet Sheet
{
get { return m_Sheet; }
set { m_Sheet = value; }
}
private Excel.Range m_Range;
public ExcelReader(string path)
{
Init(path, 1);
}
public ExcelReader(string path, int sheetIndex)
{
Init(path, sheetIndex);
}
private void Init(string path, int sheetIndex)
{
m_App = new Excel.Application();
m_App.Visible = false;
m_App.DisplayAlerts = false;
OpenWorkbook(path, sheetIndex);
}
public void OpenWorkbook(string path, int sheetIndex)
{
m_WorkBook = m_App.Workbooks.Open(path, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_Sheet = (Excel.Worksheet)m_WorkBook.Sheets[sheetIndex];
}
private void CloseWorkbook(Excel.Workbook workbook, bool saveChanges, string fileName)
{
workbook.Close(saveChanges, fileName, oMissing);
}
public void CloseWorkbook()
{
CloseWorkbook(m_WorkBook, false, "");
NAR(m_WorkBook);
}
public void CloseWorkbook(int bookIndex)
{
SetActiveBook(bookIndex);
CloseWorkbook();
}
public enum BeforeAfter
{
Before,
After
}
/// <summary>
/// 复制工作表，分为两步来做，之后需调用void PasteSheet(BeforeAfter beforeafter)方法
/// </summary>
public void CopySheet()
{
m_CopySheet = m_Sheet;
flgCopyMove = 1;
}
/// <summary>
/// 移动工作表，分为两步来做，之后需调用void PasteSheet(BeforeAfter beforeafter)方法
/// </summary>
public void CutSheet()
{
m_CopySheet = m_Sheet;
flgCopyMove = 2;
}
/// <summary>
/// 复制或移动工作表之后调用此方法
/// </summary>
/// <param name="beforeafter"></param>
public void PasteSheet(BeforeAfter beforeafter)
{
if (flgCopyMove == 1)
{
if (beforeafter == BeforeAfter.Before)
{
m_CopySheet.Copy(m_Sheet, oMissing);
m_Sheet = (Excel.Worksheet)m_Sheet.Previous;
}
else
{
m_CopySheet.Copy(oMissing, m_Sheet);
m_Sheet = (Excel.Worksheet)m_Sheet.Next;
}
flgCopyMove = 0;
}
else if (flgCopyMove == 2)
{
if (beforeafter == BeforeAfter.Before)
{
m_CopySheet.Move(m_Sheet, oMissing);
m_Sheet = (Excel.Worksheet)m_Sheet.Previous;
}
else
{
m_CopySheet.Move(oMissing, m_Sheet);
m_Sheet = (Excel.Worksheet)m_Sheet.Next;
}
flgCopyMove = 0;
}
NAR(m_CopySheet);
}
/// <summary>
/// 重命名工作表
/// </summary>
public void RenameSheet(string sheetName)
{
m_Sheet.Name = sheetName;
}
/// <summary>
/// 设置当前要操作的工作簿
/// </summary>
/// <param name="sheetIndex"></param>
public void SetActiveBook(int bookIndex)
{
m_WorkBook = (Excel.Workbook)m_App.Workbooks[bookIndex];
}
/// <summary>
/// 设置当前要操作的工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void SetActiveSheet(int sheetIndex)
{
m_Sheet = (Excel.Worksheet)m_WorkBook.Sheets[sheetIndex];
}
/// <summary>
/// 设置当前要操作的工作表
/// </summary>
/// <param name="sheetName"></param>
public bool SetActiveSheet(string sheetName)
{
int sheetCount = m_WorkBook.Sheets.Count;
for (int i = 1; i <= sheetCount; i++)
{
Excel.Worksheet oSheet = (Excel.Worksheet)m_WorkBook.Sheets[0];
if (oSheet.Name == sheetName)
{
m_Sheet = oSheet;
return true;
}
}
return false;
}
/// <summary>
/// 设置当前要操作的整行
/// </summary>
/// <param name="iRow">行号</param>
public void SetActiveRow(int iRow1, int iRow2)
{
SetActiveRange(iRow1, 0, iRow2, 0);
}
/// <summary>
/// 设置当前要操作的整列
/// </summary>
/// <param name="iCol">列号</param>
public void SetActiveColumn(int iCol1, int iCol2)
{
SetActiveRange(0, iCol1, 0, iCol2);
}
/// <summary>
/// 设置当前要操作的单元格区域
/// </summary>
/// <param name="region">单元格区域表达式</param>
public void SetActiveRange(string region)
{
m_Range = m_Sheet.get_Range(region, oMissing);
}
/// <summary>
/// 设置当前要操作的单元格
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
public void SetActiveRange(int iRow, int iCol)
{
if (iRow > 0 && iCol > 0)
{
m_Range = (Excel.Range)m_Sheet.Cells[iRow, iCol];
}
else if (iRow <= 0 && iCol <= 0)
{
m_Range = m_Sheet.Cells;
}
else
{
SetActiveRange(iRow, iCol, iRow, iCol);
}
}
/// <summary>
/// 设置当前要操作的单元格区域
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
public void SetActiveRange(int iRow1, int iCol1, int iRow2, int iCol2)
{
SetActiveRange(
string.Format(
"{0}{1}:{2}{3}"
, CIndex2CName(iCol1), iRow1 > 0 ? iRow1.ToString() : ""
, CIndex2CName(iCol2), iRow2 > 0 ? iRow2.ToString() : ""
)
);
}
/// <summary>
/// 设置单元格的公式
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(int iRow, int iCol, string formula)
{
SetActiveRange(iRow, iCol);
SetRangeFormula(formula);
}
/// <summary>
/// 设置活动单元格的公式
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(string formula)
{
m_Range.Formula = formula;
}
/// <summary>
/// 设置单元格的公式
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(int iRow, int iCol, string formula, string formatString)
{
SetActiveRange(iRow, iCol);
SetRangeFormula(formula, formatString);
}
/// <summary>
/// 设置单元格的公式
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(string region, string formula, string formatString)
{
SetActiveRange(region);
SetRangeFormula(formula, formatString);
}
/// <summary>
/// 设置活动单元格的公式
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(string formula, string formatString)
{
SetRangeFormula(formula);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(string region, string formula, string formatString)
{
SetActiveRange(region);
SetRangeFormulaArray(formula, formatString);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(int iRow1, int iCol1, int iRow2, int iCol2, string formula, string formatString)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
SetRangeFormulaArray(formula, formatString);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(int iRow1, int iCol1, int iRow2, int iCol2, string formula)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
SetRangeFormulaArray(formula);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(string formula, string formatString)
{
SetRangeFormulaArray(formula);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(string formula)
{
m_Range.FormulaArray = formula;
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(int iRow, int iCol, object value)
{
SetActiveRange(iRow, iCol);
SetRangeValue(value);
}
/// <summary>
/// 设置活动单元格的值
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(object value)
{
m_Range.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, value);
}
/// <summary>
/// 设置活动单元格的值
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(object value, string formatString, bool useFormat)
{
SetRangeValue(value);
if (useFormat)
{
SetRangeNumberFormat(formatString);
}
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(int iRow, int iCol, object value, string formatString)
{
SetActiveRange(iRow, iCol);
SetRangeValue(value, formatString, true);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(int iRow1, int iCol1, int iRow2, int iCol2, object value)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
SetRangeValue(value);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(int iRow1, int iCol1, int iRow2, int iCol2, object value, string formatString)
{
SetRangeValue(iRow1, iRow2, iCol1, iCol2, value);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(string region, object value)
{
SetActiveRange(region);
SetRangeValue(value);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(string region, object value, string formatString)
{
SetActiveRange(region);
SetRangeValue(value, formatString, true);
}
/// <summary>
/// 设置单元格数字格式
/// </summary>
/// <param name="formatString"></param>
public void SetRangeNumberFormat(string formatString)
{
if (formatString != null && formatString != "")
{
m_Range.NumberFormat = formatString;
}
}
/// <summary>
/// 设置单元格数字格式
/// </summary>
/// <param name="formatString"></param>
public void SetRangeNumberFormat(int iRow, int iCol, string formatString)
{
SetActiveRange(iRow, iCol);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 设置单元格数字格式
/// </summary>
/// <param name="formatString"></param>
public void SetRangeNumberFormat(int iRow1, int iCol1, int iRow2, int iCol2, string formatString)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 合并单元格
/// </summary>
public void MergeRange(int iRow1, int iCol1, int iRow2, int iCol2)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
MergeRange();
}
/// <summary>
/// 合并单元格
/// </summary>
public void MergeRange(string region)
{
SetActiveRange(region);
MergeRange();
}
/// <summary>
/// 合并单元格
/// </summary>
public void MergeRange()
{
m_Range.Merge(false);
}
/// <summary>
/// 设置单元格的水平排列
/// </summary>
/// <param name="halign"></param>
public void SetRangeHAlign(string halign)
{
if (halign == "left")
{
m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
else if (halign == "center")
{
m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
else if (halign == "right")
{
m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
}
/// <summary>
/// 设置单元格的垂直排列
/// </summary>
/// <param name="halign"></param>
public void SetRangeVAlign(string valign)
{
if (valign == "top")
{
m_Range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
}
else if (valign == "center")
{
m_Range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
else if (valign == "bottom")
{
m_Range.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
}
}
/// <summary>
/// 设置单元格的背景颜色
/// </summary>
public void SetRangeBKColor(int icolor)
{
m_Range.Interior.Color = icolor;
}
/// <summary>
/// 设置单元格的背景颜色
/// </summary>
public void SetRangeBKColor(int r, int g, int b)
{
int icolor = ArgbToOleColor(r, g, b);
SetRangeBKColor(icolor);
}
/// <summary>
/// 设置单元格的背景颜色
/// </summary>
public void SetRangeBKColor(Color c)
{
int icolor = ArgbToOleColor(c);
SetRangeBKColor(icolor);
}
/// <summary>
/// 设置单元格边框
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="top"></param>
/// <param name="bottom"></param>
public void SetRangeBorders(bool left, bool right, bool top, bool bottom)
{
if (left)
{
Excel.XlBordersIndex allow = Excel.XlBordersIndex.xlEdgeLeft;
m_Range.Borders[allow].Weight = Excel.XlBorderWeight.xlThin;
m_Range.Borders[allow].LineStyle = Excel.XlLineStyle.xlContinuous;
m_Range.Borders[allow].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
m_Range.Borders[allow].Color = 0;
}
if (right)
{
Excel.XlBordersIndex allow = Excel.XlBordersIndex.xlEdgeRight;
m_Range.Borders[allow].Weight = Excel.XlBorderWeight.xlThin;
m_Range.Borders[allow].LineStyle = Excel.XlLineStyle.xlContinuous;
m_Range.Borders[allow].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
m_Range.Borders[allow].Color = 0;
}
if (top)
{
Excel.XlBordersIndex allow = Excel.XlBordersIndex.xlEdgeTop;
m_Range.Borders[allow].Weight = Excel.XlBorderWeight.xlThin;
m_Range.Borders[allow].LineStyle = Excel.XlLineStyle.xlContinuous;
m_Range.Borders[allow].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
m_Range.Borders[allow].Color = 0;
}
if (bottom)
{
Excel.XlBordersIndex allow = Excel.XlBordersIndex.xlEdgeBottom;
m_Range.Borders[allow].Weight = Excel.XlBorderWeight.xlThin;
m_Range.Borders[allow].LineStyle = Excel.XlLineStyle.xlContinuous;
m_Range.Borders[allow].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
m_Range.Borders[allow].Color = 0;
}
}
/// <summary>
/// 设置单元格的文字样式
/// </summary>
public void SetRangeFontStyle(int icolor, bool iBold, bool iItalic)
{
m_Range.Font.Color = icolor;
if (iBold)
{
m_Range.Font.Bold = iBold;
}
if (iItalic)
{
m_Range.Font.Italic = iItalic;
}
}
/// <summary>
/// 设置单元格的文字样式
/// </summary>
public void SetRangeFontStyle(int r, int g, int b, bool iBold, bool iItalic)
{
int icolor = ArgbToOleColor(r, g, b);
SetRangeFontStyle(icolor, iBold, iItalic);
}
/// <summary>
/// 设置单元格的文字样式
/// </summary>
public void SetRangeFontStyle(Color c, bool iBold, bool iItalic)
{
int icolor = ArgbToOleColor(c);
SetRangeFontStyle(icolor, iBold, iItalic);
}
/// <summary>
/// 添加名称，在EXCEL窗口中“公式”工具栏上的名称管理器中可以看到已添加的名称。
/// </summary>
/// <param name="names"></param>
/// <param name="region"></param>
public void AddNames(string name, string region)
{
((Excel.Names)m_WorkBook.Names).Add(name, region, true, oMissing, oMissing, oMissing, oMissing
, oMissing, oMissing, oMissing, oMissing);
}
/// <summary>
/// 设置当前工作表为选中状态
/// </summary>
public void SetSheetSelected()
{
((Excel._Worksheet)m_Sheet).Activate();
}
/// <summary>
/// 设置当前单元格区域为选中状态
/// </summary>
public void SetRangeSelected()
{
m_Range.Select();
}
/// <summary>
/// 设置工作表是否可见
/// </summary>
/// <param name="visible"></param>
public void SetSheetVisible(bool visible)
{
if (visible)
{
m_Sheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
}
else
{
m_Sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
}
}
/// <summary>
/// 设置单元格的数据格式
/// </summary>
/// <param name="visible"></param>
public void SetRangeFormat()
{
m_Range.NumberFormat = "#,##0.00";
}
/// <summary>
/// 将列索引转化为字母形式的列名
/// Add by shenjj 2009-12-28
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="top"></param>
/// <param name="bottom"></param>
public static string CIndex2CName(int colIndex)
{
StringBuilder cname = new StringBuilder();
int n = 26;
char[] c ={ 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
int m = 0;
while (colIndex > 0)
{
m = colIndex % n;
if (m == 0) m = n;
cname.Insert(0, c[m - 1]);
colIndex = (colIndex - 1) / n;
}
return cname.ToString();
}
/// <summary>
/// 将指定的 System.Drawing.Color 结构翻译成 OLE 颜色。
/// </summary>
public static int ArgbToOleColor(int r, int g, int b)
{
Color c = Color.FromArgb(r, g, b);
return ArgbToOleColor(c);
}
/// <summary>
/// 将指定的 System.Drawing.Color 结构翻译成 OLE 颜色。
/// </summary>
public static int ArgbToOleColor(Color c)
{
return ColorTranslator.ToOle(c);
}
public void Save()
{
m_WorkBook.Save();
}
public void SaveAs(string fileName)
{
m_WorkBook.SaveAs(fileName, oMissing, oMissing,
oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive, oMissing, oMissing, oMissing, oMissing, oMissing);
}
public void Close(bool saveChanges)
{
if (m_WorkBook != null)
{
m_WorkBook.Close(saveChanges, oMissing, oMissing);
}
if (m_CopyBook != null)
{
m_CopyBook.Close(saveChanges, oMissing, oMissing);
}
}
public void DeleteRows(int[] rowNos)
{
m_Range = m_Sheet.get_Range("A" + rowNos[0].ToString(), "A" + rowNos[rowNos.Length - 1]);
m_Range.Select();
m_Range.EntireRow.Delete(oMissing);
}
/// <summary>
/// Excel导入DataTable
/// </summary>
/// <param name="strFileName">文件名称</param>
/// <param name="isHead">是否包含表头</param>
/// <param name="iSheet">Sheet</param>
/// <param name="strErrorMessage">错误信息</param>
/// <param name="iRowsIndex">导入的Excel的开始行</param>
/// <returns></returns>
public static DataTable GetDataFromExcel(string strFileName, bool isHead, int iSheet,
int iRowsIndex)
{
Excel.Application appExcel = new Excel.Application();
Excel.Workbook workbookData;
Excel.Worksheet worksheetData;
workbookData = appExcel.Workbooks.Open(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheetData = (Excel.Worksheet)workbookData.Sheets[iSheet];
Excel.Range xlRang = null;
int iRowCount = worksheetData.UsedRange.Cells.Rows.Count;
int iParstedRow = 0, iCurrSize = 0;
int iEachSize = 1000; // each time you
int iColumnAccount = worksheetData.UsedRange.Cells.Columns.Count;
int iHead = iRowsIndex;
if (isHead)
iHead = iRowsIndex + 1;
DataTable dt = new DataTable();
for (int i = 1; i <= iColumnAccount; i++)
{
if (isHead)
dt.Columns.Add(worksheetData.get_Range(worksheetData.Cells[iRowsIndex, i], worksheetData.Cells[iRowsIndex, i]).Text.ToString());
else
dt.Columns.Add("Columns" + i.ToString());
}
object[,] objVal = new object[iEachSize, iColumnAccount];
try
{
iCurrSize = iEachSize;
while (iParstedRow < iRowCount)
{
if ((iRowCount - iParstedRow) < iEachSize)
iCurrSize = iRowCount - iParstedRow;
xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + iHead)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString()
+ (((int)(iParstedRow + iCurrSize + 1)).ToString()));
objVal = (object[,])xlRang.Value2;
int iLength = objVal.Length / iColumnAccount;
for (int i = 1; i < iLength; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= iColumnAccount; j++)
{
if (objVal[i, j] != null)
{
dr[j - 1] = objVal[i, j].ToString();
}
}
dt.Rows.Add(dr);
}
iParstedRow = iParstedRow + iCurrSize;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
xlRang = null;
}
catch (Exception ex)
{
appExcel.Quit();
//strErrorMessage = ex.Message;
return null;
}
appExcel.Quit();
return dt;
}
/// <summary>
/// 用OLEDB方式读入整张表，默认sheet名为sheet1
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <returns></returns>
public static DataTable ImportWholeSheet(string filePath)
{
return ImportWholeSheet(filePath, "sheet1");
}
/// <summary>
/// 用OLEDB方式读入整张表
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="sheetName">要读入的sheet名</param>
/// <returns></returns>
public static DataTable ImportWholeSheet(string filePath, string sheetName)
{
if (!File.Exists(filePath)) return null;
OleDbConnection conn = null;
try
{
//根据后缀判断Excel文件版本后，建立连接
if (".xlsx" == Path.GetExtension(filePath).ToLower())
{
conn = new OleDbConnection(string.Format(ConnectionString_2007, filePath));
}
else
{
conn = new OleDbConnection(string.Format(ConnectionString, filePath));
}
//读取指定sheet名的所有数据
string sql = "SELECT * FROM [" + sheetName + "$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
DataTable result = new DataTable();
adapter.Fill(result);
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
conn.Close();
}
}
/// <summary>
/// 释放COM+对象
/// </summary>
/// <param name="o"></param>
private static void NAR(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch { }
finally
{
o = null;
}
}
#region IDisposable 成员
public void Dispose()
{
Close(false);
NAR(m_Range);
NAR(m_Sheet);
NAR(m_CopySheet);
NAR(m_WorkBook);
NAR(m_CopyBook);
if (m_App != null)
{
m_App.Quit();
NAR(m_App);
}
GC.Collect();
}
#endregion
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
using System.Reflection;
namespace SJJSKY.Windows.Toolkit
{
public class ExcelHelper : IDisposable
{
private static readonly string ConnectionString_2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
private static readonly string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
public static object oMissing = Type.Missing;
private Excel.Application m_App = null; //Excel应用程序对象
private Excel.Workbook m_WorkBook = null; //工作簿
private Excel.Worksheet m_Sheet = null; //sheet对象
private Excel.Workbook m_CopyBook = null;
private Excel.Worksheet m_CopySheet = null;
private int flgCopyMove = 0;
public Excel.Application Application
{
get { return m_App; }
set { m_App = value; }
}
public Excel.Workbook WorkBook
{
get { return m_WorkBook; }
set { m_WorkBook = value; }
}
public Excel.Worksheet Sheet
{
get { return m_Sheet; }
set { m_Sheet = value; }
}
private Excel.Range m_Range;
public ExcelReader(string path)
{
Init(path, 1);
}
public ExcelReader(string path, int sheetIndex)
{
Init(path, sheetIndex);
}
private void Init(string path, int sheetIndex)
{
m_App = new Excel.Application();
m_App.Visible = false;
m_App.DisplayAlerts = false;
OpenWorkbook(path, sheetIndex);
}
public void OpenWorkbook(string path, int sheetIndex)
{
m_WorkBook = m_App.Workbooks.Open(path, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_Sheet = (Excel.Worksheet)m_WorkBook.Sheets[sheetIndex];
}
private void CloseWorkbook(Excel.Workbook workbook, bool saveChanges, string fileName)
{
workbook.Close(saveChanges, fileName, oMissing);
}
public void CloseWorkbook()
{
CloseWorkbook(m_WorkBook, false, "");
NAR(m_WorkBook);
}
public void CloseWorkbook(int bookIndex)
{
SetActiveBook(bookIndex);
CloseWorkbook();
}
public enum BeforeAfter
{
Before,
After
}
/// <summary>
/// 复制工作表，分为两步来做，之后需调用void PasteSheet(BeforeAfter beforeafter)方法
/// </summary>
public void CopySheet()
{
m_CopySheet = m_Sheet;
flgCopyMove = 1;
}
/// <summary>
/// 移动工作表，分为两步来做，之后需调用void PasteSheet(BeforeAfter beforeafter)方法
/// </summary>
public void CutSheet()
{
m_CopySheet = m_Sheet;
flgCopyMove = 2;
}
/// <summary>
/// 复制或移动工作表之后调用此方法
/// </summary>
/// <param name="beforeafter"></param>
public void PasteSheet(BeforeAfter beforeafter)
{
if (flgCopyMove == 1)
{
if (beforeafter == BeforeAfter.Before)
{
m_CopySheet.Copy(m_Sheet, oMissing);
m_Sheet = (Excel.Worksheet)m_Sheet.Previous;
}
else
{
m_CopySheet.Copy(oMissing, m_Sheet);
m_Sheet = (Excel.Worksheet)m_Sheet.Next;
}
flgCopyMove = 0;
}
else if (flgCopyMove == 2)
{
if (beforeafter == BeforeAfter.Before)
{
m_CopySheet.Move(m_Sheet, oMissing);
m_Sheet = (Excel.Worksheet)m_Sheet.Previous;
}
else
{
m_CopySheet.Move(oMissing, m_Sheet);
m_Sheet = (Excel.Worksheet)m_Sheet.Next;
}
flgCopyMove = 0;
}
NAR(m_CopySheet);
}
/// <summary>
/// 重命名工作表
/// </summary>
public void RenameSheet(string sheetName)
{
m_Sheet.Name = sheetName;
}
/// <summary>
/// 设置当前要操作的工作簿
/// </summary>
/// <param name="sheetIndex"></param>
public void SetActiveBook(int bookIndex)
{
m_WorkBook = (Excel.Workbook)m_App.Workbooks[bookIndex];
}
/// <summary>
/// 设置当前要操作的工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void SetActiveSheet(int sheetIndex)
{
m_Sheet = (Excel.Worksheet)m_WorkBook.Sheets[sheetIndex];
}
/// <summary>
/// 设置当前要操作的工作表
/// </summary>
/// <param name="sheetName"></param>
public bool SetActiveSheet(string sheetName)
{
int sheetCount = m_WorkBook.Sheets.Count;
for (int i = 1; i <= sheetCount; i++)
{
Excel.Worksheet oSheet = (Excel.Worksheet)m_WorkBook.Sheets[0];
if (oSheet.Name == sheetName)
{
m_Sheet = oSheet;
return true;
}
}
return false;
}
/// <summary>
/// 设置当前要操作的整行
/// </summary>
/// <param name="iRow">行号</param>
public void SetActiveRow(int iRow1, int iRow2)
{
SetActiveRange(iRow1, 0, iRow2, 0);
}
/// <summary>
/// 设置当前要操作的整列
/// </summary>
/// <param name="iCol">列号</param>
public void SetActiveColumn(int iCol1, int iCol2)
{
SetActiveRange(0, iCol1, 0, iCol2);
}
/// <summary>
/// 设置当前要操作的单元格区域
/// </summary>
/// <param name="region">单元格区域表达式</param>
public void SetActiveRange(string region)
{
m_Range = m_Sheet.get_Range(region, oMissing);
}
/// <summary>
/// 设置当前要操作的单元格
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
public void SetActiveRange(int iRow, int iCol)
{
if (iRow > 0 && iCol > 0)
{
m_Range = (Excel.Range)m_Sheet.Cells[iRow, iCol];
}
else if (iRow <= 0 && iCol <= 0)
{
m_Range = m_Sheet.Cells;
}
else
{
SetActiveRange(iRow, iCol, iRow, iCol);
}
}
/// <summary>
/// 设置当前要操作的单元格区域
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
public void SetActiveRange(int iRow1, int iCol1, int iRow2, int iCol2)
{
SetActiveRange(
string.Format(
"{0}{1}:{2}{3}"
, CIndex2CName(iCol1), iRow1 > 0 ? iRow1.ToString() : ""
, CIndex2CName(iCol2), iRow2 > 0 ? iRow2.ToString() : ""
)
);
}
/// <summary>
/// 设置单元格的公式
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(int iRow, int iCol, string formula)
{
SetActiveRange(iRow, iCol);
SetRangeFormula(formula);
}
/// <summary>
/// 设置活动单元格的公式
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(string formula)
{
m_Range.Formula = formula;
}
/// <summary>
/// 设置单元格的公式
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(int iRow, int iCol, string formula, string formatString)
{
SetActiveRange(iRow, iCol);
SetRangeFormula(formula, formatString);
}
/// <summary>
/// 设置单元格的公式
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(string region, string formula, string formatString)
{
SetActiveRange(region);
SetRangeFormula(formula, formatString);
}
/// <summary>
/// 设置活动单元格的公式
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="formula">公式</param>
public void SetRangeFormula(string formula, string formatString)
{
SetRangeFormula(formula);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(string region, string formula, string formatString)
{
SetActiveRange(region);
SetRangeFormulaArray(formula, formatString);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(int iRow1, int iCol1, int iRow2, int iCol2, string formula, string formatString)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
SetRangeFormulaArray(formula, formatString);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(int iRow1, int iCol1, int iRow2, int iCol2, string formula)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
SetRangeFormulaArray(formula);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(string formula, string formatString)
{
SetRangeFormulaArray(formula);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 设置单元格的数组公式
/// </summary>
/// <param name="formula">公式</param>
public void SetRangeFormulaArray(string formula)
{
m_Range.FormulaArray = formula;
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(int iRow, int iCol, object value)
{
SetActiveRange(iRow, iCol);
SetRangeValue(value);
}
/// <summary>
/// 设置活动单元格的值
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(object value)
{
m_Range.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, value);
}
/// <summary>
/// 设置活动单元格的值
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(object value, string formatString, bool useFormat)
{
SetRangeValue(value);
if (useFormat)
{
SetRangeNumberFormat(formatString);
}
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(int iRow, int iCol, object value, string formatString)
{
SetActiveRange(iRow, iCol);
SetRangeValue(value, formatString, true);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(int iRow1, int iCol1, int iRow2, int iCol2, object value)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
SetRangeValue(value);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="iRow">行号</param>
/// <param name="iCol">列号</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(int iRow1, int iCol1, int iRow2, int iCol2, object value, string formatString)
{
SetRangeValue(iRow1, iRow2, iCol1, iCol2, value);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(string region, object value)
{
SetActiveRange(region);
SetRangeValue(value);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="region">单元格区域表达式</param>
/// <param name="value">单元格的值</param>
public void SetRangeValue(string region, object value, string formatString)
{
SetActiveRange(region);
SetRangeValue(value, formatString, true);
}
/// <summary>
/// 设置单元格数字格式
/// </summary>
/// <param name="formatString"></param>
public void SetRangeNumberFormat(string formatString)
{
if (formatString != null && formatString != "")
{
m_Range.NumberFormat = formatString;
}
}
/// <summary>
/// 设置单元格数字格式
/// </summary>
/// <param name="formatString"></param>
public void SetRangeNumberFormat(int iRow, int iCol, string formatString)
{
SetActiveRange(iRow, iCol);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 设置单元格数字格式
/// </summary>
/// <param name="formatString"></param>
public void SetRangeNumberFormat(int iRow1, int iCol1, int iRow2, int iCol2, string formatString)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
SetRangeNumberFormat(formatString);
}
/// <summary>
/// 合并单元格
/// </summary>
public void MergeRange(int iRow1, int iCol1, int iRow2, int iCol2)
{
SetActiveRange(iRow1, iCol1, iRow2, iCol2);
MergeRange();
}
/// <summary>
/// 合并单元格
/// </summary>
public void MergeRange(string region)
{
SetActiveRange(region);
MergeRange();
}
/// <summary>
/// 合并单元格
/// </summary>
public void MergeRange()
{
m_Range.Merge(false);
}
/// <summary>
/// 设置单元格的水平排列
/// </summary>
/// <param name="halign"></param>
public void SetRangeHAlign(string halign)
{
if (halign == "left")
{
m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
else if (halign == "center")
{
m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
else if (halign == "right")
{
m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
}
/// <summary>
/// 设置单元格的垂直排列
/// </summary>
/// <param name="halign"></param>
public void SetRangeVAlign(string valign)
{
if (valign == "top")
{
m_Range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
}
else if (valign == "center")
{
m_Range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
else if (valign == "bottom")
{
m_Range.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
}
}
/// <summary>
/// 设置单元格的背景颜色
/// </summary>
public void SetRangeBKColor(int icolor)
{
m_Range.Interior.Color = icolor;
}
/// <summary>
/// 设置单元格的背景颜色
/// </summary>
public void SetRangeBKColor(int r, int g, int b)
{
int icolor = ArgbToOleColor(r, g, b);
SetRangeBKColor(icolor);
}
/// <summary>
/// 设置单元格的背景颜色
/// </summary>
public void SetRangeBKColor(Color c)
{
int icolor = ArgbToOleColor(c);
SetRangeBKColor(icolor);
}
/// <summary>
/// 设置单元格边框
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="top"></param>
/// <param name="bottom"></param>
public void SetRangeBorders(bool left, bool right, bool top, bool bottom)
{
if (left)
{
Excel.XlBordersIndex allow = Excel.XlBordersIndex.xlEdgeLeft;
m_Range.Borders[allow].Weight = Excel.XlBorderWeight.xlThin;
m_Range.Borders[allow].LineStyle = Excel.XlLineStyle.xlContinuous;
m_Range.Borders[allow].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
m_Range.Borders[allow].Color = 0;
}
if (right)
{
Excel.XlBordersIndex allow = Excel.XlBordersIndex.xlEdgeRight;
m_Range.Borders[allow].Weight = Excel.XlBorderWeight.xlThin;
m_Range.Borders[allow].LineStyle = Excel.XlLineStyle.xlContinuous;
m_Range.Borders[allow].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
m_Range.Borders[allow].Color = 0;
}
if (top)
{
Excel.XlBordersIndex allow = Excel.XlBordersIndex.xlEdgeTop;
m_Range.Borders[allow].Weight = Excel.XlBorderWeight.xlThin;
m_Range.Borders[allow].LineStyle = Excel.XlLineStyle.xlContinuous;
m_Range.Borders[allow].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
m_Range.Borders[allow].Color = 0;
}
if (bottom)
{
Excel.XlBordersIndex allow = Excel.XlBordersIndex.xlEdgeBottom;
m_Range.Borders[allow].Weight = Excel.XlBorderWeight.xlThin;
m_Range.Borders[allow].LineStyle = Excel.XlLineStyle.xlContinuous;
m_Range.Borders[allow].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
m_Range.Borders[allow].Color = 0;
}
}
/// <summary>
/// 设置单元格的文字样式
/// </summary>
public void SetRangeFontStyle(int icolor, bool iBold, bool iItalic)
{
m_Range.Font.Color = icolor;
if (iBold)
{
m_Range.Font.Bold = iBold;
}
if (iItalic)
{
m_Range.Font.Italic = iItalic;
}
}
/// <summary>
/// 设置单元格的文字样式
/// </summary>
public void SetRangeFontStyle(int r, int g, int b, bool iBold, bool iItalic)
{
int icolor = ArgbToOleColor(r, g, b);
SetRangeFontStyle(icolor, iBold, iItalic);
}
/// <summary>
/// 设置单元格的文字样式
/// </summary>
public void SetRangeFontStyle(Color c, bool iBold, bool iItalic)
{
int icolor = ArgbToOleColor(c);
SetRangeFontStyle(icolor, iBold, iItalic);
}
/// <summary>
/// 添加名称，在EXCEL窗口中“公式”工具栏上的名称管理器中可以看到已添加的名称。
/// </summary>
/// <param name="names"></param>
/// <param name="region"></param>
public void AddNames(string name, string region)
{
((Excel.Names)m_WorkBook.Names).Add(name, region, true, oMissing, oMissing, oMissing, oMissing
, oMissing, oMissing, oMissing, oMissing);
}
/// <summary>
/// 设置当前工作表为选中状态
/// </summary>
public void SetSheetSelected()
{
((Excel._Worksheet)m_Sheet).Activate();
}
/// <summary>
/// 设置当前单元格区域为选中状态
/// </summary>
public void SetRangeSelected()
{
m_Range.Select();
}
/// <summary>
/// 设置工作表是否可见
/// </summary>
/// <param name="visible"></param>
public void SetSheetVisible(bool visible)
{
if (visible)
{
m_Sheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
}
else
{
m_Sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
}
}
/// <summary>
/// 设置单元格的数据格式
/// </summary>
/// <param name="visible"></param>
public void SetRangeFormat()
{
m_Range.NumberFormat = "#,##0.00";
}
/// <summary>
/// 将列索引转化为字母形式的列名
/// Add by shenjj 2009-12-28
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="top"></param>
/// <param name="bottom"></param>
public static string CIndex2CName(int colIndex)
{
StringBuilder cname = new StringBuilder();
int n = 26;
char[] c ={ 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
int m = 0;
while (colIndex > 0)
{
m = colIndex % n;
if (m == 0) m = n;
cname.Insert(0, c[m - 1]);
colIndex = (colIndex - 1) / n;
}
return cname.ToString();
}
/// <summary>
/// 将指定的 System.Drawing.Color 结构翻译成 OLE 颜色。
/// </summary>
public static int ArgbToOleColor(int r, int g, int b)
{
Color c = Color.FromArgb(r, g, b);
return ArgbToOleColor(c);
}
/// <summary>
/// 将指定的 System.Drawing.Color 结构翻译成 OLE 颜色。
/// </summary>
public static int ArgbToOleColor(Color c)
{
return ColorTranslator.ToOle(c);
}
public void Save()
{
m_WorkBook.Save();
}
public void SaveAs(string fileName)
{
m_WorkBook.SaveAs(fileName, oMissing, oMissing,
oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive, oMissing, oMissing, oMissing, oMissing, oMissing);
}
public void Close(bool saveChanges)
{
if (m_WorkBook != null)
{
m_WorkBook.Close(saveChanges, oMissing, oMissing);
}
if (m_CopyBook != null)
{
m_CopyBook.Close(saveChanges, oMissing, oMissing);
}
}
public void DeleteRows(int[] rowNos)
{
m_Range = m_Sheet.get_Range("A" + rowNos[0].ToString(), "A" + rowNos[rowNos.Length - 1]);
m_Range.Select();
m_Range.EntireRow.Delete(oMissing);
}
/// <summary>
/// Excel导入DataTable
/// </summary>
/// <param name="strFileName">文件名称</param>
/// <param name="isHead">是否包含表头</param>
/// <param name="iSheet">Sheet</param>
/// <param name="strErrorMessage">错误信息</param>
/// <param name="iRowsIndex">导入的Excel的开始行</param>
/// <returns></returns>
public static DataTable GetDataFromExcel(string strFileName, bool isHead, int iSheet,
int iRowsIndex)
{
Excel.Application appExcel = new Excel.Application();
Excel.Workbook workbookData;
Excel.Worksheet worksheetData;
workbookData = appExcel.Workbooks.Open(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheetData = (Excel.Worksheet)workbookData.Sheets[iSheet];
Excel.Range xlRang = null;
int iRowCount = worksheetData.UsedRange.Cells.Rows.Count;
int iParstedRow = 0, iCurrSize = 0;
int iEachSize = 1000; // each time you
int iColumnAccount = worksheetData.UsedRange.Cells.Columns.Count;
int iHead = iRowsIndex;
if (isHead)
iHead = iRowsIndex + 1;
DataTable dt = new DataTable();
for (int i = 1; i <= iColumnAccount; i++)
{
if (isHead)
dt.Columns.Add(worksheetData.get_Range(worksheetData.Cells[iRowsIndex, i], worksheetData.Cells[iRowsIndex, i]).Text.ToString());
else
dt.Columns.Add("Columns" + i.ToString());
}
object[,] objVal = new object[iEachSize, iColumnAccount];
try
{
iCurrSize = iEachSize;
while (iParstedRow < iRowCount)
{
if ((iRowCount - iParstedRow) < iEachSize)
iCurrSize = iRowCount - iParstedRow;
xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + iHead)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString()
+ (((int)(iParstedRow + iCurrSize + 1)).ToString()));
objVal = (object[,])xlRang.Value2;
int iLength = objVal.Length / iColumnAccount;
for (int i = 1; i < iLength; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= iColumnAccount; j++)
{
if (objVal[i, j] != null)
{
dr[j - 1] = objVal[i, j].ToString();
}
}
dt.Rows.Add(dr);
}
iParstedRow = iParstedRow + iCurrSize;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
xlRang = null;
}
catch (Exception ex)
{
appExcel.Quit();
//strErrorMessage = ex.Message;
return null;
}
appExcel.Quit();
return dt;
}
/// <summary>
/// 用OLEDB方式读入整张表，默认sheet名为sheet1
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <returns></returns>
public static DataTable ImportWholeSheet(string filePath)
{
return ImportWholeSheet(filePath, "sheet1");
}
/// <summary>
/// 用OLEDB方式读入整张表
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="sheetName">要读入的sheet名</param>
/// <returns></returns>
public static DataTable ImportWholeSheet(string filePath, string sheetName)
{
if (!File.Exists(filePath)) return null;
OleDbConnection conn = null;
try
{
//根据后缀判断Excel文件版本后，建立连接
if (".xlsx" == Path.GetExtension(filePath).ToLower())
{
conn = new OleDbConnection(string.Format(ConnectionString_2007, filePath));
}
else
{
conn = new OleDbConnection(string.Format(ConnectionString, filePath));
}
//读取指定sheet名的所有数据
string sql = "SELECT * FROM [" + sheetName + "$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
DataTable result = new DataTable();
adapter.Fill(result);
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
conn.Close();
}
}
/// <summary>
/// 释放COM+对象
/// </summary>
/// <param name="o"></param>
private static void NAR(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch { }
finally
{
o = null;
}
}
#region IDisposable 成员
public void Dispose()
{
Close(false);
NAR(m_Range);
NAR(m_Sheet);
NAR(m_CopySheet);
NAR(m_WorkBook);
NAR(m_CopyBook);
if (m_App != null)
{
m_App.Quit();
NAR(m_App);
}
GC.Collect();
}
#endregion
}
}
