首页 > 代码库 > C# 将dataset数据导出到excel中

C# 将dataset数据导出到excel中

//添加引用 NPOI.dll

//添加 using NPOI.HSSF.UserModel;

/// <summary>
/// 导出数据到Excel
/// </summary>
/// <param name="returnMsg"></param>
/// <returns></returns>
public bool ExportOrderToExcel(ref string returnMsg)
{
  try
    {
      DataSet dt = "SQL语句返回dataset数据集";
      int rowIndexS = 0; //表格总行
      if(dt!=null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
        {
          HSSFWorkbook workbook = new HSSFWorkbook();
          HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet("工作簿名称");
          HSSFRow headerRow1 = (HSSFRow)sheet1.CreateRow(0);
          HeadControl(headerRow1); //行名称
          foreach (DataRow dr in dt.Tables[0].Rows)
          {
            rowIndexS++;
            HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(rowIndexS);
            OrderFill(dataRow, dr); //填充数据
          }
          //保存
          string path = "/FileRoot/temp/";//文件保存路径
          string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; //文件名已时间命名
          path += fileName;
          System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(path), System.IO.FileMode.Create);
          workbook.Write(file);
          //关闭文件,释放对象
          file.Close();
          workbook = null;
          returnMsg =path;//返回文件路径
          return true;
        }
        else
        {
          returnMsg = "没有查到要导出的数据!";
          return false;
        }
      }
    catch (Exception)
    {
      returnMsg = "导出数据失败!";
      return false;
    }
  }

/// <summary>
/// 填充报表数据
/// </summary>
/// <param name="dataRow"></param>
/// <param name="dr"></param>
public void OrderFill(HSSFRow dataRow, DataRow dr)
{
  dataRow.CreateCell(0).SetCellValue(dr["字段名"].ToString()); 
  dataRow.CreateCell(1).SetCellValue(dr["字段名"].ToString()); 
  dataRow.CreateCell(2).SetCellValue(dr["字段名"].ToString()); 
  dataRow.CreateCell(3).SetCellValue(dr["字段名"].ToString()); 
  dataRow.CreateCell(4).SetCellValue(dr["字段名"].ToString()); 
  dataRow.CreateCell(5).SetCellValue(dr["字段名"].ToString()); 
}
/// <summary>
/// 导出已支付订单的表头
/// </summary>
/// <param name="headerRow"></param>
public void HeadControl(HSSFRow headerRow)
{
  headerRow.CreateCell(0).SetCellValue("列名");
  headerRow.CreateCell(1).SetCellValue("列名");
  headerRow.CreateCell(2).SetCellValue("列名");
  headerRow.CreateCell(3).SetCellValue("列名");
  headerRow.CreateCell(4).SetCellValue("列名");
  headerRow.CreateCell(5).SetCellValue("列名");
}

 

附件:NOPI.DLL下载链接 http://files.cnblogs.com/a-mumu/NPOI.zip

C# 将dataset数据导出到excel中