public static void CreatePieChart(IWorkbook wbook)
{
DevExpress.Spreadsheet.Worksheet worksheet = SetActiveWorksheet(wbook, "Range1");
// Create a chart and specify its location
Chart chart = worksheet.Charts.Add(ChartType.PieExploded, worksheet["B2:C7"]);
// Display the chart title
chart.Title.Visible = true;
chart.Title.SetReference(worksheet["B1"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["K15"];
// Set the chart style
chart.Style = ChartStyle.ColorGradient;
// Hide the legend
chart.Legend.Visible = false;
// Rotate the pie chart view
chart.Views[0].FirstSliceAngle = 100;
// Display data labels
DataLabelOptions dataLabels = chart.Views[0].DataLabels;
dataLabels.ShowCategoryName = true;
dataLabels.ShowPercent = true;
dataLabels.Separator = "\n";
}
private void TransferModel(DevExpress.Spreadsheet.Worksheet sheet)
{
//取消合并
sheet.UnMergeCells(sheet.Range["D2:J2"]);
sheet.UnMergeCells(sheet.Range["K2:N2"]);
//合并
sheet.MergeCells(sheet.Range["D2:D3"]);
sheet.MergeCells(sheet.Range["E2:E3"]);
sheet.MergeCells(sheet.Range["F2:F3"]);
sheet.MergeCells(sheet.Range["G2:G3"]);
sheet.MergeCells(sheet.Range["H2:H3"]);
sheet.MergeCells(sheet.Range["I2:I3"]);
sheet.MergeCells(sheet.Range["J2:J3"]);
sheet.MergeCells(sheet.Range["K2:K3"]);
sheet.MergeCells(sheet.Range["L2:L3"]);
sheet.MergeCells(sheet.Range["M2:M3"]);
sheet.MergeCells(sheet.Range["N2:N3"]);
//删除
sheet.Rows[2].Delete();
//更改样式
sheet.Cells["D2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["E2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["F2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["H2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["I2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["J2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["K2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["L2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["M2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
sheet.Cells["N2"].Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Medium);
}
private void btnImportData_ItemClick(object sender, ItemClickEventArgs e)
{
try
{
OpenFileDialog o = new OpenFileDialog();
o.Filter = "线路文件(*.txt)|*.txt|Excel文件(*.csv)|*.csv";
o.Title = "打开文件";
o.Multiselect = true;
o.InitialDirectory = Application.StartupPath;
o.RestoreDirectory = false;
if (o.ShowDialog() == DialogResult.OK)
{
string[] filepaths = o.FileNames;
foreach (string s in filepaths)
{
if (!FilePaths.Contains(s))//如果没有添加过此文件
{
//添加ComboBox
((DevExpress.XtraEditors.Repository.RepositoryItemComboBox)DataSources.Edit).Items.Add(s.Substring(s.LastIndexOf("\\") + 1));
List <string[]> latlngs = new List <string[]>();
ReadFormatTxt(s, ref latlngs);//读格式的文件返回经纬度
string trackName = s.Substring(s.LastIndexOf("\\") + 1);
string filename = trackName;
trackName = trackName.Split(new char[] { ',' })[0];
List <TxtPoint> points = stringsToPoints(latlngs, trackName, filename);
//添加哈希表--文件名,和TxtPoints
Hashtable.Add(s.Trim(), points); //保存路径和TxtPoints
AddFileRow(s, true); //在文件管理栏加文件
//添加打开的文件
FilePaths.Add(s.Trim()); //添加路径
}
}
//List<string[]> latlngs = new List<string[]>();
//ReadFormatTxt(filepath, ref latlngs);//读格式的文件返回经纬度
//List<TxtPoint> points = stringsToPoints(latlngs);
//Hashtable.Add(filepath, points);
if (spreadsheetControl.ActiveWorksheet != null)
{
IWorkbook workbook = spreadsheetControl.Document;
DevExpress.Spreadsheet.Worksheet sheet = spreadsheetControl.Document.Worksheets[0];
string filepath = filepaths.First();//第一个文件为默认值
List <TxtPoint> last = (List <TxtPoint>)Hashtable[filepath];
filepath = filepath.Substring(filepath.LastIndexOf("\\") + 1);
workbook.MailMergeDataSource = last;
DataSources.EditValue = filepath;
siStatus.Caption = string.Format("共有{0}行数据", last.Count);
siInfo.Caption = string.Format("当前绑定数据为{0}", filepath);
//InsertRows(sheet, 6, points.Count - 4, 3);
//ImportData(sheet, 2, points);
}
}
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message);
}
}
/// <summary>
/// 插入N行
/// </summary>
/// <param name="workbook">Excel工作区</param>
/// <param name="sheetIndex">sheet索引</param>
/// <param name="startRowIndex">开始插入行的索引(向下插入表的)</param>
/// <param name="rowcount">插入的行数</param>
/// <param name="formatRowIndex">格式行的索引值</param>
private void InsertRows(DevExpress.Spreadsheet.Worksheet sheet, int startRowIndex, int rowcount, int formatRowIndex)
{
DevExpress.Spreadsheet.RowCollection rows = sheet.Rows;
//example:
//sheet.Rows.Insert(6, 5);//在行索引为6的行下方插入5行,即增加7、8、9、10、11
sheet.Rows.Insert(startRowIndex, rowcount);
for (int j = startRowIndex; j < startRowIndex + rowcount; j++)
{
sheet.Rows[j].CopyFrom(sheet.Rows[formatRowIndex]);//复制行格式
}
}
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() : "");
}
//private void selectData()
//{
// if (Program.sql_con.State == ConnectionState.Closed) Program.sql_con.Open();
// SqlCommand cmd = Program.sql_con.CreateCommand();
// cmd.CommandType = CommandType.Text;
// cmd.CommandText = $"select p.Aop as 'N°AOP' ,CAST(e.fdr as varchar(10)) as 'FDR' , e.délai_dexecution , s.num_Marcher as 'N°MARCHE' , l.localite as 'Localité des Tran' ,e.objet, p.date_op as 'Date ouverture des Plis' , e.estimation as 'Estimation' , s.attributaire as 'ATTRIBUTAIRE' , e.montant as 'MONTANT' , CAST(o.Etat as varchar(20)) as 'Etat' from localite l FULL OUTER JOIN etude e on l.id_l = e.localite inner join fk k on e.id1 = k.id1 FULL OUTER JOIN publication p on p.id2 = k.id2 FULL OUTER JOIN fk2 pk on pk.id2 = p.id2 FULL OUTER JOIN SIMPLE_overture s on s.id3 = pk.id3 FULL OUTER JOIN order_service o on o.id_order = s.id3 where 1=1";
// DataTable dt1 = new DataTable();
// SqlDataAdapter da1 = new SqlDataAdapter(cmd);
// da1.Fill(dt1);
// foreach (DataRow row in dt1.Rows)
// {
// //do what you need to calculate myNewValue here
// if (row["Etat"].ToString() == "-1")
// {
// row["Etat"] = "order de commencement";
// }
// else if (row["Etat"].ToString() == "0"){
// row["Etat"] = "order d'arrêt";
// }
// else if (row["Etat"].ToString() == "1")
// {
// row["Etat"] = "order de reprise";
// }
// else if (row["Etat"].ToString() == "2")
// {
// row["Etat"] = "réception provisoire";
// }
// else if (row["Etat"].ToString() == "3")
// {
// row["Etat"] = "réception définitive";
// }
// if (row["FDR"].ToString() == "0")
// {
// row["FDR"] = "Non";
// }
// else
// {
// row["FDR"] = "Oui";
// }
// }
// //gridControl1.DataSource = dt1;
// DataSet ds = new DataSet("tab");
// ds.Tables.Add(dt1);
// dataView = new DataView(ds.Tables[0]);
//}
private void CreateDataSourse()
{
Worksheet worksheet1 = spreadsheetControl1.Document.Worksheets[0];
//DataTable sourceTable = new DataTable("Products");
//sourceTable.Columns.Add("Product", typeof(string));
//sourceTable.Columns.Add("Price", typeof(float));
//sourceTable.Columns.Add("Quantity", typeof(Int32));
//sourceTable.Columns.Add("Discount", typeof(float));
//sourceTable.Rows.Add("Chocolade", 5, 15, 0.03);
//sourceTable.Rows.Add("Konbu", 9, 55, 0.1);
//sourceTable.Rows.Add("Geitost", 15, 70, 0.07);
//DataSet ds = new DataSet("Products");
//SqlDataAdapter da = new SqlDataAdapter();
////da.Fill(sourceTable);
//ds.Tables.Add(sourceTable);
//dataView = new DataView(ds.Tables[0]);
////////////////////:
//dossierMarcherDataSet dataSet = new dossierMarcherDataSet();
//dossierMarcherDataSetTableAdapters.etudeTableAdapter etudeTableAdapter = new dossierMarcherDataSetTableAdapters.etudeTableAdapter();
//etudeTableAdapter.Fill(dataSet.etude);
//dataSet.etude.Columns["objet"].SetOrdinal(1);
////dataSet.etude.Columns["objet"].SetOrdinal(2);
////dataSet.etude.Columns["objet"].SetOrdinal(3);
//dataSet.etude.Columns["objet"].ReadOnly = true;
//dataView = new DataView(dataSet.etude);
//dataView.Sort = "objet";
}
private void BindingDataSource()
{
run();
Workbook = spreadsheetControl1.Document;
Workbook.LoadDocument(@"Document\situation.xlsx", DocumentFormat.Xlsx);
IWorkbook workbook = spreadsheetControl1.Document;
Worksheet worksheet = Workbook.Worksheets[0];
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
worksheet.DataBindings.Clear();
worksheet.Columns.ClearOutline();
//worksheet.DataBindings.BindTableToDataSource(null);
worksheet.DataBindings.BindTableToDataSource(dataView, 4, 1, options);
worksheet.Cells.AutoFitColumns();
worksheet.Cells.AutoFitRows();
worksheet.Cells.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
worksheet.Cells.Style.Alignment.Vertical = SpreadsheetVerticalAlignment.Top;
worksheet.ActiveView.Zoom = 80;
//worksheet.RowColumnHeadersVisible = false;
}
finally
{
workbook.EndUpdate();
}
}
public IWorkbook putTKB(string file)
{
fileName = file;
using (FileStream stream = new FileStream(file, FileMode.Open))
{
//Nạp file excel Học kỳ và Tuần
tempXlsxReader.Visible = false;
tempXlsxReader.LoadDocument(stream, DocumentFormat.Xlsx);
wb_temp = tempXlsxReader.Document;
//Nếu là lần đầu thì gán bằng wb_HK = temp, ko thì nạp thêm vô
//wb_HK = wb_HK == null ? wb_temp : wb_HK;
if (wb_HK == null)
{
xlsxLopTemp.LoadDocument(stream);
wb_HK = xlsxLopTemp.Document;
}
//else
//{
//Nạp thêm vô HKs
int l = wb_temp.Worksheets.Count;
ws_lops.Clear();
for (int i = 0; i < l; i++)
{
ws_lops.Add(wb_temp.Worksheets[i]);
}
foreach (Worksheet ws in wb_temp.Worksheets)
{
if (wb_HK.Worksheets.Contains(ws.Name))
{
wb_HK.Worksheets[ws.Name].CopyFrom(ws);
}
else
{
Worksheet w = wb_HK.Worksheets.Add(ws.Name);
w.CopyFrom(ws);
}
}
//}
}
return(wb_HK);
}
//Import Data Form txtPoints
//轨道地理信息表
private void ImportData(DevExpress.Spreadsheet.Worksheet sheet, int tableHeader, List <TxtPoint> txtPoints)
{
int j = 0;
for (int i = tableHeader; i < tableHeader + txtPoints.Count; i++)
{
TxtPoint t = txtPoints[j];
sheet.Cells[i, 0].SetValue(j + 1); //数据编号
sheet.Cells[i, 1].SetValue(t.StationName); //车站名称
//sheet.Cells[i, 2].SetValue();//车站编号
sheet.Cells[i, 3].SetValue(t.TrackName); //轨道名称
//sheet.Cells[i,4].SetValue();//轨道编号
sheet.Cells[i, 5].SetValue(t.Longtitude); //经度(毫秒)
sheet.Cells[i, 6].SetValue(t.Latitude); //纬度(单位毫秒)
sheet.Cells[i, 7].SetValue(t.Height); //高程(厘米)
sheet.Cells[i, 8].SetValue(t.KiloPos); //里程
sheet.Cells[i, 9].SetValue(t.Bear); //航向角
sheet.Cells[i, 10].SetValue(t.DeltaBear); //增量航向角
j++;
}
}
public void showTKBTuan(DateTime date)
{
if (wb_HK == null)
{
return;
}
TKBTuanViewer.Visible = true;
using (FileStream stream = new FileStream(@"TKB TUAN.xlsx", FileMode.Open))
{
//Nạp file excel Học kỳ và Tuần
TKBTuanViewer.LoadDocument(stream, DocumentFormat.Xlsx);
wb_Tuan = TKBTuanViewer.Document;
formulaBar.SpreadsheetControl = TKBTuanViewer;
//Cell cell = null;
int day = date.Day, month = date.Month;
string dd = (day < 10) ? "0" + day : day.ToString();
string mm = (month < 10) ? "0" + month : month.ToString();
foreach (Worksheet ws_Tuan in wb_Tuan.Worksheets)
{
//gán ngày cho TKB Tuần
DateTime dt2 = date;
dt2 = dt2.AddDays(5);
int month2 = dt2.Month;
string dd2 = (dt2.Day < 10) ? "0" + dt2.Day : dt2.Day.ToString();
string mm2 = (month2 < 10) ? "0" + month2 : month2.ToString();
IEnumerable <Cell> cells = ws_Tuan.Search("_ngay");
foreach (Cell c in cells)
{
ws_Tuan[c.RowIndex, c.ColumnIndex].Value = $"Từ ngày {dd} tháng {mm} năm {date.Year} đến ngày {dd2} tháng {mm2} năm {dt2.Year}";
}
cells = ws_Tuan.Search("TRƯỞNG PHÒNG");
dt2 = dt2.AddDays(-9);
dd2 = (dt2.Day < 10) ? "0" + dt2.Day : dt2.Day.ToString();
mm2 = (month2 < 10) ? "0" + month2 : month2.ToString();
foreach (Cell c in cells)
{
ws_Tuan[c.RowIndex - 1, c.ColumnIndex].Value = $"Ngày {dd2} tháng {mm2} năm {dt2.Year}";
}
dt2 = date;
cells = ws_Tuan.Search("_d");
foreach (Cell c in cells)
{
dd2 = (dt2.Day < 10) ? "0" + dt2.Day : dt2.Day.ToString();
ws_Tuan[c.RowIndex, c.ColumnIndex].Value = dd2;
dt2 = dt2.AddDays(1);
}
string s = search(ws_Tuan, "ĐƠN VỊ");
//Duyệt từng cột lớp trong TKB tuần
int col_Tuan = col, row_Tuan = row;
while (ws_Tuan.GetCellValue(col_Tuan, row_Tuan).ToString() != "")
{
//Duyệt từng lớp
string lop = ws_Tuan.GetCellValue(col_Tuan, row_Tuan).ToString();
bool chk = wb_HK.Worksheets.Contains(lop);
if (chk)
{
Worksheet ws = wb_HK.Worksheets[lop];
copyFromHKToTuan(date, ws_Tuan, ws, col_Tuan, row_Tuan);
}
col_Tuan++;
}
}
xlsxViewer.Visible = true;
}
}
private void copyFromHKToTuan(DateTime date, Worksheet ws_tuan, Worksheet ws_hk, int col_t, int row_t)
{
int day = date.Day, month = date.Month;
string dd = (day < 10) ? "0" + day : day.ToString(), mm = (month < 10) ? "0" + (month) : (month).ToString();
string title = mm + "/" + date.Year.ToString();
string s = search(ws_hk, title);
int col_hk = col - 1, row_hk = row + 1;
string caption_t = ws_tuan.Columns[col_t].GetReferenceA1();
string[] arr = caption_t.Split(new Char[] { '$', ':' });
caption_t = arr[1];
string caption_hk = ws_hk.Columns[col_hk].GetReferenceA1();
arr = caption_hk.Split(new Char[] { '$', ':' });
caption_hk = arr[1];
//Tìm cột ngày THỨ 2 trong TKB Học kỳ
do
{
caption_hk = ws_hk.Columns[col_hk].GetReferenceA1();
arr = caption_hk.Split(new Char[] { '$', ':' });
caption_hk = arr[1];
string str1 = ws_hk[caption_hk + row_hk].Value.ToString();
//string str2 = ws_hk.GetCellValue(col_hk, row_hk).ToString();
if (str1 == dd)
{
break; //Tìm thấy cột ngày
}
col_hk++;
} while (true);
row_hk++; //Chuyển xuống dòng bên dưới
//Bắt đầu copy từ bên HK sang bên Tuần (6 ngày)
row_t += 5; //chạy từ dòng có chữ ĐƠN VỊ xuống tiết đầu tiên
for (int i = 0; i < 36; i++)
{
string value = ws_hk[caption_hk + row_hk].Value.ToString();
if ((value.Length == 2 && value[0] > 47 && value[0] < 58 && value[1] > 47 && value[1] < 58) || value.Contains("/"))
{
row_hk++;
continue;
}
if (value != "")
{
var rt = ws_hk[caption_hk + row_hk].GetRichText();
ws_tuan[caption_t + (row_t)].SetRichText(rt);
//Kiểm tra merge
for (int j = 1; j < 5; j++)
{
string r = caption_hk + row_hk + ':' + caption_hk + (row_hk + j);
CellRange cell_range = ws_hk[r];
if (cell_range.IsMerged)
{
string r_t = caption_t + row_t + ":" + caption_t + (row_t + j);
ws_tuan.MergeCells(ws_tuan[r_t]);
}
}
//CellRange range = ws_tuan.Range[caption_t + (row_t)];
//Formatting rangeFormatting = range.BeginUpdateFormatting();
//rangeFormatting.Font.Size = 18;
//range.EndUpdateFormatting(rangeFormatting);
}
row_hk++; row_t++;
}
}
/// <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"] = "完成";
}
}
private void readDataFromSheet()
{
IWorkbook workbook = spreadExcel.Document;
try
{
//sheet equipment
DevExpress.Spreadsheet.Worksheet worksheet0 = workbook.Worksheets[0];
sheetEquipment = new string[33];
sheetEquipment[0] = "Sheet Equipment";
for (int i = 1; i < 33; i++)
{
sheetEquipment[i] = worksheet0.Cells[1, i - 1].Value.ToString();
Debug.WriteLine("Equipment " + sheetEquipment[i]);
}
//sheet component
DevExpress.Spreadsheet.Worksheet worksheet1 = workbook.Worksheets[1];
sheetComponent = new string[33];
sheetComponent[0] = "Sheet Component";
for (int i = 1; i < 33; i++)
{
sheetComponent[i] = worksheet1.Cells[1, i - 1].Value.ToString();
Debug.WriteLine("Component " + sheetComponent[i]);
}
//sheet operating condition
DevExpress.Spreadsheet.Worksheet worksheet2 = workbook.Worksheets[2];
sheetOperatingCondition = new string[19];
sheetOperatingCondition[0] = "Sheet Operating Condition";
for (int i = 1; i < 19; i++)
{
sheetOperatingCondition[i] = worksheet2.Cells[1, i - 1].Value.ToString();
Debug.WriteLine("Operating Condition " + sheetOperatingCondition[i]);
}
//sheet Stream
DevExpress.Spreadsheet.Worksheet worksheet3 = workbook.Worksheets[3];
sheetStream = new string[23];
sheetStream[0] = "Sheet Stream";
for (int i = 1; i < 23; i++)
{
sheetStream[i] = worksheet3.Cells[1, i - 1].Value.ToString();
Debug.WriteLine("Stream " + sheetStream[i]);
}
//sheet Material
DevExpress.Spreadsheet.Worksheet worksheet4 = workbook.Worksheets[4];
sheetMaterial = new string[23];
sheetMaterial[0] = "Sheet Material";
for (int i = 1; i < 23; i++)
{
sheetMaterial[i] = worksheet4.Cells[1, i - 1].Value.ToString();
Debug.WriteLine("Material " + sheetMaterial[i]);
}
//sheet Coating
DevExpress.Spreadsheet.Worksheet worksheet5 = workbook.Worksheets[5];
sheetCoating = new string[16];
sheetCoating[0] = "Sheet Coating";
for (int i = 1; i < 16; i++)
{
sheetCoating[i] = worksheet5.Cells[1, i - 1].Value.ToString();
Debug.WriteLine("Coating " + sheetCoating[i]);
}
}
catch (Exception e)
{
MessageBox.Show("Error Read Excel File!" + e.ToString(), "Cortek");
}
}
private void CalculateCustomFunction()
{
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["E4:E8"].ArrayFormula = "=SPHEREMASS(D4:D8, C4:C8)";
}
private void MedStuffUpdateButton_Click(object sender, RoutedEventArgs e)
{
medStuffUpdateButton.IsEnabled = false;
dtDb.Clear();
excelDataTable.Clear();
changesTable.Clear();
#region File choise
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "XLSX-files|*.xlsx";
openFileDialog.ShowDialog();
#endregion
#region Range search
Workbook wbook = new Workbook();
if (openFileDialog.FileName == "")
{
System.Windows.MessageBox.Show("Выберите файл", "", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
fileName = Path.GetFileNameWithoutExtension(openFileDialog.FileName);
wbook.LoadDocument(openFileDialog.FileName);
DevExpress.Spreadsheet.Worksheet worksheet =
wbook.Worksheets[0];
int lastRow = 0;
while (!worksheet.Cells[lastRow, 1].Value.IsEmpty)
{
lastRow++;
}
//var range = worksheet.Tables[0].Range;
var range = worksheet.Range[$"A1:D{lastRow}"].CurrentRegion;
#endregion
#region Excel datatable definition
//excelDataTable = worksheet..CreateDataTable(range, true);
////excelDataTable.Columns[1].DataType = System.Type.GetType("System.String");
//excelDataTable.Columns[0].ColumnName = "numberrrr";
//excelDataTable.Columns[1].ColumnName = "code";
//excelDataTable.Columns[2].ColumnName = "name";
//excelDataTable.Columns[3].ColumnName = "cost";
#endregion
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, excelDataTable, true);
//exporter.CellValueConversionError += exporter_CellValueConversionError;
//MyConverter myconverter = new MyConverter();
//exporter.Options.CustomConverters.Add("As Of", myconverter);
//// Set the export value for empty cell.
//myconverter.EmptyCellValue = "N/A";
exporter.Options.ConvertEmptyCells = true;
exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false;
exporter.Export();
//grid.ItemsSource = excelDataTable;
//grid.Visibility = Visibility.Visible;
//DataTable dtDb = new DataTable();
//DataTable changesTable = new DataTable();
#region Datatable with changes definition
//if (changesTable.Columns.Count < 1)
//{
// changesTable.Columns.Add("code");
// changesTable.Columns.Add("old_cost");
// changesTable.Columns.Add("new_cost");
// changesTable.Columns.Add("old_name");
// changesTable.Columns.Add("new_name");
// changesTable.Columns.Add("file_n");
// changesTable.Columns.Add("difference");
//}
#endregion
//string connStr = @"Data Source=DESKTOP-N6R6KEU\SQLEXPRESS;Initial Catalog=STD_DB_DICT_HIVE;Integrated Security=True";
#region Connection to DB
if (INI.KeyExists("Source", "DB_Connection") && INI.KeyExists("Catalog", "DB_Connection"))
{
try
{
string connStr = $"Data Source={INI.ReadINI("DB_Connection", "Source")};Initial Catalog={INI.ReadINI("DB_Connection", "Catalog")};Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connStr))
{
SqlDataAdapter adapter = new SqlDataAdapter();
//SqlDataAdapter adapter2 = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("SELECT * FROM med_stuf", connection);
adapter.Fill(dtDb);
adapter.SelectCommand = new SqlCommand("SELECT * FROM log.med_stuf", connection);
adapter.Fill(changesTable);
changesTable.Clear();
if (changesTable.Columns.Count < 9)
{
changesTable.Columns.Add("difference");
}
//gridControl1.DataSource = dtDb;
#region Filling datatable with changes
foreach (DataRow newDataRow in excelDataTable.Rows)
{
if (!newDataRow["code"].Equals(DBNull.Value))
{
if (!newDataRow["name"].Equals(DBNull.Value))
{
foreach (DataRow dbRow in dtDb.Select($"code={newDataRow["code"]}"))
{
object oldPrice = DBNull.Value;
object oldName = DBNull.Value;
object newPrice = DBNull.Value;
object newName = DBNull.Value;
if (!dbRow["cost"].Equals(newDataRow["cost"]))
{
oldPrice = dbRow["cost"];
dbRow["cost"] = newDataRow["cost"];
newPrice = newDataRow["cost"];
}
if (!dbRow["name"].Equals(newDataRow["name"]))
{
oldName = dbRow["name"];
dbRow["name"] = newDataRow["name"];
newName = newDataRow["name"];
}
if (!oldName.Equals(newName) || !oldPrice.Equals(newPrice))
{
changesTable.Rows.Add(new Object[] { newDataRow["code"],
oldPrice,
newPrice,
oldName,
newName,
fileName,
DBNull.Value,
DBNull.Value,
Convert.ToDouble(newPrice) - Convert.ToDouble(oldPrice) });
}
}
}
else
{
System.Windows.MessageBox.Show($"Наименования услуг в файле {openFileDialog.FileName} должны быть заполнены", "Ошибка!", MessageBoxButton.OK, MessageBoxImage.Error);
changesTable.Clear();
}
}
else
{
System.Windows.MessageBox.Show($"Коды услуг в файле {openFileDialog.FileName} должны быть заполнены", "Ошибка!", MessageBoxButton.OK, MessageBoxImage.Error);
changesTable.Clear();
}
}
#endregion
if (changesTable.Rows.Count > 0)
{
grid.ItemsSource = changesTable;
grid.Visibility = Visibility.Visible;
OkButton.IsEnabled = true;
CancelButton.IsEnabled = true;
}
else
{
System.Windows.MessageBox.Show($"В файле {openFileDialog.FileName} нет изменений", "Обновление", MessageBoxButton.OK, MessageBoxImage.Information);
medStuffUpdateButton.IsEnabled = true;
}
}//connection
}
catch (Exception)
{
System.Windows.MessageBox.Show("Проверьте параметры подключения к базе данных");
medStuffUpdateButton.IsEnabled = true;
}
}
else
{
System.Windows.MessageBox.Show("Проверьте наличие файла конфигурации подключкния к БД", "Ошибка подключения к БД", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
#endregion
}//mainButtonClick
private bool CheckFormatFile()
{
IWorkbook workbook = spreadExcel.Document;
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[0];
if (!workbook.IsProtected)
{
workbook.Protect("hoang", true, false);
}
bool isCorrect = true;
if (workbook.Worksheets.Count != 8)
{
MessageBox.Show("Format is not correct! Please check again", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
return
(false);
}
for (int i = 0; i < 6; i++)
{
string sheetName = workbook.Worksheets[i].Name;
switch (i)
{
case 0:
if (sheetName != "Equipment")
{
MessageBox.Show("Sheet Name " + sheetName + " is not correct!", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
isCorrect = false;
}
break;
case 1:
if (sheetName != "Component")
{
MessageBox.Show("Sheet Name " + sheetName + " is not correct!", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
isCorrect = false;
}
break;
case 2:
if (sheetName != "Operating Condition")
{
MessageBox.Show("Sheet Name " + sheetName + " is not correct!", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
isCorrect = false;
}
break;
case 3:
if (sheetName != "Stream")
{
MessageBox.Show("Sheet Name " + sheetName + " is not correct!", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
isCorrect = false;
}
break;
case 4:
if (sheetName != "Material")
{
MessageBox.Show("Sheet Name " + sheetName + " is not correct!", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
isCorrect = false;
}
break;
default:
if (sheetName != "CoatingCladdingLiningInsulation")
{
MessageBox.Show("Sheet Name " + sheetName + " is not correct!", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
isCorrect = false;
}
break;
}
}
if (worksheet.Columns.LastUsedIndex > 32)
{
MessageBox.Show("This is Storage Tank excel file! Select again", "Cortek RBI", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
return(isCorrect);
}
void LoadGrid()
{
if (string.IsNullOrEmpty(file))
{
var openFileDialog = UtilDisplay.LoadOpenFileDialog;
openFileDialog.Multiselect = false;
openFileDialog.Filter = "CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*.xls|XLSX Files (*.xlsx)|*.xlsx|TXT Files (*.txt)|*.txt |All files (*.*)|*.*";
bool?userClickedOK = openFileDialog.ShowDialog();
if (userClickedOK == true)
{
file = openFileDialog.FileName;
}
if (string.IsNullOrEmpty(file))
{
return;
}
}
string fileExtension = System.IO.Path.GetExtension(file);
try
{
bool hasType;
DataTable DataTable;
if (fileExtension == ".csv")
{
hasType = false;
updateDelimiter = true;
DataTable = FromCsv(file);
}
else if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
hasType = true;
IWorkbook workBook = importSpreadSheet.Document;
workBook.LoadDocument(file);
DevExpress.Spreadsheet.Worksheet worksheet = importSpreadSheet.Document.Worksheets[0];
var range = worksheet.GetUsedRange();
var dataTable = worksheet.CreateDataTable(range, true);
for (int col = 0; col < range.ColumnCount; col++)
{
CellValueType cellType = range[0, col].Value.Type;
for (int r = 1; r < range.RowCount; r++)
{
if (cellType != range[r, col].Value.Type)
{
dataTable.Columns[col].DataType = typeof(string);
break;
}
}
}
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
exporter.Export();
DataTable = exporter.DataTable;
}
else if (fileExtension == ".txt")
{
hasType = false;
DataSet theDataSet = new DataSet();
theDataSet.ReadXml(file);
DataTable = theDataSet.Tables[0];
}
else
{
file = string.Empty;
return;
}
dgBankStmt.ItemsSource = DataTable;
customDataColumnSource = CreateCustomDataColumn(DataTable, hasType).ToList();
dgBankStmt.ColumnsSource = customDataColumnSource;
listBoxProperties.ItemsSource = customDataColumnSource;
}
catch (Exception ex)
{
UnicontaMessageBox.Show(ex);
}
}
