C# NPOI导出Excel横向纵向显示

  /// <summary>  /// DataTable导出Excel(纵向导出)  /// </summary>  /// <param name="dataTable">数据源</param>  /// <param name="filePath">保存的路径</param>  /// <param name="documentname">表名</param>  public static void Excel2(this DataTable dataTable, string filePath, string documentname)  {   string sheetName = "Sheet1";   if (dataTable == null || dataTable.Rows.Count == 0)    throw new Exception("No data to export");   ISheet sheet = null;   IWorkbook workbook = null;   try   {    if (Directory.Exists(filePath) == false)    {     Directory.CreateDirectory(filePath);    }    string filedocmentname = "\\File Card.xls";//文件名    using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))    {     workbook = new HSSFWorkbook();     if (string.IsNullOrEmpty(sheetName))      sheetName = "Sheet1";//工作簿     sheet = workbook.CreateSheet(sheetName);     IRow row = sheet.CreateRow(0);     IFont font = workbook.CreateFont();     font.FontName = "Arial";//字体样式     font.Boldweight = (short)FontBoldWeight.Bold;     ICellStyle headerStyle2 = workbook.CreateCellStyle();     headerStyle2.VerticalAlignment = VerticalAlignment.Center; //垂直居中     headerStyle2.WrapText = true;//自动换行     font.FontHeightInPoints = 9;//字体     for (int i = 0; i < dataTable.Columns.Count; i++)     {      ICellStyle header= workbook.CreateCellStyle();      header.VerticalAlignment = VerticalAlignment.Center; //垂直居中      header.WrapText = true;//自动换行      var row1_ = sheet.CreateRow(i);//创建行      var cellName = row1_.CreateCell(0);//列名      cellName.SetCellValue(dataTable.Columns[i].ColumnName.ToString().Replace("<br />", "\n"));      IFont font2 = workbook.CreateFont();      font2.Boldweight = (short)FontBoldWeight.Bold;      header.SetFont(font);      cellName.CellStyle = header;
　　　　　　　　　　　　　　 //填充内容      for (int j = 0; j < dataTable.Rows.Count; j++)      {       var cell1_ = row1_.CreateCell(j + 1);       cell1_.SetCellValue(dataTable.Rows[j][i].ToString().Replace("<br />", "\n"));       cell1_.CellStyle = headerStyle2; //把样式赋给单元格      }     }     //设置列宽     sheet.SetColumnWidth(0, 10 * 256+200);//列宽为10
     //设置行高
     //row.Height = 30 * 20;//行高为30     workbook.Write(fs); //写入到excel    }   }   finally   {    if (workbook != null)     workbook.Clear();   }  }

 　　　　 /// <summary>  /// DataTable导出Excel(横向导出)  /// </summary>  /// <param name="dataTable">数据源</param>  /// <param name="filePath">保存的路径</param>  /// <param name="documentname">表名</param>  public static void Excel(this DataTable dataTable, string filePath, string documentname)  {
