Might find an alternative solution. Just need to know the syntax for using the CELL function on a Direct Calculation of Formula with Aspose. The formula is as follows:
=CELL(“col”, F_Dep) . If you insert the formula in any cell in the workbook provided in the 1st thread (test.zip) you’ll see that will display the index no of the named column heading.
For documentation on the function please see: CELL function - Microsoft Support
Hi,
Could you post some csv files , different template files, expected files here? We will check it soon.
@Remus87
We changed your template file as attched fileInputData.zip (234 Bytes)
We think data in the first row of txt file should be the defined name of template file.
Please try the following codes:
static void Main(string[] args)
TxtLoadOptions loadOptions = new TxtLoadOptions(LoadFormat.Csv);
Workbook data = new Workbook(dir + “InputData.txt”,loadOptions);
SetData(dir + “template1.xlsx”, data, dir + “output1.xlsx”);
SetData(dir + “template2.xlsx”, data, dir + “output2.xlsx”);
}
private static void SetData(string file, Workbook data, string dest)
{
Workbook template1 = new Workbook(file);
Cells dataCells = data.Worksheets[0].Cells;
int maxDataRow = dataCells.MaxDataRow;
Row row = dataCells.Rows[0];
for(IEnumerator ie = row.GetEnumerator();ie.MoveNext();)
{
Cell cell = (Cell)ie.Current;
string str = cell.StringValue;
Range range = template1.Worksheets.GetRangeByName(str);
int dataCountInColumn = maxDataRow - 1 + 1;
Range sourceRange = dataCells.CreateRange(1, cell.Column, dataCountInColumn, 1);
Range destRange = range.Worksheet.Cells.CreateRange(2, range.FirstColumn, dataCountInColumn, 1);
destRange.CopyData(sourceRange);
}
template1.Save(dest);
}Hi !
The code snippet provided is throwing error (NullReferenceException on template1.xlsx -> cell A3)
I guess you have not used the data file and sample code which @simon.zhao shared in his post. I have tested using your template1.xlsx, template2.xls, InputData.txt (updated) with latest version of Aspose.Cells and it works fine. Here is sample code (the code is same as @simon.zhao shared in his post):
e.g.
Sample code:
TxtLoadOptions loadOptions = new TxtLoadOptions(LoadFormat.Csv);
Workbook data = new Workbook("e:\\test2\\InputData.txt",loadOptions);
SetData("e:\\test2\\template1.xlsx", data, "e:\\test2\\output1.xlsx");
SetData("e:\\test2\\template2.xlsx", data, "e:\\test2\\output2.xlsx");
........
private static void SetData(string file, Workbook data, string dest)
{
Workbook template1 = new Workbook(file);
Cells dataCells = data.Worksheets[0].Cells;
int maxDataRow = dataCells.MaxDataRow;
Row row = dataCells.Rows[0];
for(IEnumerator ie = row.GetEnumerator();ie.MoveNext();)
{
Cell cell = (Cell)ie.Current;
string str = cell.StringValue;
Range range = template1.Worksheets.GetRangeByName(str);
int dataCountInColumn = maxDataRow - 1 + 1;
Range sourceRange = dataCells.CreateRange(1, cell.Column, dataCountInColumn, 1);
Range destRange = range.Worksheet.Cells.CreateRange(2, range.FirstColumn, dataCountInColumn, 1);
destRange.CopyData(sourceRange);
}
template1.Save(dest);
}
Please find attached all the files (input files, data file and output files) in the attached zipped archive for your reference.
files1.zip (34.8 KB)
Another query that is not related with the present thread but since we have the snippet of code already supplied by Simon i don’t have to supply a console app and could demo in the code supplied my query:
- at the start of the method SetData(…) kill the Process (dest)
foreach (var process in Process.GetProcessesByName(“Excel”))
if (process.MainWindowTitle.Contains(dest))
process.Kill(); - at the end of the method run the same process
Process.Start(dest);
The 2nd time when running the method while the workbook is open it will trigger the Excel AutoRecover panel. How do i get rid of the panel (don’t want to get displayed) ?
Thanks in advance.
Please note, if the Excel file is already opened in MS Excel and you again process the SetData method, it will surely give you error message, something like: “The process cannot access the file ‘e:\test2\output1.xlsx’ because it is being used by another process”. This error is inevitable and you cannot escape it. But if you could manually close the Workbook(file) in MS Excel, it will work. Mind you, this is not an issue with Aspose.Cells APIs by any means but you cannot access a file to re-save it (with the same name) again which is already consumed/opened in another process (MS Excel in this case). Even you will get the same issue if you do not use/involve Aspose.Cells APIs and just use System.IO APIs to read/write the Excel file (while MS Excel already has opened the file in parallel).
Hi Amjad!
Thanks for the quick reply. Yes i know will throw the “…being used by another process” error due to the integrity of the spreadsheet file format. But i’m just trying to simulate the COM environment: excelApp.Visible = true where the user can view the file (is displayed on screen) without saving it (hence the SetDat in theory should be able to process). I know is not possible to have the identical functionality as with Interop Office, and the target is to achieve a closer scenario. That is why we’re using the technique kill / start the process so that it feels as the process (report file) is continuous running. Therefore, when opened the process in M. Excel it will trigger the AutoRecovery panel (when starting process in other spreadsheet editors eg. OpenOffice is fine). Just have a go with the sample and instructions from here and let me know if you can reproduce the behaviour on different spreadsheet editors.
It looks you are pointing some other issue which is not the same with the exception I mentioned, is not it? Do you mean the generated file causes MS Excel display some sort of “recovery” panel? If so, it means the generated file by Aspose.Cells is corrupted. I do not find such an issue using/processing SetData method two times (in a row) after embedding your suggested code segments.
We are not entirely certain what is your exact process on your end. We require more information and complete step-by-step details to reproduce this issue ( “it will trigger the AutoRecovery panel”) on our end.
Try the below method please:
public void TestWorkbook()
{
foreach (var process in Process.GetProcessesByName("Excel"))
{ if (process.MainWindowTitle.Contains("Test777")) process.Kill(); }
Workbook book = new Workbook("Test777.xlsx");
Cells cells = book.Worksheets[0].Cells;
if (cells[0, 0].StringValue != string.Empty) cells[cells.MaxDataRow+1, 0].PutValue("Test");
else cells[0, 0].PutValue(DateTime.Now);
book.Save("Test777.xlsx");
Process.Start("Test777.xlsx");
Application.Exit();
}
Just call it on a simple console app on the Main. Run the program -> the file will open -> don’t close the excel file and rerun the program.
I attach both the Test777.xlsx (which is just an empty workbook) and the runnable project sample (console app) . Just run the console, will trigger file -> don’t close the file and run again the console app (can run the console multiple times, keeping the file open always)Test777.zip (5.2 KB)
SampleProject1.zip (5.5 MB)
Thanks for the sample file and project.
I did open your sample project into VS.NET 2015 while setting/adding reference to latest version/fix: Aspose.Cells for .NET v21.11. I then run the project and got the same error which I already told you with complete details in one of my previous reply. See the attached screenshot of the error for your reference.
sc_shot1.png (98.5 KB)
I will again paste some of the contents of the reply here.
By the way, to cope with the error, one way can be, you may re-save the file with different/unique name.
I’m using Aspose.Cells for .Net 21.5 in VS.NET 2019 Runtime version=“v4.0” and .NETFramework version 4.6.1.
You shouldn’t get the error as you’re ending the Test777.xlsx Process in the first 2 lines of the TestWorkbook() method
And on the last line you detach the Process from the Application.
So i am able to run each time the program with the Test777.xlsx open, and each time it keeps adding to the Document Recovery toolbar a new version of the file. See screen shotSample_ProcessOpened.PNG (21.2 KB)
You provided your sample project and you asked to download and try your exact project to get your issue. We did run your exact project (as we supposed to do) and got the error we mentioned in our previous reply. Anyways, I deleted the first two lines and last line in the method and re-run it. On the second run, I got the same issue. See the attached screenshot.
sc_shot12.png (95.2 KB)
Please note, you cannot re-save an Excel file with same name while it is already opened in Excel (because the output file is already a part of the Excel process). You have to close the file from MS Excel or save with different name. For confirmation, please do not use Aspose.Cells APIs and use only System.IO APIs to read and write the file (while the file is opened into MS Excel) and you should get the same error.
Yes the whole idea is to run my provided project and replicate the same exact behaviour.
Regarding the lines of code (2 lines at front and the last line) i just wanted to showcase that the error should not occur exactly because you have those lines present in the method.
Otherwise is normal that the error message should occur. So you should not comment at all those lines.
Step by step:
- Run the program
- Rerun the Program (without doing any action as closing the Test777.xlsx process)
- Repeat step 2
It looks you want MS Excel recover the file with the newly imported data/records automatically. We are very sorry but this is impossible. We should not and in fact, cannot modify the temp files maintained (in the process) and locked by MS Excel. We are afraid there is no way for us to support such kind of requirements. If you still think it is possible, you may provide us the sample code but without using our APIs, e.g., just use an existing Excel file with some demo data with your own code to achieve your goal. We can check whether we can simulate this behavior with our component.