NPOI导入导出数据

安装NOPI包

 //导入数据 

 public static DataTable Import(string filePath,string sheetName = "")         {             var excelType = Path.GetExtension(filePath).ToLower();             DataTable dt;             using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))             {                 dt = ImportExcel(stream, excelType, sheetName);             }             return dt;       
  }
private static DataTable ImportExcel(Stream stream, string type, string sheetName)
{
DataTable dt = new DataTable();
IWorkbook workbook ;
try
{
if (type != ".xls")
{
workbook = new XSSFWorkbook(stream);
}
else
{
workbook = new HSSFWorkbook(stream);
}
ISheet sheet = null;
//获取工作表 默认取第一张
if (string.IsNullOrWhiteSpace(sheetName))
sheet = workbook.GetSheetAt(0);
else
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
return null;
IEnumerator rows = sheet.GetRowEnumerator();
#region 获取表头
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell != null)
{
dt.Columns.Add(cell.ToString());
}
else
{
dt.Columns.Add("");
}
}
endregion
#region 获取内容
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { //判断单元格是否为日期格式 if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) { if (row.GetCell(j).DateCellValue.Year >= 1970)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
#endregion
}
catch (Exception ex)
{
dt = null;
}
finally
{
if (stream != null)
{
stream.Close();
stream.Dispose();
}
}
return dt;
}


//导出数据
protected void ExportExcel(DataTable dt)
        {
            HttpContext curContext = System.Web.HttpContext.Current;
            //设置编码及附件格式 
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string fullName = HttpUtility.UrlEncode("FileName.xlsx", Encoding.UTF8);
            curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fullName, Encoding.UTF8));
            //attachment后面是分号
            byte[] data = TableToExcel(dt, fullName).GetBuffer();
            curContext.Response.BinaryWrite(TableToExcel(dt, fullName).GetBuffer());
            curContext.Response.End();
        }
        public MemoryStream TableToExcel(DataTable dt, string file)
        {
            //创建workbook 
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx")
                workbook = new XSSFWorkbook();
            else if (fileExt == ".xls")
                workbook = new HSSFWorkbook();
            else workbook = null;
            //创建sheet 
            ISheet sheet = workbook.CreateSheet("Sheet1");
            //表头 
            IRow headrow = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell headcell = headrow.CreateCell(i);
                headcell.SetCellValue(dt.Columns[i].ColumnName);
            }
            //表内数据 
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转化为字节数组 
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            //ms.Position = 0;
            return ms;
        }

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注