Same Excel workbook, different sheets

[jcknick]

Hi guys,

is it possible to use single excel workbook but take and write data to different spreadsheets in that workbook? If yes, then how do I do it?

 

[jcknick]

Anyone?

 

[LaGir]

is it possible to use single excel workbook but take and write data to different spreadsheets in that workbook? If yes, then how do I do it?

ZennoPoster supports reading/writing data only to the first spreadsheet.
But you can add any specialized external library to the References (e.g. EPPlus), and use it.

 

[jcknick]

Sounds great but a bit advanced for me

Can you give a couple real life script samples to take from a specific sheet in a workbook and to write to a specific sheet in a workbook. Maybe an .xmlz file?

I think I managed to add the library to GAC References list, just need to figure out how to do the above for my new year's needs.

 

[LaGir]

In this topic there are some examples of how to write to different sheets, use templates, apply conditional formatting with EPPlus. Also there are .xmlz file with those code examles. Оne problem - all of this in Russian, but I think it is not difficult to understand how it works. Also, you can ask me about unclear things.

Example of reading data from a specific sheet, output to zennotable:

Спойлер: Code Example

//Getting zennotable
IZennoTable tableTest = project.Tables["Table 1"];
tableTest.Clear();

//Getting Excel-file with input data
FileInfo excelFile = new FileInfo(project.Directory + @"\Data.xlsx");
//Working with Excel-file
using (ExcelPackage exPack = new ExcelPackage(excelFile))
{
    //Getting worksheet by its number/position
    ExcelWorksheet ws = exPack.Workbook.Worksheets[1];
    //Getting worksheet by its name
    //ExcelWorksheet ws = exPack.Workbook.Worksheets["Sheet1"];
 
    //Setting start row and start column of data range
    int startRow = 1;
    int startCol = 1;
    //Setting end row and end column of data range
    int totalRows = ws.Dimension.End.Row;
    int totalCols = ws.Dimension.End.Column;
    //Also it possible take row/column by number
    //int totalRows = 100;
 
    //Getting data from current sheet of Excel-file to our zennotable
    for (int row = startRow; row <= totalRows; row++){
        string[] arrRow = new string[totalCols];
        for (int col = startCol; col <= totalCols; col++){
            if (ws.Cells[row, col].Value==null){
                arrRow[col-1] = String.Empty;
            }else{
                arrRow[col-1] = ws.Cells[row, col].Value.ToString();
            }
        }
        //Current row to our zennotable
        tableTest.AddRow(arrRow);
    }
}

 

[jcknick]

Superb! I will look into it, also my Russian is pretty much native Using English for shotgun effect here on the forum