Have a sample template where the data must be populated dynamically based where column heading will reside. On the template i’ve assigned variable names for each column that will be used. You’ll see on the template if you click on column heading “A” is assigned variable S_Ref, “B” -> F_No and few more as per the workbook.
The reason for that is to allow the user to interact with the workbook and move the columns wherever he needs to, so that the data will be filled based on those variables rather than the column names (Ref. , No. , Depth.) because these will not be always same (column names)
test.zip (8.0 KB)
Some options (choose one) which you may try if it suits your needs.
-
You can use Smart Markers (see the document on Smart Markers for reference) feature. Insert Smart Markers into the cells of the sheet, so you may fill up the data based on Smart Markers pointers for each column accordingly.
-
You can insert data cell by cell for each column based on your specified named range for your needs. You got to write your own code yourselves, so relevant data should be inserted into corresponding column based on your named cells.
-
You can try to use data importing techniques provided by Aspose.Cells APIs. See the document on data importing options for your reference.
Hi . I’m afraid you didn’t understood the requirement. Therefore can’t use the Smart Markers as with this feature you can read data dynamically from what i’ve tested but can’t populate the worksheet dynamically as it should have the markers on fixed cells on the designer workbook.
We need to write as well data dynamically into the workbook because the user will be able to interact with worksheet and move columns as per his / her needs.
The data to be inputted in the workbook will come in .csv or .txt format and from that we can easily transfer into a new workbook (to read it dynamically). Up to here is ok.
Now we just need a marker or pointer on the new workbook that will map that data into it based on column headings (the instructions you provided are for column names and are ok, but since the column names could differ (different language) we must map to another higher element from spreadsheet which we believe are column headers).
For an easier understanding will describe in few steps the scenario:
- Open Excel and create New Workbook
- Click a letter of the column you want to rename to highlight the entire column (for eg A)
- Click the “Name” box, located to the left of the formula bar, and press “Delete” to remove the current name
- Enter a new name for the column and press “Enter.”
When you click that column you’ll see the name you just entered instead of the default column header name (eg. A)
Now the ideal scenario will be to apply CellsHelper to retrieve the index by that column name: int colHeadInd = CellsHelper.ColumnNameToIndex(“YourEnteredName”);
but any workaround that will do the job is ok really.
There is no such overload (static) method in CellsHelper class which could give you the column index based on named range; it can only give you column index based on actual Column name (e.g. A, B, C, etc.).
To get named range, you may try like following:
e.g.
Sample code:
............
Range range = workbook.Worksheets.GetRangeByName("yourname");
int col = range.FirstColumn;
int row = range.FirstRow;
........
Hope, this helps a bit.
Hi Amjad !
Yes getting the range will be a good solution if we would have already know a pointer / marker / fieldname from the workbook created from csv or text file or have any match element to map with. But in this case as mentioned above the data and the column names from the workbook to be read from won’t always have the same values. That is why is needed an element (most suitable element is column headers) in the new workbook so it can be mapped with the feed data.
Because as explained previous the user can copy the column let’s say column A to column X for example. Afterwards, the data (from workbook to be read which will hold 1 row (beside col names) so will feed 1 row per process) that supposed to fill column A will fill column X (will follow header column name)
We are sorry but we could not understand you. Please give us step by step detail on how to accomplish the task in MS Excel manually and then provide sample (runnable) code (via Aspose.Cells APIs) where you are finding any issue with it. Give us complete details where Aspose.Cells could not implement the same thing which MS Excel can do. Please note, if MS Excel has any built-in option to accomplish the task in one go, then Aspose.Cells can do that. Otherwise, similar to MS Excel, you got to populate data row by row (cell by cell).
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: https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf
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.