NPOI基本操作XLS

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
/*
 如果想要设置单元格为只读或可写，可以参考这里，实际上只需要如下两个步骤：
cell.CellStyle.IsLocked = false;//设置该单元格为非锁定
sheet.ProtectSheet("password");//保护表单，password为解锁密码
cell.CellStyle.IsLocked 默认就是true，因此第2步一定要执行，才能实现锁定单元格，
对于不想锁定的单元格，就一定要设置cell.CellStyle.IsLocked = false
 */

namespace NPOI操作
{
    public class ProcessXls
    {
        /// <summary>
        /// 输出
        /// </summary>
        /// <param name="filePath"></param>
        public static void WriteFromExcelFile(string filePath)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();//创建一个表
            ISheet sheet1 = workbook.CreateSheet("Sheet1");//创建一个sheet
            IRow row1 = sheet1.CreateRow(0);//创建一行
            IRow row2 = sheet1.CreateRow(1);//再创建二行
            //sheet1.CreateRow(0).CreateCell(0).SetCellValue("B");//直接在第一行第一列创建并设值
            row1.CreateCell(0).SetCellValue("姓名");//创建一列并在第一列添加内容
            row1.CreateCell(1).SetCellValue("参加工作时间");
            row1.CreateCell(2).SetCellValue("当前日期");
            row1.CreateCell(3).SetCellValue("工作年限");
            ICell cel1 = row2.CreateCell(0);//在第二行创建一列
            ICell cel2 = row2.CreateCell(1);
            ICell cel3 = row2.CreateCell(2);
            ICell cel4 = row2.CreateCell(3);
            cel1.SetCellValue("Tom");//在第二行的第一列添加内容
            cel2.SetCellValue(new DateTime(2004, 7, 1));//添加日期
            cel3.CellFormula = "TODAY()";//添加当前日期
            cel4.CellFormula = "CONCATENATE(DATEDIF(B2,TODAY(),\"y\"),\"年\",DATEDIF(B2,TODAY(),\"ym\"),\"个月\")";
            //在poi中日期是以double类型表示的，所以要格式化
            //设置日期格式
            ICellStyle cellStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
            cel2.CellStyle = cellStyle;
            cel3.CellStyle = cellStyle;
            using (FileStream file = new FileStream(@"c:\tes.xls", FileMode.Create))
            {
                workbook.Write(file);//写入输出流中
            }
        }

