使用File.WriteAllText创建（）函数后无法读取Excel文件 [英] Can't read excel file after creating it using File.WriteAllText() function

问题描述

我已创建数据表，从使用功能的Excel工作表。我想编程方式使用下面的ConnectionString读取Excel工作表。此字符串正常工作的所有其他Excel工作表，但不适合我使用函数创建的。我想这是因为Excel版本问题。

  OleDbConnection的康恩=新的OleDbConnection（数据源='+路径+;供应商= Microsoft.Jet.OLEDB.4.0;扩展属性= Excel的8.0;）;
 

任何人都可以提出一个途径，使我可以创建一个Excel工作表，使其可读再次使用上面的查询。我不能使用微软互操作库，因为它不是由我的主机支持。我甚至已经改变了不同的编码格式。它仍然不工作

 公共无效ExportDataSetToExcel（DataTable的DT）
{
    HTT presponse响应= HttpContext.Current.Response;
    response.Clear（）;
    Response.Charset的=UTF-8;
    response.ContentEncoding = Encoding.GetEncoding（UTF-8）;
    response.ContentType =应用程序/ vnd.ms-EXCEL;
    随机兰特=新的随机（）; INT INUM = Rand.Next（10000，99999）;
    字符串扩展=.xls的;
    字符串filenamepath = AppDomain.CurrentDomain.BaseDirectory +图\\\\+ INUM +的.xl​​s
    字符串FILE_PATH =图/+ INUM +扩展名;    response.AddHeader（内容处置，附件;文件名= \\+ INUM +\\）;
    查询字符串=插入graphtable（graphtitle，graphpath，creategraph，年）VALUES（'+ iNum.ToString（）+，+ FILE_PATH +，+真+'，'+日期时间.Now.Year.ToString（）+'）;
    尝试
    {
        INT N = connect.UpdateDb（查询）;
        如果（N 0）
        {
            resultLabel.Text =合并全成;
        }
        其他
        {
            resultLabel.Text =合并失败;
        }
        resultLabel.Visible =真;
    }
    赶上{}
    使用（StringWriter的SW =新的StringWriter（））
    {
        使用（HTW的HtmlTextWriter =新的HtmlTextWriter（SW））
        {
            //实例化一个DataGrid
            DataGrid的DG =新的DataGrid（）;
            dg.DataSource = DT; //ds.Tables[0];
            dg.DataBind（）;
            dg.RenderControl（HTW）;
            File.WriteAllText（filenamepath，sw.ToString（））; // File.WriteAllText（filenamepath，sw.ToString（），Encoding.UTF8）;
            RESPONSE.WRITE（sw.ToString（））;
            到Response.End（）;
        }
    }
}
 

解决方案

您似乎是写一个数据集作为的htmlText，然后试图告诉它，它是一个Excel文件。除非它是我丢失的东西那是不可能工作，因为Excel文件是一个特定的格式，因此需要在这一格式编写。如果你把你所创建的文件，并试图在Excel中打开它，会发生什么？

一个办法解决这将是写你的数据可以与Excel或用一个OleDbConnection既可以读取一个CSV文件。

I have created an excel sheet from datatable using function. I want to read the excel sheet programatically using the below connectionstring. This string works fine for all other excel sheets but not for the one i created using the function. I guess it is because of excel version problem.

   OleDbConnection conn= new OleDbConnection("Data Source='" + path +"';provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;";);  

Can anyone suggest a way by which i can create an excel sheet such that it is readable again using above query. I cannot use Microsoft InterOp library as it is not supported by my host. I have even changed different encoding formats. Still it doesnt work

 public void ExportDataSetToExcel(DataTable dt)
{
    HttpResponse response = HttpContext.Current.Response;        
    response.Clear();
    response.Charset = "utf-8";
    response.ContentEncoding = Encoding.GetEncoding("utf-8"); 
    response.ContentType = "application/vnd.ms-excel";
    Random Rand = new Random(); int iNum = Rand.Next(10000, 99999);
    string extension = ".xls";
    string filenamepath = AppDomain.CurrentDomain.BaseDirectory + "graphs\\" + iNum + ".xls";        
    string file_path = "graphs/" + iNum + extension;

    response.AddHeader("Content-Disposition", "attachment;filename=\"" + iNum + "\"");
    string query = "insert into graphtable(graphtitle,graphpath,creategraph,year) VALUES('" + iNum.ToString() + "','" + file_path + "','" + true + "','" + DateTime.Now.Year.ToString() + "')";
    try
    {
        int n = connect.UpdateDb(query);
        if (n > 0)
        {
            resultLabel.Text = "Merge Successfull";
        }
        else
        {
            resultLabel.Text = " Merge Failed";
        }
        resultLabel.Visible = true;
    }
    catch { }    
    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter htw = new HtmlTextWriter(sw))
        {
            // instantiate a datagrid
            DataGrid dg = new DataGrid();
            dg.DataSource = dt; //ds.Tables[0];
            dg.DataBind();                
            dg.RenderControl(htw);
            File.WriteAllText(filenamepath, sw.ToString());    // File.WriteAllText(filenamepath, sw.ToString(), Encoding.UTF8);
            response.Write(sw.ToString());
            response.End();
        }
    }
}

解决方案

You seem to be writing a dataset as HtmlText and then trying to tell it that it's an Excel file. Unless it's something I'm missing that's unlikely to work since Excel files are a specific format and so needs to be written in that format. If you take the file you've created and tries to open it in Excel, what happens?

One way around it would be to write your data as a CSV file which can be read both with Excel or with an OleDBConnection.

这篇关于使用File.WriteAllText创建（）函数后无法读取Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！