We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Creating excel work book for each customer

Thread ID:

Created:

Updated:

Platform:

Replies:

132573 Sep 9,2017 01:56 PM UTC Sep 11,2017 09:16 AM UTC Windows Forms 2
loading
Tags: XlsIO
sateesh kumar
Asked On September 9, 2017 02:11 PM UTC

Dear team,

Am using Syncfusion.XlsIO.Base dll for read and write excel. am having multiple customer data with duplicate data of excel.
Now my goal is create new work book for every customer. i am getting error while copying rows to data table.
here is my code.

distinctTable is having only key values without duplicate.
dt table is actual data with duplicate

for (int i = 0;i<=distinctTable.Rows.Count-1; i++ )
            {
                DataTable dtCust= new DataTable();               
                DataRow[] rowsToCopy;
                string id= distinctTable.Rows[i][0].ToString();
                dtCust.TableName = custid;
             /// Where condition
                string filter = "ID=" + id;
          // Get the all  rows from table where matching with
                rowsToCopy = dt.Select(filter);
                foreach (DataRow dr in rowsToCopy)
                {                    
                    dtCust.NewRow();                    
                    dtCust.ImportRow(dr); 
                    //dtCust.NewRow();
                    //dtCust.Rows.Add(dr);
                }
}

Am not able to import the data into data table when i=0.
and am getting error (Cannot perform '=' operation on System.String and System.Int32.)   for the second customer, near  rowsToCopy = dt.Select(filter);

Please find the attached sample data.

Thanks for advance.
Sateesh Kumar


Attachment: Create_Excel_1daf74c.zip

sateesh kumar
Replied On September 11, 2017 05:38 AM UTC

After trying in other ways, i got solution.

here is my working and expected code.

 for (int i = 0; i <= distinctTable.Rows.Count - 1; i++)

            {

                DataTable dtCust = new DataTable();  

                string id = distinctTable.Rows[i][0].ToString();               

                var matchingRows = dt.AsEnumerable().Where(r => r.Field("ID") == id);

                string filter = "ID=" + id;

                if (matchingRows.Any())

                {

                    dtCust = matchingRows.CopyToDataTable();

                    dtCust.TableName = custid;                   

                    IWorkbook custWb =app.Workbooks.Add();

                    IWorksheet cust = custWb.Worksheets[0];

                    cust.Name = Path.GetFileNameWithoutExtension(source).ToString();

                    cust.ImportDataTable(dtCust, true, 1, 1);

                    cust.Range[1, 1, 1, cust.UsedRange.End.Column].AutofitColumns();

                    string filename = Path.Combine(destination, id + ".xls");              

                    custWb.SaveAs(filename);

                    custWb.Close();

                }

                    

                

            }



Mohan Chandran [Syncfusion]
Replied On September 11, 2017 09:16 AM UTC

Hi Sateesh, 
 
We are glad that the issue is resolved at your side. Please let us know if you have any other queries. 
 
Regards, 
Mohan Chandran. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;