求从SQLserver2000中将表的数据导入到Excel的(C#)的操作代码 用于网页的模式(B/S)

Question

求从SQLserver2000中将表的数据导入到Excel的(C#)的操作代码 用于网页的模式(B/S)

Answer 1

给你一个从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(); 
} 

Answer 2

怎么将 服务器表单上DataGrid 中的数据 导入到本机上Excel 表中呢?望各位 大虾 赐教 谢谢了阿!

Answer 3

将SQL 2000的数据导出到EXCEL比较简单的就是把EXCEL也当做是数据库来操作就可以了前段时间做了一个将SQL 2000的数据到出到EXCEL模版（就是已有的EXCEL文件，里面已经有定义了标题之类的信息了，要将数据添加到后面）的东西，那是用EXCEL类来实现的，可惜现在代码不在身边，只能告诉你这些了

Answer 4

偶也是新人，各位见笑了
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 其他行

Answer 5

else
                    {
                        //逐行插入
                        titleList.Add(dr["FieldName"].ToString());
                        #region 判断内外联情况
                        //string strTest = dr["BDTypeID"].ToString();
                        if (dr["BDTypeID"] != null && dr["BDTypeID"].ToString().Contains(","))//内联
                        {
                            string[] innerList = dr["BDTypeID"].ToString().Split(splitDot);
                            strSQL = "select Data_Value from b_base_data where type_id='" + innerList[1] + "'";
                            DataTable innerdt = DatabaseFactory.ExecuteQuery(strSQL);
                            //innerList = new string[innerdt.Rows.Count];
                            //for (int innerIndex = 0; innerIndex < innerdt.Rows.Count; innerIndex++)
                            //{
                            //    innerList[innerIndex] = innerdt.Rows[innerIndex]["Data_Value"].ToString();
                            //}
                            parameterInnerList.Add(innerdt);
                            corresponding.Add(titleList.Count);
                        }
                        #endregion 内联结束
                        #region 外联
                        else if (dr["ForeignData"] != null && dr["ForeignData"].ToString().Contains(","))//外联
                        {
                            string[] outerList = dr["ForeignData"].ToString().Split(splitDot);
                            strSQL = "select ";
                            //循环得到所有需要查询的列
                            for (int i = 2; i < outerList.Length; i++)
                            {
                                strSQL += outerList[i] + ",";
                            }
                            //去掉最后一个 ,
                            strSQL = strSQL.Remove(strSQL.Length - 1);
                            //得到表名称
                            strSQL += " from " + outerList[1];
                            DataTable outerdt = DatabaseFactory.ExecuteQuery(strSQL);                            parameterOuterList.Add(outerdt);
                            corresponding.Add(titleList.Count);
                        }
                        #endregion 外联结束
                    }
                    #endregion 其他行结束
                }
                #endregion 遍历结束                if (areaTitle != null && areaTitle != "")//如果有地区
                {
                    //地区头
                    oSheet.Cells[1, 1] = areaTitle;
                    //各地区
                    if (areaList != null && areaList.Rows.Count > 0)
                    {
                        for (int i = 0; i < areaList.Rows.Count; i++)
                        {
                            oSheet.Cells[i + 2, 1] = areaList.Rows[i]["Area_Name"];
                        }
                    }
                    //表头
                    if (titleList != null && titleList.Count > 0)
                    {
                        for (int i = 0; i < titleList.Count; i++)
                        {
                            oSheet.Cells[1, i + 2] = titleList[i];
                        }
                    }
                    //内联参数
                    if (parameterInnerList != null && parameterInnerList.Count > 0)
                    {
                        for (int i = 0; i < parameterInnerList.Count; i++)
                        {
                            DataTable dtParameter = parameterInnerList[i] as DataTable;
                            int j;
                            for (j = 0; j < dtParameter.Rows.Count; j++)
                            {
                                oSheet.Cells[j + 1, i + 2 + titleList.Count] = dtParameter.Rows[j]["Data_Value"];
                            }
                            //设置选项，隐藏列
                            oRange = null;
                            oRange = oSheet.get_Range(IntToLetter((int)corresponding[i] + 3) + 2, missing);
                            oRange = oRange.get_Resize(areaList.Rows.Count, 1);
                            oRange.Select();
                            oRange.Validation.Delete();
                            oRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "=$" + ExcelExportToDB.IntToLetter(i + 2 + titleList.Count) + "$1:$" + ExcelExportToDB.IntToLetter(i + 2 + titleList.Count) + "$" + j, null);
                            oRange.Validation.IgnoreBlank = true;
                            oRange.Validation.InCellDropdown = true;
                            oRange.Validation.InputTitle = "";
                            oRange.Validation.ErrorTitle = "";
                            oRange.Validation.InputMessage = "";
                            oRange.Validation.ErrorMessage = "";
                            oRange.Validation.IMEMode = (int)Excel.XlIMEMode.xlIMEModeNoControl;
                            oRange.Validation.ShowInput = true;
                            oRange.Validation.ShowError = true;                            oRange = null;
                            oRange = oSheet.get_Range(oSheet.Cells[1, i + 2 + titleList.Count], oSheet.Cells[1 + j, i + 2 + titleList.Count]);
                            oRange.ColumnWidth = 0;
                        }
                    }
                    //外联参数
                    if (parameterOuterList != null && parameterOuterList.Count > 0)
                    {
                        for (int i = 0; i < parameterOuterList.Count; i++)
                        {
                            DataTable dtParameter = parameterOuterList[i] as DataTable;
                            int j;
                            for (j = 0; j < dtParameter.Rows.Count; j++)
                            {
                                oSheet.Cells[j + 1, i + 2 + titleList.Count + parameterInnerList.Count] = dtParameter.Rows[j]["_1"];
                            }
                            //设置选项，隐藏列
                            oRange = null;
                            oRange = oSheet.get_Range(IntToLetter((int)corresponding[i] + 1) + 2, missing);
                            oRange = oRange.get_Resize(areaList.Rows.Count, 1);
                            oRange.Select();
                            oRange.Validation.Delete();
                            oRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "=$" + ExcelExportToDB.IntToLetter(i + 2 + titleList.Count + parameterInnerList.Count) + "$1:$" + ExcelExportToDB.IntToLetter(i + 2 + titleList.Count + parameterInnerList.Count) + "$" + j, null);
                            oRange.Validation.IgnoreBlank = true;
                            oRange.Validation.InCellDropdown = true;
                            oRange.Validation.InputTitle = "";
                            oRange.Validation.ErrorTitle = "";
                            oRange.Validation.InputMessage = "";
                            oRange.Validation.ErrorMessage = "";
                            oRange.Validation.IMEMode = (int)Excel.XlIMEMode.xlIMEModeNoControl;
                            oRange.Validation.ShowInput = true;
                            oRange.Validation.ShowError = true;                            oRange = null;
                            oRange = oSheet.get_Range(oSheet.Cells[1, i + 2 + titleList.Count + parameterInnerList.Count], oSheet.Cells[1 + j, i + 2 + titleList.Count + parameterInnerList.Count]);
                            oRange.ColumnWidth = 0;
                        }
                    }
                }

