使用NPOI实现数据库中获得的数据表导出本地Excel表。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DevComponents.DotNetBar.Controls;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Windows.Forms;

namespace databaseBackups
{
    class clsExExcel
    {

        /// <summary>
        /// 本功能实现将程序中datagridview中的数据表导出到本地的excel
        /// </summary>
        /// <param name="dgv">datagridview的名称</param>
        /// <param name="localFilePath">当地要保存的路径</param>
        /// <param name="tableName">保存的表名</param>
        public void Ex_Excel(DataGridView dgv, string localFilePath, string tableName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            // 表单名

            NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Sheet1");

            //DataGridView行数
            int rowCount = dgv.Rows.Count;
            //DataGridView列数
            int colCount = dgv.Columns.Count;

            //数据表格样式
            NPOI.SS.UserModel.CellStyle dataStyle = workbook.CreateCellStyle();
            dataStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;
            dataStyle.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
            dataStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;
            dataStyle.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
            // 标题表格样式
            NPOI.SS.UserModel.CellStyle titleStyle = workbook.CreateCellStyle();
            titleStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;
            titleStyle.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
            titleStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;
            titleStyle.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
            NPOI.SS.UserModel.Font font = workbook.CreateFont();
            font.Boldweight = 10;
            font.FontHeightInPoints = 16;
            titleStyle.SetFont(font);
            titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            //数据表标题
            NPOI.SS.Util.CellRangeAddress rangTitle = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, colCount - 1);
            sheet.AddMergedRegion(rangTitle);
            NPOI.SS.UserModel.Cell titleCel = sheet.CreateRow(0).CreateCell(0);
            titleCel.SetCellValue(tableName);
            titleCel.CellStyle = titleStyle;


            NPOI.SS.UserModel.Row colNameRow = sheet.CreateRow(1);
            for (int i = 0; i < colCount; i++)
            {
                if (dgv.Columns[i].Visible)
                {
                    //把表头存入 
                    NPOI.SS.UserModel.Cell cel = colNameRow.CreateCell(i);
                    cel.SetCellValue(dgv.Columns[i].HeaderText.ToString());
                    cel.CellStyle = dataStyle;
                }
            }
            //数据表数据
            for (int j = 0; j < rowCount; j++)
            {
                NPOI.SS.UserModel.Row dataRow = sheet.CreateRow(j + 2);
                for (int k = 0; k < colCount; k++)
                {
                    if (dgv.Columns[k].Visible && dgv.Rows[j].Cells[k].Value != null)
                    {
                        //把数据保存到二维数组  
                        NPOI.SS.UserModel.Cell cel = dataRow.CreateCell(k);
                        //cel.SetCellValue(dgv.Rows[j].Cells[k].Value.ToString());
                        cel.SetCellValue(dgv.Rows[j].Cells[k].FormattedValue.ToString());
                        cel.CellStyle = dataStyle;
                    }
                }
            }
            workbook.Write(ms);
            FileStream file = new FileStream(@localFilePath, FileMode.Create);
            workbook.Write(file);
            file.Close();
            workbook = null;
            ms.Close();
            ms.Dispose();
        }
    }
}

附：npoi是外部程序集。

一、npoi读取excle表操作：

读取

 using (FileStream stream = new FileStream(@"c:\客户资料.xls", FileMode.Open, FileAccess.Read))

            {

                HSSFWorkbook workbook = new HSSFWorkbook(stream);

                MessageBox.Show(workbook.GetSheetName(0));

            }

•读取字符串类型数据MessageBox.Show(sheet.GetRow(3).GetCell(4).StringCellValue);

读取数字类型数据NumericCellValue。

•判断单元格数据类型：读取GetCell(4).CellType，与HSSFCell类中定义的常量比较即可。

•判断使用区域：结束行号：LastRowNum

二、写入Excel

 HSSFWorkbook workbook = new HSSFWorkbook();

            HSSFSheet sheet = workbook.CreateSheet();

            HSSFRow row = sheet.CreateRow(0);

            row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue("Hello");

            row.CreateCell(1, HSSFCell.CELL_TYPE_NUMERIC).SetCellValue(3.14);

            using (FileStream stream = new FileStream(@"c:\1.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite))

            {

                workbook.Write(stream);

            }