Npoi导出Excel、读取Excel

Npoi简单说明

1、整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。

2、NPOI是POI的C#版本，NPOI的行和列的index都是从0开始

3、POI读取Excel有两种格式一个是HSSF，另一个是XSSF。 HSSF和XSSF的区别如下：
HSSF is the POI Project’s pure Java implementation of the Excel ‘97(-2007) file format.
XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
即：HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。

DataTable导出为Excel

后台代码：

public void DataTableToExcel(DataTable dt, string templatePath,string sheetName, string savePath)
{
    try
    {
        FileStream fileStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileStream);
        HSSFSheet sheet1 = hssfworkbook.GetSheet(sheetName) as HSSFSheet;
        //Excel中有50行数据时sheet.LastRowNum返回49
        int count = sheet1.LastRowNum + 1;
        if (dt.Rows.Count > count - 1)
        {
            for (int ii = 0; ii < dt.Rows.Count - count + 1; ii++)
            {
                sheet1.CopyRow(6, count + ii);
            }
        }

        var colCount = dt.Columns.Count;
        int rowCount = 0;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            //表头占n行,rowCount=i+n
            //当n=2时，dt中的第2行数据，写到Excel文件中的第4行
            rowCount = i + 1;
            for (int j = 0; j < colCount; j++)
            {
                string contnts = (dt.Rows[i][j] == DBNull.Value) ? "" : dt.Rows[i][j].ToString();
                //判断Excel单元格是否为null
                sheet1.GetRow(rowCount).GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellValue(contnts);
            }
        }

        sheet1.ForceFormulaRecalculation = true;
        FileStream file = new FileStream(savePath, FileMode.Create);
        hssfworkbook.Write(file);
        file.Close();
    }
    catch (Exception ex)
    {
        string error = ex.Message;
        throw;
    }
}

前台调用：

protected void btnExport_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Name", Type.GetType("System.String"));
    dt.Columns.Add("Sex", Type.GetType("System.String"));
    dt.Columns.Add("Age", Type.GetType("System.Int32"));
    dt.Columns.Add("Address", Type.GetType("System.String"));
    dt.Columns.Add("PhoneNumber", Type.GetType("System.String"));
    for (int i = 0; i < 5; i++)
    {
        DataRow row = dt.NewRow();
        row[0] = "姓名" + i;
        row[1] = i % 2 == 0 ? "女" : "男";
        row[2] = "18";
        row[3] = "葡萄牙罗卡角";
        row[4] = "18233399999";
        dt.Rows.Add(row);
    }
    string savePath = Server.MapPath("SavePath/用户信息") + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
    string templatePath=Server.MapPath("Temp/用户信息.xls");

    new NpoiHelper().DataTableToExcel(dt, templatePath, "Sheet1", savePath);
}

Excel导入数据转换为DataTable

后台代码：

public DataTable ExcelToDataTable(DataTable dt, string filePath, string sheetName, int titleCount)
{
    try
    {
        ISheet sheet = null;
        IWorkbook workbook = null;
        DataTable dtResult = dt;
        FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
        if (filePath.IndexOf(".xlsx") > 0)
            workbook = new XSSFWorkbook(fs);
        else if (filePath.IndexOf(".xls") > 0)
            workbook = new HSSFWorkbook(fs);
        if (sheetName != null)
        {
            sheet = workbook.GetSheet(sheetName);
        }
        else
        {
            sheet = workbook.GetSheetAt(0);
        }
        if (sheet != null)
        {
            IRow row = sheet.GetRow(0);
            int colCount = row.LastCellNum;
            //Excel中有50行数据时sheet.LastRowNum返回49
            int rowCount = sheet.LastRowNum + 1;
            //Excel中有50行数据时sheet.LastRowNum返回50
            int physicalRowCount = sheet.PhysicalNumberOfRows;
            int startRow = sheet.FirstRowNum + titleCount;
            for (int i = startRow; i < rowCount; i++)
            {
                row = sheet.GetRow(i);
                if (!RowIsValid(row)) continue;

                DataRow dataRow = dt.NewRow();
                for (int j = 0; j < colCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }
                dtResult.Rows.Add(dataRow);
            }
        }
        return dtResult;
    }
    catch (Exception ex)
    {
        string error = ex.Message;
        throw;
    }
}

/// <summary>
/// 判断Excel中的行是否为空行
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public bool RowIsValid(IRow row)
{
    bool flag = false;
    for (int i = 0; i < row.LastCellNum; i++)
    {
        if (row.GetCell(i) != null)
        {
            if (!string.IsNullOrEmpty(row.GetCell(i).ToString()))
            {
                flag = true;
                break;
            }
        }
    }
    return flag ? true : false;
}

前台调用：

protected void btnImport_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Name", Type.GetType("System.String"));
    dt.Columns.Add("Sex", Type.GetType("System.String"));
    dt.Columns.Add("Age", Type.GetType("System.String"));
    dt.Columns.Add("Address", Type.GetType("System.String"));
    dt.Columns.Add("PhoneNumber", Type.GetType("System.String"));
    /*先把用户选择的文件保存在服务器端*/
    string path = Path.Combine(Server.MapPath("UploadPath"), FileUpload1.FileName);            
    FileUpload1.PostedFile.SaveAs(path);
    /*再从服务器端读取Excel文件，转换为DataTable*/
    DataTable result = new NpoiHelper().ExcelToDataTable(dt, path, "Sheet1", 1);
    int rowCount = result.Rows.Count;
}