自己整理的Excel操作类:
using System;
using System.Collections.Generic;using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Reflection;
using System.IO;
namespace FoaAuto
{
public static class ExcelHelper
{
//注:Excel的行号、列号均≥1,DataSet的行号、列号均≥0。
private static Excel.Application app = null;
public static void InitExcel()
{
if (app == null)
{
app = new Excel.ApplicationClass();
}
}
#region 退出Excel
public static void QuitExcel()
{
if (app != null)
{
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
GC.Collect();
}
}
#endregion
#region 获取Excel的所有Sheet名称
public static string[] GetSheetNames(string strExcelPath)
{
string strDbConCmd = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'";
OleDbConnection oleDbCon = new OleDbConnection(strDbConCmd);
oleDbCon.Open();
DataTable dTable = oleDbCon.GetSchema("TABLES");
DataTableReader dtReader = new DataTableReader(dTable);
List<string> lSheetNames = new List<string>();
while (dtReader.Read())
{
string strSheetName = dtReader["TABLE_NAME"].ToString().Replace('$', ' ').Trim();
lSheetNames.Add(strSheetName);
}
oleDbCon.Close();
string[] strSheetNames = lSheetNames.ToArray();
return strSheetNames;
}
public static string[] GetSheetNames2(string strExcelPath)
{
string strDbConCmd = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'";
OleDbConnection oleDbCon = new OleDbConnection(strDbConCmd);
oleDbCon.Open();
DataTable dtSheetName = oleDbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string[] strSheetNames = new string[dtSheetName.Rows.Count];
for(int k=0; k<dtSheetName.Rows.Count; k++)
{
strSheetNames[k]=dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
oleDbCon.Close();
return strSheetNames;
}
#endregion
#region Excel转换为DataSet
public static DataSet ExcelToDataSet(string strExcelFilePath, string strTableName)
{
string strCon = " Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + strExcelFilePath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "SELECT * FROM [" + strTableName + "$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, strTableName);
myConn.Close();
return myDataSet;
}
#endregion
#region DataSet转换为Excel
public static bool DataSetToExcel(DataSet dataSet, string strExcelSavedPath)
{
if (app == null)
{
app = new Excel.ApplicationClass();
}
try
{
app.Visible = false;
Excel.Workbook wBook = (Excel.Workbook)app.Workbooks.Add(true);
Excel.Worksheet wSheet = (Excel.Worksheet)wBook.ActiveSheet;
int nTableCount = dataSet.Tables.Count;
if (nTableCount == 0)
{
MessageBox.Show("Excel的Table个数不能为0!");
return false;
}
for (int i = 0; i < nTableCount; i++)
{
wSheet = (Excel.Worksheet)wBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wSheet.Name = dataSet.Tables[i].TableName;
if (dataSet.Tables[i].Rows.Count > 0)
{
int nRowCount = dataSet.Tables[i].Rows.Count;
int nColumnCount = dataSet.Tables[i].Columns.Count;
for (int j = 0; j < nRowCount; j++)
{
for (int k = 0; k < nColumnCount; k++)
{
//数据写入Excel
string strCellData = dataSet.Tables[i].Rows[j][k].ToString();
wSheet.Cells[j + 1, k + 1] = strCellData;
}
}
}
}
app.ActiveWorkbook.SaveAs(strExcelSavedPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
QuitExcel();
MessageBox.Show("保存文件成功!");
return true;
}
catch (Exception err)
{
MessageBox.Show("保存Excel发生错误!" + err.Message);
return false;
}
}
#endregion
#region 创建Excel模板
public static bool CreateExcelModel(string[] strColumnNames, string strSheetName, string strExcelSavedPath)
{
if (app == null)
{
app = new Excel.ApplicationClass();
}
try
{
app.Visible = false;
Excel.Workbook wBook = (Excel.Workbook)app.Workbooks.Add(true);
Excel.Worksheet wSheet = (Excel.Worksheet)wBook.ActiveSheet;
wSheet = (Excel.Worksheet)wBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wSheet.Name = strSheetName;
int nColCount = strColumnNames.Length;
for (int i = 0; i < nColCount; i++)
{
wSheet.Cells[1, i + 1] = strColumnNames[i];
}
Excel.Range range = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[1, nColCount]);
//自动调整列宽
range.EntireColumn.AutoFit();
app.ActiveWorkbook.SaveAs(strExcelSavedPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
QuitExcel();
return true;
}
catch (Exception err)
{
MessageBox.Show("保存Excel发生错误!" + err.Message);
return false;
}
}
#endregion
#region 添加一行数据到指定的Excel
public static bool AddOneRowDataToExcel(string strExcelPath, string strSheetName, int nRowNo, string[] strOneRowData)
{
if (app == null)
{
app = new Excel.ApplicationClass();
}
try
{
app.Visible = false;
Excel.Workbook wBook = app.Workbooks.Open(strExcelPath, 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);
bool bFound = false;
foreach (Excel.Worksheet wSheet in wBook.Sheets)
{
if (wSheet.Name == strSheetName)
{
bFound = true;
int nColCount = strOneRowData.Length;
for (int i = 0; i < nColCount; i++)
{
string strTemp = strOneRowData[i];
wSheet.Cells[nRowNo, i + 1] = strTemp;
}
wBook.Save();
QuitExcel();
break;
}
}
if (!bFound)
{
MessageBox.Show("没有这个Sheet:" + strSheetName);
return false;
}
else
{
return true;
}
}
catch (System.Exception ex)
{
MessageBox.Show("保存Excel发生错误!" + ex.Message);
return false;
}
}
#endregion
#region 更新Excel文件
public static bool UpdateExcelFile(string strExcelFilePath, string strSheetName, string[] strNames, string[] strValues)
{
if (strNames.Length <= 0 || strValues.Length <= 0)
{
return false;
}
//检查是否已经存在输出文件
bool bIsExsit = File.Exists(strExcelFilePath);
bool bRet = true;
if (!bIsExsit)
{
//新建文件,添加数据
bRet = ExcelHelper.CreateExcelModel(strNames, strSheetName, strExcelFilePath);
if (!bRet)
{
return false;
}
bRet = ExcelHelper.AddOneRowDataToExcel(strExcelFilePath, strSheetName, 2, strValues);
if (!bRet)
{
return false;
}
}
else
{
//打开文件,添加数据
DataSet ds = null;
try
{
ds = ExcelHelper.ExcelToDataSet(strExcelFilePath, strSheetName);
}
catch (System.Exception ex)
{
return false;
}
int nRowNum = ds.Tables[0].Rows.Count;
bRet = ExcelHelper.AddOneRowDataToExcel(strExcelFilePath, strSheetName, nRowNum + 1, strValues);
if (!bRet)
{
return false;
}
}
return true;
}
#endregion
#region DataSet列表转成Excel
public static void DataSetListToExcel(List<DataSet> listDs, string strOutputFilePath)
{
if (app == null)
{
app = new Excel.ApplicationClass();
}
app.Visible = false;
app.Workbooks.Add(true);
int nDataSetCount = listDs.Count;
for (int i = 0; i < nDataSetCount; i++)
{
app.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
for (int k = 0; k < nDataSetCount; k++)
{
DataSet ds = listDs[k];
int nRowCount = ds.Tables[0].Rows.Count;
int nColumnCount = ds.Tables[0].Columns.Count;
for (int i = 0; i < nRowCount; i++)
{
for (int j = 0; j < nColumnCount; j++)
{
//数据写入Excel
string strCellData = ds.Tables[0].Rows[i][j].ToString();
((Excel.Worksheet)app.Sheets[k+1]).Cells[i + 1, j + 1] = strCellData;
}
}
((Excel.Worksheet)app.Sheets[k+1]).Name = ds.Tables[0].TableName;
}
app.ActiveWorkbook.SaveAs(strOutputFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
QuitExcel();
}
#endregion
#region 合并Excel
public static void MergeExcels(string[] strInputFilePaths, string strOutputFilePath)
{
List<DataSet> listDs = new List<DataSet>();
foreach (string strCurFilePath in strInputFilePaths)
{
string strSheetName = Path.GetFileNameWithoutExtension(strCurFilePath);
DataSet ds = null;
try
{
ds = ExcelHelper.ExcelToDataSet(strCurFilePath, strSheetName);
}
catch (System.Exception ex)
{
continue;
}
if (ds != null)
{
listDs.Add(ds);
}
}
DataSetListToExcel(listDs, strOutputFilePath);
}
#endregion
}
}