Home Library Learn Samples Downloads Support Community Forums

none
how to add columns in excel sheet programatically using spreadsheet in sharepoint

 > 

    Question

  • Question
    Sign In to Vote
    0
    Sign In to Vote

    Hi,

    I saved one blank excel sheet in document library and now i want to open it programatically and want to add some columns.

    am using open xml.sdk (Spreadsheet).

    Can anyone please tell me ?

    Thanks.

    Monday, June 24, 2013 5:52 AM
    |
    |

All Replies

  • Question
    Sign In to Vote
    0
    Sign In to Vote

    Hi,

     Refer the below link and it will help you to add a column in xl.

    http://social.msdn.microsoft.com/Forums/office/en-US/1d93eca8-2949-4d12-8dd9-15cc24128b10/trouble-added-columns-to-define-width-using-openxml

    Balaji -Please click mark as answer if my reply solves your problem.

    Monday, June 24, 2013 6:07 AM
    |
    |
  • Question
    Sign In to Vote
    0
    Sign In to Vote

    Hi, Balaji

    But I dont want to create new excel sheet I want to add in exsisting file that is saved in document library.?

    Thanks

    Monday, June 24, 2013 6:11 AM
    |
    |
  • Question
    Sign In to Vote
    0
    Sign In to Vote

    Hi,

     Ok. You can get a document from SP document library and do the processing using Open XML SDK.Please find the below link for your reference, [exclude workflow concept which is explained in link]

    http://msdn.microsoft.com/en-us/library/gg317441(v=office.14).aspx

    Balaji -Please click mark as answer if my reply solves your problem.

    Monday, June 24, 2013 6:16 AM
    |
    |
  • Question
    Sign In to Vote
    0
    Sign In to Vote

    Thanks,Balaji

    hope it works,can you please check my code it works or not ?

    namespace ListExcel.ExcelSheet
{
    public partial class ExcelSheetUserControl : UserControl
    {
           protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.Add("Name", typeof(string));
                dt.Columns.Add("Age", typeof(int));
                dt.Columns.Add("Address", typeof(string));
                DataRow dr = dt.NewRow();
                dr["Name"] = "John";
                dr["Age"] = 24;
                dr["Address"] = "ABC";
                dt.Rows.Add(dr);
                dr = dt.NewRow();
                dr["Name"] = "Mack";
                dr["Age"] = 24;
                dr["Address"] = "XYZ";
                dt.Rows.Add(dr);
                dr = dt.NewRow();
                dr["Name"] = "Harry";
                dr["Age"] = 34;
                dr["Address"] = "LMN";
                dt.Rows.Add(dr);
                UploadDataTableToExcel(dt);
            }
         }
        private void UploadDataTableToExcel(System.Data.DataTable dtRecords)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                //string content = string.Empty;
                using (SPSite osite = new SPSite(SPContext.Current.Site.Url))
                {
                    using (SPWeb oweb = osite.OpenWeb())
                    {
                        System.Text.UnicodeEncoding enc = new System.Text.UnicodeEncoding();
                        oweb.AllowUnsafeUpdates = true;
                        SPDocumentLibrary oDocumentLibrary = (SPDocumentLibrary)oweb.Lists["Documents"];
                        SPListItemCollection collListItems = oDocumentLibrary.Items;
                        string SPSitePath = "http://:12892";
                        String url = oweb.Lists["Documents"].RootFolder.ServerRelativeUrl.ToString();
                        string url2 = SPSitePath + url + "/" + "EmployeeDetails.xlsx";
                        SPFile tempFile = oweb.GetFile(url2);
                        SPFile file = collListItems[0].File;
                        byte[] byteArray = file.OpenBinary();
                        SPFile htmlFile = oDocumentLibrary.Items[0].File;
                        string strFileContentsBefore = enc.GetString(byteArray);
                        UTF8Encoding encoding = new UTF8Encoding(true);
                        Response.BinaryWrite(encoding.GetBytes("my string"));
                        using (Stream sourceFileStream = tempFile.OpenBinaryStream())
                        {
                            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(sourceFileStream, true))
                            {
                            }


    Monday, June 24, 2013 6:21 AM
    |
    |
  • Question
    Sign In to Vote
    0
    Sign In to Vote

    hi,Balaji

    Can you please tell me ?

    thanks

    Monday, June 24, 2013 7:13 AM
    |
    |
  • Question
    Sign In to Vote
    0
    Sign In to Vote

    Hi,

     Yes. The code which will get the excel document from Sharepoint document library..And it should work. After getting the document, you can add the columns in excel and save it.

    Balaji -Please click mark as answer if my reply solves your problem.

    Monday, June 24, 2013 8:54 AM
    |
    |
  • Question
    Sign In to Vote
    0
    Sign In to Vote

    Hi,

    That code is too complicated using so many methods that i dont want in that solution they are using predefined list column name but I want to create new column in excel sheet.Can you please tell me some other way ? Am having datatable in my code can you please check my code,and tell me some other possible solution.

    Thanks


    Monday, June 24, 2013 9:35 AM
    |
    |
  • Question
    Sign In to Vote
    0
    Sign In to Vote

    hi,

    can anyone tell me the whats the possible solution?

    Monday, June 24, 2013 1:12 PM
    |
    |
© 2013 Microsoft. All rights reserved.