How to import data table with its data type using template markers?

24 Jan 202312 minutes to read

You can import data table with its data type using template markers by setting the VariableTypeAction to None. The following code snippet illustrates this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IWorkbook workbook = excelEngine.Excel.Workbooks.Open("TemplateMarker_Formulas.xlsx");

  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

  //Add marker variable and preserve the text value without detecting the number formats automatically
  marker.AddVariable("NumbersTable", GetTable(), VariableTypeAction.None);

  //Process the markers in the template and fill the values as it is in the DataTable
  marker.ApplyMarkers();

  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs("TemplateMarkerFormulas.xlsx");
}

//GetTable() method
private static DataTable GetTable()
{
  Random r = new Random();
  DataTable dt = new DataTable("NumbersTable");

  int nCols = 4;
  int nRows = 10;

  for (int i = 0; i < nCols; i++)
    dt.Columns.Add(new DataColumn("Column" + i.ToString()));

  for (int i = 0; i < nRows; ++i)
  {
    DataRow dr = dt.NewRow();
    for (int j = 0; j < nCols; j++)
      dr[j] = r.Next(0, 10);
    dt.Rows.Add(dr);
  }

  for (int rowIndex = 0;  rowIndex < nRows; rowIndex++)
  {
    dt.Rows[rowIndex]["Column3"] = "2888-" + dt.Rows[rowIndex]["Column3"].ToString();
  }
  return dt;
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("TemplateMarker_Formulas.xlsx")

  'Create Template Marker Processor
  Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()

  'Add marker variable and preserve the text value without detecting the number formats automatically
  marker.AddVariable("NumbersTable", GetTable(), VariableTypeAction.None)

  'Process the markers in the template and fill the values as it is in the DataTable
  marker.ApplyMarkers()

  workbook.Version = ExcelVersion.Excel2013
  workbook.SaveAs("TemplateMarkerFormulas.xlsx")
End Using

'GetTable() method
Private Function GetTable() As DataTable
  Dim r As Random = New Random()
  Dim dt As DataTable = New DataTable("NumbersTable")
  Dim nCols As Integer = 4
  Dim nRows As Integer = 10

  For i As Integer = 0 To nCols - 1
    dt.Columns.Add(New DataColumn("Column" & i.ToString()))
  Next

  For i As Integer = 0 To nRows - 1
    Dim dr As DataRow = dt.NewRow()

    For j As Integer = 0 To nCols - 1
      dr(j) = r.[Next](0, 10)
    Next

    dt.Rows.Add(dr)
  Next

  For rowIndex As Integer = 0 To nRows - 1
      dt.Rows(rowIndex)("Column3") = "2888-" & dt.Rows(rowIndex)("Column3").ToString()
  Next

  Return dt
End Function
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  Stream inputStream = new FileStream("TemplateMarker_Formulas.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = await application.Workbooks.OpenAsync(inputStream, ExcelOpenType.Automatic);

  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

  //Add marker variable and preserve the text value without detecting the number formats automatically
  marker.AddVariable("NumbersTable", GetTable(), VariableTypeAction.None);

  //Process the markers in the template and fill the values as it is in the DataTable
  marker.ApplyMarkers();

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "TemplateMarkerFormulas";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}

//GetTable() method
private static DataTable GetTable()
{
  Random r = new Random();
  DataTable dt = new DataTable("NumbersTable");

  int nCols = 4;
  int nRows = 10;

  for (int i = 0; i < nCols; i++)
    dt.Columns.Add(new DataColumn("Column" + i.ToString()));

  for (int i = 0; i < nRows; ++i)
  {
    DataRow dr = dt.NewRow();
    for (int j = 0; j < nCols; j++)
      dr[j] = r.Next(0, 10);
    dt.Rows.Add(dr);
  }

  for (int rowIndex = 0; rowIndex < nRows; rowIndex++)
  {
    dt.Rows[rowIndex]["Column3"] = "2888-" + dt.Rows[rowIndex]["Column3"].ToString();
  }

  return dt;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream inputStream = new FileStream("TemplateMarker_Formulas.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);

  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

  //Add marker variable and preserve the text value without detecting the number formats automatically
  marker.AddVariable("NumbersTable", GetTable(), VariableTypeAction.None);

  //Process the markers in the template and fill the values as it is in the DataTable
  marker.ApplyMarkers();

  FileStream stream = new FileStream("TemplateMarkerFormulas.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
  workbook.SaveAs(stream);

  workbook.Close();
  excelEngine.Dispose();
}

//GetTable() method
private static DataTable GetTable()
{
  Random r = new Random();
  DataTable dt = new DataTable("NumbersTable");

  int nCols = 4;
  int nRows = 10;

  for (int i = 0; i < nCols; i++)
    dt.Columns.Add(new DataColumn("Column" + i.ToString()));

  for (int i = 0; i < nRows; ++i)
  {
    DataRow dr = dt.NewRow();
    for (int j = 0; j < nCols; j++)
      dr[j] = r.Next(0, 10);
    dt.Rows.Add(dr);
  }

  for (int rowIndex = 0; rowIndex < nRows; rowIndex++)
  {
    dt.Rows[rowIndex]["Column3"] = "2888-" + dt.Rows[rowIndex]["Column3"].ToString();
  }

  return dt;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream fileStream = assembly.GetManifestResourceStream("App.TemplateMarker_Formulas.xlsx");
  IWorkbook workbook = application.Workbooks.Open(fileStream);

  //Create Template Marker Processor
  ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

  //Add marker variable and preserve the text value without detecting the number formats automatically
  marker.AddVariable("NumbersTable", GetTable(), VariableTypeAction.None);

  //Process the markers in the template and fill the values as it is in the DataTable
  marker.ApplyMarkers();

  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the stream as a file in the device and invoke it for viewing
  Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("TemplateMarkerFormulas.xlsx", "application/msexcel", stream);
}

//GetTable() method
private static DataTable GetTable()
{
  Random r = new Random();
  DataTable dt = new DataTable("NumbersTable");

  int nCols = 4;
  int nRows = 10;

  for (int i = 0; i < nCols; i++)
    dt.Columns.Add(new DataColumn("Column" + i.ToString()));

  for (int i = 0; i < nRows; ++i)
  {
    DataRow dr = dt.NewRow();
    for (int j = 0; j < nCols; j++)
      dr[j] = r.Next(0, 10);
    dt.Rows.Add(dr);
  }

  for (int rowIndex = 0; rowIndex < nRows; rowIndex++)
  {
    dt.Rows[rowIndex]["Column3"] = "2888-" + dt.Rows[rowIndex]["Column3"].ToString();
  }

  return dt;
}

See Also