How can I set a row before columns when generating Excel (xlsx)

Question

I just would like to set a row for Title of my excel document before the columns. Can someone help me?! Follow the code:

Here I create my excel format, with columns

DataTable dt = new DataTable();

dt.Columns.Add("Value 1", typeof(string));
dt.Columns.Add("Value 2", typeof(string));

And here after already defined my values (var value1 = "exemple")

dt.Rows.Add(
value1,
value2);

WriteExcelWithNPOI(dt, "xlsx");

I hope it's understandable.

If necessary also follow the code that makes generate xlsx:

public void WriteExcelWithNPOI(DataTable dt, String extension){

IWorkbook workbook;

if (extension == "xlsx")
{
    workbook = new XSSFWorkbook();
}
else if (extension == "xls")
{
    workbook = new HSSFWorkbook();
}
else
{
    throw new Exception("This format is not supported");
}

ISheet sheet1 = workbook.CreateSheet("Sheet 1");

//make a header row
IRow row1 = sheet1.CreateRow(0);

for (int j = 0; j < dt.Columns.Count; j++)
{
    ICell cell = row1.CreateCell(j);
    String columnName = dt.Columns[j].ToString();
    cell.SetCellValue(columnName);
}

//loops through data
for (int i = 0; i < dt.Rows.Count; i++)
{
    IRow row = sheet1.CreateRow(i + 1);
    for (int j = 0; j < dt.Columns.Count; j++)
    {

        ICell cell = row.CreateCell(j);
        String columnName = dt.Columns[j].ToString();
        cell.SetCellValue(dt.Rows[i][columnName].ToString());
    }
}

using (var exportData = new MemoryStream())
{
    Response.Clear();
    workbook.Write(exportData);
    if (extension == "xlsx") //xlsx file format
    {
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "Relatorio-Rotas.xlsx"));
        Response.BinaryWrite(exportData.ToArray());
    }
    else if (extension == "xls")  //xls file format
    {
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "Relatorio-Rotas.xls"));
        Response.BinaryWrite(exportData.GetBuffer());
    }
    Response.End();
}}

public void WriteTsv<T>(IEnumerable<T> data, TextWriter output){

PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
foreach (PropertyDescriptor prop in props)
{
    output.Write(prop.DisplayName); // header
    output.Write("\t");
}
output.WriteLine();
foreach (T item in data)
{
    foreach (PropertyDescriptor prop in props)
    {
        output.Write(prop.Converter.ConvertToString(
                prop.GetValue(item)));
        output.Write("\t");
    }
    output.WriteLine();
}}

krlzlx · Accepted Answer · 2017-02-12T00:13:11.600

You can add first two columns: the first one with a title, the second one with a space character (because if set to empty the column name will be Column1):

DataTable dt = new DataTable();

dt.Columns.Add("Title", typeof(string));
dt.Columns.Add(" ", typeof(string));

dt.Rows.Add("Value 1", "Value 2");
dt.Rows.Add("Example 1", "Example 2");

WriteExcelWithNPOI(dt, "xlsx");

Note that DataTable does not have a colspan property, so if you want to merge columns, you'll have to do it in your WriteExcelWithNPOI method.

score 1 · Answer 2 · edited May 23 '17 at 10:29

1

I think the easiest thing to do would be to write your title information to an Excel file, close it, and then open it again to add your actual datatable to the Excel file. An example implementation can be found in this answer.

As a note, make sure you use FileStream and not MemoryStream so that your changes will be saved.

edited May 23 '17 at 10:29

Community

1
1

answered Feb 08 '17 at 15:12

C. Helling

1,394
6
20
34

How can I set a row before columns when generating Excel (xlsx)

2 Answers2