private void MergeCells(Cell cell, int numToMerge)
{
string label = cell.GetReferenceA1();
//int countMergeCells = CountMergeCells(cell);
string column = label[0] + "";
string row = label[1] + "";
//sheet_Tuans.MergeCells(sheet_HKs[column + row + ":" + column + (row + numToMerge)]);
}
private void AddActivity(DevExpress.Spreadsheet.Cell cellTempActivity)
{
try
{
if (!cellTempActivity.Value.IsEmpty)
{
DateTime date;
String[] splitCellActivity = cellTempActivity.Value.ToString().Split(new char[] { '-', '=' });
string strActivity = "";
string strActivityShortcut = "";
if (!DateTime.TryParseExact(cellTempActivity.Value.ToString(), "MMMM", new CultureInfo("en-US"), DateTimeStyles.None, out date))
{
if (splitCellActivity.Count() == 1)
{
splitCellActivity = cellTempActivity.Value.ToString().Split(' ');
strActivityShortcut = splitCellActivity.Last().Trim();
strActivity = cellTempActivity.Value.ToString().Replace(cellTempActivity.Value.ToString(), "").Trim();
}
else
{
strActivityShortcut = splitCellActivity[1].Trim();
strActivity = splitCellActivity[0].Trim();
}
if (strActivity.Equals("") || strActivityShortcut.Equals(""))
{
}
else
{
using (var context = new LorikeetAppEntities())
{
var checkIfActivityExists = (from l in context.Labels
where l.Shortcut == strActivityShortcut
select l).DefaultIfEmpty().First();
if (checkIfActivityExists == null)
{
var activityLabel = new Lorikeet.Data.Label();
activityLabel.DisplayName = strActivity;
activityLabel.MenuCaption = strActivity;
activityLabel.Shortcut = strActivityShortcut;
Random r = new Random(DateTime.Now.Millisecond);
Color randomColor = Color.FromArgb(r.Next(0, 255), r.Next(0, 255), r.Next(0, 255));
activityLabel.Color = randomColor.ToArgb();
context.Labels.Add(activityLabel);
context.SaveChanges();
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(MiscStuff.GetAllMessages(ex));
return;
}
}
private string search(Worksheet ws, string s)
{
Cell cell = null;
IEnumerable <Cell> searchResult = ws.Search(s);
foreach (Cell c in searchResult)
{
cell = c;
s = c.GetReferenceA1();
col = cell.ColumnIndex;
row = cell.RowIndex;
col++; row++;
break;
}
return(cell != null?cell.GetReferenceA1() : "");
}
/// <summary>
/// 生成表格
/// </summary>
public void DoReport()
{
CommonClass common = new CommonClass();
ConnectDB db = new ConnectDB();
DataTable DT = db.GetDataBySql("select GLDWNAME from GISDATA_GLDW where GLDW = '" + common.GetConfigValue("GLDW") + "'");
DataRow dr = DT.Select(null)[0];
string path = common.GetConfigValue("SAVEDIR") + "\\" + dr["GLDWNAME"].ToString() + "\\表格";
//string path = @"D:\report\";
if (Directory.Exists(path) == false)
{
Directory.CreateDirectory(path);
}
int[] selectRows = this.gridView1.GetSelectedRows();
DataTable dtDs = new DataTable();
foreach (int itemRow in selectRows)
{
mydelegate = new myDelegate(setPos);
Thread myThread = new Thread((ThreadStart)(delegate()
{
this.BeginInvoke(mydelegate, new object[] { itemRow });
}));
myThread.IsBackground = true;
myThread.Start();
//Thread MyThread = new Thread(new ParameterizedThreadStart(setPos));
//MyThread.IsBackground = true;
//MyThread.Start(itemRow);
DataRow row = this.gridView1.GetDataRow(itemRow);
String reportType = row["REPORTTYPE"].ToString();
String sheetname = row["SHEETNAME"].ToString();
String sqlstr = row["SQLSTR"].ToString();
String rowname = row["ROWNAME"].ToString().Trim();
String columnsname = row["COLUMNSNAME"].ToString().Trim();
String ValueStr = row["VALUESTRING"].ToString().Trim();
String sortfield = row["SORTFIELD"].ToString().Trim();
string[] dataSourceArr = row["DATASOURCE"].ToString().Split(',');
SpreadsheetControl sheet = new SpreadsheetControl();
Spread.IWorkbook book = sheet.Document;
book.LoadDocument(Application.StartupPath + "\\Report\\" + row["REPORTMOULD"].ToString(), Spread.DocumentFormat.OpenXml);
if (reportType == "任务完成统计表")
{
wwcxmTable = new DataTable();
string gldw = common.GetConfigValue("GLDW") == "" ? "520121" : common.GetConfigValue("GLDW");
DataTable dtTaskDb = db.GetDataBySql("select YZLGLDW,YZLFS,ZCSBND,XMMC,RWMJ from GISDATA_TASK where YZLGLDW = '" + gldw + "'");
dtTaskDb.TableName = "GISDATA_TASK";
DataTable dtTask = common.TranslateDataTable(dtTaskDb);
DataRow[] drTask = dtTask.Select(null);
dtTask.Columns.Add("SBMJ");
dtDs.Columns.Add("YZLGLDW");
dtDs.Columns.Add("YZLFS");
dtDs.Columns.Add("ZCSBND");
dtDs.Columns.Add("XMMC");
dtDs.Columns.Add("RWMJ");
dtDs.Columns.Add("SBMJ");
wwcxmTable.Columns.Add("YZLFS");
wwcxmTable.Columns.Add("ZCSBND");
wwcxmTable.Columns.Add("XMMC");
wwcxmTable.Columns.Add("RWMJ");
wwcxmTable.Columns.Add("SBMJ");
DataTable dt = new DataTable();
for (int i = 0; i < dataSourceArr.Length; i++)
{
DataTable itemDt = common.GetTableByName(dataSourceArr[i].Trim());
//DataTable itemDt = ToDataTable(table);
dt.Merge(itemDt);
}
for (int i = 0; i < drTask.Length; i++)
{
DataRow rowItem = drTask[i];
string zcsbnd = rowItem["ZCSBND"].ToString();
gldw = rowItem["YZLGLDW"].ToString();
string yzlfs = rowItem["YZLFS"].ToString();
string xmmc = rowItem["XMMC"].ToString();
string sbmj = "";
var query = from t in dt.AsEnumerable()
where (t.Field <string>("YZLGLDW") == gldw && t.Field <string>("ZCSBND") == zcsbnd && t.Field <string>("YZLFS") == yzlfs && t.Field <string>("XMMC") == xmmc)
group t by new { t1 = t.Field <string>("YZLGLDW"), t2 = t.Field <string>("ZCSBND"), t3 = t.Field <string>("YZLFS"), t4 = t.Field <string>("XMMC") } into m
select new
{
gldwItem = m.Key.t1,
zcsbndItem = m.Key.t2,
yzlfsItem = m.Key.t3,
xmmcItem = m.Key.t4,
sbmjItem = m.Sum(n => Convert.ToDouble(n["SBMJ"]))
};
if (query.ToList().Count > 0)
{
query.ToList().ForEach(q =>
{
sbmj = q.sbmjItem.ToString();
});
}
rowItem["SBMJ"] = sbmj == "" ? "0" : sbmj;
if (rowItem["SBMJ"].ToString() != rowItem["RWMJ"].ToString())
{
wwcxmTable.ImportRow(rowItem);
}
dtDs.ImportRow(rowItem);
}
book.MailMergeDataSource = dtDs;
Spread.IWorkbook resultBook = book.GenerateMailMergeDocuments()[0];
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
if (resultBook != null)
{
using (MemoryStream result = new MemoryStream())
{
resultBook.SaveDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx");
result.Seek(0, SeekOrigin.Begin);
}
}
}
else if (reportType == "透视表")
{
DataTable dt = new DataTable();
for (int i = 0; i < dataSourceArr.Length; i++)
{
DataTable itemDt = common.GetTableByName(dataSourceArr[i].Trim());
//DataTable itemDt = ToDataTable(table);
dt.Merge(itemDt);
}
dt.DefaultView.Sort = sortfield;
ConvertPivotTable conver = new ConvertPivotTable();
DataTable dtPivot = conver.CreatePivotTable(dt, columnsname.Trim(), "SBMJ", "", rowname.Trim());
Spread.Worksheet Spreadsheet = book.Worksheets[sheetname];
Model.DocumentModel documentModel = new Model.DocumentModel();
FileInfo xlxsFile = new FileInfo(Application.StartupPath + "\\Report\\" + row["REPORTMOULD"].ToString());
System.IO.Stream stream = xlxsFile.OpenRead();
Spread.DocumentFormat format = documentModel.AutodetectDocumentFormat(xlxsFile.Name);
documentModel.LoadDocument(stream, format, string.Empty);
MailMergeOptions option = new MailMergeOptions(documentModel);
Model.CellRangeBase detail = option.DetailRange;
Model.CellRangeBase header = option.HeaderRange;
IEnumerable <Spread.Cell> dynamiccolArr = Spreadsheet.Search("=DYNAMICCOL(\"" + columnsname + "\")");
IEnumerable <Spread.Cell> dynamicfieldArr = Spreadsheet.Search("=DYNAMICFIELD(\"" + columnsname + "\")");
Spread.Cell dynamiccol = null;
foreach (Spread.Cell str in dynamiccolArr)
{
dynamiccol = str;
break;
}
Spread.Cell dynamicFiled = null;
foreach (Spread.Cell str in dynamicfieldArr)
{
dynamicFiled = str;
break;
}
int ColumnIndexItem = 0;
for (int i = 0; i < dtPivot.Columns.Count; i++)
{
DataColumn itemColumn = dtPivot.Columns[i];
if (itemColumn.Namespace == columnsname)
{
Spread.Cell rangeHeader = Spreadsheet.Cells[dynamiccol.RowIndex, dynamiccol.ColumnIndex + ColumnIndexItem];
Spread.Cell rangeDetail = Spreadsheet.Cells[dynamicFiled.RowIndex, dynamiccol.ColumnIndex + ColumnIndexItem];
rangeHeader.CopyFrom(dynamiccol);
rangeDetail.CopyFrom(dynamicFiled);
rangeHeader.Value = itemColumn.Caption.ToString();
rangeDetail.Calculate();
rangeHeader.Calculate();
rangeDetail.Value = "=FIELD(\"" + itemColumn.ColumnName.ToString() + "\")";
rangeDetail.Formula = "=FIELD(\"" + itemColumn.ColumnName.ToString() + "\")";
ColumnIndexItem++;
}
}
//标题range
if (Spreadsheet.DefinedNames.GetDefinedName("TITLESTRING") != null)
{
Spread.Range titleRange = Spreadsheet.DefinedNames.GetDefinedName("TITLESTRING").Range;
Model.CellPosition TitleStarPosition = new Model.CellPosition(titleRange.LeftColumnIndex, titleRange.TopRowIndex);
Model.CellPosition TitleEndPosition = new Model.CellPosition(titleRange.RightColumnIndex + ColumnIndexItem - 1, titleRange.BottomRowIndex);
Model.CellRange newTitle = new Model.CellRange(header.Worksheet, TitleStarPosition, TitleEndPosition);
titleRange = Spreadsheet.Range[newTitle.ToString()];
Spreadsheet.MergeCells(titleRange);
}
//单位Range
if (Spreadsheet.DefinedNames.GetDefinedName("UNITSTRING") != null)
{
Spread.Range unitRange = Spreadsheet.DefinedNames.GetDefinedName("UNITSTRING").Range;
Model.CellPosition UnitStarPosition = new Model.CellPosition(unitRange.LeftColumnIndex, unitRange.TopRowIndex);
Model.CellPosition UnitEndPosition = new Model.CellPosition(unitRange.RightColumnIndex + ColumnIndexItem - 1, unitRange.BottomRowIndex);
Model.CellRange newUnit = new Model.CellRange(header.Worksheet, UnitStarPosition, UnitEndPosition);
unitRange = Spreadsheet.Range[newUnit.ToString()];
Spreadsheet.MergeCells(unitRange);
Spread.Style unitStyle = unitRange.Style;
unitStyle.Alignment.Horizontal = Spread.SpreadsheetHorizontalAlignment.Right;
}
//Detail Range
Model.CellRange newDetail = new Model.CellRange(header.Worksheet, detail.TopLeft.Column, detail.TopLeft.Row, detail.TopRight.Column + ColumnIndexItem, detail.BottomRight.Row);
Spread.Range detailRange = Spreadsheet.Range[newDetail.ToString()];
Spreadsheet.DefinedNames.GetDefinedName("DETAILRANGE").Range = detailRange;
//Header Range
Model.CellRange newHeader = new Model.CellRange(header.Worksheet, header.TopLeft.Column, header.TopLeft.Row, header.TopRight.Column + ColumnIndexItem, header.BottomRight.Row);
Spread.Range headerRange = Spreadsheet.Range[newHeader.ToString()];
Spreadsheet.DefinedNames.GetDefinedName("HEADERRANGE").Range = headerRange;
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
book.MailMergeDataSource = dtPivot;
Spread.IWorkbook resultBook = book.GenerateMailMergeDocuments()[0];
if (resultBook != null)
{
using (MemoryStream result = new MemoryStream())
{
resultBook.SaveDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx");
result.Seek(0, SeekOrigin.Begin);
SpreadsheetControl mergesheet = new SpreadsheetControl();
Spread.IWorkbook mergebook = mergesheet.Document;
mergebook.LoadDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx", Spread.DocumentFormat.OpenXml);
Spread.Worksheet MergeSpreadsheet = mergebook.Worksheets[sheetname];
int TableRowCnts = detail.TopLeft.Row + dtPivot.Rows.Count;
int tmpA;
int tmpB;
var PerTxt = "";
var CurTxt = "";
var groupPerTxt = "";
var groupCurTxt = "";
for (int i = detail.TopRight.Column; i > 0; i--)
{
PerTxt = "";
tmpA = 1;
tmpB = 0;
for (int j = detail.TopLeft.Row; j <= TableRowCnts; j++)
{
groupCurTxt = "";
if (j == TableRowCnts)
{
CurTxt = "";
}
else
{
CurTxt = MergeSpreadsheet.GetCellValue(i - 1, j).ToString();
}
for (int k = i - 1; k > 0; k--)
{
groupCurTxt += MergeSpreadsheet.GetCellValue(k - 1, j).ToString();
}
if (PerTxt == CurTxt && groupCurTxt == groupPerTxt)
{
tmpA += 1;
}
else
{
tmpB += tmpA;
if (tmpA > 1)
{
Model.CellRange MergePos = new Model.CellRange(newDetail.Worksheet, i - 1, j - tmpA, i - 1, j - 1);
Spread.Range MergeRange = MergeSpreadsheet.Range[MergePos.ToString()];
MergeSpreadsheet.MergeCells(MergeRange);
}
tmpA = 1;
}
PerTxt = CurTxt;
groupPerTxt = groupCurTxt;
}
}
mergebook.SaveDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx");
}
}
}
else
{
DataTable dt = new DataTable();
for (int i = 0; i < dataSourceArr.Length; i++)
{
DataTable itemDt = common.GetTableByName(dataSourceArr[i].Trim());
//DataTable itemDt = ToDataTable(table);
dt.Merge(itemDt);
}
//dtDs = common.TranslateDataTable(dt);
book.MailMergeDataSource = dt;
Spread.IWorkbook resultBook = book.GenerateMailMergeDocuments()[0];
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
if (resultBook != null)
{
using (MemoryStream result = new MemoryStream())
{
resultBook.SaveDocument(path + "\\" + row["REPORTNAME"].ToString() + time + ".xlsx");
result.Seek(0, SeekOrigin.Begin);
}
}
}
row["STATE"] = "完成";
}
}