        /// <summary>
        /// 读取XLS指定列数据
        /// </summary>
        /// <param name="filePath">XLS路径</param>
        /// <param name="cellNum">列位置，从0开始</param>
        /// <returns></returns>
        public static List<string> ReadFromExcelFile(string filePath,int cellNum)
        {
            List<string> list = new List<string>();
            IWorkbook workbook = null;
            string extension = Path.GetExtension(filePath);
            using (FileStream fs = File.OpenRead(filePath))
            {
                if (extension.Equals(".xls"))
                {
                    workbook = new HSSFWorkbook(fs);////把xls文件中的数据写入workbook中
                }
                if (extension.Equals(".xlsx"))
                {
                    workbook = new XSSFWorkbook(fs);//把xlsx文件中的数据写入workbook中
                }

                ISheet sheet = workbook.GetSheetAt(0);//读取当前表数据
                //LastRowNum 是当前表的总行数（注意）
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);//读取当前行数据
                    if (row != null && row.GetCell(cellNum) != null)
                    {
                        string value = Convert.ToString(GetCellValue(row.GetCell(cellNum)));
                        list.Add(value);
                    }
                }
            }
            return list;
        }

        #region 读取所有行
        /*public static List<string> ReadFromExcelFile(string filePath, int cellNum)
        {
            List<string> list = new List<string>();
            IWorkbook workbook = null;
            string extension = Path.GetExtension(filePath);
            using (FileStream fs = File.OpenRead(filePath))
            {
                if (extension.Equals(".xls"))
                {
                    workbook = new HSSFWorkbook(fs);////把xls文件中的数据写入workbook中
                }
                else
                {
                    workbook = new XSSFWorkbook(fs);//把xlsx文件中的数据写入workbook中
                }

                ISheet sheet = workbook.GetSheetAt(0);//读取当前表数据
                var row = sheet.GetRow(0).GetCell(0);//读取当前行数据
                //int count = row.LastRowNum;
                //ICell row1 = row.LastRowNum;
                //LastRowNum 是当前表的总行数-1（注意）
                //for (int i = 0; i < row.LastRowNum; i++)
                //{
                //    row1 = row.GetCell(i);
                //    if (row != null)
                //    {
                //        //LastCellNum 是当前行的总列数
                //        for (int j = 0; j < row.LastCellNum; j++)
                //        {
                //            //读取该行的第j列数据
                //            string value = row.GetCell(j) == null ? "" : Convert.ToString(GetCellValue(row.GetCell(j)));
                //            list.Add(value.ToString());
                //        }
                //    }
                //}
            }
            return list;
        }*/
        
        #endregion

        /// <summary>
        /// 获取cell的数据，并设置为对应的数据类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static object GetCellValue(ICell cell)
        {
            object value = null;
            try
            {
                if (cell.CellType != CellType.Blank)
                {
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            // Date Type的数据CellType是Numeric
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                value = cell.DateCellValue;
                            }
                            else
                            {
                                // Numeric type
                                value = cell.NumericCellValue;
                            }
                            break;
                        case CellType.Boolean:
                            // Boolean type
                            value = cell.BooleanCellValue;
                            break;
                        default:
                            // String type
                            value = cell.StringCellValue;
                            break;
                    }
                }
            }
            catch (Exception)
            {
                value = "";
            }

            return value;
        }

        /// <summary>
        /// 根据数据类型设置不同类型的cell
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="obj"></param>
        public static void SetCellValue(ICell cell, object obj)
        {
            if (obj.GetType() == typeof(int))
            {
                cell.SetCellValue((int)obj);
            }
            else if (obj.GetType() == typeof(double))
            {
                cell.SetCellValue((double)obj);
            }
            else if (obj.GetType() == typeof(IRichTextString))
            {
                cell.SetCellValue((IRichTextString)obj);
            }
            else if (obj.GetType() == typeof(string))
            {
                cell.SetCellValue(obj.ToString());
            }
            else if (obj.GetType() == typeof(DateTime))
            {
                cell.SetCellValue((DateTime)obj);
            }
            else if (obj.GetType() == typeof(bool))
            {
                cell.SetCellValue((bool)obj);
            }
            else
            {
                cell.SetCellValue(obj.ToString());
            }
        }

        /// <summary>
        /// 写入
        /// </summary>
        /// <param name="filePath"></param>
        public static void WriteToExcel(string filePath)
        {
            //创建工作薄  
            IWorkbook wb;
            string extension = System.IO.Path.GetExtension(filePath);
            //根据指定的文件格式创建对应的类
            //URL：http://www.bianceng.cn/Programming/csharp/201410/45750.htm
            if (extension.Equals(".xls"))
            {
                wb = new HSSFWorkbook();
            }
            else
            {
                wb = new XSSFWorkbook();
            }

            ICellStyle style1 = wb.CreateCellStyle();//样式
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
            //设置边框
            style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.WrapText = true;//自动换行

            ICellStyle style2 = wb.CreateCellStyle();//样式
            IFont font1 = wb.CreateFont();//字体
            font1.FontName = "楷体";
            font1.Color = HSSFColor.Red.Index;//字体颜色
            font1.Boldweight = (short)FontBoldWeight.Normal;//字体加粗样式
            style2.SetFont(font1);//样式里的字体设置具体的字体样式
            //设置背景色
            style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            style2.FillPattern = FillPattern.SolidForeground;
            style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式

            //创建一个表单
            ISheet sheet = wb.CreateSheet("Sheet0");
            //设置列宽
            int[] columnWidth = { 10, 10, 10, 20 };

            //测试数据
            int rowCount = 3, columnCount = 4;
            object[,] data = 
            {
                {"列0", "列1", "列2", "列3"},
                {"", 400, 5.2, 6.01},
                {"", DateTime.Today, true, "2014-07-02"}
            };

            for (int i = 0; i < columnWidth.Length; i++)
            {
                //设置列宽度，256*字符数，因为单位是1/256个字符
                sheet.SetColumnWidth(i, 256 * columnWidth[i]);
            }

            IRow row;
            ICell cell;
            for (int i = 0; i < rowCount; i++)
            {
                row = sheet.CreateRow(i);//创建第i行
                for (int j = 0; j < columnCount; j++)
                {
                    cell = row.CreateCell(j);
                    cell.CellStyle = j % 2 == 0 ? style1 : style2;
                    //根据数据类型设置不同类型的cell
                    //SetCellValue(cell, data[i, j]);
                }
            }

            //合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
            //CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
            CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
            sheet.AddMergedRegion(region);

            try
            {
                FileStream fs = File.OpenWrite(filePath);
                wb.Write(fs);   //向打开的这个xls文件中写入表并保存。  
                fs.Close();
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
            }
        }
    }
}

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using _01ado复习;
using System.Data.SqlClient;

