save cells style in database

Question

Good evening,i'm using a spreadsheetcontrol to save data into an SQL database, and it works, but i would like to save cells styles too, not only data. I'm talking bout cells bacground, font, dimension... is there a way to "serialize" those info so to save them in a database?Thank YouBest regardsMarco Del Frate

Vijayarasan Sivanandham · Answer

Hi Marco del frate,

SfSpreadSheet does not contain direct support to save cells style in the database.

However, you can serialize and deserialize the SfSpreadSheet as an XML file by
using the workbook like in the below code snippet and it should include
formatting, formulas, named ranges, etc. Please refer to the below code snippet,

private void OnSerializeClicked(object sender, RoutedEventArgs e)
  {
              //serialize
              IWorkbook
  workbook = this.spreadsheet.Workbook;
             
  workbook.SaveAsXml("test.xml", ExcelXmlSaveType.MSExcel);
  }
   
  private void OnDeserializeClicked(object sender, RoutedEventArgs e)
  {
              //Deserialize
              ExcelEngine
  engine = new ExcelEngine();
              IWorkbook
  workbook = engine.Excel.Workbooks.OpenFromXml("test.xml", ExcelXmlOpenType.MSExcel);
              this.spreadsheet.Open(workbook);
  }

KB Link: https://www.syncfusion.com/kb/6197/how-to-serialize-deserialize-in-sfspreadsheet

Please find the sample in the attachment and let us know if you have any
concerns in this.Regards,Vijayarasan SAttachment: SpreadSheetDemo_9b79d5e6.zip

marco del frate · Answer

Good evening and thank You for Your answer but it's not clear to me how i can use it.

I try to explain what i need:

Once i have my worksheet compiled, i would like to save data in a sql Datatable (and i can do this) and save all the cell style and text formatting too to recall them. I'm trying to save the datasheet style in a sql column (and i can do this using "worksheet.saveashtml").

Dim filePath As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\Testxls.html"

Dim filename As String = Path.GetFileName(filePath)

SpreadTarg.ActiveSheet.SaveAsHtml(filePath)

Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)

Dim br As BinaryReader = New BinaryReader(fs)

Dim bytes As Byte() = br.ReadBytes(Convert.ToInt32(fs.Length))

i save bytes in a sql column, this works good.

The thing i can not do is to load the datasheet style back and apply it to a new datasheet and then load all the data.

Dim bytes() As Byte = CType(TARGHETTEDataSet.Formattazioni.Rows(0)("FileFormat"), Byte())

Dim filePath As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\Testxls.html"

File.WriteAllBytes(filePath, bytes)

SpreadTarg.Open(filePath)

The problem is that when i try to load the datasheet style i obtain the visualization of the html code... not the cells style.

I hope to be clear.

Thank You in advance

Vijayarasan Sivanandham · Answer

Hi Marco del frate,

We regret to inform you that Spreadsheet does not support saving and reloading
cell styles through an HTML file.As we mentioned earlier, you can serialize and
deserialize as an XML file only in the SfSpreadSheet.Regards,
Vijayarasan SIf this post is helpful, please consider Accepting it as the solution so that other members
can locate it more quickly.

marco del frate · Answer

Good evening,

i'm trying to serialize the cells Istyle but i get a reflection error and i'm not enough skilled to understand the way to do it. Your workbook serialization sample is not what i need because i need the styles of the cells of just one sheet, not all the workbook... At the end: is it possible to use your control to save cells data in a SQL datatatable and the cell style at the same time, in the same datatable?

Thank You

Vasanthkumar Durairaj · Answer

Hi Marco del frate, Currently, we are analyzing your requirement. We will validate and update you on the details on or before September 7, 2022.Regards,Vasanthkumar D

Vijayarasan Sivanandham · Answer

Hi Marco del frate,

We deeply regret for the delay and inconvenience caused.We are still working on this. So, we need two more business
days to validate this. We will update with further details on September 09,
2022.We appreciate your patience and understanding.

Regards,
Vijayarasan S

Vijayarasan Sivanandham · Answer

Hi Marco del frate,
We have checked the
feasibility of achieving your requirement. We regret to inform you that there
is no possibility to save cell data in a SQL data table and the cell style at
the same time in the same data table.

But our SfSpreadSheetControl allows you to save the data by
exporting the sheet into a DataTable by using ExportDataTable. Please refer to
the below code snippet.

Private Sub OnExporttoDataTableClicked(ByVal sender As Object, ByVal e As RoutedEventArgs)
                Dim sheet As IWorksheet = spreadsheet.Workbook.Worksheets(0)
                Dim range As IRange = sheet.Range("A1:K50")
                'here export the sheet into DataTable
                Dim data_table As DataTable = sheet.ExportDataTable(range,
  ExcelExportDataTableOptions.ColumnNames)
   
                'You can add your codes here to save the DataTable into SQL
  DataBase
  End Sub

UG Link: https://help.syncfusion.com/wpf/spreadsheet/data-management#export-to-datatable

Please find the sample in the attachment and let us know if you have any
concerns in this.Regards,Vijayarasan SIf this post is helpful, please consider Accepting it as the
solution so that other members can locate it more quickly.Attachment: SfSpreadSheetDemo_afafe511.zip

marco del frate · Answer

Thank You for You effort.I'll try to find a way to save the mot important value .Best regards