Hi,
I have this html string (attached with zip file) and i want to make table in excel using that(sample output also inside zip file)
Input and Required Output.zip (7.1 KB)
Is there any way i can do that?
@Amrinder_Singh
Please try the following codes:
HtmlLoadOptions options = new HtmlLoadOptions();
Workbook workbook = new Workbook(dir + "html string.txt", options);
workbook.Save(dir + "dest.xlsx");Hi @simon.zhao Thanks for quick reply
But I have one existing excel I want to export table into that file and i want my table to start in new sheet of the existing excel from the 8th row
One more point i will not have html string in the file it will be string variable in my code. i added in file just for your reference
@Amrinder_Singh
We do not support assigning start row or column when loading the file, so you have to copy loaded to data existed file as the following 
string text = File.ReadAllText(dir + "html string.txt");
using (MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(text)))
{
HtmlLoadOptions options = new HtmlLoadOptions();
Workbook dataBook = new Workbook(ms, options);
Range dataRange = dataBook.Worksheets[0].Cells.MaxDisplayRange;
Workbook workbook = new Workbook(dir + "Book1.xlsx");
Range expRange = workbook.Worksheets[0].Cells.CreateRange(7, 0, dataRange.RowCount, dataRange.ColumnCount);
expRange.Copy(dataRange);
workbook.Save(dir + "dest.xlsx");
}
@simon.zhao Thank you for this suggestion.
One more suggestion i need I have one workbook(e.g workbook 1) inside that i have four sheets(sheet 1, sheet2, sheet 3, sheet 4)
I want to give link of sheet 4 inside cell of sheet 2. So that if someone click on that link he should go to that sheet. How can i do that?
I did the same and its throwing me error saying reference not found
var tableSheetLink = cellss[row, 3];
sheet.Hyperlinks.Add(tableSheetLink.Name, 1, 1, “My Worksheet4”);
Please change as :
sheet.Hyperlinks.Add(tableSheetLink.Name, 1, 1, “‘My Worksheet4’!A1”);
@simon.zhao
Yes this is working, i have one more query i one table which will start from 7th row, i want to lock rows and columns of this table dynamically.(sample file is attached)
LockRows.zip (7.0 KB)
Thanks for the sample file.
By locking, I think you meant protect/lock rows/cols/cells in the worksheet. See the document on how to protect whole worksheet, specific row(s), columns, cells, etc., for your reference.
https://docs.aspose.com/cells/net/protecting-worksheets/
@Amrinder_Singh
Could you make an excepted Excel file in MS Excel and share it here? We will check it soon.
I’m not quite sure if you need to protect the worksheet or freeze the rows and columns
@simon.zhao
The rows and columns which i marked as red should be locked and it should be handled dynamicaly
LockRows (3).zip (7.9 KB)
Plese use this new file attached for your reference
Thanks for the sample file.
See the following sample code to accomplish your task for your reference. I have locked the cells shaded in red color in the worksheet. Other cells are not protected and can be edited.
e.g.
Sample code:
Workbook wb = new Workbook("g:\\test2\\LockRows.xlsx");
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
// Define the styleflag object
StyleFlag styleflag;
// Loop through all the columns in the worksheet and unlock them.
// by default, all cells in the worksheet are locked
// so when we protect a sheet all cells in the sheet would be protected
for (int i = 0; i <= 16383; i++)
{
style = sheet.Cells.Columns[(byte)i].GetStyle();
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);
}
// Lock the cells...i.e. A1, B1, A2, B2, A7, B7, A8, B8, A9, B9
style = sheet.Cells["A1"].GetStyle();
style.IsLocked = true;
sheet.Cells["A1"].SetStyle(style);
style = sheet.Cells["B1"].GetStyle();
style.IsLocked = true;
sheet.Cells["B1"].SetStyle(style);
style = sheet.Cells["A2"].GetStyle();
style.IsLocked = true;
sheet.Cells["A2"].SetStyle(style);
style = sheet.Cells["B2"].GetStyle();
style.IsLocked = true;
sheet.Cells["B2"].SetStyle(style);
style = sheet.Cells["A7"].GetStyle();
style.IsLocked = true;
sheet.Cells["A7"].SetStyle(style);
style = sheet.Cells["B7"].GetStyle();
style.IsLocked = true;
sheet.Cells["B7"].SetStyle(style);
style = sheet.Cells["A8"].GetStyle();
style.IsLocked = true;
sheet.Cells["A8"].SetStyle(style);
style = sheet.Cells["B8"].GetStyle();
style.IsLocked = true;
sheet.Cells["B8"].SetStyle(style);
style = sheet.Cells["A9"].GetStyle();
style.IsLocked = true;
sheet.Cells["A9"].SetStyle(style);
style = sheet.Cells["B9"].GetStyle();
style.IsLocked = true;
sheet.Cells["B9"].SetStyle(style);
// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save("g:\\test2\\output.out.xlsx");
Please find attached the output Excel file.
output.out.zip (8.4 KB)
@amjad.sahi
I See you have locked the cells hardcording with cell names but this will not always be the case. Table rows and columns can increase and also no. of rows and columns to lock.
suppose you have two variables in which values are filled with how many rows and columns of table should be locked and table will always start from row 7
First of all your so called table in your provided file is not a table but ordinary/common data. How come we know some particular data range is your desired table as it is mixed with other data? Anyways, if your table starts from 7th row, at least you can start locking cells from the 7th row. Also, if you have some variables which define number of rows and columns of the table, you may easily evaluate data table range. For example, variable1 defines 10 rows starting from 7th row. variable2 defines 2 columns. So, your final table matrix (data range) would be evaluated as:
start cell = A7
end row => start row + number of rows => 7+10 =17
end cell = B17.
table range: A7:B17
Hope, this helps a bit.