随笔- 119 文章- 1 评论- 2419 阅读- 77万

使用aspose.cell动态导出多表头 EXCEL

效果图：

前台调用：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using ExportCells;
 
namespace WebApplication1
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            /***********************参数赋值***********************/
 
            //设置列
            List<ExportCells.AsposeHelper.JqxTableColumns> columns = new List<ExportCells.AsposeHelper.JqxTableColumns>();
            columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "id" });
            columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "name", columngroup = "namesex" });
            columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "sex", columngroup = "namesex" });
            columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "id2" });
            columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "cat", columngroup = "Animal" });
            columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "dog", columngroup = "Animal" });
            columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "rabbit", columngroup = "Animal" });
            columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "id3" });
 
            //设置分组
            List<ExportCells.AsposeHelper.JqxTableColumnsGroup> group = new List<ExportCells.AsposeHelper.JqxTableColumnsGroup>();
            group.Add(new ExportCells.AsposeHelper.JqxTableColumnsGroup() { name = "Animal", text = "动物" });
            group.Add(new ExportCells.AsposeHelper.JqxTableColumnsGroup() { name = "namesex", text = "名字性别" });
 
            //设置数据
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("name");
            dt.Columns.Add("sex");
            dt.Columns.Add("id2");
            dt.Columns.Add("cat");
            dt.Columns.Add("dog");
            dt.Columns.Add("rabbit");
            dt.Columns.Add("id3");
            var dr = dt.NewRow();
            dr[0] = 0;
            dr[1] = 1;
            dr[2] = 2;
            dr[3] = 3;
            dr[4] = 4;
            dr[5] = 5;
            dr[6] = 6;
            dr[7] = 7;
            dt.Rows.Add(dr);
            var dr2 = dt.NewRow();
            dr2[0] = 10;
            dr2[1] = 11;
            dr2[2] = 12;
            dr2[3] = 13;
            dr2[4] = 14;
            dr2[5] = 15;
            dr2[6] = 16;
            dr2[7] = 17;
            dt.Rows.Add(dr2);
 
            AsposeHelper.SaveColumnsHierarchy("1.xls", columns, group, dt);
        }
    }
}

ASPOSE封装类

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Drawing;
using System.Web;
 
namespace ExportCells
{
    /// <summary>
    /// ** 描述：Aspose
    /// ** 创始时间：2015-9-10
    /// ** 修改时间：-
    /// ** 修改人：sunkaixuan
    /// ** 使用说明：
    /// </summary>
    public class AsposeHelper
    {
        /// <summary>
        /// 导出EXCEL并且动态生成多级表头
        /// </summary>
        /// <param name="columns">列</param>
        /// <param name="group">分组</param>
        /// <param name="dt">dataTable</param>
        /// <param name="path">保存路径</param>
        public static void SaveColumnsHierarchy(List<JqxTableColumns> columns, List<JqxTableColumnsGroup> group, DataTable dt, string path)
        {
 
            Workbook workbook = new Workbook(); //工作簿 
            Worksheet sheet = workbook.Worksheets[0]; //工作表 
            Cells cells = sheet.Cells;//单元格
            for (int i = 0; i <= dt.Rows.Count + 1; i++)
            {
                sheet.Cells.SetRowHeight(i, 30);
            }
            List<AsposeCellInfo> acList = new List<AsposeCellInfo>();
            List<string> acColumngroupHistoryList = new List<string>();
            int currentX = 0;
            foreach (var it in columns)
            {
                AsposeCellInfo ac = new AsposeCellInfo();
                ac.y = 0;
                if (it.columngroup == null)
                {
                    ac.text = it.text;
                    ac.x = currentX;
                    ac.xCount = 1;
                    acList.Add(ac);
                    currentX++;
                    ac.yCount = 2;
                }
                else if (!acColumngroupHistoryList.Contains(it.columngroup))//防止重复
                {
                    var sameCount = columns.Where(itit => itit.columngroup == it.columngroup).Count();
                    ac.text = group.First(itit => itit.name == it.columngroup).text;
                    ac.x = currentX;
                    ac.xCount = sameCount;
                    acList.Add(ac);
                    currentX = currentX + sameCount;
                    acColumngroupHistoryList.Add(it.columngroup);
                    ac.yCount = 1;
                    ac.groupName = it.columngroup;
                }
                else
                {
                    //暂无逻辑
                }
            }
            //表头
            foreach (var it in acList)
            {
                cells.Merge(it.y, it.x, it.yCount, it.xCount);//合并单元格 
                cells[it.y, it.x].PutValue(it.text);//填写内容 
                cells[it.y, it.x].SetStyle(_thStyle);
                if (!string.IsNullOrEmpty(it.groupName))
                {
                    var cols = columns.Where(itit => itit.columngroup == it.groupName).ToList();
                    foreach (var itit in cols)
                    {
                        var colsIndex = cols.IndexOf(itit);
                        cells[it.y + 1, it.x + colsIndex].PutValue(itit.text);//填写内容 
                        cells[it.y + 1, it.x + colsIndex].SetStyle(_thStyle);
                    }
                }
            }
            //表格
            if (dt != null && dt.Rows.Count > 0)
            {
                var rowList = dt.AsEnumerable().ToList();
                foreach (var it in rowList)
                {
                    int dtIndex = rowList.IndexOf(it);
                    var dtColumns = dt.Columns.Cast<DataColumn>().ToList();
                    foreach (var itit in dtColumns)
                    {
                        var dtColumnsIndex = dtColumns.IndexOf(itit);
                        cells[2 + dtIndex, dtColumnsIndex].PutValue(it[dtColumnsIndex]);
                        cells[2 + dtIndex, dtColumnsIndex].SetStyle(_tdStyle);
 
                    }
                }
            }
            workbook.Save(path);
        }
 
