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