using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; //using SourceGrid; using Aspose.Cells; using System.Drawing.Drawing2D; namespace WinTest { public partial class sourceGridTest : Form { public sourceGridTest() { InitializeComponent(); try { // CreateGrid(); } catch (Exception) { return; } } private Dictionary<string, Dictionary<string, List<string>>> dicContents = new Dictionary<string, Dictionary<string, List<string>>>(); private bool firstColumnLoad = true;//第一列初次加载 private bool towColumnLoad = true;//第二列初次加载 private bool threeColumnLoad = true;//第三列初次加载 private bool isNull = false;//是否子类别为空 /// <summary> /// 添加标题内容 /// </summary> public void AddDictionary() { string key = string.Empty; string strKey = string.Empty; key = "借阅_1"; Dictionary<string, List<string>> dicBorrow = new Dictionary<string, List<string>>(); strKey = "内阅_Y"; dicBorrow.Add(strKey, new List<string>()); dicBorrow[strKey].Add("借次_JYRC"); dicBorrow[strKey].Add("纸阅（本）_ZZJY"); strKey = "外阅_N"; dicBorrow.Add(strKey, new List<string>()); dicBorrow[strKey].Add("借次_JYRC"); dicBorrow[strKey].Add("纸阅（本）_ZZJY"); dicContents.Add(key, dicBorrow); key = "查印_3"; Dictionary<string, List<string>> dicSearchAndPrint = new Dictionary<string, List<string>>(); strKey = "对询_Y"; dicSearchAndPrint.Add(strKey, new List<string>()); dicSearchAndPrint[strKey].Add("查次_CDRC"); dicSearchAndPrint[strKey].Add("查目_CKTM"); strKey = "对1询_N"; dicSearchAndPrint.Add(strKey, new List<string>()); dicSearchAndPrint[strKey].Add("查次_CDRC"); dicSearchAndPrint[strKey].Add("查目_CKTM"); strKey = "电询_T"; dicSearchAndPrint.Add(strKey, new List<string>()); dicSearchAndPrint[strKey].Add("查次_CDRC"); dicSearchAndPrint[strKey].Add("查目_CKTM"); dicContents.Add(key, dicSearchAndPrint); key = "在阅_2"; Dictionary<string, List<string>> dicSearchOnline = new Dictionary<string, List<string>>(); strKey = "审数_1,4"; dicSearchOnline.Add(strKey, new List<string>()); dicContents.Add(key, dicSearchOnline); key = "土记"; Dictionary<string, List<string>> dicLandUse = new Dictionary<string, List<string>>(); strKey = "法控_7"; dicLandUse.Add(strKey, new List<string>()); dicLandUse[strKey].Add("查次_CDRC"); dicLandUse[strKey].Add("查目_CKTM"); strKey = "原询_4"; dicLandUse.Add(strKey, new List<string>()); dicLandUse[strKey].Add("查次_CDRC"); dicLandUse[strKey].Add("查目_CKTM"); strKey = "登询_5"; dicLandUse.Add(strKey, new List<string>()); dicLandUse[strKey].Add("查次_CDRC"); dicLandUse[strKey].Add("查目_CKTM"); strKey = "他询_6"; dicLandUse.Add(strKey, new List<string>()); dicLandUse[strKey].Add("次_CDRC"); dicLandUse[strKey].Add("目_CKTM"); dicContents.Add(key, dicLandUse); key = "打计_3"; Dictionary<string, List<string>> dicPrint = new Dictionary<string, List<string>>(); strKey = "对印_IP"; dicPrint.Add(strKey, new List<string>()); strKey = "对印_OP"; dicPrint.Add(strKey, new List<string>()); dicContents.Add(key, dicPrint); key = "统计_3"; Dictionary<string, List<string>> dicImport = new Dictionary<string, List<string>>(); strKey = "对出_IEP"; dicImport.Add(strKey, new List<string>()); strKey = "对出_OEP"; dicImport.Add(strKey, new List<string>()); dicContents.Add(key, dicImport); } /// <summary> /// 创建表格 /// 如需扩展只需加入新的集合，增加行数/列数即可 /// </summary> public void CreateGrid() { threeColumnLoad = true;//第三列初次加载 towColumnLoad = true;//第二列初次加载 firstColumnLoad = true;//第一列初次加载 dicContents.Clear(); //设置grid SourceGrid.Grid grid = grid1; grid.Rows.Clear(); grid.Columns.Clear(); grid.BorderStyle = BorderStyle.FixedSingle; grid.FixedRows = 1; grid.FixedColumns = 1; grid.Height = 1000; grid.Width = 1000; grid.ColumnsCount = 19; grid.Rows.Insert(0); //行数 int rowsCount = 25; //列数 int columnsCount = 18; #region 添加表格标题 AddDictionary(); #endregion //内容列 SourceGrid.Cells.ColumnHeader head = null; head = new SourceGrid.Cells.ColumnHeader("月份"); grid[0, 0] = head; grid[0, 0].ColumnSpan = 6; grid[0, 0].Editor = null; head.AutomaticSortEnabled = false;//禁止排序 //计算中间每列宽度 int ColumnsWidthCount = 0; for (int i = 6; i <= columnsCount; i++) { head = new SourceGrid.Cells.ColumnHeader(i - 5 + "月"); grid[0, i] = head; grid[0, i].View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter; grid.Columns[i].Width = 50; ColumnsWidthCount += 50; head.AutomaticSortEnabled = false;//禁止排序 } head = new SourceGrid.Cells.ColumnHeader("横向合计"); head.AutomaticSortEnabled = false;//禁止排序 grid[0,columnsCount] = head; //最后一列的宽度填充 grid.Columns[columnsCount].Width = grid1.Width - ColumnsWidthCount - 2 * 12; //内容行，以后扩展可以增加行数 for (int i = 1; i <= rowsCount; i++) { grid.Rows.Insert(i); } //第一列计数器 int rowIndex = 0; int num = 0; //第二列计数器 int rowIndex_Tow=0; int num_Tow = 0; //第三列计数器 int rowIndex_Three = 0; #region 绘制横向标题栏 foreach (string iKey in dicContents.Keys) { rowIndex = 0; //计算每个大类要占用多少行 foreach (string jKey in dicContents[iKey].Keys) { rowIndex_Tow = 0; //如果数据标题只有两列 if (dicContents[iKey][jKey].Count == 0) { rowIndex = dicContents[iKey].Count; rowIndex_Tow = 1; rowIndex_Three++; isNull = true; } else { //数据标题有三列 foreach (string kKey in dicContents[iKey][jKey]) { rowIndex++; rowIndex_Tow++; rowIndex_Three++; if (threeColumnLoad == true) { //第二列第一次加载的列计算方式 grid[1, 4] = new SourceGrid.Cells.Cell(kKey, typeof(string)); grid[1, 4].ColumnSpan = 2; grid[1, 4].Editor = null;//禁止修改 threeColumnLoad = false; } else { //第二列非第一次加载以后的列计算方式 grid[rowIndex_Three, 4] = new SourceGrid.Cells.Cell(kKey, typeof(string)); grid[rowIndex_Three, 4].ColumnSpan = 2; grid[rowIndex_Three, 4].Editor = null;//禁止修改 } } isNull = false; } LoadGridRowsTitle(grid, rowIndex_Tow, ref num_Tow, ref towColumnLoad, isNull, jKey, true, 1, 2); } LoadGridRowsTitle(grid, rowIndex, ref num, ref firstColumnLoad, isNull, iKey, false, 1, 0); } #endregion #region 填充数据 //for (int i = 1; i < num; i++) //{ // for (int j = 6; j <= columnsCount; j++) // { // grid[i, j] = new SourceGrid.Cells.Cell(grid[i, 0].Value.ToString() + "、" + grid[i, 3].Value.ToString() + "、" + grid[i, 5].Value.ToString(), typeof(string)); // grid[i, j].Editor = null; // } //} #endregion #region 页脚 //利用档案总计 grid[num, 0] = new SourceGrid.Cells.Cell("总计", typeof(string)); ; grid[num, 0].ColumnSpan = 6; grid[num, 0].Editor = null; head.AutomaticSortEnabled = false;//禁止排序 //利用档案总计 grid[num, 6] = new SourceGrid.Cells.Cell("KKCOUNT", typeof(string)); ; grid[num, 6].ColumnSpan = 13; grid[num, 6].Editor = null; head.AutomaticSortEnabled = false;//禁止排序 //利用档案人次总计 grid[num + 1, 0] = new SourceGrid.Cells.Cell("利用总计", typeof(string)); ; grid[num + 1, 0].ColumnSpan = 6; grid[num + 1, 0].Editor = null; head.AutomaticSortEnabled = false;//禁止排序 //利用档案总计 grid[num + 1, 6] = new SourceGrid.Cells.Cell("KKCOUNT1", typeof(string)); ; grid[num + 1, 6].ColumnSpan = 13; grid[num + 1, 6].Editor = null; head.AutomaticSortEnabled = false;//禁止排序 #endregion } /// <summary> /// sourceGrid处理合并列的类 /// </summary> /// <param name="grid">控件</param> /// <param name="rowIndex">合并行数</param> /// <param name="num">下一个标题的位置</param> /// <param name="firstLoad">是否第一次加载</param> /// <param name="isNull">是否有第三级别</param> /// <param name="Key">标题名</param> /// <param name="isSecondColum">是否为无第三级别的类</param> /// <param name="x">初始横坐标</param> /// <param name="y">单元格纵坐标</param> private static void LoadGridRowsTitle(SourceGrid.Grid grid, int rowIndex, ref int num, ref bool firstLoad, bool isNull, string Key, bool isSecondColum, int x, int y) { if (firstLoad == true) { //第二列第一次加载的列计算方式 grid[x, y] = new SourceGrid.Cells.Cell(Key, typeof(string)); grid[x, y].ColumnSpan = 2; grid[x, y].RowSpan = rowIndex; grid[x, y].Editor = null;//禁止修改 grid[x, y].View.Border = new DevAge.Drawing.RectangleBorder(new DevAge.Drawing.BorderLine(Color.Black, 1, DashStyle.Solid, 0), new DevAge.Drawing.BorderLine(Color.Black, 1, DashStyle.Solid, 0));//边框 //居中 grid[x, y].View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter; firstLoad = false; num = 1 + rowIndex; } else { //try //{ //第二列非第一次加载以后的列计算方式 grid[num, y] = new SourceGrid.Cells.Cell(Key, typeof(string)); grid[num, y].ColumnSpan = 2; grid[num, y].RowSpan = rowIndex; grid[num, y].Editor = null;//禁止修改 if (isSecondColum) { if (isNull) { grid[num, y].ColumnSpan = 4; } } //} //catch (Exception) //{ // return; //} num = num + rowIndex; } } private void button1_Click(object sender, EventArgs e) { //第一列计数器 int rowIndex = 0; int num = 0; //int index = 0; //第二列计数器 int rowIndex_Tow = 0; int num_Tow = 0; //第三列计数器 int rowIndex_Three = 1; firstColumnLoad = true;//第一列初次加载 towColumnLoad = true;//第二列初次加载 threeColumnLoad = true;//第三列初次加载 isNull = false;//是否子类别为空 int columnsCount = 18;//列数 using (SaveFileDialog dlg = new SaveFileDialog()) { dlg.Filter = "Xls文件(*.xls)|*.xls"; dlg.Title = "保存导出结果"; if (dlg.ShowDialog(this) == DialogResult.OK) { Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells;//单元格 #region Excel样式设置 //大标题样式 Style styleTitle = workbook.Styles[workbook.Styles.Add()];//新增样式 styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中 styleTitle.Font.Name = "宋体";//文字字体 styleTitle.Font.Size = 15;//文字大小 styleTitle.Font.IsBold = true;//粗体 styleTitle.IsTextWrapped = true;//单元格内容自动换行 //子标题样式 Style styleSubtitle = workbook.Styles[workbook.Styles.Add()];//新增样式 styleSubtitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中 styleSubtitle.Font.Name = "宋体";//文字字体 styleSubtitle.Font.Size = 11;//文字大小 styleSubtitle.Font.IsBold = true;//粗体 styleSubtitle.IsTextWrapped = true;//单元格内容自动换行 //styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; cells.Merge(0, 0, 1, grid1.Columns.Count);//合并单元格 cells[0, 0].PutValue("利用统计（2016年度）");//填写内容 cells[0, 0].SetStyle(styleTitle); cells.Rows[0].Height = 50; #endregion //内容列 cells.Merge(1, 0, 1, 6); cells[1, 0].PutValue("月份"); cells[1, 0].SetStyle(styleSubtitle); for (int i = 6; i <= columnsCount; i++) { cells[1, i].PutValue(i - 5 + "月"); cells[1, i].SetStyle(styleSubtitle); } cells[1, columnsCount].PutValue("横向合计"); cells[1, columnsCount].SetStyle(styleSubtitle); #region 绘制横向标题栏 foreach (string iKey in dicContents.Keys) { rowIndex = 0; //计算每个大类要占用多少行 foreach (string jKey in dicContents[iKey].Keys) { rowIndex_Tow = 0; //如果数据标题只有两列 if (dicContents[iKey][jKey].Count == 0) { rowIndex = dicContents[iKey].Count; rowIndex_Tow = 1; rowIndex_Three++; isNull = true; } else { //数据标题有三列 foreach (string kKey in dicContents[iKey][jKey]) { rowIndex++; rowIndex_Tow++; rowIndex_Three++; if (threeColumnLoad == true) { //第二列第一次加载的列计算方式 cells.Merge(2, 4, 1, 2); cells[2, 4].PutValue(kKey);//填写内容 cells[2, 4].SetStyle(styleSubtitle); threeColumnLoad = false; } else { try { //第二列非第一次加载以后的列计算方式 cells.Merge(rowIndex_Three, 4, 1, 2); cells[rowIndex_Three, 4].PutValue(kKey);//填写内容 cells[rowIndex_Three, 4].SetStyle(styleSubtitle); } catch (Exception) { break; } } } isNull = false; } LoadExcelRowsTitle(rowIndex_Tow, ref num_Tow, ref towColumnLoad, cells, styleSubtitle, isNull, jKey, true, 2, 2); } LoadExcelRowsTitle(rowIndex, ref num, ref firstColumnLoad, cells, styleSubtitle,isNull,iKey,false,2,0); } #endregion #region 填充主要数据 for (int i = 1; i < grid1.Rows.Count - 2; i++) { for (int j = 6; j <= grid1.Columns.Count - 1; j++) { cells[i + 1, j].PutValue("kkk"); var contentStyle = cells[i + 1, j].GetStyle(); contentStyle.HorizontalAlignment = TextAlignmentType.Center; cells[i + 1, j].SetStyle(contentStyle); } } #endregion #region 页脚 //利用档案总计 cells[num, 0].PutValue("总计"); cells.Merge(num, 0, 1, 6); cells[num, 0].SetStyle(styleSubtitle); cells[num, 6].PutValue(grid1[num-1 , grid1.Columns.Count - 1].Value); var footerStyle = cells[num, 6].GetStyle(); footerStyle.HorizontalAlignment = TextAlignmentType.Center; cells[num, 6].SetStyle(footerStyle); cells.Merge(num, 6, 1, 13); //利用档案人次总计 cells[num+1, 0].PutValue("利用总计"); cells.Merge(num + 1, 0, 1, 6); cells[num+1, 0].SetStyle(styleSubtitle); cells[num+1, 6].PutValue(grid1[num, grid1.Columns.Count - 1].Value); footerStyle.HorizontalAlignment = TextAlignmentType.Center; cells[num+1, 6].SetStyle(footerStyle); cells.Merge(num + 1, 6, 1, 13); #endregion workbook.Save(dlg.FileName); } } } /// <summary> /// Excel处理合并列的类 /// </summary> /// <param name="grid">控件</param> /// <param name="rowIndex">合并行数</param> /// <param name="num">下一个标题的位置</param> /// <param name="firstLoad">是否第一次加载</param> /// <param name="isNull">是否有第三级别</param> /// <param name="Key">标题名</param> /// <param name="isSecondColum">是否为无第三级别的类</param> /// <param name="x">初始横坐标</param> /// <param name="y">单元格纵坐标</param> private static void LoadExcelRowsTitle(int rowIndex, ref int num, ref bool firstLoad, Cells cells, Style styleTitle, bool isNull, string Key, bool isSecondColum, int x, int y) { if (firstLoad == true) { //第一列第一次加载的列计算方式 cells.Merge(x, y, rowIndex, 2);//合并列 cells[x, y].PutValue(Key);//填写内容 cells[x, y].SetStyle(styleTitle);//填写内容 firstLoad = false; num = 2 + rowIndex; } else { //第一列第一次加载的列计算方式 cells.Merge(num, y, rowIndex, 2);//合并列 cells[num, y].PutValue(Key);//填写内容 cells[num, y].SetStyle(styleTitle);//填写内容 if (isSecondColum) { if (isNull) { cells.Merge(num, y, 1, 4); } } num = num + rowIndex; } } private void button2_Click(object sender, EventArgs e) { CreateGrid(); } } }
grid样式设置：
例如文字居左：
grid[i, j].View.TextAlignment = DevAge.Drawing.ContentAlignment.TopLeft;
此版本适用于C/S端