Spire.XLS is a professional Excel .NET library that can be used to any type of .NET( C#, VB.NET, ASP.NET, .NET Core) application and Java (J2SE and J2EE) application.

Sun May 02, 2021 4:13 am

Hi,

I'm trying to copy data from a workbook to another workbook while modifying the data from the first workbook on the go.

I've attached 2 excel files (TESTSHEET & output) for better understanding. The data is copied from TESTSHEET.xlsx to output.xlsx.

Here is my code:
Code: Select all
         string xlsPath = @"D:\TESTSHEET\TESTSHEET.xlsx";
         Workbook workbook = new Workbook();
         workbook.LoadFromFile(xlsPath);
         Worksheet sheet = workbook.Worksheets[0];
         
         Workbook newBook = new Workbook();
         newBook.LoadFromFile(@"D:\TESTSHEET\output.xlsx");
         Worksheet newSheet = newBook.Worksheets[0];

         //get the last row of TESTSHEET.xlsx
         int lastFilledRow = sheet.LastRow;
         for (int i = sheet.LastRow; i >=0; i--)
         {
            CellRange cr =sheet.Rows[i-1].Columns[1];
            if (!cr.IsBlank)
            {
               lastFilledRow =i ;
               break;
            }
         }
         
         //get the last row of output.xlsx
         int lFilledRow = newSheet.LastRow;
         for (int j = newSheet.LastRow; j >=0; j--)
         {
            CellRange lcr =newSheet.Rows[j-1].Columns[1];
            if (!lcr.IsBlank)
            {
               lFilledRow =j+1 ;
               break;
            }
         }


         sheet.Range["B5:B"+ lastFilledRow].NumberFormat = "dd-MM-yyyy";
         sheet.Range["Y5:Y"+ lastFilledRow].NumberFormat = "0.00";
         
         int x=5;
         
         //condition for some data manipulation
         if (sheet.Range["V6"].Text.StartsWith("RAW MATERIAL"))
         {
            foreach (var element in sheet.Range["A6:A"+lastFilledRow])
            {
               sheet.Columns[0].CellList[x].Value=string.Concat("RM-2021-22/",element.Value);
               x++;
            }
            
         }
         
         else if (sheet.Range["V6"].Text.StartsWith("PACKING MATERIAL"))
         {
            foreach (var element in sheet.Range["A6:A"+lastFilledRow])
            {
               sheet.Columns[0].CellList[x].Value=string.Concat("PM-2021-22/",element.Value);
               x++;
            }
         }
         
         else
         {
            foreach (var element in sheet.Range["A6:A"+lastFilledRow])
            {
               sheet.Columns[0].CellList[x].Value=string.Concat("OTH-2021-22/",element.Value);
               x++;
            }
         }
         sheet.Copy(sheet.Range["A6:B"+ lastFilledRow],newSheet.Range["A"+lFilledRow],true);
         sheet.Copy(sheet.Range["D6:D"+ lastFilledRow],newSheet.Range["C"+lFilledRow],true);
         sheet.Copy(sheet.Range["K6:K"+ lastFilledRow],newSheet.Range["D"+lFilledRow],true);
         sheet.Copy(sheet.Range["M6:M"+ lastFilledRow],newSheet.Range["E"+lFilledRow],true);
         sheet.Copy(sheet.Range["N6:N"+ lastFilledRow],newSheet.Range["F"+lFilledRow],true);
         sheet.Copy(sheet.Range["Y6:Y"+ lastFilledRow],newSheet.Range["G"+lFilledRow],true);

                        //remove duplicate rows
         for (int i = 0; i < newSheet.Columns[0].Count - 1; i++)
         {
            for (int j = i + 1; j < newSheet.Columns[0].Count; j++)
            {
               if (newSheet.Columns[0].CellList[i].Value == newSheet.Columns[0].CellList[j].Value)
               {
                  for (int col = 0; col < newSheet.Rows[0].Count; col++)
                  {
                     if (col == newSheet.Rows[0].Count - 1 && newSheet.Rows[i].CellList[col].Value == newSheet.Rows[j].CellList[col].Value)
                     {
                        newSheet.DeleteRow(j + 1);
                        j--;
                     }
                     else if (col != newSheet.Rows[0].Count - 1 && newSheet.Rows[i].CellList[col].Value == newSheet.Rows[j].CellList[col].Value)
                     {
                        continue;
                     }
                     else
                     {
                        break;
                     }
                  }
               }
            }
         }
         
         //remove the word "M/S" from any cell...but it's not working
         CellRange[] ranges = newSheet.FindAllString("M/S", false, false);

         foreach (CellRange range in ranges)
         {
            range.Text = "";
         }
         
         
         for (int i = 1; i <= newSheet.LastRow; i++)
         {
            for (int j = 1; j <= newSheet.LastColumn; j++)
            {
               CellRange range = newSheet.Range[i, j];
               String str = range.Value;
               String strings = str.Trim();
               range.Value = strings;
            }
         }
         newSheet.AllocatedRange.AutoFitColumns();
         
         newBook.SaveToFile(@"D:\TESTSHEET\output.xlsx",ExcelVersion.Version2013);
         
         newBook.Dispose();
         
         
         Console.WriteLine("Hello World!");
         
         System.Diagnostics.Process.Start(@"D:\TESTSHEET\output.xlsx");


The above code produces the output.xlsx as attached.

Now, there are a couple of issues,
1) the below code not doing what I intended it to do it just makes the entire cell blank instead of just removing the keyword "M/S"
Code: Select all
         CellRange[] ranges = newSheet.FindAllString("M/S", false, false);

         foreach (CellRange range in ranges)
         {
            range.Text = "";
         }


