locked
Rename Excel worksheet RRS feed

  • Question

  • User1888449941 posted

    Hello, I have a function to upload an Excel file into to the webserver but I would also like it to rename the worksheet inside that file. Is this possible, if so how?

    Tuesday, February 23, 2010 11:14 AM

Answers

  • User-1199946673 posted

    Microsoft would always say such things to save face just in case anything happens
     

    If you search on this and other forums about Excel Automation, you would find out that a lot of people have problems whit it in a web environment. The reason is very obvious, Excel is designed to be used on a PC, with a single user and a User Interface.

    And not only they don't recommend or support it, I'm sure that the locense doesn't allow you to use the dll on a webserver!

    I am using the System.Data.OleDb namespace

     OK. In that case you must realize something. The Jet OleDb provider sees the excel  sheet you're opening as some kind of database table. You can only access the values of the fields of the excel sheet, but for example not the text color, alignment etc... You can't do anything else including changing the sheet name as far as I know. I can't think of a way of doing that. If you want to manipulate excel sheets, you need a third party component. Take it or leave it. Can you explain why you don't want to use a (free) third party component?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 24, 2010 3:02 PM
  • User2130758966 posted

    Microsoft would always say such things to save face just in case anything happens
     

    If you search on this and other forums about Excel Automation, you would find out that a lot of people have problems whit it in a web environment. The reason is very obvious, Excel is designed to be used on a PC, with a single user and a User Interface.

    And not only they don't recommend or support it, I'm sure that the locense doesn't allow you to use the dll on a webserver!

    Yeah the specific reason is that it leaks memory so in a long running situation such as a 24/7 server you will eventually crash your server.

    IF you really want all the gory details then I keep this following link in my delicious bookmarks for just such a person as yourself [:)]


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 24, 2010 6:10 PM

