locked
Bug with Excel RRS feed

  • Question

  • Hello to all,

    I am doing a project in C#.net 2010 with MS Excel as back end. Project is working quite well as expected however one issue is giving some trouble in moving ahead.

    Using the below code i am creating the new Sheet in excel


                string connstring = "provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\\Testing Folder\\TestFile.xls; Extended Properties=Excel 8.0";
                OleDbConnection objcon = new OleDbConnection(connstring);
                objcon.Open();
                string CustomerCode = "A001";
                OleDbCommand CustCode = new OleDbCommand("Create Table " + CustomerCode + " ([Customer Code] string, [Company Name] string, [Address1] string, [Address2] string, [Area] string, [City] string, [PIN] string, [State] string)", objcon);
                CustCode.ExecuteNonQuery();
                objcon.Close();

    This above code will execute perfectly but the problem am facing is when i open excel sheet i get an Underscore (_) before A001

    Example: _A001

    The below snapshots shows the same

    Is there any way to resolve this

    With Warm Regards

    Amit

    Wednesday, September 5, 2012 5:10 AM

Answers

  • maybe you can try to use OpenXML method for this, to update the sheet name OR trying to create the excel with that name. It is a default method from excel to put _ in front of names containing integer.

    http://www.codeproject.com/Articles/371203/Creating-basic-Excel-workbook-with-Open-XML

    http://www.microsoft.com/en-us/download/details.aspx?id=5124

    regards

    joon



    • Edited by Joon84 Wednesday, September 5, 2012 7:22 AM
    • Proposed as answer by Lisa Zhu Thursday, September 6, 2012 2:56 AM
    • Marked as answer by Lisa Zhu Wednesday, September 19, 2012 9:53 AM
    Wednesday, September 5, 2012 7:21 AM
  • Hello Joon84,

    The link which you provided helped and it solved the problem, now A001 is getting created.

    However i altered the code slightly

    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("E:\\Testing Folder\\TestFile.xls", SpreadsheetDocumentType.Workbook);

    Instead of creating every time, i am opening and creating a new sheet; it throws exception as 

    File contains corrupted data. // this is for TestFile.xls

    But if i test on TestFile.xlsx

    Only one instance of the type is allowed for this parent. //Exception

    With Warm Regards

    Amit

    well OpenXML worls quite fine with Excel I guess, but it plays things with xml format. You may try to update the work sheet names with OpenXML maybe after creating with normal Oledb connections.

    regards

    joon

    • Marked as answer by Lisa Zhu Wednesday, September 19, 2012 9:53 AM
    Wednesday, September 5, 2012 9:15 AM