2) I do not want to hard code the year range in the below code
Code: Select all
         if (sheet.Range["V6"].Text.StartsWith("RAW MATERIAL"))
         {
            foreach (var element in sheet.Range["A6:A"+lastFilledRow])
            {
               sheet.Columns[0].CellList[x].Value=string.Concat("RM-2021-22/",element.Value);
               x++;
            }
            
         }
         
         else if (sheet.Range["V6"].Text.StartsWith("PACKING MATERIAL"))
         {
            foreach (var element in sheet.Range["A6:A"+lastFilledRow])
            {
               sheet.Columns[0].CellList[x].Value=string.Concat("PM-2021-22/",element.Value);
               x++;
            }
         }
         
         else
         {
            foreach (var element in sheet.Range["A6:A"+lastFilledRow])
            {
               sheet.Columns[0].CellList[x].Value=string.Concat("OTH-2021-22/",element.Value);
               x++;
            }
         }

I want to dynamically get the year range i.e. 2021-22 in this case. So I tried to replace the above code with
Code: Select all
         for (int y = lastFilledRow; y <= lastFilledRow; y++)
         {
            string myFormula = "=IF(MONTH($C"+y+")>3,YEAR($C"+y+")&\"-\"&YEAR($C"+y+")+1,YEAR($C"+y+")-1&\"-\"&YEAR($C"+y+"))";

            object formulaResult = workbook.CaculateFormulaValue(myFormula);

            string value = "RM-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();
            
            sheet.Range[y, 27].Value = value;
         }


But it isn't working either.

Help.

Thanks in advance
Attachments
TESTSHEET.zip
(19.09 KiB) Downloaded 47 times

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Mon May 03, 2021 7:37 am

Hello,

Thanks for your inquiry.
For issue 1, please use the following code instead.
Code: Select all
            CellRange[] ranges = newSheet.FindAllString("M/S", false, false);

            foreach (CellRange range in ranges)
            {
                //range.Text = "";
                range.Text = range.Text.Replace("M/S", "");
            }


For issue 2, I noticed that in your source file "TESTSHEET.xlsx", the "C" column does not contain a date. So I changed it to "B" and made some adjustments to your formula, as shown below. Please give it a try on your side. If there is any question, just feel free to contact us.
Code: Select all
            for (int y = 6; y <= lastFilledRow; y++)
            {
                string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                object formulaResult = workbook.CaculateFormulaValue(myFormula);

                string value = "RM-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                sheet.Range[y, 1].Text = value;
            }


Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1531
Joined: Tue Jul 09, 2019 2:22 am

Fri May 07, 2021 12:42 am

Hi,

Thanks.

Working good :D

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Fri May 07, 2021 1:06 am

Hello,

Glad to hear that!
If you have any other questions, just feel free to contact us.
Wish you all the best!

Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1531
Joined: Tue Jul 09, 2019 2:22 am

Mon May 17, 2021 1:27 am

Hi,

In some case where the date is like 03-Feb-20, 13-Mar-20 your code is giving results like RM-False/5, PM-False/10 etc.

Why is this and how to fix it?

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Mon May 17, 2021 5:51 am

Hello,

Thanks for your feedback.

After further investigation, I found it was caused by a missing symbol ")" in the formula. Please use this formula instead. If there are any questions, just feel free to let us know.
Code: Select all
string myFormula = "= IF(MONTH($B" + y + ") > 3, YEAR($B" + y + ") & \"-\" & RIGHT((YEAR($B" + y + ") + 1), 2), YEAR($B" + y + ") - 1 & \"-\" & RIGHT(YEAR($B" + y + "), 2))";


Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1531
Joined: Tue Jul 09, 2019 2:22 am

Fri May 21, 2021 10:27 am

Hello,

Hope you are doing well.
Has your issue been resolved? Thanks in advance for your feedback.

Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1531
Joined: Tue Jul 09, 2019 2:22 am

Fri May 21, 2021 4:36 pm

Has your issue been resolved?


I think so. No issues till now :)

Jayanta.KMC
 
Posts: 21
Joined: Sat Mar 06, 2021 2:42 pm

Mon May 24, 2021 1:23 am

Okay, thanks for your feedback!

If you have any other questions, just feel free to contact us.

Have a nice day!

Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1531
Joined: Tue Jul 09, 2019 2:22 am

Return to Spire.XLS