You must Sign In to post a response.
  • Category: .NET

    How to Export Issue overcome

    Hi

    when i export excel using C# code
    i meet this issue when i exported excel file open . How to Resolve it


    This is error

    Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
  • #768750
    Hi,

    It's looks like your excel is corrupted, as you said some sheets got corrupted right? I suggest you to create new workbook and try to open that particular workbook and re-confirm yourself whether you got the same error or not?

    If you are not getting the same error then the problem might be excel file, if you got the same error for new workbook too then the problem might be your code which you wrote for opening the excel workbook.

    Please use below piece of code while open the workbook.


    public static DataTable ReadExcel(string inputPath, string sheetName)
    {
    DataTable dtSheet = new DataTable();
    try
    {
    #region Read data from excel
    objApp.Visible = false;

    //Open the Workbook
    objWrkbook = objApp.Workbooks.Open(inputPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

    //Get all sheets available under the workbook
    objSheets = (Excel.Sheets)objWrkbook.Worksheets;

    // Get the particular sheet which you want to get the information
    objWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(sheetName);

    //Get the column count of the sheet
    int colcnt = objWrkSheet.UsedRange.Columns.Count;
    //Get Row count of the sheet
    int rowcnt = objWrkSheet.UsedRange.Rows.Count;



    #endregion

    }
    }
    catch (Exception ex)
    {
    throw;
    }
    finally
    {
    if (objWrkSheet != null)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkSheet);
    if (objWrkbook != null)
    objWrkbook.Close(false, inputPath, Type.Missing);
    if (objApp != null)
    objApp.Quit();
    }
    return dtSheet;
    }


    Make sure in the above code final block should be mandatory , you have to close all the objects without fail then only your excel won't be corrupted.

    Hope this helps you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768754
    Hai,
    Looks like the Excel file is either of another version and you are using different version of driver to open it. Or the file got some issue and not opening properly.
    You need to check which version of Excel file you are trying to open and then which driver you have for the excel in the system where the application is working.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #768767
    How do you have create EXCEL file ? post you code so that we can help you in order to resolve your issue

    This is the way you open the workbook. Check the last parameter, and set it to xlNormalLoad. Then, according to the documentation,
    Excel will not try to repair the file.
    Open(filePath, // The file name
    0, // Update links, 0 means no charts are created
    true, // Read only
    5, // Format, 5 means nothing
    "", // Password
    "", // WriteResPassword
    true, // Ignore read only recommended
    XlPlatform.xlWindows, // Origin
    "\t", // Delimiter
    true, // Editable for Excel 4.0 add-in
    false, // Notify
    0, // Converter
    false, // AddToMru
    true, // Local
    true); // CorruptLoad, can be: xlNormalLoad, xlRepairFile or xlExtractData



    or you can use OpenXML API to create excel flle
    https://blogs.msdn.microsoft.com/chrisquon/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml/

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #768796
    This is the code snippet for export excel using C# code
    private void button1_Click(object sender, EventArgs e)
    {
    SqlConnection cnn ;
    string connectionString = null;
    string sql = null;
    string data = null;
    int i = 0;
    int j = 0;

    Excel.Application xlApp ;
    Excel.Workbook xlWorkBook ;
    Excel.Worksheet xlWorkSheet ;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
    cnn = new SqlConnection(connectionString);
    cnn.Open();
    sql = "SELECT * FROM Product";
    SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
    DataSet ds = new DataSet();
    dscmd.Fill(ds);

    for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
    for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
    {
    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
    xlWorkSheet.Cells[i + 1, j + 1] = data;
    }
    }

    xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);

    MessageBox.Show("Excel file created ");
    }


Sign In to post your comments