2005年12月份工资表
部门:销售部 填表日期:2005-12-19
------------------------------------------------------------
ID 姓名 职务 薪水 应扣税额 实发工资
001 zhangsan 2000
002 lisi 3000
------------------------------------------------------------
注:1、*********************************************
2、******************************等等
现求一计算应扣税额和实发工资的解决方案实例!
我已经用ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DB\b4.xls;Extended Properties=Excel 8.0;";试过了,没有成功。可能是因为有表头和尾部的原因。
大家可以用直接在Excel写脚本函数的方法(我不会调用),也可以用C# 程序实现。如001的薪水是D4。后台写一个方法,获取D4的值,然后计算所得税(这个代码不需要大家提供)后把值写到D5(应扣税额)。请高手指教,先谢了。
12 个解决方案
#1
希望大家能提供一个简单的例子。
#2
http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599
#3
大家帮我想想办法啊!
#4
//创建Application对象
Excel.Application xApp=new Excel.ApplicationClass();
xApp.Visible=true;
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
// Excel.Workbook xBook=xApp.Workbooks._Open(@"D:\Sample.xls",
// Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//Excel.Workbook xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
//指定要操作的Sheet,两种方式:
//Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
//Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;
//读取数据,通过Range对象
//Excel.Range rng1=xSheet.get_Range("A1",Type.Missing);
//Console.WriteLine(rng1.Value2);
//读取,通过Range对象,但使用不同的接口得到Range
//Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1];
//Console.WriteLine(rng2.Value2);
//写入数据
//Excel.Range rng3=xSheet.get_Range("C6",Missing.Value);
Excel.Workbook xBook=xApp.Workbooks.Add(System.Reflection.Missing.Value);//新建文件的代码
Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
Excel.Range rng3=xSheet.get_Range("C6",System.Reflection.Missing.Value);
rng3.Cells.FormulaR1C1 = txtnr.Text;
//rng3.Value2="Hello";
rng3.Interior.ColorIndex=6; //设置Range的背景色
//保存方式一:保存WorkBook
//xBook.SaveAs(@"D:\CData.xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value
//,Missing.Value,Missing.Value);
//保存方式二:保存WorkSheet
//xSheet.SaveAs(@"D:\CData2.xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value
//,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//保存方式三
//xBook.Save();
//xSheet=null;
//xBook=null;
//xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
//xApp=null;
Excel.Application xApp=new Excel.ApplicationClass();
xApp.Visible=true;
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
// Excel.Workbook xBook=xApp.Workbooks._Open(@"D:\Sample.xls",
// Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//Excel.Workbook xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
//指定要操作的Sheet,两种方式:
//Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
//Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;
//读取数据,通过Range对象
//Excel.Range rng1=xSheet.get_Range("A1",Type.Missing);
//Console.WriteLine(rng1.Value2);
//读取,通过Range对象,但使用不同的接口得到Range
//Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1];
//Console.WriteLine(rng2.Value2);
//写入数据
//Excel.Range rng3=xSheet.get_Range("C6",Missing.Value);
Excel.Workbook xBook=xApp.Workbooks.Add(System.Reflection.Missing.Value);//新建文件的代码
Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
Excel.Range rng3=xSheet.get_Range("C6",System.Reflection.Missing.Value);
rng3.Cells.FormulaR1C1 = txtnr.Text;
//rng3.Value2="Hello";
rng3.Interior.ColorIndex=6; //设置Range的背景色
//保存方式一:保存WorkBook
//xBook.SaveAs(@"D:\CData.xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value
//,Missing.Value,Missing.Value);
//保存方式二:保存WorkSheet
//xSheet.SaveAs(@"D:\CData2.xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value
//,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//保存方式三
//xBook.Save();
//xSheet=null;
//xBook=null;
//xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
//xApp=null;
#5
jiang8282(雪山飞狐)
不是这个方法,我的Excel表有表头和尾。用这个方法不行。
不是这个方法,我的Excel表有表头和尾。用这个方法不行。
#6
我这是针对webform的
操作excel和操作数据库差不多
如下 验证过的东东
//创建一个数据链接
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\sample.xls;Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConn.Open ( ) ;
string strCom1 = " SELECT * FROM [Sheet1$] " ; //选择出数据
//string strCom1 = "update [Sheet1$] set name='ggg' " ;//更新数据 "name"--> (excel里第一行数据相当于字段名)
OleDbCommand myconmm=new OleDbCommand (strCom1,myConn);
myconmm.ExecuteNonQuery ();
//打开数据链接,得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
//创建一个 DataSet对象
DataSet myDataSet = new DataSet ( ) ;
//得到自己的DataSet对象
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
//关闭此数据链接
myConn.Close ( ) ;
操作excel和操作数据库差不多
如下 验证过的东东
//创建一个数据链接
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\sample.xls;Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConn.Open ( ) ;
string strCom1 = " SELECT * FROM [Sheet1$] " ; //选择出数据
//string strCom1 = "update [Sheet1$] set name='ggg' " ;//更新数据 "name"--> (excel里第一行数据相当于字段名)
OleDbCommand myconmm=new OleDbCommand (strCom1,myConn);
myconmm.ExecuteNonQuery ();
//打开数据链接,得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
//创建一个 DataSet对象
DataSet myDataSet = new DataSet ( ) ;
//得到自己的DataSet对象
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
//关闭此数据链接
myConn.Close ( ) ;
#7
aspczlover(你的骄傲...)
谢谢你!但是你的这个方法行不通,因为我的Excel有表头和表尾。
谢谢你!但是你的这个方法行不通,因为我的Excel有表头和表尾。
#8
首先要从com引入excel命名空间
运行dcomcnfg命令配置excel权限
excel里第一行数据列的值相当于字段名
比如excel假如是这样的数据:
name age addr
wang 22 beijing
zhao 23 shanghai
SELECT age,addr FROM [Sheet1$] where name='wang'
就会选择出 22,beijing
运行dcomcnfg命令配置excel权限
excel里第一行数据列的值相当于字段名
比如excel假如是这样的数据:
name age addr
wang 22 beijing
zhao 23 shanghai
SELECT age,addr FROM [Sheet1$] where name='wang'
就会选择出 22,beijing
#9
using System;
using System.Data;
using System.Windows.Forms;
using Excel;
namespace TaskVision
{
public class ExcelExport
{
// C# Note: This class required extensive changes from the VB.NET version due to differences in
// the Excel COM object interface seen by C# compared to the interface seen by VB.NET.
private object _optionalValue = System.Reflection.Missing.Value;
// Export to Excel if it is installed on their machine
public void ExportTasks(DataSet TasksData, DataView TasksDataView, DataGrid TasksGrid)
{
Excel.Application excelApp;
Excel.Workbook workBook;
Excel.Worksheet ws;
Excel.Range r;
int row = 1;
int cell = 1;
int priorityHigh, priorityNormal, priorityLow;
try
{
// which ever version of Excel was referenced to build the interop file
// should be installed on client machines
excelApp = new Excel.Application();
// create the task list on the first worksheet in the workbook
workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws = (Worksheet) workBook.Worksheets[1];
}
catch
{
// remove the excel references: Excel, Microsoft.Office.Core, and VBIDE and re-add
// the COM reference Microsoft Excel 10.0 Type Library.
MessageBox.Show("An incompatible version of Microsoft Excel is intalled on your system.");
return;
}
// Title
ws.Name = "Task Management";
ws.Cells[row, 1] = "Tasks as of " + DateTime.Now.ToShortDateString();
r = (Range) ws.Cells[row, 1];
r.Font.Bold = true;
row += 2;
// create Headers
foreach(DataColumn dc in TasksData.Tables[0].Columns)
{
foreach(DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
{
if (cs.Width != 0)
{
if (cs.MappingName == dc.ColumnName)
{
ws.Cells[row, cell] = cs.HeaderText;
// Autofit the column: unusual C# interface: use Columns indexer,
// pass in column index as first parameter (row index), optional value
// as second parameter (column index), and it uses the first parameter as column index.
r = (Range) ws.Columns[cell, _optionalValue];
r.AutoFit();
break;
}
}
cell++;
}
cell = 1;
}
row++;
// create rows
priorityHigh = 0;
priorityNormal = 0;
priorityLow = 0;
int i;
for (i = 0; i < TasksDataView.Count; i++)
{
foreach (DataColumn dc in TasksData.Tables[0].Columns)
{
foreach (DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
{
if (cs.Width != 0)
{
if (cs.MappingName == dc.ColumnName)
{
if (TasksDataView[i][dc.ColumnName.ToString()] is DateTime)
ws.Cells[row, cell] = String.Format("{0:d}", TasksDataView[i][dc.ColumnName.ToString()]);
else
ws.Cells[row, cell] = TasksDataView[i][dc.ColumnName.ToString()];
if (cs.MappingName == "PriorityText")
{
switch (TasksDataView[i][dc.ColumnName.ToString()].ToString())
{
case "Major":
priorityHigh++;
break;
case "Medium":
priorityNormal++;
break;
case "Minor":
priorityLow++;
break;
}
}
// Do the AutoFit C# column call again:
r = (Range) ws.Columns[cell, _optionalValue];
r.AutoFit();
break;
}
}
cell++;
}
cell = 1;
}
row++;
}
// Priority Summary and Chart on Second Worksheet
Excel.Worksheet ws2 = (Worksheet) workBook.Worksheets.Add(_optionalValue, ws, 1, XlWBATemplate.xlWBATWorksheet);
ws2.Name = "Priority Summary";
row = 1;
ws2.Cells[row, 1] = "Priority Summary";
r = (Range) ws.Cells[row, 1];
r.Font.Bold = true;
row += 2;
ws2.Cells[row, 1] = "Major";
ws2.Cells[row, 2] = priorityHigh.ToString();
row++;
ws2.Cells[row, 1] = "Medium";
ws2.Cells[row, 2] = priorityNormal.ToString();
row++;
ws2.Cells[row, 1] = "Minor";
ws2.Cells[row, 2] = priorityLow.ToString();
// AutoFit both columns:
r = (Range) ws2.Columns[1, _optionalValue];
r.AutoFit();
r = (Range) ws2.Columns[2, _optionalValue];
r.AutoFit();
// Create the chart--this is quite different than the VB.NET version
r = ws2.get_Range("A3", "B5");
r.Select();
Chart newChart = (Chart) workBook.Charts.Add(_optionalValue, ws, _optionalValue, _optionalValue);
newChart.ChartType = XlChartType.xl3DPieExploded;
newChart.SetSourceData(r, XlRowCol.xlColumns);
newChart.Name = "Priority Summary Chart";
newChart.HasTitle = true;
newChart.ChartTitle.Text = "Tasks by Priority";
newChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws2.Name);
// Show(Excel)
ws.Activate();
excelApp.Visible = true;
}
}
}
using System.Data;
using System.Windows.Forms;
using Excel;
namespace TaskVision
{
public class ExcelExport
{
// C# Note: This class required extensive changes from the VB.NET version due to differences in
// the Excel COM object interface seen by C# compared to the interface seen by VB.NET.
private object _optionalValue = System.Reflection.Missing.Value;
// Export to Excel if it is installed on their machine
public void ExportTasks(DataSet TasksData, DataView TasksDataView, DataGrid TasksGrid)
{
Excel.Application excelApp;
Excel.Workbook workBook;
Excel.Worksheet ws;
Excel.Range r;
int row = 1;
int cell = 1;
int priorityHigh, priorityNormal, priorityLow;
try
{
// which ever version of Excel was referenced to build the interop file
// should be installed on client machines
excelApp = new Excel.Application();
// create the task list on the first worksheet in the workbook
workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws = (Worksheet) workBook.Worksheets[1];
}
catch
{
// remove the excel references: Excel, Microsoft.Office.Core, and VBIDE and re-add
// the COM reference Microsoft Excel 10.0 Type Library.
MessageBox.Show("An incompatible version of Microsoft Excel is intalled on your system.");
return;
}
// Title
ws.Name = "Task Management";
ws.Cells[row, 1] = "Tasks as of " + DateTime.Now.ToShortDateString();
r = (Range) ws.Cells[row, 1];
r.Font.Bold = true;
row += 2;
// create Headers
foreach(DataColumn dc in TasksData.Tables[0].Columns)
{
foreach(DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
{
if (cs.Width != 0)
{
if (cs.MappingName == dc.ColumnName)
{
ws.Cells[row, cell] = cs.HeaderText;
// Autofit the column: unusual C# interface: use Columns indexer,
// pass in column index as first parameter (row index), optional value
// as second parameter (column index), and it uses the first parameter as column index.
r = (Range) ws.Columns[cell, _optionalValue];
r.AutoFit();
break;
}
}
cell++;
}
cell = 1;
}
row++;
// create rows
priorityHigh = 0;
priorityNormal = 0;
priorityLow = 0;
int i;
for (i = 0; i < TasksDataView.Count; i++)
{
foreach (DataColumn dc in TasksData.Tables[0].Columns)
{
foreach (DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
{
if (cs.Width != 0)
{
if (cs.MappingName == dc.ColumnName)
{
if (TasksDataView[i][dc.ColumnName.ToString()] is DateTime)
ws.Cells[row, cell] = String.Format("{0:d}", TasksDataView[i][dc.ColumnName.ToString()]);
else
ws.Cells[row, cell] = TasksDataView[i][dc.ColumnName.ToString()];
if (cs.MappingName == "PriorityText")
{
switch (TasksDataView[i][dc.ColumnName.ToString()].ToString())
{
case "Major":
priorityHigh++;
break;
case "Medium":
priorityNormal++;
break;
case "Minor":
priorityLow++;
break;
}
}
// Do the AutoFit C# column call again:
r = (Range) ws.Columns[cell, _optionalValue];
r.AutoFit();
break;
}
}
cell++;
}
cell = 1;
}
row++;
}
// Priority Summary and Chart on Second Worksheet
Excel.Worksheet ws2 = (Worksheet) workBook.Worksheets.Add(_optionalValue, ws, 1, XlWBATemplate.xlWBATWorksheet);
ws2.Name = "Priority Summary";
row = 1;
ws2.Cells[row, 1] = "Priority Summary";
r = (Range) ws.Cells[row, 1];
r.Font.Bold = true;
row += 2;
ws2.Cells[row, 1] = "Major";
ws2.Cells[row, 2] = priorityHigh.ToString();
row++;
ws2.Cells[row, 1] = "Medium";
ws2.Cells[row, 2] = priorityNormal.ToString();
row++;
ws2.Cells[row, 1] = "Minor";
ws2.Cells[row, 2] = priorityLow.ToString();
// AutoFit both columns:
r = (Range) ws2.Columns[1, _optionalValue];
r.AutoFit();
r = (Range) ws2.Columns[2, _optionalValue];
r.AutoFit();
// Create the chart--this is quite different than the VB.NET version
r = ws2.get_Range("A3", "B5");
r.Select();
Chart newChart = (Chart) workBook.Charts.Add(_optionalValue, ws, _optionalValue, _optionalValue);
newChart.ChartType = XlChartType.xl3DPieExploded;
newChart.SetSourceData(r, XlRowCol.xlColumns);
newChart.Name = "Priority Summary Chart";
newChart.HasTitle = true;
newChart.ChartTitle.Text = "Tasks by Priority";
newChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws2.Name);
// Show(Excel)
ws.Activate();
excelApp.Visible = true;
}
}
}
#10
我现在测试 zwfhome1(任由风吹)兄弟提供的方法
#11
请问zwfhome1(任由风吹) Missing.Value是从哪儿来的啊?
#12
谢谢高手!
#1
希望大家能提供一个简单的例子。
#2
http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599
#3
大家帮我想想办法啊!
#4
//创建Application对象
Excel.Application xApp=new Excel.ApplicationClass();
xApp.Visible=true;
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
// Excel.Workbook xBook=xApp.Workbooks._Open(@"D:\Sample.xls",
// Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//Excel.Workbook xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
//指定要操作的Sheet,两种方式:
//Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
//Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;
//读取数据,通过Range对象
//Excel.Range rng1=xSheet.get_Range("A1",Type.Missing);
//Console.WriteLine(rng1.Value2);
//读取,通过Range对象,但使用不同的接口得到Range
//Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1];
//Console.WriteLine(rng2.Value2);
//写入数据
//Excel.Range rng3=xSheet.get_Range("C6",Missing.Value);
Excel.Workbook xBook=xApp.Workbooks.Add(System.Reflection.Missing.Value);//新建文件的代码
Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
Excel.Range rng3=xSheet.get_Range("C6",System.Reflection.Missing.Value);
rng3.Cells.FormulaR1C1 = txtnr.Text;
//rng3.Value2="Hello";
rng3.Interior.ColorIndex=6; //设置Range的背景色
//保存方式一:保存WorkBook
//xBook.SaveAs(@"D:\CData.xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value
//,Missing.Value,Missing.Value);
//保存方式二:保存WorkSheet
//xSheet.SaveAs(@"D:\CData2.xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value
//,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//保存方式三
//xBook.Save();
//xSheet=null;
//xBook=null;
//xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
//xApp=null;
Excel.Application xApp=new Excel.ApplicationClass();
xApp.Visible=true;
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
// Excel.Workbook xBook=xApp.Workbooks._Open(@"D:\Sample.xls",
// Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//Excel.Workbook xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
//指定要操作的Sheet,两种方式:
//Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
//Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;
//读取数据,通过Range对象
//Excel.Range rng1=xSheet.get_Range("A1",Type.Missing);
//Console.WriteLine(rng1.Value2);
//读取,通过Range对象,但使用不同的接口得到Range
//Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1];
//Console.WriteLine(rng2.Value2);
//写入数据
//Excel.Range rng3=xSheet.get_Range("C6",Missing.Value);
Excel.Workbook xBook=xApp.Workbooks.Add(System.Reflection.Missing.Value);//新建文件的代码
Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
Excel.Range rng3=xSheet.get_Range("C6",System.Reflection.Missing.Value);
rng3.Cells.FormulaR1C1 = txtnr.Text;
//rng3.Value2="Hello";
rng3.Interior.ColorIndex=6; //设置Range的背景色
//保存方式一:保存WorkBook
//xBook.SaveAs(@"D:\CData.xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value
// ,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value
//,Missing.Value,Missing.Value);
//保存方式二:保存WorkSheet
//xSheet.SaveAs(@"D:\CData2.xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value
//,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//保存方式三
//xBook.Save();
//xSheet=null;
//xBook=null;
//xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
//xApp=null;
#5
jiang8282(雪山飞狐)
不是这个方法,我的Excel表有表头和尾。用这个方法不行。
不是这个方法,我的Excel表有表头和尾。用这个方法不行。
#6
我这是针对webform的
操作excel和操作数据库差不多
如下 验证过的东东
//创建一个数据链接
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\sample.xls;Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConn.Open ( ) ;
string strCom1 = " SELECT * FROM [Sheet1$] " ; //选择出数据
//string strCom1 = "update [Sheet1$] set name='ggg' " ;//更新数据 "name"--> (excel里第一行数据相当于字段名)
OleDbCommand myconmm=new OleDbCommand (strCom1,myConn);
myconmm.ExecuteNonQuery ();
//打开数据链接,得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
//创建一个 DataSet对象
DataSet myDataSet = new DataSet ( ) ;
//得到自己的DataSet对象
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
//关闭此数据链接
myConn.Close ( ) ;
操作excel和操作数据库差不多
如下 验证过的东东
//创建一个数据链接
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\sample.xls;Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConn.Open ( ) ;
string strCom1 = " SELECT * FROM [Sheet1$] " ; //选择出数据
//string strCom1 = "update [Sheet1$] set name='ggg' " ;//更新数据 "name"--> (excel里第一行数据相当于字段名)
OleDbCommand myconmm=new OleDbCommand (strCom1,myConn);
myconmm.ExecuteNonQuery ();
//打开数据链接,得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
//创建一个 DataSet对象
DataSet myDataSet = new DataSet ( ) ;
//得到自己的DataSet对象
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
//关闭此数据链接
myConn.Close ( ) ;
#7
aspczlover(你的骄傲...)
谢谢你!但是你的这个方法行不通,因为我的Excel有表头和表尾。
谢谢你!但是你的这个方法行不通,因为我的Excel有表头和表尾。
#8
首先要从com引入excel命名空间
运行dcomcnfg命令配置excel权限
excel里第一行数据列的值相当于字段名
比如excel假如是这样的数据:
name age addr
wang 22 beijing
zhao 23 shanghai
SELECT age,addr FROM [Sheet1$] where name='wang'
就会选择出 22,beijing
运行dcomcnfg命令配置excel权限
excel里第一行数据列的值相当于字段名
比如excel假如是这样的数据:
name age addr
wang 22 beijing
zhao 23 shanghai
SELECT age,addr FROM [Sheet1$] where name='wang'
就会选择出 22,beijing
#9
using System;
using System.Data;
using System.Windows.Forms;
using Excel;
namespace TaskVision
{
public class ExcelExport
{
// C# Note: This class required extensive changes from the VB.NET version due to differences in
// the Excel COM object interface seen by C# compared to the interface seen by VB.NET.
private object _optionalValue = System.Reflection.Missing.Value;
// Export to Excel if it is installed on their machine
public void ExportTasks(DataSet TasksData, DataView TasksDataView, DataGrid TasksGrid)
{
Excel.Application excelApp;
Excel.Workbook workBook;
Excel.Worksheet ws;
Excel.Range r;
int row = 1;
int cell = 1;
int priorityHigh, priorityNormal, priorityLow;
try
{
// which ever version of Excel was referenced to build the interop file
// should be installed on client machines
excelApp = new Excel.Application();
// create the task list on the first worksheet in the workbook
workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws = (Worksheet) workBook.Worksheets[1];
}
catch
{
// remove the excel references: Excel, Microsoft.Office.Core, and VBIDE and re-add
// the COM reference Microsoft Excel 10.0 Type Library.
MessageBox.Show("An incompatible version of Microsoft Excel is intalled on your system.");
return;
}
// Title
ws.Name = "Task Management";
ws.Cells[row, 1] = "Tasks as of " + DateTime.Now.ToShortDateString();
r = (Range) ws.Cells[row, 1];
r.Font.Bold = true;
row += 2;
// create Headers
foreach(DataColumn dc in TasksData.Tables[0].Columns)
{
foreach(DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
{
if (cs.Width != 0)
{
if (cs.MappingName == dc.ColumnName)
{
ws.Cells[row, cell] = cs.HeaderText;
// Autofit the column: unusual C# interface: use Columns indexer,
// pass in column index as first parameter (row index), optional value
// as second parameter (column index), and it uses the first parameter as column index.
r = (Range) ws.Columns[cell, _optionalValue];
r.AutoFit();
break;
}
}
cell++;
}
cell = 1;
}
row++;
// create rows
priorityHigh = 0;
priorityNormal = 0;
priorityLow = 0;
int i;
for (i = 0; i < TasksDataView.Count; i++)
{
foreach (DataColumn dc in TasksData.Tables[0].Columns)
{
foreach (DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
{
if (cs.Width != 0)
{
if (cs.MappingName == dc.ColumnName)
{
if (TasksDataView[i][dc.ColumnName.ToString()] is DateTime)
ws.Cells[row, cell] = String.Format("{0:d}", TasksDataView[i][dc.ColumnName.ToString()]);
else
ws.Cells[row, cell] = TasksDataView[i][dc.ColumnName.ToString()];
if (cs.MappingName == "PriorityText")
{
switch (TasksDataView[i][dc.ColumnName.ToString()].ToString())
{
case "Major":
priorityHigh++;
break;
case "Medium":
priorityNormal++;
break;
case "Minor":
priorityLow++;
break;
}
}
// Do the AutoFit C# column call again:
r = (Range) ws.Columns[cell, _optionalValue];
r.AutoFit();
break;
}
}
cell++;
}
cell = 1;
}
row++;
}
// Priority Summary and Chart on Second Worksheet
Excel.Worksheet ws2 = (Worksheet) workBook.Worksheets.Add(_optionalValue, ws, 1, XlWBATemplate.xlWBATWorksheet);
ws2.Name = "Priority Summary";
row = 1;
ws2.Cells[row, 1] = "Priority Summary";
r = (Range) ws.Cells[row, 1];
r.Font.Bold = true;
row += 2;
ws2.Cells[row, 1] = "Major";
ws2.Cells[row, 2] = priorityHigh.ToString();
row++;
ws2.Cells[row, 1] = "Medium";
ws2.Cells[row, 2] = priorityNormal.ToString();
row++;
ws2.Cells[row, 1] = "Minor";
ws2.Cells[row, 2] = priorityLow.ToString();
// AutoFit both columns:
r = (Range) ws2.Columns[1, _optionalValue];
r.AutoFit();
r = (Range) ws2.Columns[2, _optionalValue];
r.AutoFit();
// Create the chart--this is quite different than the VB.NET version
r = ws2.get_Range("A3", "B5");
r.Select();
Chart newChart = (Chart) workBook.Charts.Add(_optionalValue, ws, _optionalValue, _optionalValue);
newChart.ChartType = XlChartType.xl3DPieExploded;
newChart.SetSourceData(r, XlRowCol.xlColumns);
newChart.Name = "Priority Summary Chart";
newChart.HasTitle = true;
newChart.ChartTitle.Text = "Tasks by Priority";
newChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws2.Name);
// Show(Excel)
ws.Activate();
excelApp.Visible = true;
}
}
}
using System.Data;
using System.Windows.Forms;
using Excel;
namespace TaskVision
{
public class ExcelExport
{
// C# Note: This class required extensive changes from the VB.NET version due to differences in
// the Excel COM object interface seen by C# compared to the interface seen by VB.NET.
private object _optionalValue = System.Reflection.Missing.Value;
// Export to Excel if it is installed on their machine
public void ExportTasks(DataSet TasksData, DataView TasksDataView, DataGrid TasksGrid)
{
Excel.Application excelApp;
Excel.Workbook workBook;
Excel.Worksheet ws;
Excel.Range r;
int row = 1;
int cell = 1;
int priorityHigh, priorityNormal, priorityLow;
try
{
// which ever version of Excel was referenced to build the interop file
// should be installed on client machines
excelApp = new Excel.Application();
// create the task list on the first worksheet in the workbook
workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws = (Worksheet) workBook.Worksheets[1];
}
catch
{
// remove the excel references: Excel, Microsoft.Office.Core, and VBIDE and re-add
// the COM reference Microsoft Excel 10.0 Type Library.
MessageBox.Show("An incompatible version of Microsoft Excel is intalled on your system.");
return;
}
// Title
ws.Name = "Task Management";
ws.Cells[row, 1] = "Tasks as of " + DateTime.Now.ToShortDateString();
r = (Range) ws.Cells[row, 1];
r.Font.Bold = true;
row += 2;
// create Headers
foreach(DataColumn dc in TasksData.Tables[0].Columns)
{
foreach(DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
{
if (cs.Width != 0)
{
if (cs.MappingName == dc.ColumnName)
{
ws.Cells[row, cell] = cs.HeaderText;
// Autofit the column: unusual C# interface: use Columns indexer,
// pass in column index as first parameter (row index), optional value
// as second parameter (column index), and it uses the first parameter as column index.
r = (Range) ws.Columns[cell, _optionalValue];
r.AutoFit();
break;
}
}
cell++;
}
cell = 1;
}
row++;
// create rows
priorityHigh = 0;
priorityNormal = 0;
priorityLow = 0;
int i;
for (i = 0; i < TasksDataView.Count; i++)
{
foreach (DataColumn dc in TasksData.Tables[0].Columns)
{
foreach (DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
{
if (cs.Width != 0)
{
if (cs.MappingName == dc.ColumnName)
{
if (TasksDataView[i][dc.ColumnName.ToString()] is DateTime)
ws.Cells[row, cell] = String.Format("{0:d}", TasksDataView[i][dc.ColumnName.ToString()]);
else
ws.Cells[row, cell] = TasksDataView[i][dc.ColumnName.ToString()];
if (cs.MappingName == "PriorityText")
{
switch (TasksDataView[i][dc.ColumnName.ToString()].ToString())
{
case "Major":
priorityHigh++;
break;
case "Medium":
priorityNormal++;
break;
case "Minor":
priorityLow++;
break;
}
}
// Do the AutoFit C# column call again:
r = (Range) ws.Columns[cell, _optionalValue];
r.AutoFit();
break;
}
}
cell++;
}
cell = 1;
}
row++;
}
// Priority Summary and Chart on Second Worksheet
Excel.Worksheet ws2 = (Worksheet) workBook.Worksheets.Add(_optionalValue, ws, 1, XlWBATemplate.xlWBATWorksheet);
ws2.Name = "Priority Summary";
row = 1;
ws2.Cells[row, 1] = "Priority Summary";
r = (Range) ws.Cells[row, 1];
r.Font.Bold = true;
row += 2;
ws2.Cells[row, 1] = "Major";
ws2.Cells[row, 2] = priorityHigh.ToString();
row++;
ws2.Cells[row, 1] = "Medium";
ws2.Cells[row, 2] = priorityNormal.ToString();
row++;
ws2.Cells[row, 1] = "Minor";
ws2.Cells[row, 2] = priorityLow.ToString();
// AutoFit both columns:
r = (Range) ws2.Columns[1, _optionalValue];
r.AutoFit();
r = (Range) ws2.Columns[2, _optionalValue];
r.AutoFit();
// Create the chart--this is quite different than the VB.NET version
r = ws2.get_Range("A3", "B5");
r.Select();
Chart newChart = (Chart) workBook.Charts.Add(_optionalValue, ws, _optionalValue, _optionalValue);
newChart.ChartType = XlChartType.xl3DPieExploded;
newChart.SetSourceData(r, XlRowCol.xlColumns);
newChart.Name = "Priority Summary Chart";
newChart.HasTitle = true;
newChart.ChartTitle.Text = "Tasks by Priority";
newChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws2.Name);
// Show(Excel)
ws.Activate();
excelApp.Visible = true;
}
}
}
#10
我现在测试 zwfhome1(任由风吹)兄弟提供的方法
#11
请问zwfhome1(任由风吹) Missing.Value是从哪儿来的啊?
#12
谢谢高手!