Answer 6

else//无地区
                {
                    //表头
                    if (titleList != null && titleList.Count > 0)
                    {
                        for (int i = 0; i < titleList.Count; i++)
                        {
                            oSheet.Cells[1, i + 1] = titleList[i];
                        }
                    }
                    //内联参数
                    if (parameterInnerList != null && parameterInnerList.Count > 0)
                    {
                        for (int i = 0; i < parameterInnerList.Count; i++)
                        {
                            DataTable dtParameter = parameterInnerList[i] as DataTable;
                            int j;
                            for (j = 0; j < dtParameter.Rows.Count; j++)
                            {
                                oSheet.Cells[j + 1, i + 1 + titleList.Count] = dtParameter.Rows[j]["Data_Value"];
                            }
                            //设置选项，隐藏列
                            oRange = null;
                            oRange = oSheet.get_Range(IntToLetter((int)corresponding[i]) + 1, missing);
                            oRange.Select();
                            oRange.Validation.Delete();
                            oRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, oSheet.Cells[1, i + 1 + titleList.Count], oSheet.Cells[j + 1, i + 1 + titleList.Count]);
                            oRange.Validation.IgnoreBlank = true;
                            oRange.Validation.InCellDropdown = true;
                            oRange.Validation.InputTitle = "";
                            oRange.Validation.ErrorTitle = "";
                            oRange.Validation.InputMessage = "";
                            oRange.Validation.ErrorMessage = "";
                            oRange.Validation.IMEMode = (int)Excel.XlIMEMode.xlIMEModeNoControl;
                            oRange.Validation.ShowInput = true;
                            oRange.Validation.ShowError = true;                            oRange = null;
                            oRange = oSheet.get_Range(oSheet.Cells[1, i + 1 + titleList.Count], oSheet.Cells[1 + j, i + 1 + titleList.Count]);
                            oRange.ColumnWidth = 0;
                        }
                    }
                    //外联参数
                    if (parameterOuterList != null && parameterOuterList.Count > 0)
                    {
                        for (int i = 0; i < parameterOuterList.Count; i++)
                        {
                            DataTable dtParameter = parameterOuterList[i] as DataTable;
                            int j;
                            for (j = 0; j < dtParameter.Rows.Count; j++)
                            {
                                oSheet.Cells[j + 1, i + 1 + titleList.Count + parameterInnerList.Count] = dtParameter.Rows[j]["_1"];
                            }
                            //设置选项，隐藏列
                            oRange = null;
                            oRange = oSheet.get_Range(IntToLetter((int)corresponding[i]) + 1, missing);
                            oRange.Select();
                            oRange.Validation.Delete();
                            oRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, oSheet.Cells[1, i + 1 + titleList.Count], oSheet.Cells[j + 1, i + 1 + titleList.Count]);
                            oRange.Validation.IgnoreBlank = true;
                            oRange.Validation.InCellDropdown = true;
                            oRange.Validation.InputTitle = "";
                            oRange.Validation.ErrorTitle = "";
                            oRange.Validation.InputMessage = "";
                            oRange.Validation.ErrorMessage = "";
                            oRange.Validation.IMEMode = (int)Excel.XlIMEMode.xlIMEModeNoControl;
                            oRange.Validation.ShowInput = true;
                            oRange.Validation.ShowError = true;                            oRange = null;
                            oRange = oSheet.get_Range(oSheet.Cells[1, i + 1 + titleList.Count + parameterInnerList.Count], oSheet.Cells[1 + j, i + 1 + titleList.Count + parameterInnerList.Count]);
                            oRange.ColumnWidth = 0;
                        }
                    }
                }                oWorkbook.SaveAs(HttpContext.Current.Server.MapPath(path), missing, missing,
                    missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
                    missing, missing, missing, missing, missing);
                oWorkbook.Close(false, missing, missing);
                oExcel.Quit();
            //}            excelName = strExcelName;
            excelPath = path;

Answer 7

高手就是高手，这些都是winform里面的吧，有没有asp.net里面的