private void btnStatisticalTable_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excel = null; Workbook wb = null; try { ///统计长度或面积总和 double DSum = 0; //建立Excel对象 excel = new Microsoft.Office.Interop.Excel.Application(); wb = excel.Application.Workbooks.Add(true); excel.Visible = true; wb.Application.ActiveWindow.Caption = m_Title + " 统计表"; excel.Cells[1, 1] = m_XLabels; excel.Cells[1, 2] = m_Title.Substring(0, m_Title.Length - 3); ///遍历获得X轴和对应的值 for (int i = 0; i < m_XLable.Count; i++) { excel.Cells[i + 2, 1] = m_XLable[i]; excel.Cells[i + 2, 2] = m_CharData[0][i]; DSum = DSum + Convert.ToDouble(m_CharData[0][i].ToString()); } excel.Cells[m_XLable.Count + 2, 1] = "合计为:"; excel.Cells[m_XLable.Count + 2, 2] = DSum.ToString(); ///弹出对话保存生成统计表的路径 Microsoft.Office.Core.FileDialog fd = wb.Application.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogSaveAs); fd.InitialFileName = m_Title + " 统计表"; int result = fd.Show(); if (result == 0) { return; } string fileName = fd.InitialFileName; if (!string.IsNullOrEmpty(fileName)) { if (fileName.IndexOf(".xls") == -1) { fileName += ".xls"; } ///保存生成的统计表 wb.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); GC.Collect(); } catch { } }
private void saveAllFilesToolStripMenuItem_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Core.FileDialog fileDialog = app.FileDialog[Microsoft.Office.Core.MsoFileDialogType.msoFileDialogFolderPicker]; fileDialog.InitialFileName = @"D:\Programmation\MercyBotCore\MercyBot\Others"; int nres = fileDialog.Show(); if (nres == -1) //ok { Microsoft.Office.Core.FileDialogSelectedItems selectedItems = fileDialog.SelectedItems; string[] selectedFolders = selectedItems.Cast <string>().ToArray(); if (selectedFolders.Length > 0) { string selectedFolder = selectedFolders[0]; Dictionary <string, List <string> > lines = new Dictionary <string, List <string> >(); foreach (DataGridViewTextBoxColumn column in dgvLangs.Columns) { if (column.HeaderText == "Key") { continue; } lines.Add(column.HeaderText, new List <string>()); } foreach (DataGridViewRow row in dgvLangs.Rows) { foreach (var kvp in lines) { string line = $"{row.Cells[0].Value}|{row.Cells.Cast<DataGridViewCell>().First(c => c.OwningColumn.HeaderText == kvp.Key).Value}"; if (line == "|") { continue; } kvp.Value.Add(line); } } foreach (var kvp in lines) { File.WriteAllLines(Path.Combine(selectedFolder, $"{kvp.Key}.lang"), kvp.Value); } } } }
private void button1_Click(object sender, EventArgs e) { string defaultpath = @"C:\Program Files\Altium"; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Core.FileDialog fileDialog = app.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogFolderPicker); fileDialog.InitialFileName = (Directory.Exists(defaultpath)) ? defaultpath : @"C:\"; int nres = fileDialog.Show(); if (nres == -1) //ok { Microsoft.Office.Core.FileDialogSelectedItems selectedItems = fileDialog.SelectedItems; string[] selectedFolders = selectedItems.Cast <string>().ToArray(); if (selectedFolders.Length > 0) { string selectedFolder = selectedFolders[0]; folderPath = selectedFolder; richTextBox1.Text += "You've selected: " + folderPath + "\n"; } } }
private void selectLangsFolderToolStripMenuItem_Click(object sender, EventArgs e) { dgvLangs.Rows.Clear(); dgvLangs.Columns.Clear(); Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Core.FileDialog fileDialog = app.FileDialog[Microsoft.Office.Core.MsoFileDialogType.msoFileDialogFolderPicker]; fileDialog.InitialFileName = @"D:\Programmation\MercyBotCore\MercyBot\Others"; int nres = fileDialog.Show(); if (nres == -1) //ok { Microsoft.Office.Core.FileDialogSelectedItems selectedItems = fileDialog.SelectedItems; string[] selectedFolders = selectedItems.Cast <string>().ToArray(); if (selectedFolders.Length > 0) { string selectedFolder = selectedFolders[0]; dgvLangs.Columns.Add(new DataGridViewTextBoxColumn { HeaderText = "Key" }); string[] files = Directory.GetFiles(selectedFolder, "*.lang", SearchOption.AllDirectories); Dictionary <string, Dictionary <string, string> > langs = new Dictionary <string, Dictionary <string, string> >(); string[] keys = File.ReadAllLines(files[0]).Select(line => line.Split(new[] { "|" }, StringSplitOptions.RemoveEmptyEntries)[0]).ToArray(); foreach (string key in keys) { langs.Add(key, new Dictionary <string, string>()); } Array.ForEach(files, f => { string lang = Path.GetFileNameWithoutExtension(f); dgvLangs.Columns.Add(new DataGridViewTextBoxColumn { HeaderText = lang }); foreach (var kvp in langs) { kvp.Value.Add(lang, ""); } }); foreach (string file in files) { string lang = Path.GetFileNameWithoutExtension(file); foreach (string line in File.ReadAllLines(file)) { if (line == "") { continue; } string[] infos = line.Split('|'); string key = infos[0]; string value = infos[1]; langs[key][lang] = value; } } foreach (var kvp in langs) { object[] values = new object[files.Length + 1]; values[0] = kvp.Key; for (int i = 1; i < values.Length; i++) { values[i] = kvp.Value[Path.GetFileNameWithoutExtension(files[i - 1])]; } dgvLangs.Rows.Add(values); } } } }
internal WorkBookClass(string production) { pidCheck = new PidChecker(production); //instantiation for later use in checking keys try { xlApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application; } catch (Exception) { xlApp = new Excel.Application(); } Microsoft.Office.Core.FileDialog fd = this.xlApp.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogOpen); fd.AllowMultiSelect = false; fd.Filters.Clear(); fd.Filters.Add("Excel Files", "*.xls;*.xlsx"); fd.Filters.Add("All Files", "*.*"); fd.Title = "Tax-Aide Update Product Keys"; if (fd.Show() == -1) { try { fd.Execute(); } catch (Exception) { MessageBox.Show("There is a problem opening the excel file.\r\nPlease close any open Excel applications and start the program again."); Environment.Exit(1); } } else { Environment.Exit(1); } xlApp.Visible = true; xlWBook = xlApp.ActiveWorkbook; xlWsheet = xlWBook.ActiveSheet; Excel.Range colHdrsRng = xlWsheet.Range["A1:E1"]; object[,] colHdrsObj = new object[5, 1]; colHdrsObj = colHdrsRng.Value2; var colHdrsStr = colHdrsObj.Cast <string>(); //Debug.WriteLine(string.Join(Environment.NewLine + "\t",colHdrsStr.Select(x => x.ToString()))); var result = colHdrsStr.SequenceEqual(colHdrsExpected); if (!result) { MessageBox.Show("The Column Headers in this spreadsheet do not conform to specification.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys"); DisposeX(); } //First make sure that working range is one area and has no blank rows at the end. if (xlWsheet.UsedRange.Areas.Count != 1) { MessageBox.Show("The used range on this spreadsheet is not contiguous, something is wrong.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys"); DisposeX(); } //xlApp.ScreenUpdating = true; xlRangeWorking = xlWsheet.UsedRange; while (xlApp.WorksheetFunction.CountA(xlRangeWorking.Offset[xlRangeWorking.Rows.Count - 1, 0].Resize[1, Type.Missing]) == 0) {//eliminates blank rows at end of working range from the working range xlRangeWorking = xlRangeWorking.Resize[xlRangeWorking.Rows.Count - 1, Type.Missing]; } resultMessageCell = xlWsheet.Range[xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1], xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1]]; //Console.WriteLine(xlRangeWorking.Row.ToString() + " rowCnt= " + xlRangeWorking.Rows.Count.ToString()); //pull spreadsheet data across for faster performance object[,] sysObj = new object[5, xlRangeWorking.Rows.Count]; sysObj = xlRangeWorking.Value2; rowList = (from idx1 in Enumerable.Range(1, sysObj.GetLength(0)) select new RowData { mMfr = (string)sysObj[idx1, 1], mSerNo = (sysObj[idx1, 2] != null) ? sysObj[idx1, 2].ToString() : "", mOsPK = (string)sysObj[idx1, 3], mStatus = (string)sysObj[idx1, 4], mRsult = (string)sysObj[idx1, 5] }).ToList <RowData>(); rowList.RemoveAt(0); //Remove Column headers from list //Debug.WriteLine(sysData.GetType().ToString() + " count elements " + sysData.Count()); //Debug.WriteLine(sysData.ElementAt(1).mMfr); Debug.WriteLine(string.Join(Environment.NewLine + "\t", from row in rowList select row.mMfr + " " + row.mSerNo)); //Change topic setup status column to receive text strings try { xlWsheet.Range["D1:E1"].ColumnWidth = 40; } catch (Exception e) { MessageBox.Show("Excel appears to be in a mode of not taking programmatic input. Please close all open Excel copies and restart\r\nThe error message was " + e.Message); DisposeX(); } userMessageCell = xlWsheet.Range["D1"]; userMessageCell.Value2 = "Querying the database for these systems"; userMessageCell.Font.Italic = true; userMessageCell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet); }
//导出数据 public static bool ExportDataGridview(DataGridViewX gridView, List <string> lstFields, string defaultName) { //添加进度条 ygc 2012-10-8 SysCommon.CProgress vProgress = new SysCommon.CProgress(); vProgress.ShowDescription = true; vProgress.ShowProgressNumber = true; vProgress.TopMost = true; vProgress.EnableCancel = true; vProgress.EnableUserCancel(true); vProgress.ProgresssValue = 0; vProgress.Step = 1; vProgress.ShowProgress(); vProgress.SetProgress("正在导出" + defaultName + "数据......"); Microsoft.Office.Interop.Excel.Application excel = null; Workbook wb = null; try { if (gridView.Rows.Count == 0) { return(false); } //建立Excel对象 excel = new Microsoft.Office.Interop.Excel.Application(); wb = excel.Application.Workbooks.Add(true); excel.Visible = true; wb.Application.ActiveWindow.Caption = defaultName; //生成字段名称 //for (int i = 0; i < gridView.ColumnCount; i++) //{ // if(!lstFields.Contains(gridView.Columns[i].HeaderText)) continue; // excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText; //} for (int i = 0; i < lstFields.Count; i++) { //if (!lstFields.Contains(gridView.Columns[i].HeaderText)) continue; excel.Cells[1, i + 1] = gridView.Columns[lstFields[i]].HeaderText; } vProgress.MaxValue = gridView.Columns.Count * gridView.Rows.Count; int t = 0; //填充数据 for (int i = 0; i < gridView.RowCount; i++) { for (int j = 0; j < lstFields.Count; j++) { //if (!lstFields.Contains(gridView.Columns[j].HeaderText)) continue; int intFieldIndex = gridView.Columns.IndexOf(gridView.Columns[lstFields[j]]); if (gridView[intFieldIndex, i].ValueType == typeof(string)) { if (gridView[intFieldIndex, i].Value != null) { excel.Cells[i + 2, j + 1] = "'" + gridView[intFieldIndex, i].Value.ToString(); } } else { if (gridView[intFieldIndex, i].Value != null) { excel.Cells[i + 2, j + 1] = gridView[intFieldIndex, i].Value.ToString(); } } t++; vProgress.ProgresssValue = t; } } vProgress.Close(); Microsoft.Office.Core.FileDialog fd = wb.Application.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogSaveAs); fd.InitialFileName = defaultName; int result = fd.Show(); if (result == 0) { return(true); } string fileName = fd.InitialFileName; if (!string.IsNullOrEmpty(fileName)) { if (fileName.IndexOf(".xls") == -1) { fileName += ".xls"; } wb.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } return(true); } catch { vProgress.Close(); return(false); } }