使用NPOI导出Excel文件

using Comm.Library.XML;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Xml.Serialization;

namespace GXT.BidFileMaker
{
    public static class MulPriceXmlToExcel
    {
        //string path = @"C:\Users\Administrator\Desktop\临时文件\MulPriceGrid_2c913b46672b728901672f886dad0dc7.xml";
        #region 读取数据，创建xls文件
        /// <summary>
        /// 读取xml数据，导出excel文件
        /// </summary>
        /// <param name="path">xml路径</param>
        public static void XmlToExcel(string path)
        {
            if (!File.Exists(path))
            {
                System.Windows.Forms.MessageBox.Show("导出失败！");
                return;//文件不存在，退出
            }
            //读取xml文件
            Table table = new Table();
            table = XmlSerializerHelper.DeserializerFromFile<Table>(path, "table");
            if (table != null)
            {
                SaveFileDialog saveFile = new SaveFileDialog();
                //设置保存文件对话框的标题
                saveFile.Title = "请选择要导出的文件路径";
              
                //设置保存文件的类型
                saveFile.Filter = "Excel文件（*.xls）|*.xls";
                if (saveFile.ShowDialog() == DialogResult.OK)
                {
                    //获得保存文件的路径
                    string filePath = saveFile.FileName;
                    //string fileNameExt = filePath.Substring(filePath.LastIndexOf("\\") + 1); //获取文件名，不带路径
                    //保存
                    WriteToExcel(filePath, table);
                   
                }
            }
            //DataTable dt = new DataTable();
            //int count = 0;
            //foreach (var item in table.tr[0].td)
            //{
            //    dt.Columns.Add(new DataColumn(item));
            //}
            //DataRow dr;
            //for (int i = 1; i < table.tr.Count; i++)
            //{
            //    count = 0;
            //    dr = dt.NewRow();
            //    foreach (var item in table.tr[i].td)
            //    {

            //        dr[table.tr[0].td[count]] = item;
            //        count++;
            //    }
            //    dt.Rows.Add(dr);
            //}
        }
        public static void WriteToExcel(string filePath, Table table)
        {
            //创建工作薄  
            IWorkbook wb;
            string extension = System.IO.Path.GetExtension(filePath);
            //根据指定的文件格式创建对应的类
            if (extension.Equals(".xls"))
            {
                wb = new HSSFWorkbook();
            }
            else
            {
                wb = new XSSFWorkbook();
            }

            #region 设置单元格样式
            ICellStyle style1 = wb.CreateCellStyle();//样式
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//文字水平对齐方式
            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;//自动换行
            IFont font1 = wb.CreateFont();//字体
            font1.FontName = "微软雅黑";
            font1.Color = HSSFColor.Black.Index;//字体颜色
            font1.Boldweight = (short)FontBoldWeight.Bold;//字体加粗样式
            style1.SetFont(font1);//样式里的字体设置具体的字体样式

            ICellStyle style2 = wb.CreateCellStyle();//样式
            IFont font2 = wb.CreateFont();//字体
            font2.FontName = "微软雅黑";
            font2.Color = HSSFColor.Black.Index;//字体颜色
            font2.Boldweight = (short)FontBoldWeight.Normal;//字体加粗样式
            style2.SetFont(font2);//样式里的字体设置具体的字体样式
            style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.WrapText = true;//自动换行
            style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//文字水平对齐方式
            style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式 
            #endregion

            //创建一个表单
            ISheet sheet = wb.CreateSheet("Sheet0");
            //设置列宽
            int[] columnWidth = { 10, 10, 20, 10 };
            for (int i = 0; i < columnWidth.Length; i++)
            {
                //设置列宽度，256*字符数，因为单位是1/256个字符
                sheet.SetColumnWidth(i, 256 * columnWidth[i]);
            }
            IRow row;
            NPOI.SS.UserModel.ICell cell;
            for (int i = 0; i < table.tr.Count; i++)
            {
                row = sheet.CreateRow(i);//创建第i行
                for (int j = 0; j < table.tr[i].td.Count; j++)
                {
                    cell = row.CreateCell(j);//创建第j列
                    cell.CellStyle = i == 0 ? style1 : style2;
                    //根据数据类型设置不同类型的cell
                    object obj = table.tr[i].td[j];
                    SetCellValue(cell, obj);
                    sheet.AutoSizeColumn(j);
                }
            }
            try
            {
                FileStream fs = File.OpenWrite(filePath);
                wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。  
                fs.Close();
            }
            catch (Exception e)
            {
                //Debug.WriteLine(e.Message);
            }
        }
        //根据数据类型设置不同类型的cell
        public static void SetCellValue(NPOI.SS.UserModel.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());
            }
        }
        #endregion
      
    }
    public class Table
    {
        [XmlElement("tr")]
        public List<TR> tr { get; set; }
    }


    public class TR
    {
        [XmlElement("td")]
        public List<string> td { get; set; }
    }
}