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 = ;
grid.FixedColumns = ;
grid.Height = ;
grid.Width = ;
grid.ColumnsCount = ;
grid.Rows.Insert();
//行数
int rowsCount = ;
//列数
int columnsCount = ;
#region 添加表格标题
AddDictionary();
#endregion
//内容列
SourceGrid.Cells.ColumnHeader head = null;
head = new SourceGrid.Cells.ColumnHeader("月份");
grid\[, \] = head;
grid\[, \].ColumnSpan = ;
grid\[, \].Editor = null;
head.AutomaticSortEnabled = false;//禁止排序
//计算中间每列宽度
int ColumnsWidthCount = ;
for (int i = ; i <= columnsCount; i++)
{
head = new SourceGrid.Cells.ColumnHeader(i - + "月");
grid\[, i\] = head;
grid\[, i\].View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter;
grid.Columns\[i\].Width = ;
ColumnsWidthCount += ;
head.AutomaticSortEnabled = false;//禁止排序
}
head = new SourceGrid.Cells.ColumnHeader("横向合计");
head.AutomaticSortEnabled = false;//禁止排序
grid\[,columnsCount\] = head;
//最后一列的宽度填充
grid.Columns\[columnsCount\].Width = grid1.Width - ColumnsWidthCount - \* ;
//内容行,以后扩展可以增加行数
for (int i = ; i <= rowsCount; i++)
{
grid.Rows.Insert(i);
}
//第一列计数器
int rowIndex = ;
int num = ;
//第二列计数器
int rowIndex\_Tow=;
int num\_Tow = ;
//第三列计数器
int rowIndex\_Three = ;
#region 绘制横向标题栏
foreach (string iKey in dicContents.Keys)
{
rowIndex = ;
//计算每个大类要占用多少行
foreach (string jKey in dicContents\[iKey\].Keys)
{
rowIndex\_Tow = ;
//如果数据标题只有两列
if (dicContents\[iKey\]\[jKey\].Count == )
{
rowIndex = dicContents\[iKey\].Count;
rowIndex\_Tow = ;
rowIndex\_Three++;
isNull = true;
}
else
{
//数据标题有三列
foreach (string kKey in dicContents\[iKey\]\[jKey\])
{
rowIndex++;
rowIndex\_Tow++;
rowIndex\_Three++;
if (threeColumnLoad == true)
{
//第二列第一次加载的列计算方式
grid\[, \] = new SourceGrid.Cells.Cell(kKey, typeof(string));
grid\[, \].ColumnSpan = ;
grid\[, \].Editor = null;//禁止修改
threeColumnLoad = false;
}
else
{
//第二列非第一次加载以后的列计算方式
grid\[rowIndex\_Three, \] = new SourceGrid.Cells.Cell(kKey, typeof(string));
grid\[rowIndex\_Three, \].ColumnSpan = ;
grid\[rowIndex\_Three, \].Editor = null;//禁止修改
}
}
isNull = false;
}
LoadGridRowsTitle(grid, rowIndex\_Tow, ref num\_Tow, ref towColumnLoad, isNull, jKey, true, , );
}
LoadGridRowsTitle(grid, rowIndex, ref num, ref firstColumnLoad, isNull, iKey, false, , );
}
#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, \] = new SourceGrid.Cells.Cell("总计", typeof(string)); ;
grid\[num, \].ColumnSpan = ;
grid\[num, \].Editor = null;
head.AutomaticSortEnabled = false;//禁止排序
//利用档案总计
grid\[num, \] = new SourceGrid.Cells.Cell("KKCOUNT", typeof(string)); ;
grid\[num, \].ColumnSpan = ;
grid\[num, \].Editor = null;
head.AutomaticSortEnabled = false;//禁止排序
//利用档案人次总计
grid\[num + , \] = new SourceGrid.Cells.Cell("利用总计", typeof(string)); ;
grid\[num + , \].ColumnSpan = ;
grid\[num + , \].Editor = null;
head.AutomaticSortEnabled = false;//禁止排序
//利用档案总计
grid\[num + , \] = new SourceGrid.Cells.Cell("KKCOUNT1", typeof(string)); ;
grid\[num + , \].ColumnSpan = ;
grid\[num + , \].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 = ;
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, , DashStyle.Solid, ), new DevAge.Drawing.BorderLine(Color.Black, , DashStyle.Solid, ));//边框
//居中
grid\[x, y\].View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter;
firstLoad = false;
num = + rowIndex;
}
else
{
//try
//{
//第二列非第一次加载以后的列计算方式
grid\[num, y\] = new SourceGrid.Cells.Cell(Key, typeof(string));
grid\[num, y\].ColumnSpan = ;
grid\[num, y\].RowSpan = rowIndex;
grid\[num, y\].Editor = null;//禁止修改
if (isSecondColum)
{
if (isNull)
{
grid\[num, y\].ColumnSpan = ;
}
}
//}
//catch (Exception)
//{
// return;
//}
num = num + rowIndex;
}
}
private void button1\_Click(object sender, EventArgs e)
{
//第一列计数器
int rowIndex = ;
int num = ;
//int index = 0;
//第二列计数器
int rowIndex\_Tow = ;
int num\_Tow = ;
//第三列计数器
int rowIndex\_Three = ;
firstColumnLoad = true;//第一列初次加载
towColumnLoad = true;//第二列初次加载
threeColumnLoad = true;//第三列初次加载
isNull = false;//是否子类别为空
int columnsCount = ;//列数
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\[\]; //工作表
Cells cells = sheet.Cells;//单元格
#region Excel样式设置
//大标题样式
Style styleTitle = workbook.Styles\[workbook.Styles.Add()\];//新增样式
styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
styleTitle.Font.Name = "宋体";//文字字体
styleTitle.Font.Size = ;//文字大小
styleTitle.Font.IsBold = true;//粗体
styleTitle.IsTextWrapped = true;//单元格内容自动换行
//子标题样式
Style styleSubtitle = workbook.Styles\[workbook.Styles.Add()\];//新增样式
styleSubtitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
styleSubtitle.Font.Name = "宋体";//文字字体
styleSubtitle.Font.Size = ;//文字大小
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(, , , grid1.Columns.Count);//合并单元格
cells\[, \].PutValue("利用统计(2016年度)");//填写内容
cells\[, \].SetStyle(styleTitle);
cells.Rows\[\].Height = ;
#endregion
//内容列
cells.Merge(, , , );
cells\[, \].PutValue("月份");
cells\[, \].SetStyle(styleSubtitle);
for (int i = ; i <= columnsCount; i++)
{
cells\[, i\].PutValue(i - + "月");
cells\[, i\].SetStyle(styleSubtitle);
}
cells\[, columnsCount\].PutValue("横向合计");
cells\[, columnsCount\].SetStyle(styleSubtitle);
#region 绘制横向标题栏
foreach (string iKey in dicContents.Keys)
{
rowIndex = ;
//计算每个大类要占用多少行
foreach (string jKey in dicContents\[iKey\].Keys)
{
rowIndex\_Tow = ;
//如果数据标题只有两列
if (dicContents\[iKey\]\[jKey\].Count == )
{
rowIndex = dicContents\[iKey\].Count;
rowIndex\_Tow = ;
rowIndex\_Three++;
isNull = true;
}
else
{
//数据标题有三列
foreach (string kKey in dicContents\[iKey\]\[jKey\])
{
rowIndex++;
rowIndex\_Tow++;
rowIndex\_Three++;
if (threeColumnLoad == true)
{
//第二列第一次加载的列计算方式
cells.Merge(, , , );
cells\[, \].PutValue(kKey);//填写内容
cells\[, \].SetStyle(styleSubtitle);
threeColumnLoad = false;
}
else
{
try
{
//第二列非第一次加载以后的列计算方式
cells.Merge(rowIndex\_Three, , , );
cells\[rowIndex\_Three, \].PutValue(kKey);//填写内容
cells\[rowIndex\_Three, \].SetStyle(styleSubtitle);
}
catch (Exception)
{
break;
}
}
}
isNull = false;
}
LoadExcelRowsTitle(rowIndex\_Tow, ref num\_Tow, ref towColumnLoad, cells, styleSubtitle, isNull, jKey, true, , );
}
LoadExcelRowsTitle(rowIndex, ref num, ref firstColumnLoad, cells, styleSubtitle,isNull,iKey,false,,);
}
#endregion
#region 填充主要数据
for (int i = ; i < grid1.Rows.Count - ; i++)
{
for (int j = ; j <= grid1.Columns.Count - ; j++)
{
cells\[i + , j\].PutValue("kkk");
var contentStyle = cells\[i + , j\].GetStyle();
contentStyle.HorizontalAlignment = TextAlignmentType.Center;
cells\[i + , j\].SetStyle(contentStyle);
}
}
#endregion
#region 页脚
//利用档案总计
cells\[num, \].PutValue("总计");
cells.Merge(num, , , );
cells\[num, \].SetStyle(styleSubtitle);
cells\[num, \].PutValue(grid1\[num- , grid1.Columns.Count - \].Value);
var footerStyle = cells\[num, \].GetStyle();
footerStyle.HorizontalAlignment = TextAlignmentType.Center;
cells\[num, \].SetStyle(footerStyle);
cells.Merge(num, , , );
//利用档案人次总计
cells\[num+, \].PutValue("利用总计");
cells.Merge(num + , , , );
cells\[num+, \].SetStyle(styleSubtitle);
cells\[num+, \].PutValue(grid1\[num, grid1.Columns.Count - \].Value);
footerStyle.HorizontalAlignment = TextAlignmentType.Center;
cells\[num+, \].SetStyle(footerStyle);
cells.Merge(num + , , , );
#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, );//合并列
cells\[x, y\].PutValue(Key);//填写内容
cells\[x, y\].SetStyle(styleTitle);//填写内容
firstLoad = false;
num = + rowIndex;
}
else
{
//第一列第一次加载的列计算方式
cells.Merge(num, y, rowIndex, );//合并列
cells\[num, y\].PutValue(Key);//填写内容
cells\[num, y\].SetStyle(styleTitle);//填写内容
if (isSecondColum)
{
if (isNull)
{
cells.Merge(num, y, , );
}
}
num = num + rowIndex;
}
}
private void button2\_Click(object sender, EventArgs e)
{
CreateGrid();
}
}
}
grid样式设置:
例如文字居左:
grid[i, j].View.TextAlignment = DevAge.Drawing.ContentAlignment.TopLeft;
此版本适用于C/S端
手机扫一扫
移动阅读更方便
你可能感兴趣的文章