Size of Excel file generated using Component one (C1) changes on opening & save

Posted by: Pratikshukla657 on 2 July 2018, 8:40 pm EST

  • Posted 2 July 2018, 8:40 pm EST

    Hi Team,
    I am generating an excel file using Component one library. generated excel file is not getting uploaded in HP QuickTest Professional (QTP) and it gives an invalid file error. I analysed and found that on opening the generated file and hitting Ctrl + s the size of file changes. Moreover, the last cell of file also changes from the end of column to my last column with data So when i open the generated file and save, it successfully get uploaded. I am unable to trace down the exact issue?. Can someone please guide me on this.
  • Replied 2 July 2018, 8:49 pm EST

    Below is the code which i am writing to achieve the generation of excel file
     _logger.InfoFormat(Constants.GENERATEEXCELSPREADSHEETSTR, MethodBase.GetCurrentMethod().Name, Constants.DIGITIZEDDATACLASSNAME, dtSheetSource.Rows.Count.ToString(), excelDestinationPath, templateName, destinationFileName);
    string U_Drivepath = ConfigurationManager.AppSettings[Constants.REPORT_ARCHIVE_PATH].ToString();
    string TempFileName = ConfigurationManager.AppSettings["TmpAddacctDatQTPExcelTemplate"].ToString();
    string DestFileName = Constants.ADDACCTDATFILENAME;

    // Create a new workbook
    var workBookTemplate = new C1XLBook();
    var TmpWorkBookTemplate = new C1XLBook();
    try
    {
    using (var fs = new FileStream(Server.MapPath(templateName), FileMode.Open, FileAccess.Read))
    {
    // Load the existing template in C1XLBook
    workBookTemplate.Load(fs);
    //Close the File Stream
    fs.Close();
    }
    int j = 0;
    // Starting inserting the values in work book template sheets's cell.
    foreach (DataRow item in dtSheetSource.Rows)
    {
    int k = 0;
    for (k = 0; k < dtSheetSource.Columns.Count; k++)
    {
    // Add content to the sheet.
    workBookTemplate.Sheets[0][j + 2, k].Value = item[k];
    }
    j++;
    }


    // Save the workbook
    workBookTemplate.Save(Server.MapPath(U_Drivepath) + destinationFileName);

    using (var fs = new FileStream(Server.MapPath(TempFileName), FileMode.Open, FileAccess.Read))
    {
    // Load the existing template in C1XLBook
    TmpWorkBookTemplate.Load(fs);
    //Close the File Stream
    fs.Close();
    }

    // Save the workbook
    TmpWorkBookTemplate.Save(excelDestinationPath + DestFileName);

    _logger.InfoFormat(Constants.GENERATEDSHEESAVEPATHTSTR, excelDestinationPath, destinationFileName);
    using (var fs = new FileStream(excelDestinationPath + destinationFileName, FileMode.Open, FileAccess.Read))
    {
    // Create a byte array of file stream length
    var temp = new byte[fs.Length];
    // Read block of bytes from stream into the byte array
    fs.Read(temp, 0, temp.Length);
    //Close the File Stream
    fs.Close();
    }
  • Marked as Answer

    Replied 4 July 2018, 9:53 pm EST

    Hi Pratik,
    We are sorry for the delayed response on this.
    Could you please let us know if you are able to upload the generated file from this demo sample:
    http://demo.componentone.com/aspnet/ControlExplorer/C1Excel/Overview.aspx
    >>>>>>>>
    Moreover, the last cell of the file also changes from the end of the column to my last column with data So when I open the generated file and save, it successfully gets uploaded.
    <<<<<<<
    We are sorry, could you please elaborate above statement.

    Also, if there any restriction where you are trying to upload Excel sheet generated using C1Excel.

    ~Manish
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels