求从SQLserver2000中将表的数据导入到Excel的(C#)的操作代码 用于网页的模式(B/S)
求从SQLserver2000中将表的数据导入到Excel的(C#)的操作代码 用于网页的模式(B/S)
[解决办法]
给你一个从excel到数据库的，反之亦然
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source= "+ Path + "; "+ "Extended Properties=Excel 8.0; ";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = " ";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel= "select * from [sheet1$] ";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1 ");
return ds;
}
对于EXCEL中的表即sheet([sheet1$])如果不是固定的可以使用下面的方法得到
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source= "+ Path + "; "+ "Extended Properties=Excel 8.0; ";
OleDbConnection conn = new OleDbConnection(strConn);
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
string tableName=schemaTable.Rows[0][2].ToString().Trim();
Excel文件的写入
public void DSToExcel(string Path,DataSet oldds)
{
//先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+path1+ ";Extended Properties=Excel 8.0 " ;
OleDbConnection myConn = new OleDbConnection(strCon) ;
string strCom= "select * from [Sheet1$] ";
myConn.Open ( ) ;
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom, myConn ) ;
ystem.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
builder.QuotePrefix= "[ "; //获取insert语句中保留字符（起始位置）
builder.QuoteSuffix= "] "; //获取insert语句中保留字符（结束位置）
DataSet newds=new DataSet();
myCommand.Fill(newds , "Table1 ") ;
for(int i=0;i <oldds.Tables[0].Rows.Count;i++)
{
//在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added
DataRow nrow=aDataSet.Tables[ "Table1 "].NewRow();
for(int j=0;j <newds.Tables[0].Columns.Count;j++)
{
nrow[j]=oldds.Tables[0].Rows[i][j];
}
newds.Tables[ "Table1 "].Rows.Add(nrow);
}
myCommand.Update(newds, "Table1 ");
myConn.Close();
}
[解决办法]
将sql server中的数据倒入Excel
author:ouyang76cn()
虽然，sql server中的DTS也能将数据倒入Excel，但不如使用程序灵活,
本程序主要代码在按钮函数内。可适应于报表开发的读取数据部分:)
我删除了原程序的很多垃圾代码,只留主要起作用的代码
//加入名称空间
using System.Data;
using System.Data.SqlClient;
//定义方法GetData(),返回一个数据表
private System.Data.DataTable GetData()
{
SqlConnection conn= new SqlConnection(@ "Server=PXGD2;Initial Catalog=pingxiang;Uid=sa;Pwd=; ");
SqlDataAdapter adapter= new SqlDataAdapter( "select username 用户名,catalyst_port 占用端口,home_address 住宅地址,ip_address
ip地址,phone 电话,addtime 开通日期 from userinfo where catalyst_port=1 or catalyst_port= ' order by ip_address desc ",conn);
DataSet ds= new DataSet();
try
{
adapter.Fill(ds, "Customer ");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
return ds.Tables[0];
}
//按钮
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel= new Excel.Application();
int rowIndex=1;
int colIndex=0;
excel.Application.Workbooks.Add(true);
DataTable table=GetData();
//将所得到的表的列名,赋值给单元格
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
//同样方法处理数据
foreach(DataRow row in table.Rows)
{
rowIndex++;
colIndex=0;
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
}
}
//不可见,即后台处理
excel.Visible=true;
}
[解决办法]
偶也是新人，各位见笑了
public static void ExportToExcel(string tableName, out string excelName, out string excelPath)
{
string areaTitle = null;
DataTable areaList = null;
ArrayList titleList = new ArrayList();
ArrayList parameterInnerList = new ArrayList();//内联参数
ArrayList parameterOuterList = new ArrayList();//外联
ArrayList corresponding = new ArrayList();
char[] splitDot = new char[] { '. ', ', ' };
string strSQL = null;
DataTable dt = null;
Boolean bArea = false;//true=插入下一级地区，fale=插入下属所有县
Excel.Application oExcel = null;
Excel.Workbooks oWorkbooks = null;
Excel._Workbook oWorkbook = null;
Excel.Sheets oSheets = null;
Excel._Worksheet oSheet = null;
Excel.Range oRange = null;
//Excel.Font oFont = null;
// Frequenty-used variable for optional arguments.
object missing = System.Reflection.Missing.Value;
//Start Excel and get Application object.
oExcel = new Excel.Application();
//Excel项目可见
//oExcel.Visible = true;
//Get a new workbooks
oWorkbooks = (Excel.Workbooks)oExcel.Workbooks;
//Get a new workbook.
oWorkbook = (Excel._Workbook)(oWorkbooks.Add(missing));
//Get a new worksheets
oSheets = (Excel.Sheets)oWorkbook.Worksheets;
//Get a worksheet
oSheet = (Excel._Worksheet)(oSheets.get_Item(1));
strSQL = "select ID from tit_tables where TableIndex= ' " + tableName + " ' ";
dt = DatabaseFactory.ExecuteQuery(strSQL);
string strExcelName = tableName + "$1.xls ";
string path = "../DataTemplate " + "/ " + strExcelName;
////判断该文件是否存在，如果已经存在则直接跳过
//if (!File.Exists(HttpContext.Current.Server.MapPath(path)))
//{
strSQL = "select * from tit_fields where tableid= " + dt.Rows[0][0].ToString()+ " AND FieldIndex <> 'ID ' ";
dt = null;
dt = DatabaseFactory.ExecuteQuery(strSQL);
#region 遍历
foreach (DataRow dr in dt.Rows)
{
#region 找到代表地区的行
//string strTest = dr[ "Is_AreaCode "].ToString();
if (dr[ "Is_AreaCode "] != null && dr[ "Is_AreaCode "].ToString() == "True ")
{
//将地区范围插入第一行第一列dr[ "FieldName "].ToString();
areaTitle = dr[ "FieldName "].ToString();
if (bArea)
{
//逐行插入下属一级地区
strSQL = "select Area_name from area where Parent_ID= " + HttpContext.Current.Session[ "AreaID "].ToString() + " and Parent_ID <> Area_ID ";
}
else
{
//逐行插入下属所有县级地区
strSQL = "select Area_Code,Area_Lv from Area where Area_ID= " + HttpContext.Current.Session[ "AreaID "].ToString();
DataTable dtTmp = DatabaseFactory.ExecuteQuery(strSQL);
string strTemp = dtTmp.Rows[0][ "Area_Lv "].ToString();
switch (strTemp)
{
case "0 ":
strSQL = "select Area_Name from Area where Area_Lv=3 ";
break;
case "1 ":
strSQL = "select Area_Name from Area where Area_Lv=3 and Area_Code like '% " + dtTmp.Rows[0][ "Area_Code "].ToString().Remove(2) + "% ' ";
break;
case "2 ":
strSQL = "select Area_Name from Area where Area_Lv=3 and Area_Code like '% " + dtTmp.Rows[0][ "Area_Code "].ToString().Remove(4) + "% ' ";
break;
default:
strSQL = "select Area_Name from Area where Area_ID= " + HttpContext.Current.Session[ "AreaID "].ToString();
break;
}
}
areaList = DatabaseFactory.ExecuteQuery(strSQL);
//foreach (DataRow areadr in areadt.Rows)
//{
// areaList.Add(areadr[ "Area_Name "].ToString());
//}
}
#endregion 地区行结束
#region 其他行