namespace _02Excel操作
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            #region 读取Excel
            using (FileStream fsRead = File.OpenRead("ReadExcel.xls"))
            {
                //读取磁盘上的excel文件到一个"工作薄",Workbook
                using (Workbook wk = new HSSFWorkbook(fsRead))
                {
                    //遍历该工作薄中的所有“工作表”
                    //wk.NumberOfSheets 获取工作表的总个数。
                    for (int i = 0; i < wk.NumberOfSheets; i++)
                    {
                        //获取每个"工作表"
                        using (Sheet sheet = wk.GetSheetAt(i))
                        {
                            Console.WriteLine("================={0}========================", sheet.SheetName);

                            //遍历每一行
                            //sheet.GetRow(
                            //sheet.LastRowNum;获取最后一行的索引
                            for (int j = 0; j <= sheet.LastRowNum; j++)
                            {
                                //获取每一行
                                Row row = sheet.GetRow(j);

                                //获取当前行中的所有的单元格
                                // row.GetCell(
                                //row.LastCellNum
                                //循环遍历当前行中的每个单元格。
                                for (int k = 0; k < row.LastCellNum; k++)
                                {
                                    Cell cell = row.GetCell(k);
                                    Console.Write(cell.ToString());
                                }
                                Console.WriteLine();

                            }

                        }
                    }
                }
            }



            #endregion
        }

        private void button2_Click(object sender, EventArgs e)
        {
            #region 写入Excel

            //1.创建Workbook对象
            using (Workbook wk = new HSSFWorkbook())
            {
                //创建工作表
                using (Sheet sheet = wk.CreateSheet("My Sheet1"))
                {
                    //向工作表中创建行
                    for (int i = 0; i < 5; i++)
                    {
                        Row row = sheet.CreateRow(i);
                        //向当前行中创建单元格
                        for (int j = 0; j < 5; j++)
                        {
                            Cell cell = row.CreateCell(j);
                            cell.SetCellValue(j);
                        }
                    }
                    using (FileStream fsWrite = File.OpenWrite("my.xls"))
                    {
                        wk.Write(fsWrite);
                    }
                }

            }
            MessageBox.Show("ok");

            #endregion
        }

        private void button3_Click(object sender, EventArgs e)
        {
            bool b = false;
            //读取数据库中的数据
            using (SqlDataReader reader = SqlHelper.ExecuteReader("select * from TblUsers"))
            {
                if (reader.HasRows)
                {
                    b = true;
                    //创建工作薄
                    using (Workbook wk = new HSSFWorkbook())
                    {
                        //创建工作表
                        using (Sheet sheet = wk.CreateSheet("TblUsers"))
                        {
                            #region 创建列信息

                            Row rowHeader = sheet.CreateRow(0);
                            for (int c = 0; c < reader.FieldCount; c++)
                            {
                                rowHeader.CreateCell(c).SetCellValue(reader.GetName(c));
                            }

                            #endregion




                            int rowIndex = 1;
                            while (reader.Read())
                            {
                                //创建行，创建单元格
                                Row row = sheet.CreateRow(rowIndex);

                                //循环读取表中的每一列值
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    #region 导出数据的时候都变成字符串类型了
                                    ////创建一个单元格
                                    //Cell cell = row.CreateCell(i);
                                    //cell.SetCellValue(reader[i].ToString());


                                    #endregion

                                    #region 按照对应的类型导出数据

                                    //1.循环所有的列
                                    //string s = reader.GetDataTypeName(i);
                                    //Type type = reader.GetFieldType(i);
                                    //switch (type.Name)
                                    //{

                                    //    default:
                                    //        break;
                                    //}
                                    Cell cell = row.CreateCell(i);
                                    string s = reader.GetDataTypeName(i);
                                    switch (s)
                                    {
                                        case "int":
                                            cell.SetCellValue(reader.GetInt32(i));
                                            break;
                                        case "varchar":
                                        case "char":
                                        case "nvarchar":
                                        case "nchar":
                                            cell.SetCellValue(reader.GetString(i));
                                            break;
                                    }

                                    #endregion

                                }

                                rowIndex++;
                            }
                            //写入Excel
                            using (FileStream fsWrite = File.OpenWrite("tblusers.xls"))
                            {
                                wk.Write(fsWrite);
                            }
                        }
                    }
                    MessageBox.Show("导出完成！");
                }

                if (!b)
                {
                    MessageBox.Show("没数据！");
                }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            #region Excel数据导入到数据库中

            //1.读取Excel文件
            using (FileStream fsRead = File.OpenRead("tblusers.xls"))
            {
                //创建工作薄
                using (Workbook wk = new HSSFWorkbook(fsRead))
                {
                    //1.获取第一个工作表
                    using (Sheet sheet = wk.GetSheetAt(0))
                    {
                        string sql = "insert into TblUsers values(@uid,@pwd,@realName)";


                        //循环读取工作表中的每个行
                        for (int i = 1; i <= sheet.LastRowNum; i++)
                        {
                            SqlParameter[] pms = new SqlParameter[] { 
                            new SqlParameter("@uid",SqlDbType.VarChar),
                            new SqlParameter("@pwd",SqlDbType.VarChar),
                            new SqlParameter("@realName",SqlDbType.VarChar)
                            };
                            Row row = sheet.GetRow(i);
                            //获取每行中的后三个单元格的数据
                            for (int c = 1; c < row.LastCellNum; c++)
                            {
                                pms[c - 1].Value = row.GetCell(c).ToString();
                                //Console.WriteLine(row.GetCell(c).ToString());
                            }

                            //执行Sql
                            SqlHelper.ExecuteNonQuery(sql, pms);
                        }
                    }
                }
                MessageBox.Show("导入完毕！");
            }
            //2.将读取到的数据导入到表中

            #endregion
        }
    }
}