All replies

  • Boss,

    This problem might be due to the name given.....

    instead of A001 try sample it will work fine..........

    Wednesday, September 5, 2012 6:18 AM
  • Yes it will work with sample and other names but as per client requirement we are required to follow this pattern A001, A002....

    Wednesday, September 5, 2012 6:22 AM
  • it does not look good, but while you rename it it takes the correct name. Maybe you can try that.

    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    Workbook xlBook = xlApp.Workbooks.Open(@"E:\\Testing Folder\\TestFile.xls", 0, false, 5, "", "", true, XlPlatform.xlWindows, "\t", true, true, 0, true, 0, 0);
    Sheets xlsheets = xlBook.Worksheets;
    Worksheet xlSheet = (Worksheet)xlsheets.get_Item("_A001");
    xlSheet.Name = "A001";
    xlBook.Save();
    xlApp.Workbooks.Close();
    this is using office dll from .Net framework.

    regards

    joon

    Wednesday, September 5, 2012 7:06 AM
  • Hello Joon84,

    Thanks for reply. I used the above approach earlier also but the application which we are developing will be launched on network hence we are advised not to use Interop.

    With Warm Regards

    Amit

    Wednesday, September 5, 2012 7:13 AM
  • maybe you can try to use OpenXML method for this, to update the sheet name OR trying to create the excel with that name. It is a default method from excel to put _ in front of names containing integer.

    http://www.codeproject.com/Articles/371203/Creating-basic-Excel-workbook-with-Open-XML

    http://www.microsoft.com/en-us/download/details.aspx?id=5124

    regards

    joon



    • Edited by Joon84 Wednesday, September 5, 2012 7:22 AM
    • Proposed as answer by Lisa Zhu Thursday, September 6, 2012 2:56 AM
    • Marked as answer by Lisa Zhu Wednesday, September 19, 2012 9:53 AM
    Wednesday, September 5, 2012 7:21 AM
  • You forgot to use the dollar sign in the sheet name which specifies the table as being a system table.  try this simple fix

    From :  string CustomerCode = "A001";

    To :  string CustomerCode = "A001$";


    jdweng

    Wednesday, September 5, 2012 7:37 AM
  • i tried with openxml to create a worksheet with A001, it is fine i guess.

    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("C:\\TestFile.xls", SpreadsheetDocumentType.Workbook);
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "A001" };
    sheets.Append(sheet);
    workbookpart.Workbook.Save();
    spreadsheetDocument.Close();

    you will need to download both the msi files from the link http://www.microsoft.com/en-us/download/details.aspx?id=5124:

    install them, then include the DocumentFormat.OpenXml.dll to your project from the folder where the msi is installed.

    regards

    joon


    • Edited by Joon84 Wednesday, September 5, 2012 7:44 AM
    Wednesday, September 5, 2012 7:43 AM
  • Hello Joon84,

    The link which you provided helped and it solved the problem, now A001 is getting created.

    However i altered the code slightly

    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("E:\\Testing Folder\\TestFile.xls", SpreadsheetDocumentType.Workbook);

    Instead of creating every time, i am opening and creating a new sheet; it throws exception as 

    File contains corrupted data. // this is for TestFile.xls

    But if i test on TestFile.xlsx

    Only one instance of the type is allowed for this parent. //Exception

    With Warm Regards

    Amit

    Wednesday, September 5, 2012 8:04 AM
  • Hello jdweng,

    thanks for reply, the logic which you suggested throws a Syntax Error in Create Table Statement

    With Warm Regards

    Amit

    Wednesday, September 5, 2012 8:11 AM
  • Hello Joon84,

    The link which you provided helped and it solved the problem, now A001 is getting created.

    However i altered the code slightly

    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("E:\\Testing Folder\\TestFile.xls", SpreadsheetDocumentType.Workbook);

    Instead of creating every time, i am opening and creating a new sheet; it throws exception as 

    File contains corrupted data. // this is for TestFile.xls

    But if i test on TestFile.xlsx

    Only one instance of the type is allowed for this parent. //Exception

    With Warm Regards

    Amit

    well OpenXML worls quite fine with Excel I guess, but it plays things with xml format. You may try to update the work sheet names with OpenXML maybe after creating with normal Oledb connections.

    regards

    joon

    • Marked as answer by Lisa Zhu Wednesday, September 19, 2012 9:53 AM
    Wednesday, September 5, 2012 9:15 AM
  • Do yo havve any spaces in the worksheet name.  You may need to use square brackets

    da.SelectCommand.CommandText = "select * from [Sheet1$]"; 


    jdweng

    Wednesday, September 5, 2012 1:35 PM
  • Just as an FYI, the OP is creating a file using a previous version of Excel's file format (XLS V 8.0).  I think this is Excel 2003.  He won't be able to create a file with the OpenXML SDK, which would be compatable with that version of Excel; the OpenXML SDK is intended for the newest file format in Excel 2007 and 2010 (think XLSX).  These file formats are not interchangeable.
    Wednesday, September 5, 2012 3:07 PM
  • No Joel Engineer, i dont have spaces in sheet name
    Thursday, September 6, 2012 4:33 AM
  • Chris Fo, I tested for both versions in Excel 2003 and 2007. Exception is in both cases
    Thursday, September 6, 2012 4:44 AM
  • Making a slight modification to your code by using the ACE provider, I could successfully add a new worksheet to an existing Excel 2010 Workbook.

                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\**\\Documents\\Excel\\AC1.xlsx;Extended Properties=\"EXCEL 12.0;HDR=YES\"";
                using (OleDbConnection objcon = new OleDbConnection(connstring))
                {
                    objcon.Open();
                    OleDbCommand CustCode = new OleDbCommand("Create Table Items([ItemID] INT, [ItemName] string, [CategoryID] INT)", objcon);
                    CustCode.ExecuteNonQuery();
                    objcon.Close();
                }
    
                Console.ReadLine();

    Thursday, September 6, 2012 6:01 AM
  • Thanks for reply Chris Fo, try to rename Excel Sheet name to A001 instead of Items

    OleDbCommand CustCode = new OleDbCommand("Create Table Items([ItemID] INT, [ItemName] string, [CategoryID] INT)", objcon);

    Instead of This , Try this 

    OleDbCommand CustCode = new OleDbCommand("Create Table A001([ItemID] INT, [ItemName] string, [CategoryID] INT)", objcon);

    you get Underscore: _A001 instead of A001. The _ (Underscore) is giving me bugs in whole code

    Am trying to eliminate this Underscore

    Regards

    Amit

    • Edited by amit_kumar Thursday, September 6, 2012 6:27 AM
    Thursday, September 6, 2012 6:24 AM
  • This is pretty strange.  There seems to be some internal requirement that a sheet with numbers and letters needs to have a specific number of letters, otherwise you get the underscore prepended to the sheet name.

    As you can see from my screenshot, a single letter is created without any issues, but a worksheet with letters and numbers is prepended with the underscore.  It looks like you need at least 4 letters for the underscore to vanish.

    Now, this is all interesting, but I don't have a solution.  This is simply some information that I found out.  =P  I'm quite curious about why this is occuring, and may look more into it, until I can find the cause as well as a solution.

    Thursday, September 6, 2012 3:54 PM
  • I know this is an old thread, but I'm experiencing the exact problem with Prefix '-' in the worksheet name. I wonder if anyone has new updates or solution regarding this issue? Much 

    • Edited by RedGem Thursday, April 13, 2017 6:02 PM
    Thursday, April 13, 2017 6:02 PM