public void UnMarkCell(string data)
{
bool searchChar = false;
DevExpress.Spreadsheet.IWorkbook workbook;
Worksheet worksheet;
workbook = spreadsheetControl.Document;
try
{
workbook.LoadDocument(data);
}
catch (Exception)
{
return;
}
worksheet = workbook.Worksheets[0];
worksheet.Cells.Fill.BackgroundColor = Color.Transparent;
workbook = spreadsheetControl.Document;
try
{
workbook.SaveDocument(data);
}
catch (Exception)
{
return;
}
}
public bool MarkRussianString(string path, string searchString)
{
bool searchStringFlag = false;
DevExpress.Spreadsheet.IWorkbook workbook;
Worksheet worksheet;
workbook = spreadsheetControl.Document;
try
{
workbook.LoadDocument(path);
}
catch (Exception)
{
return(true);
}
worksheet = workbook.Worksheets[0];
//workbookk.LoadDocument(data);
SearchOptions options = new SearchOptions();
options.SearchBy = SearchBy.Rows;
options.SearchIn = SearchIn.Values;
options.MatchEntireCellContents = false;
options.MatchCase = false;
IEnumerable <Cell> searchResult = worksheet.Search(searchString, options);
if (searchResult.Count() > 0)
{
searchStringFlag = true;
}
else
{
return(false);
}
foreach (Cell cell in searchResult)
{
cell.Fill.BackgroundColor = Color.LightBlue;
}
try
{
workbook.SaveDocument(path);
}
catch (Exception ex)
{
MessageBox.Show("Файл: " + path + ". Не был отредактирован так как открыт в проводнике", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
return(searchStringFlag);
}
public bool SearchRussianChar(string data, bool mark = true)
{
bool searchChar = false;
DevExpress.Spreadsheet.IWorkbook workbook;
Worksheet worksheet;
workbook = spreadsheetControl.Document;
try
{
workbook.LoadDocument(data);
}
catch (Exception)
{
return(true);
}
worksheet = workbook.Worksheets[0];
//workbookk.LoadDocument(data);
char[] alphabet = Enumerable.Range('а', 37).Select(x => (char)x).ToArray();
alphabet[34] = 'і';
alphabet[35] = 'ї';
alphabet[36] = 'є';
SearchOptions options = new SearchOptions();
options.SearchBy = SearchBy.Rows;
options.SearchIn = SearchIn.Values;
options.MatchEntireCellContents = false;
options.MatchCase = false;
foreach (var item in alphabet)
{
IEnumerable <Cell> searchResult = worksheet.Search(item.ToString(), options);
foreach (Cell cell in searchResult)
{
if (mark)
{
cell.Fill.BackgroundColor = Color.Bisque;
}
}
if (searchResult.Count() > 0)
{
searchChar = true;
}
}
try
{
workbook.SaveDocument(data);
}
catch (Exception ex)
{
MessageBox.Show("Файл: " + data + ". Не был отредактирован так как открыт в проводнике", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
return(searchChar);
}
/// <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"] = "完成";
}
}