将数据导出到Excel文件2003、2007及以上版本

Question

将数据导出到Excel文件2003、2007及以上版本

c#asp.netexcel

3

根据文件扩展名，我使用以下连接字符串：

对于2003年的版本：Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;

对于2007年的版本：Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;

这是获取连接的方法。

string con_excel = "";

        switch (Extension.ToLower())
        {
            case ".xls":
                con_excel = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;

            case ".xlsx":
                con_excel = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        con_excel = con_excel.Replace("filename", filePath);

以下是生成Excel文件的代码。

        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;

        oXL = new Excel.Application();
        oXL.Visible = false;

        oXL.SheetsInNewWorkbook = 1;
        oWB = (Excel._Workbook)(oXL.Workbooks.Add());
        oSheet = (Excel._Worksheet)oWB.ActiveSheet;

        try
        {
            string[] colNames = new string[dataTable.Columns.Count];

            int col = 0;

            foreach (DataColumn dc in dataTable.Columns)
                colNames[col++] = dc.ColumnName;

            char lastColumn = (char)(65 + dataTable.Columns.Count - 1);

            oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
            oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
            oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

            DataRow[] dr = dataTable.Select();

            string[,] rowData = new string[dr.Count<DataRow>(), dataTable.Columns.Count + 1];

            int rowCnt = 0;
            foreach (DataRow row in dr)
            {
                for (col = 0; col < dataTable.Columns.Count; col++)
                {
                    rowData[rowCnt, col] = row[col].ToString();
                }
                rowCnt++;
            }
            rowCnt++;
            oSheet.get_Range("A2", lastColumn + rowCnt.ToString()).Value = rowData;

            oXL.Visible = false;
            oXL.UserControl = true;

            String sNewFolderName = "Report_" + intReportId;
            filename = Server.MapPath("Your Report\\" + sNewFolderName + DateTime.Now.ToString("dd-MM-yyyy_hh-mm-ss") + Extension);
            oSheet.SaveAs(filename);

            System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

            oXL.Quit();

            Marshal.ReleaseComObject(oSheet);
            Marshal.ReleaseComObject(oWB);
            Marshal.ReleaseComObject(oXL);

            oSheet = null;
            oWB = null;
            oXL = null;
            GC.GetTotalMemory(false);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.GetTotalMemory(true);

            //The excel is created and opened for insert value. We most close this excel using this system        
            Process[] localByName = Process.GetProcessesByName("EXCEL");
            foreach (Process process in localByName)
            {
                process.Kill();
            }

2007文件格式没问题。

我试图上传2003(.xls)版本的Excel文件并生成2003(.xls)格式的文件。但是当我打开该文件时，出现以下错误信息：

您正尝试打开的文件“FileName.xls”与指定的文件扩展名不同。在打开文件之前，请验证该文件未损坏且来自受信任的来源。您是否要立即打开该文件？

这是由于连接字符串的原因吗？

- Lamin

您的连接字符串不完整。引号未闭合？ - Raptor

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- senthilkumar2185 · Answer 1

尝试使用此代码上传Excel 2003和2007文件。

  if (filenam.ToString() == ".xls")
    { constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathnam + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; }
    else if (filenam.ToString() == ".xlsx")
    { constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathnam + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; }
    else { Response.Write("<script>alert('Load Excel file Only')</script>"); }
    string Qry = "SELECT [Customer], [Project], [Contact], [Designation], [Phone], [EmailID],[Region] FROM [Sheet1$]";
    OleDbConnection conn = new OleDbConnection(constr);
    if (conn.State == ConnectionState.Closed)
    {
    conn.Open();
    OleDbCommand cmd = new OleDbCommand(Qry, conn);
    OleDbDataAdapter da = new OleDbDataAdapter();
    da.SelectCommand = cmd;
    DataTable dt = new DataTable();
    da.Fill(dt);
    if (dt != null && dt.Rows.Count > 0)
    {
    gv_upload.DataSource = dt;
    gv_upload.DataBind();
    }
    da.Dispose(); conn.Close(); conn.Dispose();