　　　　　　　//datatable第一列增加序号   dataTable= AddSeriNumToDataTable(dataTable);   string sheetName = "Sheet1";   if (dataTable == null || dataTable.Rows.Count == 0)    throw new Exception("No data to export");   ISheet sheet = null;   IWorkbook workbook = null;   try   {    if (Directory.Exists(filePath) == false)    {     Directory.CreateDirectory(filePath);    }    string filedocmentname = "\\List.xls";//文件名
    using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))    {     workbook = new HSSFWorkbook();     if (string.IsNullOrEmpty(sheetName))      sheetName = "Sheet1";     sheet = workbook.CreateSheet(sheetName);     IRow row = sheet.CreateRow(0);     IFont font = workbook.CreateFont();     font.FontName = "Arial";//字体样式     ICellStyle headerStyle2 = workbook.CreateCellStyle();     headerStyle2.VerticalAlignment = VerticalAlignment.Center; //垂直居中     headerStyle2.WrapText = true;//自动换行     font.FontHeightInPoints = 9;//字体     //填充表头     for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)     {      ICellStyle headerStyle = workbook.CreateCellStyle();       headerStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;//首行填充颜色      headerStyle.FillPattern = FillPattern.SolidForeground;      headerStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中       headerStyle.WrapText = true;//自动换行      headerStyle.BorderTop = BorderStyle.Thin;//上边框      headerStyle.BorderBottom = BorderStyle.Thin;//下边框      headerStyle.BorderLeft = BorderStyle.Thin;//左边框      headerStyle.BorderRight = BorderStyle.Thin;//右边框      var cell = row.CreateCell(columnIndex);      cell.CellStyle = headerStyle;      cell.SetCellValue(dataTable.Columns[columnIndex].ColumnName.Replace("<br />", "\n")); //设置列名      row.HeightInPoints= 35;     }     //填充内容     for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)     {      row = sheet.CreateRow(rowIndex + 1);      for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)      {       row.CreateCell(columnIndex).SetCellValue(Convert.ToString(dataTable.Rows[rowIndex][columnIndex]));       ICell cellData = row.CreateCell(columnIndex);       cellData.SetCellValue(dataTable.Rows[rowIndex][columnIndex].ToString().Replace("<br />", "\n"));       cellData.CellStyle = headerStyle2; //把样式赋给单元格      }     }     sheet.CreateFreezePane(0, 1, 0, 1); //首行冻结     workbook.Write(fs); //写入到excel    }   }   finally   {    if (workbook != null)     workbook.Clear();   }  }

  /// <summary>  /// 在DataTable中添加一序号列，编号从1依次递增  /// </summary>  /// <param >DataTable</param>  /// <returns></returns>  public static DataTable AddSeriNumToDataTable(DataTable dt)  {   //需要返回的值   DataTable dtNew;   if (dt.Columns.IndexOf("序号") >= 0)   {    dtNew = dt;   }   else //添加一序号列,并且在第一列   {    int rowLength = dt.Rows.Count;    int colLength = dt.Columns.Count;    DataRow[] newRows = new DataRow[rowLength];    dtNew = new DataTable();    //在第一列添加“序号”列    dtNew.Columns.Add("序号");    for (int i = 0; i < colLength; i++)    {     dtNew.Columns.Add(dt.Columns[i].ColumnName);     //复制dt中的数据     for (int j = 0; j < rowLength; j++)     {      if (newRows[j] == null)       newRows[j] = dtNew.NewRow();      //将其他数据填充到第二列之后，因为第一列为新增的序号列      newRows[j][i + 1] = dt.Rows[j][i];     }    }    foreach (DataRow row in newRows)    {     dtNew.Rows.Add(row);    }   }   //对序号列填充，从1递增   for (int i = 0; i < dt.Rows.Count; i++)   {    dtNew.Rows[i]["序号"] = i + 1;   }   return dtNew;  }

　　　　　/// <summary>  /// Datatable导出Excel(无任何样式)  /// </summary>  /// <param >DataTable</param>  /// <returns></returns>  public static void ExportExcel(DataTable dataTable, string filePath, string documentname)  {   if (dataTable != null && dataTable.Rows.Count > 0)   {    HSSFWorkbook excelBook = new HSSFWorkbook();    ISheet sheet1 = excelBook.CreateSheet("Export Data");    IRow row1 = sheet1.CreateRow(0);    //填充表头    for (int i = 0; i < dataTable.Columns.Count; i++)    {     row1.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);    }    //填充内容    for (int i = 0; i < dataTable.Rows.Count; i++)    {     IRow rowTemp = sheet1.CreateRow(i + 1);     for (int j = 0; j < dataTable.Columns.Count; j++)     {      rowTemp.CreateCell(j).SetCellValue(string.Format("{0}", dataTable.Rows[i][j]));     }    }    //保存    MemoryStream ms = new MemoryStream();    excelBook.Write(ms);    if (Directory.Exists(filePath) == false)    {     Directory.CreateDirectory(filePath);    }    string filedocmentname = "\\List.xls";
    using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))    {     byte[] data = ms.ToArray();     fs.Write(data, 0, data.Length);     fs.Flush();    }    ms.Close();    ms.Dispose();   }  }

纵向导出Excel效果图

横向导出Excel效果图

版权声明：本文为博主原创文章，转载请附上博文链接！

C# NPOI导出Excel横向纵向显示