C#使用NPOI导出Dictionary到Excel表

List<Model>这种数据格式导出为Excel之前已经写过了，今天在开发过程中需要将List<Dictionary<string, string>>格式的数据导出为Excel，然后就根据之前导出List<Model>的代码稍微修改了一下，写篇文章留个备份。

实例代码

        public static HSSFWorkbook BuildSwitchDataDic<T>(string SheetName, List<Dictionary<string, string>> list, Dictionary<string, string> FiedNames)
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(SheetName); //创建工作表
            sheet.CreateFreezePane(0, 1); //冻结列头行
            HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
            row_Title.HeightInPoints = 25F; //设置列头行高
            HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
            cs_Title_Font.IsBold = true; //字体加粗
            cs_Title_Font.FontHeightInPoints = 12; //字体大小
            cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式

            int ii = 0;
            foreach (string key in FiedNames.Keys)
            {
                HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(ii); //创建单元格
                cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
                cell_Title.SetCellValue(key);
                sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
                ii++;
            }
            HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中

            for (int i = 0; i < list.Count; i++)
            {
                HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行
                row_Content.HeightInPoints = 20;
                int jj = 0;
                foreach (var proName in list[i])
                {

                    HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(jj); //创建单元格
                    cell_Conent.CellStyle = cs_Content;

                    //获取对应属性的值
                    object value = proName.Value; //object newValue = model.uName;
                    string cell_value = value == null ? "" : value.ToString();
                    cell_Conent.SetCellValue(cell_value);
                    jj++;

                }

            }

            return wb;
        }

调用代码

 //设置导出文件列标题
            Dictionary<string, string> FiedNames = new Dictionary<string, string>();

            foreach (var item in keyValues[0])
            {
                FiedNames.Add(item.Key, item.Key);
            }

            //调用通用方法把list转excel
            HSSFWorkbook wb = NpoiHelper.BuildSwitchDataDic<List<Dictionary<string,string>>>("数据导出", keyValues, FiedNames);
            //设置文件导出目录
            string sExportFileName = @"D:/ExcelFile/";
            string fileName = "数据导出_" + DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.CurrentInfo) + ".xls";
            //将数据转化为二进制流
            MemoryStream stream = new MemoryStream();
            wb.Write(stream);
            var buf = stream.ToArray();
            //将二进制流转化为文件并保存在本地计算机
            using (FileStream fileStream = new FileStream(sExportFileName + fileName, FileMode.Create, FileAccess.Write))
            {
                fileStream.Write(buf, 0, buf.Length);
                fileStream.Flush();
            }
            stream.Close();