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
- How to import data from DataTable?
- How to import data from DataColumn?
- How to import data from DataView?
- How to export data from worksheet to DataTable?
- What are Import Data Options?
- How to bind from DataTable?
- How to bind from Nested Collection Objects with import data and group options?
- How to use Template marker with conditional formatting?