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); } }