C# 各种骚操作的导出EXCEL

最新推荐文章于 2024-02-19 23:04:21 发布

lxk8023

最新推荐文章于 2024-02-19 23:04:21 发布

阅读量531

点赞数

分类专栏： excel 文章标签： excel c#

本文链接：https://blog.csdn.net/lxk8023/article/details/107846421

版权

excel 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一导出带有图片格式的excel

在 C# 构建树形数据的文章中，就已经写好了两个帮助类。则可直接调用。

            DataTable data = new DataTable();
            data.Columns.Add("Describe");
            data.Columns.Add("MiniImgUrl");
            data.Columns.Add("ShootingTime");
            data.Columns.Add("LastEditByName");
            data.Columns.Add("LastEditDate");
           
            var query =  list的数据源;
            foreach (var dtData in query)
            {
                DataRow dtRow = data.NewRow();
                dtRow["Describe"] = dtData.Describe;
                dtRow["ShootingTime"] = dtData.ShootingTime.ToString("yyyy-MM-dd");
                dtRow["LastEditByName"] = dtData.LastEditByName;
                dtRow["LastEditDate"] = dtData.LastEditDate.Value.ToString("yyyy-MM-dd");

                var Imgs = dtData.Attachments.ToList();
                //多个图片的情况下。
                dtRow["MiniImgUrl"] = string.Join(",", Imgs.Select(t => t.FilePath));

                data.Rows.Add(dtRow);
            }
            List<string> lstColName = new List<string>();
            lstColName.Add("图片描述");
            lstColName.Add("图片");
            lstColName.Add("拍摄时间");
            lstColName.Add("记录时间");
            lstColName.Add("记录人");

         //路径
         return  ExcelHelpers.ExportContainImage(data, lstColName, "项目图片信息", 1);

导出样式。

可导出上传的多张图片

二导出动态合并单元格

1. 先把数据拼成DataTable。和表头一起

2.在进行处理

   var plan = _fullViewPlanService.GetFull(planId, type);
            var lockCells = new List<ExcelLockCell>();
            var transformTable = dataTable;
            var tableName = SpecialCharacters.RemoveSpecialCharacters(plan.StruName + "-" + plan.ProjectName + "-" + plan.StageName + "-" + plan.GroupName) + "楼栋计划";

            var names = SpecialCharacters.RemoveSpecialCharacters(tableName);
            var exportHelper = new NPOIHelper(names);

            var tableCom = 【动态的表头数据源】;

            var firstHeadColumn = new List<ExcelHeader>
            {
                new ExcelHeader{FieldName="楼栋名称",MergeRowCount=1,ColumnIndex=0},
                new ExcelHeader{FieldName="业态",MergeRowCount=1,ColumnIndex=1},
                new ExcelHeader { FieldName = "面积", ColumnIndex = 2 , MergeColumnCount = 1 }

            };

            for (int i = 0; i < tableCom.Count; i++)
            {
                firstHeadColumn.Add(new ExcelHeader { FieldName = tableCom[i].Name, ColumnIndex = 4 + 2 * i, MergeColumnCount = 1 });
            }

            var secondHeadColumn = new List<ExcelHeader>();

            secondHeadColumn.Add(new ExcelHeader { FieldName = "建筑面积", ColumnIndex = 2 });
            secondHeadColumn.Add(new ExcelHeader { FieldName = "可售面积", ColumnIndex = 3 });

            for (int i = 0; i < tableCom.Count; i++)
            {
                secondHeadColumn.Add(new ExcelHeader { FieldName = "计划完成时间", ColumnIndex = 4 + 2 * i });
                secondHeadColumn.Add(new ExcelHeader { FieldName = "实际完成时间", ColumnIndex = 5 + 2 * i });
            }
            exportHelper.CreateExcelHeadRow(firstHeadColumn, 0);
            exportHelper.CreateExcelHeadRow(secondHeadColumn, 1);
            exportHelper.CreateExcelContentRow(transformTable, 2);

            return exportHelper.GetExcelHttpResponseMessage(tableName);

三导出背景色的excel

1.在excel 单元格中，使用FillForegroundColor属性，导出的时候就会有背景颜色

ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = 47;
cell.CellStyle = cellStyle;