C# 操作Excel
需求：Excel文件有多个Sheet，每个Sheet中有一个数据表，数据表的字段必须包含在制定字段内，可少不可多。
处理：补足所有字段，顺序不做要求
导入的是Excel 2007的DLL。
方式一：用OLED方式依次取得各Sheet数据放入DataTable，并将字段补足；在利用Excel对象写文件。 此种方式写完的Excel文件不是标准Excel文件，有破坏，导致不能再次用OLED方式连接，会报“外部表不是预期格式”的错误。
代码
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Xml;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace GT.Tools.FieldsCheck
{
public class Logic
{
private OleDbConnection xlsConn = null;
private XmlDocument doc = null;
string[] colLeft = null;
public Logic(string fileName)
{
string xmlName = AppDomain.CurrentDomain.BaseDirectory + "tools_config.xml";
doc = new XmlDocument();
doc.Load(xmlName);
//好像不用区分版本也可以
if (fileName.EndsWith(".xls"))
{
xlsConn = new OleDbConnection(string.Format(this.GetConfig("xls2003-conn"), fileName));
}
else
{
xlsConn = new OleDbConnection(string.Format(this.GetConfig("xls2007-conn"), fileName));
}
colLeft = this.GetConfig("style/left").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
}
private string GetConfig(string nodeName)
{
return doc.SelectSingleNode("//" + nodeName).InnerText;
}
private void OpenExcel(string fileName)
{
}
public void GetDataFormExcel()
{
string sheetName = string.Empty;
DataTable dtSchema = null;
DataTable dtData = new DataTable();
string[] fields = null;
try
{
object objOpt = Missing.Value;
string strPathFile = @"D:\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//Excel.Application app = new Excel.ApplicationClass();
Excel.Application app = new Excel.Application();
app.Visible = true;
app.UserControl = true;
//app.DisplayAlerts = false;
Excel.Workbooks objBooks = app.Workbooks;
Excel._Workbook workBook = objBooks.Add(objOpt);
Excel.Sheets objSheets = workBook.Worksheets;
Excel._Worksheet workSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(true);
//Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//Excel.Worksheet workSheet = null;
fields = this.GetConfig("fields").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
this.xlsConn.Open();
//获取Excel各个sheet的名称
dtSchema = this.xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; dtSchema != null && i < dtSchema.Rows.Count; i++)
{
sheetName = dtSchema.Rows[i][2].ToString();
if (!sheetName.EndsWith("$"))
{
continue;
}
doWork(app, workBook, workSheet, sheetName, fields);
}
workBook.SaveAs(strPathFile, objOpt, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//workBook.Close(false, objOpt, objOpt);
workBook.Close(objOpt, strPathFile, objOpt);
//app.SendKeys("%{y}", System.Reflection.Missing.Value);//自动确认保存(ALT+'y')
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workBook = null;
app = null;
workSheet = null;
GC.Collect();
}
catch
{ }
finally
{
if (this.xlsConn.State == ConnectionState.Open)
{
this.xlsConn.Close();
}
}
}
private void doWork(Excel.Application app, Excel._Workbook workBook, Excel._Worksheet workSheet, string sheetName, string[] fields)
{
string strSql = null; // 从文件源查数据的sql语句
OleDbDataAdapter adpUploadData = null; // 从文件源查数据的适配器
DataTable dtData = new DataTable();
strSql = "Select * from [" + sheetName + "]";
adpUploadData = new OleDbDataAdapter(strSql, this.xlsConn);
adpUploadData.Fill(dtData);
if (sheetName.EndsWith("$"))
{
foreach (string f in fields)
{
//判断是否包含所有列
if (dtData.Columns.Contains(f) == false)
{
dtData.Columns.Add(f);
}
}
}
sheetName = sheetName.EndsWith("$") == true ? sheetName.Substring(0, sheetName.Length - 1) : sheetName;
Export(app, workBook, workSheet,dtData, sheetName);
}
private void Export(Excel.Application app, Excel._Workbook workBook, Excel._Worksheet workSheet, System.Data.DataTable dtData, string sheetName)
{
int colIndex = 0;
//Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
//Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
//Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets.Add(Missing.Value, Missing.Value, 1, Missing.Value);
if (null == workSheet)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
workSheet = (Excel.Worksheet)workBook.Worksheets.Add(Type.Missing, workSheet, 1, Type.Missing);
}
workSheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
workSheet.Name = sheetName;
foreach (DataColumn col in dtData.Columns)
{
colIndex++;
//app.Cells[1, colIndex] = col.ColumnName;
workSheet.Cells[1, colIndex] = col.ColumnName;
workSheet.get_Range(app.Cells[1, colIndex], app.Cells[1, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
workSheet.get_Range(app.Cells[1, colIndex], app.Cells[1, colIndex]).Font.Size = 10;
workSheet.get_Range(app.Cells[1, colIndex], app.Cells[1, colIndex]).Font.Name = "MS Sans Serif";
workSheet.get_Range(app.Cells[1, colIndex], app.Cells[1, colIndex]).Borders.LineStyle = 1;
}
for (int i = 0; i<dtData.Rows.Count; i++)
{
for (int j = 0; j < dtData.Columns.Count; j++)
{
if (this.CheckCellAlign(dtData.Columns[j].ColumnName))
{
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//workSheet.Cells[i + 2, j + 1] ="'" + dtData.Rows[i][j];//.ToString();
}
else
{
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//workSheet.Cells[i + 2, j + 1] = dtData.Rows[i][j];//.ToString();
}
workSheet.Cells[i + 2, j + 1] = dtData.Rows[i][j];//.ToString();
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).Borders.LineStyle = 1;
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).Font.Size = 10;
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).Font.Name = "MS Sans Serif";
}
}
}
private bool CheckCellAlign(string name)
{
bool flg = false;
foreach (string str in colLeft)
{
if (str == name)
{
flg = true;
break;
}
}
return flg;
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Xml;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace GT.Tools.FieldsCheck
{
public class Logic
{
private OleDbConnection xlsConn = null;
private XmlDocument doc = null;
string[] colLeft = null;
public Logic(string fileName)
{
string xmlName = AppDomain.CurrentDomain.BaseDirectory + "tools_config.xml";
doc = new XmlDocument();
doc.Load(xmlName);
//好像不用区分版本也可以
if (fileName.EndsWith(".xls"))
{
xlsConn = new OleDbConnection(string.Format(this.GetConfig("xls2003-conn"), fileName));
}
else
{
xlsConn = new OleDbConnection(string.Format(this.GetConfig("xls2007-conn"), fileName));
}
colLeft = this.GetConfig("style/left").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
}
private string GetConfig(string nodeName)
{
return doc.SelectSingleNode("//" + nodeName).InnerText;
}
private void OpenExcel(string fileName)
{
}
public void GetDataFormExcel()
{
string sheetName = string.Empty;
DataTable dtSchema = null;
DataTable dtData = new DataTable();
string[] fields = null;
try
{
object objOpt = Missing.Value;
string strPathFile = @"D:\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//Excel.Application app = new Excel.ApplicationClass();
Excel.Application app = new Excel.Application();
app.Visible = true;
app.UserControl = true;
//app.DisplayAlerts = false;
Excel.Workbooks objBooks = app.Workbooks;
Excel._Workbook workBook = objBooks.Add(objOpt);
Excel.Sheets objSheets = workBook.Worksheets;
Excel._Worksheet workSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(true);
//Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//Excel.Worksheet workSheet = null;
fields = this.GetConfig("fields").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
this.xlsConn.Open();
//获取Excel各个sheet的名称
dtSchema = this.xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; dtSchema != null && i < dtSchema.Rows.Count; i++)
{
sheetName = dtSchema.Rows[i][2].ToString();
if (!sheetName.EndsWith("$"))
{
continue;
}
doWork(app, workBook, workSheet, sheetName, fields);
}
workBook.SaveAs(strPathFile, objOpt, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//workBook.Close(false, objOpt, objOpt);
workBook.Close(objOpt, strPathFile, objOpt);
//app.SendKeys("%{y}", System.Reflection.Missing.Value);//自动确认保存(ALT+'y')
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workBook = null;
app = null;
workSheet = null;
GC.Collect();
}
catch
{ }
finally
{
if (this.xlsConn.State == ConnectionState.Open)
{
this.xlsConn.Close();
}
}
}
private void doWork(Excel.Application app, Excel._Workbook workBook, Excel._Worksheet workSheet, string sheetName, string[] fields)
{
string strSql = null; // 从文件源查数据的sql语句
OleDbDataAdapter adpUploadData = null; // 从文件源查数据的适配器
DataTable dtData = new DataTable();
strSql = "Select * from [" + sheetName + "]";
adpUploadData = new OleDbDataAdapter(strSql, this.xlsConn);
adpUploadData.Fill(dtData);
if (sheetName.EndsWith("$"))
{
foreach (string f in fields)
{
//判断是否包含所有列
if (dtData.Columns.Contains(f) == false)
{
dtData.Columns.Add(f);
}
}
}
sheetName = sheetName.EndsWith("$") == true ? sheetName.Substring(0, sheetName.Length - 1) : sheetName;
Export(app, workBook, workSheet,dtData, sheetName);
}
private void Export(Excel.Application app, Excel._Workbook workBook, Excel._Worksheet workSheet, System.Data.DataTable dtData, string sheetName)
{
int colIndex = 0;
//Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
//Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
//Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets.Add(Missing.Value, Missing.Value, 1, Missing.Value);
if (null == workSheet)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
workSheet = (Excel.Worksheet)workBook.Worksheets.Add(Type.Missing, workSheet, 1, Type.Missing);
}
workSheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
workSheet.Name = sheetName;
foreach (DataColumn col in dtData.Columns)
{
colIndex++;
//app.Cells[1, colIndex] = col.ColumnName;
workSheet.Cells[1, colIndex] = col.ColumnName;
workSheet.get_Range(app.Cells[1, colIndex], app.Cells[1, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
workSheet.get_Range(app.Cells[1, colIndex], app.Cells[1, colIndex]).Font.Size = 10;
workSheet.get_Range(app.Cells[1, colIndex], app.Cells[1, colIndex]).Font.Name = "MS Sans Serif";
workSheet.get_Range(app.Cells[1, colIndex], app.Cells[1, colIndex]).Borders.LineStyle = 1;
}
for (int i = 0; i<dtData.Rows.Count; i++)
{
for (int j = 0; j < dtData.Columns.Count; j++)
{
if (this.CheckCellAlign(dtData.Columns[j].ColumnName))
{
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//workSheet.Cells[i + 2, j + 1] ="'" + dtData.Rows[i][j];//.ToString();
}
else
{
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//workSheet.Cells[i + 2, j + 1] = dtData.Rows[i][j];//.ToString();
}
workSheet.Cells[i + 2, j + 1] = dtData.Rows[i][j];//.ToString();
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).Borders.LineStyle = 1;
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).Font.Size = 10;
workSheet.get_Range(app.Cells[i + 2, j + 1], app.Cells[i + 2, j + 1]).Font.Name = "MS Sans Serif";
}
}
}
private bool CheckCellAlign(string name)
{
bool flg = false;
foreach (string str in colLeft)
{
if (str == name)
{
flg = true;
break;
}
}
return flg;
}
}
}
方式二：利用OLED方式判断各个Sheet缺少的字段存入hashTable，在利用Excel方式Open文件，并处理，在写入文件，结果正常。
代码
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Xml;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Threading;
using System.Diagnostics;
using System.Runtime.InteropServices;
namespace GT.Tools.FieldsCheck
{
public delegate void NotifyHandler(object sender, string fileSave);
public class ExcelLogic
{
public event NotifyHandler OnExcelNotify;
public SynchronizationContext SyncContext;
private OleDbConnection xlsConn = null;
private XmlDocument doc = null;
private string fileName = string.Empty;
private string filePath = string.Empty;
private string fileExt = string.Empty;
private string fileSave = string.Empty;
string[] fields = null;
string[] colLeft = null;
int colCount = 0;
Hashtable htCol = new Hashtable();
Excel.Application app = null;
Excel.Workbooks objBooks = null;
Excel._Workbook workBook = null;
Excel.Sheets objSheets = null;
Excel._Worksheet workSheet = null;
public ExcelLogic(string fileName, string savePath, NotifyHandler handler)
{
this.OnExcelNotify = (NotifyHandler)Delegate.Combine(this.OnExcelNotify, handler);
this.fileName = fileName;
string ss = Path.GetFileNameWithoutExtension(fileName);
this.fileExt = Path.GetExtension(fileName);
this.fileSave = savePath + @"\" + Path.GetFileNameWithoutExtension(fileName) + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + this.fileExt;
string xmlName = AppDomain.CurrentDomain.BaseDirectory + "tools_config.xml";
doc = new XmlDocument();
doc.Load(xmlName);
//if (this.fileExt == ".xls")
//{
// xlsConn = new OleDbConnection(string.Format(doc.SelectSingleNode("//xls2003-conn").InnerText, fileName));
//}
//else
//{
xlsConn = new OleDbConnection(string.Format(doc.SelectSingleNode("//xls2007-conn").InnerText, fileName));
//}
fields = doc.SelectSingleNode("//fields").InnerText.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
colLeft = doc.SelectSingleNode("//style/left").InnerText.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
colCount = fields.Length;
this.GetExcelCols();
app = new Excel.Application();
app.Visible = true;
//app.UserControl = true;
objBooks = app.Workbooks;
}
private void GetExcelCols()
{
string sheetName = string.Empty;
DataTable dtSchema = null;
DataTable dtData = new DataTable();
try
{
this.xlsConn.Open();
dtSchema = this.xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; dtSchema != null && i < dtSchema.Rows.Count; i++)
{
sheetName = dtSchema.Rows[i][2].ToString();
if (!sheetName.EndsWith("$"))
{
continue;
}
doWork(sheetName);
}
}
catch
{ }
finally
{
if (this.xlsConn.State == ConnectionState.Open)
{
this.xlsConn.Close();
}
}
}
private void doWork(string sheetName)
{
string strSql = null; // 从文件源查数据的sql语句
StringBuilder cols = new StringBuilder(); // 从文件源查数据的sql语句
OleDbDataAdapter adpUploadData = null; // 从文件源查数据的适配器
DataTable dtData = new DataTable();
strSql = "Select * from [" + sheetName + "]";
adpUploadData = new OleDbDataAdapter(strSql, this.xlsConn);
adpUploadData.Fill(dtData);
for(int i=0;i<fields.Length;i++)
{
if (dtData.Columns.Contains(fields[i]) == false)
{
cols.Append(fields[i] + ",");
}
}
if (string.IsNullOrEmpty(cols.ToString()) == false)
{
sheetName = sheetName.EndsWith("$") == true ? sheetName.Substring(0, sheetName.Length - 1) : sheetName;
htCol.Add(sheetName, cols.ToString().Substring(0, cols.ToString().Length - 1));
}
}
public void OpenExcel(object obj)
{
try
{
SyncContext = obj as SynchronizationContext;
workBook = objBooks.Open(this.fileName, 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);
objSheets = workBook.Worksheets;
string[] names = null;
string name = string.Empty;
int temp = -1;
Excel.Range range = null;
for (int i = 0; i < objSheets.Count; i++)
{
workSheet = (Excel._Worksheet)objSheets[i + 1];
name = workSheet.Name;
if (htCol != null && htCol[name] != null)
{
names = ((string)htCol[name]).Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
//Excel.Range r = (Excel.Range)workSheet.Cells[1, 1];
//r.Columns.Cells.Insert(
//r.Text = "Test";
//workSheet.Cells[1,1]
for (int j = 0; j < colCount; j++)
{
range = (Excel.Range)workSheet.Cells[1, j + 1];
if (string.IsNullOrEmpty(range.Text.ToString()))
{
workSheet.Cells[1, j + 1] = names[++temp];
workSheet.get_Range(workSheet.Cells[1, j + 1], workSheet.Cells[1, j + 1]).Font.Size = 10;
workSheet.get_Range(workSheet.Cells[1, j + 1], workSheet.Cells[1, j + 1]).Font.Name = "MS Sans Serif";
workSheet.get_Range(workSheet.Cells[1, j + 1], workSheet.Cells[1, j + 1]).Borders.LineStyle = 1;
}
}
names = null;
temp = -1;
}
}
workBook.SaveAs(this.fileSave.Replace("\\\\", "\\"), Missing.Value, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//workBook.Close(false, objOpt, objOpt);
workBook.Close(Missing.Value, this.fileName, Missing.Value);
//app.SendKeys("%{y}", System.Reflection.Missing.Value);//自动确认保存(ALT+'y')
KillExcelProcess();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//workSheet = null;
//workBook = null;
//app.Quit();
//app = null;
GC.Collect();
//return this.fileSave;
this.OnExcelNotify(this, this.fileSave);
}
catch
{ }
}
[DllImport("User32.dll", CharSet = CharSet.Auto)] //调用API函数,目的获取新增EXCEL进程的进程ID
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public void KillExcelProcess()
{
IntPtr t = new IntPtr(app.Hwnd); //获取EXCEL的句柄
int k = 0;
GetWindowThreadProcessId(t, out k); //利用句柄获取进程ID
Process p = Process.GetProcessById(k);
p.Kill(); //杀死进程
}
}
}
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Xml;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Threading;
using System.Diagnostics;
using System.Runtime.InteropServices;
namespace GT.Tools.FieldsCheck
{
public delegate void NotifyHandler(object sender, string fileSave);
public class ExcelLogic
{
public event NotifyHandler OnExcelNotify;
public SynchronizationContext SyncContext;
private OleDbConnection xlsConn = null;
private XmlDocument doc = null;
private string fileName = string.Empty;
private string filePath = string.Empty;
private string fileExt = string.Empty;
private string fileSave = string.Empty;
string[] fields = null;
string[] colLeft = null;
int colCount = 0;
Hashtable htCol = new Hashtable();
Excel.Application app = null;
Excel.Workbooks objBooks = null;
Excel._Workbook workBook = null;
Excel.Sheets objSheets = null;
Excel._Worksheet workSheet = null;
public ExcelLogic(string fileName, string savePath, NotifyHandler handler)
{
this.OnExcelNotify = (NotifyHandler)Delegate.Combine(this.OnExcelNotify, handler);
this.fileName = fileName;
string ss = Path.GetFileNameWithoutExtension(fileName);
this.fileExt = Path.GetExtension(fileName);
this.fileSave = savePath + @"\" + Path.GetFileNameWithoutExtension(fileName) + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + this.fileExt;
string xmlName = AppDomain.CurrentDomain.BaseDirectory + "tools_config.xml";
doc = new XmlDocument();
doc.Load(xmlName);
//if (this.fileExt == ".xls")
//{
// xlsConn = new OleDbConnection(string.Format(doc.SelectSingleNode("//xls2003-conn").InnerText, fileName));
//}
//else
//{
xlsConn = new OleDbConnection(string.Format(doc.SelectSingleNode("//xls2007-conn").InnerText, fileName));
//}
fields = doc.SelectSingleNode("//fields").InnerText.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
colLeft = doc.SelectSingleNode("//style/left").InnerText.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
colCount = fields.Length;
this.GetExcelCols();
app = new Excel.Application();
app.Visible = true;
//app.UserControl = true;
objBooks = app.Workbooks;
}
private void GetExcelCols()
{
string sheetName = string.Empty;
DataTable dtSchema = null;
DataTable dtData = new DataTable();
try
{
this.xlsConn.Open();
dtSchema = this.xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; dtSchema != null && i < dtSchema.Rows.Count; i++)
{
sheetName = dtSchema.Rows[i][2].ToString();
if (!sheetName.EndsWith("$"))
{
continue;
}
doWork(sheetName);
}
}
catch
{ }
finally
{
if (this.xlsConn.State == ConnectionState.Open)
{
this.xlsConn.Close();
}
}
}
private void doWork(string sheetName)
{
string strSql = null; // 从文件源查数据的sql语句
StringBuilder cols = new StringBuilder(); // 从文件源查数据的sql语句
OleDbDataAdapter adpUploadData = null; // 从文件源查数据的适配器
DataTable dtData = new DataTable();
strSql = "Select * from [" + sheetName + "]";
adpUploadData = new OleDbDataAdapter(strSql, this.xlsConn);
adpUploadData.Fill(dtData);
for(int i=0;i<fields.Length;i++)
{
if (dtData.Columns.Contains(fields[i]) == false)
{
cols.Append(fields[i] + ",");
}
}
if (string.IsNullOrEmpty(cols.ToString()) == false)
{
sheetName = sheetName.EndsWith("$") == true ? sheetName.Substring(0, sheetName.Length - 1) : sheetName;
htCol.Add(sheetName, cols.ToString().Substring(0, cols.ToString().Length - 1));
}
}
public void OpenExcel(object obj)
{
try
{
SyncContext = obj as SynchronizationContext;
workBook = objBooks.Open(this.fileName, 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);
objSheets = workBook.Worksheets;
string[] names = null;
string name = string.Empty;
int temp = -1;
Excel.Range range = null;
for (int i = 0; i < objSheets.Count; i++)
{
workSheet = (Excel._Worksheet)objSheets[i + 1];
name = workSheet.Name;
if (htCol != null && htCol[name] != null)
{
names = ((string)htCol[name]).Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
//Excel.Range r = (Excel.Range)workSheet.Cells[1, 1];
//r.Columns.Cells.Insert(
//r.Text = "Test";
//workSheet.Cells[1,1]
for (int j = 0; j < colCount; j++)
{
range = (Excel.Range)workSheet.Cells[1, j + 1];
if (string.IsNullOrEmpty(range.Text.ToString()))
{
workSheet.Cells[1, j + 1] = names[++temp];
workSheet.get_Range(workSheet.Cells[1, j + 1], workSheet.Cells[1, j + 1]).Font.Size = 10;
workSheet.get_Range(workSheet.Cells[1, j + 1], workSheet.Cells[1, j + 1]).Font.Name = "MS Sans Serif";
workSheet.get_Range(workSheet.Cells[1, j + 1], workSheet.Cells[1, j + 1]).Borders.LineStyle = 1;
}
}
names = null;
temp = -1;
}
}
workBook.SaveAs(this.fileSave.Replace("\\\\", "\\"), Missing.Value, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//workBook.Close(false, objOpt, objOpt);
workBook.Close(Missing.Value, this.fileName, Missing.Value);
//app.SendKeys("%{y}", System.Reflection.Missing.Value);//自动确认保存(ALT+'y')
KillExcelProcess();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//workSheet = null;
//workBook = null;
//app.Quit();
//app = null;
GC.Collect();
//return this.fileSave;
this.OnExcelNotify(this, this.fileSave);
}
catch
{ }
}
[DllImport("User32.dll", CharSet = CharSet.Auto)] //调用API函数,目的获取新增EXCEL进程的进程ID
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public void KillExcelProcess()
{
IntPtr t = new IntPtr(app.Hwnd); //获取EXCEL的句柄
int k = 0;
GetWindowThreadProcessId(t, out k); //利用句柄获取进程ID
Process p = Process.GetProcessById(k);
p.Kill(); //杀死进程
}
}
}
