NPOI操作excel

1、基本导出方法

private void ExportToExcel()
        {
            SaveFileDialog sdfExport = new SaveFileDialog();
            sdfExport.Filter = "Excel文件|*.xls";
            if (sdfExport.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            string filename = sdfExport.FileName;
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Student");

            IRow rowHeader = sheet.CreateRow(0);//表头行
            rowHeader.CreateCell(0, CellType.String).SetCellValue("姓名");
            rowHeader.CreateCell(1, CellType.String).SetCellValue("年龄");
            rowHeader.CreateCell(2, CellType.String).SetCellValue("生日");

            //把查询结果导出到Excel
            for (int i = 0; i < dataGridViewX1.Rows.Count; i++)
            {
                Student student = new Student();
                student.StuName = dataGridViewX1.Rows[i].Cells["StuName"].Value.ToString();
                student.StuAge = dataGridViewX1.Rows[i].Cells["StuAge"].Value.ToString();
                student.StuBirthday = dataGridViewX1.Rows[i].Cells["StuBirthday"].Value.ToString();
                IRow row = sheet.CreateRow(i + 1);
                row.CreateCell(0, CellType.String).SetCellValue(student.StuName);
                row.CreateCell(1, CellType.String).SetCellValue(student.StuAge);

                ICellStyle styledate = workbook.CreateCellStyle();
                IDataFormat format = workbook.CreateDataFormat();
                //格式具体有哪些请看单元格右键中的格式，有说明
                styledate.DataFormat = format.GetFormat("yyyy/m/d");

                ICell cellInDate = row.CreateCell(2, CellType.Numeric);
                cellInDate.CellStyle = styledate;
                cellInDate.SetCellValue(student.StuBirthday);
            }

            using (Stream stream = File.OpenWrite(filename))
            {
                workbook.Write(stream);
            }
        }

2、ExcelUtility

using System;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Windows.Forms;
using System.Drawing;
using CommonHelper;

namespace NPOIOprateExcel
{
    public class ExcelUtility
    {
        /// <summary>
        /// 将excel导入到datatable
        /// </summary>
        /// <param name="filePath">excel路径</param>
        /// <param name="isColumnName">第一行是否是列名</param>
        /// <returns>返回datatable</returns>
        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行
                                int cellCount = firstRow.LastCellNum;//列数

                                //构建datatable的列
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //填充行
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);                                        
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }
        public static string getdatatype(DataTable Table,string colname)
        {
            string datatype=null;
            for (int j = 0; j < Table.Columns.Count; j++)
            {
                if (Table.Columns[j].Caption == colname)
                {
                    datatype = Table.Columns[j].DataType.ToString();
                    break;
                }

            }
            return datatype;
        }

        public int[] Datagridtosql(DevComponents.DotNetBar.Controls.DataGridViewX datagrid, System.Windows.Forms.BindingSource ds)
        {

            int[] rows;

            rows = new int[2];
            rows[0] = 0;
            rows[1] = 0;

            int insert_rows = 0;
            int update_rows = 0;
            int error_rows = 0;
            string[] cdatatype;
            int rowCount = datagrid.Rows.Count;//行数
            int columnCount = datagrid.Columns.Count;//列数

            //设置列头
            int vcount = 0;
            string sql1 = "";
            string insert_sql1 = "";
            string update_sql1 = "";
            string select_sql1 = "";
            DataSet dt = (DataSet)ds.DataSource;
            DataTable Table = dt.Tables[ds.DataMember];
            cdatatype = new string[columnCount];
            for (int c = 0; c < columnCount; c++)
            {
                if (datagrid.Columns[c].Visible)
                {
                    sql1 += datagrid.Columns[c].DataPropertyName + ",";
                }
                cdatatype[c] = getdatatype(Table, datagrid.Columns[c].DataPropertyName);
            }
            sql1 = sql1.Substring(0, sql1.Length - 1);
            sql1 += ")";


            update_sql1 = "update " + ds.DataMember + " set  ";
            select_sql1 = "select count(*) from " + ds.DataMember + " where StuName=";

            for (int i = 0; i < rowCount; i++)
            {

                string select_sql = select_sql1 + "'" + datagrid.Rows[i].Cells[1].Value.ToString() + "'";
                int num = (int)AccessHelper.ExecuteScalar(select_sql, CommandType.Text, null);
                if (num > 0)
                {
                    update_sql1 = "update " + ds.DataMember + " set ";

                    for (int j = 0; j < columnCount; j++)
                    {
                        if (datagrid.Columns[j].Visible)
                        {
                            update_sql1 += "" + datagrid.Columns[j].DataPropertyName + "=";
                            string datatype = cdatatype[j];
                            if (datatype == "System.String")
                            {
                                update_sql1 += "'" + datagrid.Rows[i].Cells[j].Value.ToString() + " ',";
                            }
                            if (datatype == "System.Boolean")
                            {
                                update_sql1 += "" + datagrid.Rows[i].Cells[j].Value.ToString() + ",";
                            }
                            if ((datatype == "System.Int32") || (datatype == "System.Int16") || (datatype == "System.Double"))
                            {
                                update_sql1 += "" + datagrid.Rows[i].Cells[j].Value.ToString() + ",";
                            }

                            if (datatype == "System.DateTime")
                            {
                                if (!string.IsNullOrEmpty(datagrid.Rows[i].Cells[j].Value.ToString()))
                                {
                                    update_sql1 += "#" + datagrid.Rows[i].Cells[j].Value.ToString() + "#,";
                                }
                                else
                                {
                                    update_sql1 += "Null,";
                                }
                            }

                        }

                    }
                    update_sql1 = update_sql1.Substring(0, update_sql1.Length - 1);
                    update_sql1 += " where StuName=" + "'" + datagrid.Rows[i].Cells[1].Value.ToString() + "'"; ;
                    try
                    {
                        AccessHelper.ExecuteQuery(update_sql1, CommandType.Text, null);
                        update_rows++;
                    }
                    catch (Exception e)
                    {
                    }
                }
                else
                {
                    insert_sql1 = "insert into " + ds.DataMember + "(" + sql1;
                    insert_sql1 += "  values(";
                    for (int j = 0; j < columnCount; j++)
                    {
                        if (datagrid.Columns[j].Visible)
                        {
                            // MessageBox.Show(getdatatype(Table, datagrid.Columns[j].DataPropertyName) + datagrid.Columns[j].DataPropertyName);
                            string datatype = cdatatype[j];
                            if (datatype == "System.String")
                            {
                                insert_sql1 += "'" + datagrid.Rows[i].Cells[j].Value.ToString() + " ',";
                            }
                            if (datatype == "System.Boolean")
                            {
                                insert_sql1 += "" + datagrid.Rows[i].Cells[j].Value.ToString() + ",";
                            }
                            if ((datatype == "System.Int32") || (datatype == "System.Int16") || (datatype == "System.Double"))
                            {
                                insert_sql1 += "" + datagrid.Rows[i].Cells[j].Value.ToString() + ",";
                            }

                            if (datatype == "System.DateTime")
                            {
                                if (!string.IsNullOrEmpty(datagrid.Rows[i].Cells[j].Value.ToString()))
                                {
                                    insert_sql1 += "#" + datagrid.Rows[i].Cells[j].Value.ToString() + "#,";
                                }
                                else
                                {
                                    insert_sql1 += "Null,";
                                }
                            }

                        }

                    }
                    insert_sql1 = insert_sql1.Substring(0, insert_sql1.Length - 1);
                    insert_sql1 += ")";
                    try
                    {
                        AccessHelper.ExecuteQuery(insert_sql1, CommandType.Text, null);
                        insert_rows++;
                    }
                    catch (Exception e)
                    {
                    }

                }
            }
            return rows; ;
        }

        public static int ExcelToDatagrid(DevComponents.DotNetBar.Controls.DataGridViewX datagrid, string filePath, bool isColumnName=true)
        {
            FileStream fs = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            char[] delimiterChars = { '.', '\' };
            string[] Mystr = filePath.Split(delimiterChars);
            string sheetName = Mystr[Mystr.Length - 2]; ;// 没有扩展名的文件名 “Default”
            int[] colarray;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheet(sheetName);//读取第一个sheet，当然也可以循环读取每个sheet
                        //dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行
                                int cellCount = firstRow.LastCellNum;//列数

                                //构建datatable的列
                                colarray = new int[cellCount];
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取
                                    
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            int columnCount = datagrid.Columns.Count;//列数
                                            for (int j = 0; j < columnCount; ++j)
                                            {
                                                if (cell.StringCellValue == datagrid.Columns[j].HeaderText)
                                                {
                                                    colarray[j] = i;
                                                    break;
                                                }

                                            }

                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        colarray[i] = i;
                                    }
                                }

                                //填充行
                                
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;
                                    datagrid.Rows.Add();

                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(colarray[j]);
                                        if (cell == null)
                                        {
                                            datagrid.Rows[i-1].Cells[j].Value = "";
                                        }
                                        else
                                        {
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    datagrid.Rows[i-1].Cells[j].Value = "";
                                                     break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        datagrid.Rows[i-1].Cells[j].Value = cell.DateCellValue;
                                                    else
                                                        datagrid.Rows[i-1].Cells[j].Value = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    datagrid.Rows[i-1].Cells[j].Value = cell.StringCellValue;
                                                    break;
                                                case CellType.Boolean:
                                                    datagrid.Rows[i - 1].Cells[j].Value = cell.BooleanCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                return 1;
            }
            catch (Exception e)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return -1;
            }
        }


        public static bool DataTableToExcel(DataTable dt,string fileName)
        {
            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            char[] delimiterChars = { '.', '\' };
            string[] Mystr = fileName.Split(delimiterChars);
            string sheetName = Mystr[Mystr.Length - 2];
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    workbook = new HSSFWorkbook();
                    sheet = workbook.CreateSheet(sheetName);//创建一个名称为Sheet0的表
                    int rowCount = dt.Rows.Count;//行数
                    int columnCount = dt.Columns.Count;//列数

                    //设置列头
                    row = sheet.CreateRow(0);//excel第一行设为列头
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                    }                    

                    //设置每行每列的单元格,
                    for (int i = 0; i <rowCount; i++)
                    {
                        row = sheet.CreateRow(i+1);
                        for (int j = 0; j < columnCount; j++)
                        {                            
                            cell = row.CreateCell(j);//excel第二行开始写入数据
                            cell.SetCellValue(dt.Rows[i][j].ToString());                            
                        }
                    }
                    using (fs = File.OpenWrite(fileName)) 
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return false;
            }
        }

        public static bool DataGridViewXToExcel(DevComponents.DotNetBar.Controls.DataGridViewX dt,string filename)
        {
            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            char[] delimiterChars = { '.', '\' };
            string[] Mystr = filename.Split(delimiterChars);
            string sheetName = Mystr[Mystr.Length - 2];;// 没有扩展名的文件名 “Default”
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    workbook = new HSSFWorkbook();
                    sheet = workbook.CreateSheet(sheetName);//创建一个名称为Sheet0的表
                    int rowCount = dt.Rows.Count;//行数
                    int columnCount = dt.Columns.Count;//列数

                    //设置列头
                    row = sheet.CreateRow(0);//excel第一行设为列头
                    for (int j = 0; j < columnCount; j++)
                    {
                        if (dt.Columns[j].Visible)
                        {
                            cell = row.CreateCell(j);
                            cell.SetCellValue(dt.Columns[j].HeaderText);
                        }
                    }

                    //设置每行每列的单元格,
                    for (int i = 0; i < rowCount; i++)
                    {   
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            if (dt.Columns[j].Visible)
                            {
                                cell = row.CreateCell(j);//excel第二行开始写入数据
                                cell.SetCellValue(dt.Rows[i].Cells[j].Value.ToString());
                            }
                        }
                    }
                    using (fs = File.OpenWrite(filename))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return false;
            }
        }
    }
}