Creating excel work book for each customer

[sateesh kumar]

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]

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]

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.