        /// <summary>
        /// 导出EXCEL并且动态生成多级表头
        /// </summary>
        /// <param name="columns">列</param>
        /// <param name="group">分组</param>
        /// <param name="dt">dataTable</param>
        /// <param name="path">保存路径</param>
        public static void SaveColumnsHierarchy(string fileName,List<JqxTableColumns> columns, List<JqxTableColumnsGroup> group, DataTable dt)
        {
 
            Workbook workbook = new Workbook(); //工作簿 
            Worksheet sheet = workbook.Worksheets[0]; //工作表 
            Cells cells = sheet.Cells;//单元格
            for (int i = 0; i <= dt.Rows.Count + 1; i++)
            {
                sheet.Cells.SetRowHeight(i, 30);
            }
            List<AsposeCellInfo> acList = new List<AsposeCellInfo>();
            List<string> acColumngroupHistoryList = new List<string>();
            int currentX = 0;
            foreach (var it in columns)
            {
                AsposeCellInfo ac = new AsposeCellInfo();
                ac.y = 0;
                if (it.columngroup == null)
                {
                    ac.text = it.text;
                    ac.x = currentX;
                    ac.xCount = 1;
                    acList.Add(ac);
                    currentX++;
                    ac.yCount = 2;
                }
                else if (!acColumngroupHistoryList.Contains(it.columngroup))//防止重复
                {
                    var sameCount = columns.Where(itit => itit.columngroup == it.columngroup).Count();
                    ac.text = group.First(itit => itit.name == it.columngroup).text;
                    ac.x = currentX;
                    ac.xCount = sameCount;
                    acList.Add(ac);
                    currentX = currentX + sameCount;
                    acColumngroupHistoryList.Add(it.columngroup);
                    ac.yCount = 1;
                    ac.groupName = it.columngroup;
                }
                else
                {
                    //暂无逻辑
                }
            }
            //表头
            foreach (var it in acList)
            {
                cells.Merge(it.y, it.x, it.yCount, it.xCount);//合并单元格 
                cells[it.y, it.x].PutValue(it.text);//填写内容 
                cells[it.y, it.x].SetStyle(_thStyle);
                if (!string.IsNullOrEmpty(it.groupName))
                {
                    var cols = columns.Where(itit => itit.columngroup == it.groupName).ToList();
                    foreach (var itit in cols)
                    {
                        var colsIndex = cols.IndexOf(itit);
                        cells[it.y + 1, it.x + colsIndex].PutValue(itit.text);//填写内容 
                        cells[it.y + 1, it.x + colsIndex].SetStyle(_thStyle);
                    }
                }
            }
            //表格
            if (dt != null && dt.Rows.Count > 0)
            {
                var rowList = dt.AsEnumerable().ToList();
                foreach (var it in rowList)
                {
                    int dtIndex = rowList.IndexOf(it);
                    var dtColumns = dt.Columns.Cast<DataColumn>().ToList();
                    foreach (var itit in dtColumns)
                    {
                        var dtColumnsIndex = dtColumns.IndexOf(itit);
                        cells[2 + dtIndex, dtColumnsIndex].PutValue(it[dtColumnsIndex]);
                        cells[2 + dtIndex, dtColumnsIndex].SetStyle(_tdStyle);
 
                    }
                }
            }
            var response = HttpContext.Current.Response;
            response.Clear();
            response.Buffer = true;
            response.Charset = "utf-8";
            response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.ContentEncoding = System.Text.Encoding.UTF8;
            response.ContentType = "application/ms-excel";
            response.BinaryWrite(workbook.SaveToStream().ToArray());
            response.End();
        }
 
        private static Style _thStyle
        {
            get
            {
                Style s = new Style();
                s.Font.IsBold = true;
                s.Font.Name = "宋体";
                s.Font.Color = Color.Black;
                s.HorizontalAlignment = TextAlignmentType.Center;  //标题居中对齐
                return s;
            }
        }
 
        private static Style _tdStyle
        {
            get
            {
                Style s = new Style();
                return s;
            }
        }
 
        public class JqxTableColumns
        {
            public string field { get; set; }
            public string cellsAlign { get; set; }
            public string align { get; set; }
            public string text { get; set; }
            public string columngroup { get; set; }
        }
 
        public class JqxTableColumnsGroup
        {
            public string text { get; set; }
            public string align { get; set; }
            public string name { get; set; }
        }
 
        public class AsposeCellInfo
        {
            public string text { get; set; }
            public int x { get; set; }
            public int xCount { get; set; }
            public int y { get; set; }
            public int yCount { get; set; }
            public string groupName { get; set; }
        }
    }
}