How to add data into excel cells row by row when pressing button click event?

Question

Hi fellas.My program flow is like when the user click record button, the data will be recorded and save into excel file (xlsx or csv). But when i click the button, the data inside excel file will  only shown current data and not record previous data if i click multiple times, anyone can help me with that? Below is my code:     using (ExcelEngine excelEngine = new ExcelEngine())                {                    //Set the default application version as Excel 2013.                    excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;                    //Create a workbook with a worksheet                    IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);                    //Access first worksheet from the workbook instance.                    IWorksheet worksheet = workbook.Worksheets[0];                    //Adding text to a cell                    worksheet.Range["A1"].Text = "CPS";                    worksheet.Range["B1"].Text = "USV";                    worksheet.Range["C1"].Text = "Max";                    worksheet.Range["D1"].Text = "Mean";                    worksheet.Range["E1"].Text = "Date";                    worksheet.Range["F1"].Text = "Location";                                        //in below, count is number button clicked, if clicked first time, count=2, then position in cell will be in A2,B2 and goes on                    worksheet.Range["A" + count].Value2 = lbl_cps.Text;                    worksheet.Range["B" + count].Value2 = lbl_usv.Text;                    worksheet.Range["C" + count].Value2 = lbl_max.Text;                    worksheet.Range["D" + count].Value2 = lbl_mean.Text;                    worksheet.Range["E" + count].Value2 = date;                    worksheet.Range["F" + count].Value2 = Infos.Location;                    worksheet.UsedRange.AutofitColumns(); // akan fit                     //Save the workbook to stream in xlsx format.                     MemoryStream stream = new MemoryStream();                    workbook.SaveAs(stream);                    MemoryStream sheetStream = new MemoryStream();                    worksheet.SaveAs(sheetStream, ",");                    workbook.Close();                    //Save the stream as a file in the device and invoke it for viewing                    SaveAndroid androidSave = new SaveAndroid();                    androidSave.SaveAndView("Radiation_reading.xlsx", "application/msexcel", stream, this);                }from code above, the current data will be shown only  but the previous save data will be flashed or just empty

Shamini Kiruba Sobers · Accepted Answer

Hi Muaaz, 

Thanks for the patience. 

We have prepared a sample with your code as base to achieve your requirement. You can download the sample from the following link. 

Sample link: https://www.syncfusion.com/downloads/support/forum/162923/ze/F162923-2124113583 

Kindly let us know if it helps.

Shamini Kiruba Sobers · Answer

Hi Muaaz, 

Greetings from Syncfusion support. 

We are trying to achieve your requirement in a sample. We will share the sample on tomorrow (February 26th, 2021). 

Regards, 
Shamini

Muaaz Badrul · Answer

It works and able to store data  row by row as well when i clicked the button. Thank you for the assist and help. Much appreciated !

Muaaz Badrul · Answer

Hi it is me again, May i know if it is possible to check if the row of data inserted already reach 1000 rows, then stop write data into excel and close the events?

Shamini Kiruba Sobers · Answer

Hi Muaaz, 

Thanks for the update. 

You can use IRange.LastRow property to check the used rows. Kindly add the highlighted condition in the code snippet to check if the row of data inserted already reached 1000 rows. 

Code snippet: 





using (ExcelEngine excelEngine = new ExcelEngine()) 
{ 
    IApplication application = excelEngine.Excel; 
    //Set the default application version as Excel 2013. 
    application.DefaultVersion = ExcelVersion.Excel2013; 
 
    IWorkbook workbook; 
    IWorksheet worksheet; 
 
    if (count == 1) 
    { 
        //Create a workbook with a worksheet 
        workbook = excelEngine.Excel.Workbooks.Create(1); 
 
        //Access first worksheet from the workbook instance. 
        worksheet = workbook.Worksheets[0]; 
 
        //Adding text to a cell 
        worksheet.Range["A1"].Text = "CPS"; 
        worksheet.Range["B1"].Text = "USV"; 
        worksheet.Range["C1"].Text = "Max"; 
        worksheet.Range["D1"].Text = "Mean"; 
        worksheet.Range["E1"].Text = "Date"; 
        worksheet.Range["F1"].Text = "Location"; 
 
        count++; 
    } 
    else 
    { 
        SaveAndroid androidOpen = new SaveAndroid(); 
        byte[] byteArray = androidOpen.Open("Radiation_reading.xlsx"); 
        Stream inputStream = new MemoryStream(byteArray); 
 
        //Opens the workbook  
        workbook = application.Workbooks.Open(inputStream); 
 
        //Access first worksheet from the workbook instance. 
        worksheet = workbook.Worksheets[0]; 
 
        if(worksheet.Range.LastRow <= 1000) 
        { 
            //in below, count is number button clicked, if clicked first time, count=2, then position in cell will be in A2,B2 and goes on 
            worksheet.Range["A" + count].Value2 = 200; 
            worksheet.Range["B" + count].Value2 = 400; 
            worksheet.Range["C" + count].Value2 = 400; 
            worksheet.Range["D" + count].Value2 = 300; 
            worksheet.Range["E" + count].Value2 = DateTime.Now; 
            worksheet.Range["F" + count].Value2 = "location"; 
 
            count++; 
        } 
    } 
 
    worksheet.UsedRange.AutofitColumns(); // akan fit  
 
    //Save the workbook to stream in xlsx format.  
    MemoryStream stream = new MemoryStream(); 
    workbook.SaveAs(stream); 
 
    workbook.Close(); 
 
    //Save the stream as a file in the device and invoke it for viewing 
    SaveAndroid androidSave = new SaveAndroid(); 
    await androidSave.SaveAndView("Radiation_reading.xlsx", "application/msexcel", stream, this); 
    stream.Dispose(); 
} 


Regards, 
Shamini

Muaaz Badrul · Answer

Thank you, it is working perfectly

Shamini Kiruba Sobers · Answer

Hi Muaaz, 
  
We are glad that you are happy with the solution. 
  
Regards, 
Shamini

Muaaz Badrul · Answer

Hi Shamini, in code belowif(worksheet.Range.LastRow <= 1000)         {             //in below, count is number button clicked, if clicked first time, count=2, then position in cell will be in A2,B2 and goes on             worksheet.Range["A" + count].Value2 = 200;             worksheet.Range["B" + count].Value2 = 400;             worksheet.Range["C" + count].Value2 = 400;             worksheet.Range["D" + count].Value2 = 300;             worksheet.Range["E" + count].Value2 = DateTime.Now;             worksheet.Range["F" + count].Value2 = "location";                                                                                    IStyle headstyle2 = workbook.Styles.Add("HeaderStle2");                    headstyle2.BeginUpdate();                    headstyle2.HorizontalAlignment = ExcelHAlign.HAlignCenter;                    headstyle2.EndUpdate();                                        worksheet.Rows[count].CellStyle = headstyle2;            count++;         } is it possible to horizontally text alligned for each row?i try to use worksheet.rows as in highlighted but it doesnt't work, any suggestion?Thank you

Shamini Kiruba Sobers · Answer

Hi Muaaz, 

You might have faced index out of range exception because the index of worksheet.Rows is zero-based. Also the style name added to the workbook must be unique. So please use the below modified code and let us know if it works. 

Code snippet: 





if(worksheet.Range.LastRow <= 1000) 
{ 
    //in below, count is number button clicked, if clicked first time, count=2, then position in cell will be in A2,B2 and goes on 
    worksheet.Range["A" + count].Value2 = 200; 
    worksheet.Range["B" + count].Value2 = 400; 
    worksheet.Range["C" + count].Value2 = 400; 
    worksheet.Range["D" + count].Value2 = 300; 
    worksheet.Range["E" + count].Value2 = DateTime.Now; 
    worksheet.Range["F" + count].Value2 = "location"; 
 
    IStyle headstyle2 = workbook.Styles.Add("HeaderStle" + count.ToString()); 
    headstyle2.BeginUpdate(); 
    headstyle2.HorizontalAlignment = ExcelHAlign.HAlignCenter; 
    headstyle2.EndUpdate(); 
 
    worksheet.Rows[count-1].CellStyle = headstyle2; 
 
    count++; 
} 

Regards, 
Shamini

Muaaz Badrul · Answer

The solution is working. Thank you for your response !

Shamini Kiruba Sobers · Answer

Hi Muaaz, 
  
We are glad that the suggestion helped you. 
  
Regards, 
Shamini