All replies

  • User2130758966 posted

    Hey,

    I dont know the exact solution to this but I would start out here:

    Its a free library for manipulating excel files in .net code.

    Tuesday, February 23, 2010 12:27 PM
  • User1888449941 posted

    this requires you to use a third party assistance/script. I need this to be done with just Visual Studio .vb codebehind

    Tuesday, February 23, 2010 12:31 PM
  • User2130758966 posted

    Hmm well you have some research to do then [:)]

    There is the Mircrosoft.Office.Interop namespace but this is not supported in a server environment.

    Perhaps you can get the library I linked to and if it supports renaming you can figure out how to code it yourself?

    Tuesday, February 23, 2010 12:37 PM
  • User1888449941 posted

    I am able to save, change the file name, access the worksheet with any name given to it, as well as add columns with information to the woorksheet inside the file. I'm assuming that changing the name of the worksheet would be something similar to these previous procedures.

    Tuesday, February 23, 2010 12:54 PM
  • User-1199946673 posted

    I dont know the exact solution to this but I would start out here:

     

    When I test this, it turns out that this component isn't creating real excel files. Another component does:

    http://npoi.codeplex.com/

    There is the Mircrosoft.Office.Interop namespace but this is not supported in a server environment.

    And that will also put a dll in the bin directory. And I don't think the Excel (Office) License allows you to copy this files to a web server

    Tuesday, February 23, 2010 7:40 PM
  • User-1199946673 posted

    this requires you to use a third party assistance/script. I need this to be done with just Visual Studio .vb codebehind

    You can't do this without a third party control. What is the problem with that?

    I am able to save, change the file name,

    That's easy using the System.IO namespace

    access the worksheet with any name given to it, as well as add columns with information to the woorksheet inside the file.

    You can't do that without a third part control. Most likely you've office installed on your development machine, and you're using the Mircrosoft.Office.Interop namespace, but as rptHarry already sais, this isn't supported, nor recommended by Microsoft:

    "Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment."

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2 

    I'm assuming that changing the name of the worksheet would be something similar to these previous procedures.

    When using Mircrosoft.Office.Interop it is just as easy/difficult

    Tuesday, February 23, 2010 7:48 PM
  • User1888449941 posted

    Microsoft would always say such things to save face just in case anything happens. I am using the System.Data.OleDb namespace procedure. I can't speak of the future but I can tell from experience it was work fine for over a year now with no problems. I am able to name columns and enter data in the fields with no problems. All I am asking is for assistance or direction into how to name the worksheet.

    Wednesday, February 24, 2010 12:40 PM
  • User1888449941 posted

    You can't do that without a third part control. Most likely you've office installed on your development machine, and you're using the Mircrosoft.Office.Interop namespace, but as rptHarry already sais, this isn't supported, nor recommended by Microsoft:

    I believe I am not using such namespace, by the way. This are the namespaces I am using:

    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    Imports System

    Wednesday, February 24, 2010 12:48 PM
  • User-1199946673 posted

    Microsoft would always say such things to save face just in case anything happens
     

    If you search on this and other forums about Excel Automation, you would find out that a lot of people have problems whit it in a web environment. The reason is very obvious, Excel is designed to be used on a PC, with a single user and a User Interface.

    And not only they don't recommend or support it, I'm sure that the locense doesn't allow you to use the dll on a webserver!

    I am using the System.Data.OleDb namespace

     OK. In that case you must realize something. The Jet OleDb provider sees the excel  sheet you're opening as some kind of database table. You can only access the values of the fields of the excel sheet, but for example not the text color, alignment etc... You can't do anything else including changing the sheet name as far as I know. I can't think of a way of doing that. If you want to manipulate excel sheets, you need a third party component. Take it or leave it. Can you explain why you don't want to use a (free) third party component?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 24, 2010 3:02 PM
  • User2130758966 posted

    Microsoft would always say such things to save face just in case anything happens
     

    If you search on this and other forums about Excel Automation, you would find out that a lot of people have problems whit it in a web environment. The reason is very obvious, Excel is designed to be used on a PC, with a single user and a User Interface.

    And not only they don't recommend or support it, I'm sure that the locense doesn't allow you to use the dll on a webserver!

    Yeah the specific reason is that it leaks memory so in a long running situation such as a 24/7 server you will eventually crash your server.

    IF you really want all the gory details then I keep this following link in my delicious bookmarks for just such a person as yourself [:)]


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 24, 2010 6:10 PM
  • User2130758966 posted

    Microsoft would always say such things to save face just in case anything happens
     

    If you search on this and other forums about Excel Automation, you would find out that a lot of people have problems whit it in a web environment. The reason is very obvious, Excel is designed to be used on a PC, with a single user and a User Interface.

    And not only they don't recommend or support it, I'm sure that the locense doesn't allow you to use the dll on a webserver!

    Yeah the specific reason is that it leaks memory so in a long running situation such as a 24/7 server you will eventually crash your server.

    IF you really want all the gory details then I keep this following link in my delicious bookmarks for just such a person as yourself Smile

    Sorry hans, I didnt scroll up before I replied to the last item in the thread... you clearly have this one under control [;)]

    Wednesday, February 24, 2010 6:12 PM
  • User-949037948 posted

    Hello,

       You need to use a third party, I used Spire.XLS can easy to this. Hope help to you. 

    Sunday, December 26, 2010 8:52 PM
  • User100585308 posted

    Hope it will be helpful for you.
    Here the code that I used with this Excel Jetcell component for NET.


    // Open excel file ExcelWorkbook xls = ExcelWorkbook.ReadXLS("excel_file.xls"); // Changing the worksheet's name xls.Wolrksheet["Sheet1"].Name = "List of empoloyee"; xls.Worksheet[1].Name = "Statistic data";

    Without doubt, the component will be good for other tasks concerned with Excel files manipulations.

    Tuesday, April 19, 2011 4:09 AM
  • User-2032597502 posted

    I used a user-defined function/method utilizing OpenXML similar to the following:

    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;

    //NOTE: Make sure to reference DocumentFormat.OpenXml and WindowsBase.  

     
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        static void Main(string[] args)
        {
            string strExcelDoc = @"C:\SampleExcel.xlsx";

            RenameSheet(strExcelDoc, "Sheet1", "MySheetName"); //give valid new sheet name

        }


        /// <summary>
        /// Rename a worksheet of an excel document
        /// </summary>
        static void RenameSheet(string strDocFullPath, string strOldSheetName, string strNewSheetName)
        {
            Sheets WorkbookSheets = null;

            // Open the source workbook. The following will throw an
            // exception if the source workbook does not exist.
            using (SpreadsheetDocument Wbook = SpreadsheetDocument.Open(strDocFullPath, true))
            {
                // Get all the Sheets element in the workbook.
                WorkbookSheets = Wbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();

                // For each worksheet in the workbook...
                foreach (Sheet childSheet in WorkbookSheets)
                {
                    if (strOldSheetName == childSheet.Name)
                    {
                        childSheet.Name = strNewSheetName;
                        //Save the workbook
                        Wbook.WorkbookPart.Workbook.Save();

                        break;
                    }
                }

            }
        }
    }

    Friday, August 5, 2011 7:17 PM