.NET使用NPOI2.0导入导出Excel

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

NPOI开源地址：http://npoi.codeplex.com/

NPOI教程: http://tonyqus.sinaapp.com/

具体的不在这里写了，感兴趣的可以去官网。

先来说导出的例子

private void ExportExcel()

        {

            DataTable table_data = MAYIXUE.BLL.NPOIUtility.GetValue();

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ISheet sheet1 = hssfworkbook.CreateSheet("mayixue的NPOI测试1");

            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();

            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

            ICellStyle stringStyle = hssfworkbook.CreateCellStyle();

            stringStyle.VerticalAlignment = VerticalAlignment.CENTER;

            //取得列宽

            int columnCount = table_data.Columns.Count;

            int[] arrColWidth = new int[columnCount];

            int width = ;

            foreach (DataColumn column in table_data.Columns)

            {

                arrColWidth[column.Ordinal] = width;

            }

            int rowIndex = ;

            string temp_col1 = "";

            int col1_s = ;

            foreach (DataRow row in table_type.Rows)

            {

                #region 新建表，填充列头，样式

                if (rowIndex ==  || rowIndex == )

                {

                    if (rowIndex != )

                    {

                        sheet1 = hssfworkbook.CreateSheet();

                    }

                    #region 列头及样式

                    IRow headerRow = sheet1.CreateRow();

                    ICellStyle headStyle = hssfworkbook.CreateCellStyle();

                    headStyle.Alignment = HorizontalAlignment.CENTER;

                    headStyle.VerticalAlignment = VerticalAlignment.CENTER;

                    IFont font = hssfworkbook.CreateFont();

                    font.FontHeightInPoints = ;

                    font.Boldweight = ;

                    headStyle.SetFont(font);

                    foreach (DataColumn column in table_type.Columns)

                    {

                        headerRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToInt32(column.ColumnName));

                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                        sheet1.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + ) * );//设置列宽

                    }

                    #endregion

                    rowIndex = ;

                }

                #endregion

                #region 填充内容

                int j = ;

                IRow dataRow = sheet1.CreateRow(rowIndex);

                foreach (DataColumn column in table_data.Columns)

                {

                    ICell newCell = dataRow.CreateCell(column.Ordinal);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())

                    {

                        case "System.String"://字符串类型

                            newCell.SetCellValue(drValue);

                            newCell.CellStyle = stringStyle;

                            break;

                        case "System.Double":

                            if (drValue != "")

                            {

                                double doubV = ;

                                double.TryParse(drValue, out doubV);

                                newCell.SetCellValue(doubV);

                            }

                            else

                            {

                                newCell.SetCellValue("");

                            }

                            newCell.CellStyle = cellStyle;

                            break;

                    }

                    #region 单元格合并（这里只合并第一列）

                        if (j ==  && temp_col1 != drValue)

                        {

                            if (temp_col1 != "")

                            {

                                sheet1.AddMergedRegion(new CellRangeAddress(col1_s, rowIndex - , , ));

                            }

                            temp_c1 = drValue;

                            col1_s = rowIndex;

                        }

                    #endregion

                    j++;

                }

                #endregion

                rowIndex++;

            }

            //冻结窗口  锁定表头和第一列

            sheet1.CreateFreezePane(, , , );

            //输出

            context.Response.ContentType = "application/vnd.ms-excel";

            context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "mayixue的NPOI测试.xls"));

            context.Response.Clear();

            MemoryStream file = new MemoryStream();

            hssfworkbook.Write(file);

            file.WriteTo(context.Response.OutputStream);

            context.Response.End();

        }

再来看下导入Excel到DataTable

/// <summary>

        /// 读取Excel文件到DataSet中

        /// </summary>

        /// <param name="filePath">文件路径</param>

        /// <returns></returns>

        private DataTable ExcelToDataTable(string filePath)

        {

            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))

            {

                hssfworkbook = new HSSFWorkbook(file);

            }

            ISheet sheet = hssfworkbook.GetSheetAt();

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow();

            int cellCount = headerRow.LastCellNum;

            for (int j = ; j < cellCount; j++)

            {

                ICell cell = headerRow.GetCell(j);

                dt.Columns.Add(cell.ToString());

            }

            for (int i = (sheet.FirstRowNum + ); 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)

                        dataRow[j] = row.GetCell(j).ToString();

                }

                dt.Rows.Add(dataRow);

            }

            return dt;

        }