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

8 Dec 20237 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())
{
  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())
{
  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

See Also