C# Excel操作类(二)
http://hi.baidu.com/lrxin0630/blog/item/46d6a7db5febc56fd0164e5a.html
C# Excel操作类(三)
http://hi.baidu.com/lrxin0630/blog/item/3702a42988e277f799250a5b.html
C# Excel操作类(四)
http://hi.baidu.com/lrxin0630/blog/item/8df0b07394cc24158701b024.html
/// <SUMMARY>
/// Excel操作类
/// </SUMMARY>
public class ExcelEdit
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 文件名(含路径)
/// </summary>
public string mFilename;
/// <summary>
/// Excel工作进程
/// </summary>
public Microsoft.Office.Interop.Excel.Application app;
/// <summary>
/// 工作簿
/// </summary>
public Microsoft.Office.Interop.Excel.Workbooks wbs;
/// <summary>
/// 工作本
/// </summary>
public Microsoft.Office.Interop.Excel.Workbook wb;
/// <summary>
/// 工作表集
/// </summary>
public Microsoft.Office.Interop.Excel.Worksheets wss;
/// <summary>
/// 工作表
/// </summary>
public Microsoft.Office.Interop.Excel.Worksheet ws;
/// <summary>
/// 构造函数,不创建Microsoft.Office.Interop.Excel工作薄
/// </summary>
public ExcelEdit()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 创建Microsoft.Office.Interop.Excel工作薄
/// </summary>
public void Create()//创建一个Microsoft.Office.Interop.Excel对象
{
try
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
}
/// <summary>
/// 显示Microsoft.Office.Interop.Excel
/// </summary>
public void ShowExcel()
{
app.Visible = true;
}
/// <summary>
/// 打开一个存在的Microsoft.Office.Interop.Excel文件
/// </summary>
/// <param name="FileName">Microsoft.Office.Interop.Excel完整路径加文件名</param>
public void Open(string FileName)//打开一个Microsoft.Office.Interop.Excel文件
{
try
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
app.UserControl = true;//如果只想用程序控制该excel而不想让用户操作时候,可以设置为false
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
}
/// <summary>
/// 获取一个工作表
/// </summary>
/// <param name="SheetName">工作表名</param>
/// <returns></returns>
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName];
return s;
}
/// <summary>
/// 添加一个工作表
/// </summary>
/// <param name="SheetName">表名</param>
/// <returns></returns>
public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName)
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
try
{
s.Name = SheetName;
return s;
}
catch
{
return null;
}
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName">表名</param>
public void DelSheet(string SheetName)
{
try
{
((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
}
/// <summary>
/// 重命名一个工作表
/// </summary>
/// <param name="OldSheetName">原名</param>
/// <param name="NewSheetName">新名</param>
/// <returns></returns>
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[OldSheetName];
try
{
s.Name = NewSheetName;
return s;
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
return null;
}
}
/// <summary>
/// 重命名一个工作表
/// </summary>
/// <param name="Sheet">工作表</param>
/// <param name="NewSheetName">工作表新名</param>
/// <returns></returns>
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)
{
try
{
Sheet.Name = NewSheetName;
return Sheet;
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
return null;
}
}
//_____________________________ole自定义函数___________________________________________________________________________
//______________________________excel和datagridview之间互操作函数_______________________________________________________
/
//把EXCEl中的某工作表显示到datagridview中
/// <summary>
/// ExcelEdit myExcel = new ExcelEdit();
/// myExcel.Open("d:\\数据库表格20071217.xls");
/// myExcel.Excel2DBView("908", this.dataGridView1);
/// myExcel.Close();
/// </summary>
/// <param name="tablename"></param>
/// <param name="dataGridView1"></param>
public void Excel2DBView(string xlsFilaName, string tablename, DataGridView dataGridView1)
{
try
{
string sExcelFile = xlsFilaName;
//string strExcelFileName = @""+ myPath +"";
//string strString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties = 'Microsoft.Office.Interop.Excel 8.0;HDR=NO;IMEX=1 '";
//string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + sExcelFile + ";Extended Properties=Microsoft.Office.Interop.Excel 8.0;";
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";Extended Properties=\"Microsoft.Office.Interop.Excel 8.0;HDR=NO;IMEX=1\"";
OleDbConnection connection = new OleDbConnection(sConnectionString);
string sql_select_commands = "Select * from [" + tablename + "$]";
OleDbDataAdapter adp = new OleDbDataAdapter(sql_select_commands, connection);
DataSet ds = new DataSet();
adp.Fill(ds, tablename);
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
//写入dataGridView控件标题
for (int j = 0; j < ds.Tables[tablename].Columns.Count; j++)
{
dataGridView1.Columns.Add(ds.Tables[tablename].Rows[0][j].ToString(), ds.Tables[tablename].Rows[0][j].ToString());
}
for (int i = 1; i < ds.Tables[tablename].Rows.Count; i++)
{
dataGridView1.Rows.Add();
}
//写入dataGridView控件行数据
for (int i = 1; i < ds.Tables[tablename].Rows.Count; i++)
for (int j = 0; j < ds.Tables[tablename].Columns.Count; j++)
{
dataGridView1.Rows[i - 1].Cells[j].Value = Convert.ToString(ds.Tables[tablename].Rows[i][j]);
}
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
/*
for (int i = 0; i < ds.Tables["Book1"].Rows.Count; i++)
{
sum1 += Convert.ToInt32(ds.Tables["Book1"].Rows[i]["字段A"]);
}
for (int j = 0; j < ds.Tables["Book1"].Rows.Count; j++)
{
sum2 += Convert.ToInt32(ds.Tables["Book1"].Rows[j]["字段B"]);
}
MessageBox.Show(sum1.ToString() + " and " + sum2.ToString());
* */
/*
*备注:
* 用OLEDB进行Microsoft.Office.Interop.Excel文件数据的读取,并返回DataSet数据集。其中有几点需要注意的:
1.连接字符串中参数IMEX 的值:
0 is Export mode 1 is Import mode 2 is Linked mode (full update capabilities)
IMEX有3个值:当IMEX=2 时,EXCEL文档中同时含有字符型和数字型时,比如第C列有3个值,2个为数值型 123,1个为字符型 ABC,当导入时,
页面不报错了,但库里只显示数值型的123,而字符型的ABC则呈现为空值。当IMEX=1时,无上述情况发生,库里可正确呈现 123 和 ABC.
2.参数HDR的值:
HDR=Yes,这代表第一行是标题,不做为数据使用 ,如果用HDR=NO,则表示第一行不是标题,做为数据来使用。系统默认的是YES
3.参数Microsoft.Office.Interop.Excel 8.0
对于Microsoft.Office.Interop.Excel 97以上版本都用Microsoft.Office.Interop.Excel 8.0
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Microsoft.Office.Interop.Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
* */
/*
你可以先用代码打开xls文件:
Set xlApp = CreateObject("Microsoft.Office.Interop.Excel.Application")
Set xlBook = xlApp.Workbooks.Open("d:\text2.xls")
for i=0 to xlBook.Worksheets.Count-1
set xlSheet = xlBook.Worksheets(i)
xlSheet.Name //这就是你需要的每个sheet的名字,保存起来,备后用
next i
这里使用的VB写的范例,变成c#即可.
*/