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
