/// <summary>
/// this functio create a excel file with the values from a table
/// </summary>
public static string Export(string path, System.Data.DataTable dt)
{
string output = "OK";
try
{
//create a new instance of excel file
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
//first let's extract the columns and create a little header
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "@";
//worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "#";
worksheet.Activate();
int horizontal = 1;
int vertical = 1;
foreach (DataColumn dc in dt.Columns)
{
excel.Cells[vertical, horizontal] = dc.ColumnName;
horizontal++;
}
//go to next row
vertical++;
foreach (DataRow row in dt.Rows)
{
horizontal = 1;
foreach (DataColumn column in dt.Columns)
{
excel.Cells[vertical, horizontal] = row[column];
horizontal++;
}
vertical++;
}
//save the excel file
worksheet.Name = "PROROGHE Non Lavorate";
//DirectoryInfo dr = new DirectoryInfo(path);
//dr.Create();
FileInfo fi = new FileInfo(path);
if (fi.Exists == true)
fi.Delete();
worksheet.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Workbooks.Close();
excel.Quit();
}
catch (Exception ex)
{
output = ex.Message + "\n" + ex.StackTrace;
}
return output;
}
public ExportResult DoExport(string fileName, int userId, ExportType exportType)
{
try
{
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excel.Application xlsApp = new excel.ApplicationClass();
xlsApp.DisplayAlerts = false;
excel.Workbook xlsWorkbook;
excel.Worksheet xlsWorksheet;
excel.Range rng = null;
object missing = System.Reflection.Missing.Value;
xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\WHO_EPIRF_PC_NATDAT.xls"),
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
Country country = demo.GetCountry();
int reportYear = DateTime.Now.Year;
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[1];
AddInfo(xlsWorksheet, rng, country, exportType, ref reportYear);
//xlsApp.Visible = true; //set to 'true' when debbugging, Exec is visible
//xlsApp.DisplayAlerts = true; //enable all the prompt alerts for debug.
DateTime start = new DateTime(reportYear, 1, 1);
DateTime end = new DateTime(reportYear, 12, 31);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[2];
AddLfMm(xlsWorksheet, start, end);
xlsApp.Run("Sheet13.UNIT_LF");
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[3];
AddOncho(xlsWorksheet, start, end);
xlsApp.Run("Sheet17.UNIT_ONCHO");
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[4];
AddSth(xlsWorksheet, start, end);
xlsApp.Run("Sheet15.UNIT_STH");
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[5];
AddSch(xlsWorksheet, start, end);
xlsApp.Run("Sheet16.UNIT_SCH");
xlsWorkbook.SaveAs(fileName, excel.XlFileFormat.xlOpenXMLWorkbook, missing,
missing, false, false, excel.XlSaveAsAccessMode.xlNoChange,
excel.XlSaveConflictResolution.xlUserResolution, true,
missing, missing, missing);
xlsApp.ScreenUpdating = true;
xlsApp.Visible = true;
rng = null;
Marshal.ReleaseComObject(xlsWorksheet);
Marshal.ReleaseComObject(xlsWorkbook);
Marshal.ReleaseComObject(xlsApp);
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
return new ExportResult { WasSuccess = true };
}
catch (Exception ex)
{
return new ExportResult(ex.Message);
}
}
private void btn_XCLOpen_Click(object sender, EventArgs e)
{
XCLFileDialog.ShowDialog();
if (XCLFileDialog.FileName != "")
{
Microsoft.Office.Interop.Excel.ApplicationClass appEXCEL = new Microsoft.Office.Interop.Excel.ApplicationClass();
// create the workbook object by opening the excel file.
//Workbook workBook = appEXCEL.Workbooks.Open(XCLFileDialog.FileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
this.workBook = appEXCEL.Workbooks.Open(XCLFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Get The Active Worksheet Using Sheet Name Or Active Sheet
foreach (Worksheet mySheet in workBook.Sheets)
{
//Worksheet mySheet = (Worksheet)workBook.Sheets.get_Item(i);
comboBox2.Items.Add(mySheet.Name);
}
Worksheet workSheet = (Worksheet)workBook.ActiveSheet;
comboBox2.SelectedItem = workSheet.Name;
lbl_messages.Text = ConfigurationSettings.AppSettings["sheetText1"] + workSheet.Name + ConfigurationSettings.AppSettings["sheetText2"];
toolStripStatusLabel2.Text = ConfigurationSettings.AppSettings["changeSheetText"];
appEXCEL.Quit();
button1.Enabled = true;
button2.Enabled = false;
btCreateXMLFlux.Enabled = true;
button3.Enabled = true;
webBrowser1.DocumentText = ConfigurationSettings.AppSettings["excelReadyText"];
webBrowser2.DocumentText = ConfigurationSettings.AppSettings["excelReadyText"];
}
}
public void Should_detect_row_and_value_changed()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls");
xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//xlWorkSheet.Activate();
xlWorkSheet.Change += new Excel.DocEvents_ChangeEventHandler(xlWorkSheet_Change);
lock (this)
{
Monitor.Wait(this);
}
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void Should_read_value_repeatedly()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
//xlApp.Visible = true;
//xlApp.UserControl = true;
string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls");
xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//xlWorkSheet.Change += new Excel.DocEvents_ChangeEventHandler(xlWorkSheet_Change);
for (int i = 0; i < 100; i++)
{
Console.WriteLine(xlWorkSheet.get_Range("B23", "B23").Value2.ToString());
//xlWorkSheet.
Thread.Sleep(1000);
}
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void Should_read_used_ranges()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
//xlApp.Visible = true;
//xlApp.UserControl = true;
string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls");
xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Range excelRange = xlWorkSheet.UsedRange;
for (int i = 0; i < 10; i++)
{
object[,] valueArray = (object[,])excelRange.get_Value(
XlRangeValueDataType.xlRangeValueDefault);
Console.WriteLine(valueArray[22, 2]);
Thread.Sleep(1000);
}
}
// Read from the Excel workbook.
private void btnRead_Click(object sender, EventArgs e)
{
// Get the Excel application object.
Excel.Application excel_app = new Excel.ApplicationClass();
// Make Excel visible (optional).
excel_app.Visible = true;
// Open the workbook read-only.
Excel.Workbook workbook = excel_app.Workbooks.Open(txtFile.Text,
Type.Missing, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
// Get the first worksheet.
Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets[1];
// Get the titles and values.
SetTitleAndListValues(sheet, 1, 1, lblTitle1, lstItems1);
SetTitleAndListValues(sheet, 1, 2, lblTitle2, lstItems2);
// Close the workbook without saving changes.
workbook.Close(false, Type.Missing, Type.Missing);
// Close the Excel server.
excel_app.Quit();
}
private void btnExport_Click(object sender, EventArgs e)
{
Excel.ApplicationClass excelSheet = new Excel.ApplicationClass();
excelSheet.Application.Workbooks.Add(true);
int columnIndex = 0;
foreach (DataGridViewColumn column in dgridResult.Columns)
{
if (column.HeaderText.Trim() != "" && column.Visible == true)
{
columnIndex++;
excelSheet.Cells[1, columnIndex] = column.HeaderText;
}
}
int rowIndex = 0;
foreach (DataGridViewRow row in dgridResult.Rows)
{
rowIndex++;
columnIndex = 0;
foreach (DataGridViewColumn column in dgridResult.Columns)
{
if (column.HeaderText.Trim() != "" && column.Visible == true)
{
columnIndex++;
excelSheet.Cells[rowIndex + 1, columnIndex] = row.Cells[column.Name].FormattedValue;
}
}
}
excelSheet.Visible = true;
Excel.Worksheet workSheet = (Excel.Worksheet)excelSheet.ActiveSheet;
Excel.Range titleRange = excelSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, columnIndex]);
titleRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
}
private void button3_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Int16 i, j;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (i = 0; i <= dataGridView1.RowCount - 2; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
xlWorkBook.SaveAs(@"C:\Users\Soroush\Desktop\HospitalDatabaseOutputDrug.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
/// <summary>
/// 获取获得当前你选择的Excel Sheet的所有名字
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static string[] GetExcelSheetNames(string filePath)
{
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Workbooks wbs = excelApp.Workbooks;
Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
int count = wb.Worksheets.Count;
string[] names = new string[count];
for (int i = 1; i <= count; i++)
{
names[i - 1] = ((Worksheet)wb.Worksheets[i]).Name;
}
wb.Close(null, null, null);
excelApp.Quit();
wbs.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
excelApp = null;
wbs = null;
wb = null;
return names;
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("csharp.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
static void Main(string[] args)
{
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
app.Workbooks.Add( System.Type.Missing );
app.ActiveCell.set_Value( Excel.XlRangeValueDataType.xlRangeValueDefault, "Hello excel" );
}
public List<ResultRowCell> SearchByFile(string[] listOfWords, SupplierType supplierType, string priceFileName)
{
Application xlApp = new Excel.ApplicationClass();
xlApp.ReferenceStyle = XlReferenceStyle.xlA1;
Workbook xlWorkBook = xlApp.Workbooks.Open(priceFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(supplierType.SheetNumber);
int lastUsedRow = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
//начало поиска
var searchResult = new List<ResultRowCell>();
for (int currentRow = supplierType.ListCell[0].Row; currentRow <= lastUsedRow; currentRow++)
{
var resultRowCell = new ResultRowCell();
resultRowCell.Clear();
for (int iCell = 0; iCell < supplierType.ListCell.Count; iCell++)
{
//для всех ячеек получаем их значения
int currentColumn = supplierType.ListCell[iCell].Col;
//проверка ячейки на существование.
var value = ((Range) xlWorkSheet.Cells[currentRow, currentColumn]).Value;
string result = "";
if (value != null)
result = value.ToString();
else
result = "";
resultRowCell.VSupplierName = supplierType.Name;
resultRowCell.Add(new ResultCell(currentRow, currentColumn, result,
supplierType.ListCell[iCell].Name));
}
//ищем по всем словам, по всем ячейкам
int qty = 0;
if ((qty = ParseRow(resultRowCell, listOfWords)) > 0)
{
//MessageBox.Show(@"Current row: " + currentRow.ToString());
resultRowCell.qty = qty;
searchResult.Add(resultRowCell);
}
}
//конец поиска
xlWorkBook.Close(false, null, null);
xlApp.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
return searchResult;
}
public List<string[]> parseExcel(string path)
{
List<string[]> parsedData = new List<string[]>();
try
{
// string[] row =new string[5];
string singlerow = string.Empty;
//Excel.FillFormat xlfile = new Excel.FillFormat();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
int cCnt = 0;
xlApp = new Excel.ApplicationClass();
// xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
//string[] row = new string[9];
string[] row = new string[range.Columns.Count];
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
try
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
row[cCnt - 1] = str;
}
catch { }
}
parsedData.Add(row);
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
return parsedData;
}
catch
{
return parsedData;
}
}
public void draw_charts()
{
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "Student1";
xlWorkSheet.Cells[1, 3] = "Student2";
xlWorkSheet.Cells[1, 4] = "Student3";
xlWorkSheet.Cells[2, 1] = "Term1";
xlWorkSheet.Cells[2, 2] = "80";
xlWorkSheet.Cells[2, 3] = "65";
xlWorkSheet.Cells[2, 4] = "45";
xlWorkSheet.Cells[3, 1] = "Term2";
xlWorkSheet.Cells[3, 2] = "78";
xlWorkSheet.Cells[3, 3] = "72";
xlWorkSheet.Cells[3, 4] = "60";
xlWorkSheet.Cells[4, 1] = "Term3";
xlWorkSheet.Cells[4, 2] = "82";
xlWorkSheet.Cells[4, 3] = "80";
xlWorkSheet.Cells[4, 4] = "65";
xlWorkSheet.Cells[5, 1] = "Term4";
xlWorkSheet.Cells[5, 2] = "75";
xlWorkSheet.Cells[5, 3] = "82";
xlWorkSheet.Cells[5, 4] = "68";
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
xlWorkbook.SaveAs("testgraph.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkbook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkbook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
private void btn_Clear_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();//实例化Excel对象
object missing = Missing.Value;//获取缺少的object类型值
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
workbook.Password = "";//设置Excel密码为空
MessageBox.Show("密码清除成功！");
excel.Application.DisplayAlerts = false;//不显示提示对话框
workbook.Save();//保存工作表
workbook.Close(false, missing, missing);//关闭工作表
}
public static Workbook GetExcelWorkbook(string sFilePath)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Workbook wb = null;
if (File.Exists(sFilePath))
{
app.Workbooks.Open(sFilePath, (object)0, (object)true, (object)5, (object)"", (object)"", (object)true, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, 0, true, 1, 1);
wb = (Workbook)app.Workbooks.Application.ActiveWorkbook;
}
return wb;
}
public static bool writeExcel(System.Data.DataTable excelTable, string filePath)
{
filePath = Command.Process.root + filePath;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
app.Visible = false;
Workbook wBook = app.Workbooks.Add();
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (excelTable.Rows.Count > 0)
{
int row = 0;
row = excelTable.Rows.Count;
int col = excelTable.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = excelTable.Rows[i][j].ToString();
wSheet.Cells[i + 2, j + 1] = str;
}
}
}
int size = excelTable.Columns.Count;
for (int i = 0; i < size; i++)
{
wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wSheet.SaveAs(filePath);
app.Quit();
//清理内存
System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(app) > 0) ;
wSheet = null;
wBook = null;
app = null;
GC.Collect();
return true;
}
catch (Exception err)
{
Console.WriteLine(err.Message);
return false;
}
}
//Método para cargar un archivo de Excel
static bool excel_init(String ruta)
{
appExcel = new ApplicationClass();
if (System.IO.File.Exists(ruta)){
workBookExl = appExcel.Workbooks.Open(ruta,0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",true, false, 0, true, false, false);
wSheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
return true;
}else{
Console.WriteLine("El documento " + ruta + " No puede abrirse");
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
return false;
}
}
private void btn_Delete_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();//实例化Excel对象
object missing = Missing.Value;//获取缺少的object类型值
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
((Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[cbox_SheetName.Text]).Delete();//删除选择的工作表
MessageBox.Show("工作表删除成功！");
excel.Application.DisplayAlerts = false;//不显示提示对话框
workbook.Save();//保存工作表
CBoxBind();//对下拉列表进行数据绑定
System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL");//实例化进程对象
foreach (System.Diagnostics.Process p in excelProcess)
p.Kill();//关闭进程
}
//Método para cerrar una conexión en excel
static void excel_close()
{
if (appExcel != null){
try{
workBookExl.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
wSheet = null;
}catch (Exception ex){
appExcel = null;
Console.WriteLine("Ocurrieron problemas al intentar liberar los recursos: " + ex.ToString());
}finally{
GC.Collect();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
String sFileImage = "C:\\ImgAppImages\\SAM_5022.jpg";
String sFilePath = "C:\\ImgAppImages\\sam.xls";
if (File.Exists(sFilePath)) { File.Delete(sFilePath); }
Microsoft.Office.Interop.Excel.ApplicationClass objApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Worksheet objSheet = new Microsoft.Office.Interop.Excel.Worksheet();
Microsoft.Office.Interop.Excel.Workbook objWorkBook = null;
//object missing = System.Reflection.Missing.Value;
try
{
objWorkBook = objApp.Workbooks.Add(Type.Missing);
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkBook.ActiveSheet;
//Add picture to single sheet1
objSheet = (Worksheet)objWorkBook.Sheets[1];
objSheet.Name = "Graph with Report";
//////////////
//Or multiple sheets
for (int iSheet = 0; iSheet < objWorkBook.Sheets.Count - 1; iSheet++)
{
//objSheet = objWorkBook.Sheets[iSheet] as Worksheet;
///(objSheet as Microsoft.Office.Interop.Excel._Worksheet).Activate();
}
/////////////////
objSheet.Shapes.AddPicture(sFileImage, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 700, 350);
objWorkBook.SaveAs(sFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
//Error Alert
}
finally
{
objApp.Quit();
objWorkBook = null;
objApp = null;
}
}
// create excel file
private void CreateExcelFile(string sName)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[2, 2] = "Olap Mining Report";
xlWorkBook.SaveAs(sName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
int cCnt = 0;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(textBox1.Text, 0,
true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
// Menampilkan nilai pada cell A10
//MessageBox.Show(xlWorkSheet.get_Range("A10", "A10").Value2.ToString());
//Menampilkan Range
string[,] myArray;
myArray = new string[range.Rows.Count, range.Columns.Count];
for (rCnt = 1; rCnt <= 49; rCnt++) //Baris
{
//for (cCnt = 1; cCnt <= 1; cCnt++) //Kolom
//{
//str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
//MessageBox.Show(str);
ListViewItem item = new ListViewItem((string)(range.Cells[rCnt, 1] as Excel.Range).Value2);
item.SubItems.Add((string)(range.Cells[rCnt, 2] as Excel.Range).Value2);
listView1.Items.Add(item);
//}
}
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void Saveto_Excel_Click()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
// xlApp.Visible = true;
//xlApp.UserControl = true;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[2, 1] = user.LastName;
xlWorkSheet.Cells[3, 1] = "Total Points";
for (int i = 1; i <= Name_Stage.Count; i++)
xlWorkSheet.Cells[1, i + 1] = Name_Stage[i - 1];
for (int j = 1; j <= Student_Stage_Count.Count; j++)
xlWorkSheet.Cells[2, j + 1] = Student_Stage_Count[j - 1].ToString();
for (int j = 1; j <= Total_Stage_Count.Count; j++)
xlWorkSheet.Cells[3, j + 1] = Total_Stage_Count[j - 1].ToString();
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("g3", "a1");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
string temp_name = "Statistic for studet " + user.LastName + " for curriculum " + curriculum.Name + ".xls";
xlWorkBook.SaveAs(temp_name, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
private void ExportByGrid()
{
var fileName = AppCtrl.ExportToExcel(gridViewDetail);
if (fileName == "") return;
// HelpExeExt.OpenFile(fileName);
Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
Excel.Workbook exBook = excelApp.Workbooks.Open(fileName,
0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
Excel.Worksheet exSheet = (Excel.Worksheet) exBook.ActiveSheet;
exSheet.Name = CHUONG_TRINH.TABLE_NAME;
((Excel.Range) exSheet.Cells[1, 1]).EntireRow.Font.Bold = true;
exSheet.Columns.AutoFit();
exSheet.Name = "Competitors";
exBook.Save();
excelApp.Visible = true;
}
static void Main(string[] args)
{
var xlApp = new MSExcel.ApplicationClass();
var xlWorkbooks = xlApp.Workbooks;
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
for (int i = 0; i < 500; i++)
{
var workbook = Path.Combine(Environment.CurrentDirectory, "us_foreign_assistance.xls");
//var workbook = "https://explore.data.gov/download/5gah-bvex/XLS";
var xlWorkBook = xlWorkbooks._Open(workbook, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
var xlWorkSheet = (MSExcel._Worksheet)xlWorkBook.Sheets["Notes"];
xlWorkSheet.Delete();
xlApp.Visible = false;
workbook = Path.Combine(Environment.CurrentDirectory, string.Format("Book-{0}.xls", i));
xlWorkBook.SaveAs(workbook, MSExcel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value,
false, false, MSExcel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
// Commenting this out will cause memory usage to spike.
xlWorkBook.Close(false, Missing.Value, false);
log.InfoFormat("Wrote \"{0}\".", workbook);
// Commenting this out does not seem to affect memory usage.
Marshal.ReleaseComObject(xlWorkBook);
}
xlApp.DisplayAlerts = true;
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkbooks);
Marshal.ReleaseComObject(xlApp);
/*
Console.Write("Press any key to continue . . . ");
Console.ReadKey(false);
Console.WriteLine();
*/
}
public XCLTrans(string excelFilePath)
{
this.myBDOCExcelLine = new BDOCExcelLine();
this.xmlDoc = new XmlDocument();
XmlDeclaration dec = this.xmlDoc.CreateXmlDeclaration("1.0", "ISO-8859-1", null);
this.xmlDoc.AppendChild(dec);
XmlElement rootNode = xmlDoc.CreateElement("import");
rootNode.SetAttribute("xmlns", "http://www.bdoc.com");
rootNode.SetAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");
this.xmlDoc.AppendChild(rootNode);
XmlNode root = xmlDoc.DocumentElement;
XmlElement childNodestreams = xmlDoc.CreateElement("streams");
root.AppendChild(childNodestreams);
this.childNodestream = xmlDoc.CreateElement("stream");
childNodestreams.AppendChild(childNodestream);
childNodestream.SetAttribute("type", "XPATH");
this.childNodeEntities = xmlDoc.CreateElement("entities");
root.AppendChild(childNodeEntities);
this.childNodeData = xmlDoc.CreateElement("datas");
root.AppendChild(childNodeData);
this.appEXCEL = new Microsoft.Office.Interop.Excel.ApplicationClass();
// create the workbook object by opening the excel file.
workBook = appEXCEL.Workbooks.Open(excelFilePath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
// Get The Active Worksheet Using Sheet Name Or Active Sheet
workSheet = (Worksheet)workBook.ActiveSheet;
childNodestream.SetAttribute("name", workSheet.Name);
//************ TXT init
FinalTXTData = "";
FinalTXTEntity = "";
FinalTXTStream = "";
// throw new System.NotImplementedException();
}
//Method to close excel connection
static void excel_close()
{
if (appExcel != null)
{
try
{
newWorkbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
appExcel = null;
objsheet = null;
}
catch (Exception ex)
{
appExcel = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
/// <summary>
/// 创建Excel工作薄
/// </summary>
public void CreateExcel(string path)
{
if (File.Exists(path))
{
return;
}
else
{
Excel.Application excelApp; //Excel应用程序
Excel.Workbook excelDoc; //Excel文档
excelApp = new Excel.ApplicationClass();
Object nothing = Missing.Value;
excelDoc = excelApp.Workbooks.Add(nothing);
Object format = Excel.XlFileFormat.xlWorkbookDefault;
excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing,
Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing);
excelDoc.Close(nothing, nothing, nothing);
excelApp.Quit();
}
}
public static void WriteExcelEx(string fileName, object[,] data)
{
var missing = System.Reflection.Missing.Value;
var application = new ExcelInterop.ApplicationClass();
try
{
var workbook = application.Workbooks.Add(missing);
var worksheet = workbook.ActiveSheet as ExcelInterop.Worksheet;
var rowsCount = data.GetLength(0);
var columnsCount = data.GetLength(1);
var lastCell = GetColumeLetter(columnsCount) + rowsCount;
var range = worksheet.Cells.get_Range("A1", lastCell);
range.NumberFormat = "@";
range.Value2 = data;
var format = fileName.EndsWith("xlsx") ?
ExcelInterop.XlFileFormat.xlOpenXMLWorkbook :
ExcelInterop.XlFileFormat.xlWorkbookNormal;
workbook.SaveAs(fileName, format, missing, missing, missing, missing,
ExcelInterop.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
workbook.Close(true, missing, missing);
application.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(worksheet);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
Marshal.ReleaseComObject(application);
}
}
void Button3Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.ApplicationClass();
Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)xla.ActiveSheet;
xla.Visible = true;
ws.Cells[1, 1] = "NAME";
ws.Cells[1, 2] = "ORDER";
ws.Cells[1, 3] = "PRICE";
ws.Cells[1, 4] = "LOCATE";
ws.Cells[1, 5] = "DATE";
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
}
}
}
static object[,] ReadExcelEx(string fileName)
{
var application = new ExcelInterop.ApplicationClass();
var missing = System.Reflection.Missing.Value;
var workbook = application.Application.Workbooks.Open(fileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
var worksheet = (ExcelInterop.Worksheet)workbook.Worksheets.get_Item(1);
var rowsCount = worksheet.UsedRange.Cells.Rows.Count;
var columnsCount = worksheet.UsedRange.Cells.Columns.Count;
var lastCell = GetColumeLetter(columnsCount) + rowsCount;
var range = worksheet.Cells.get_Range("A1", lastCell);
var ret = range.Value2 as object[, ];
workbook.Close(false, missing, missing);
application.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(application);
return(ret);
}
/// <summary>
///
/// </summary>
public int open(bool isVisible = false)
{
if (!File.Exists(_path))
{
MessageBox.Show("此Excel不存在！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(0);
}
_app = new Microsoft.Office.Interop.Excel.ApplicationClass();
_app.Visible = isVisible;
try
{
_wBook = _app.Workbooks.Open(this._path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
if (_wBook != null)
{
_wBook.Close(true, _path, Type.Missing);
}
//退出excel
_app.Quit();
//释放资源
if (WBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(WBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(_app);
//调用GC的垃圾回收方法
GC.Collect();
GC.WaitForPendingFinalizers();
return(0);
}
_HwndOfApp = _app.Hwnd;
AppManagement.add(_HwndOfApp);
return(_app.Hwnd);
}
public void CreateFiles(bool state)
{
// String[] sheets = GetExcelSheetNames(this.BookName);
String[] sheets = new string[17] {
"Historique&", "Comptes annuels&", "P&", "CmpcWacc&", "CalculFCF&", "DiscountedFCF&", "MéthodesMixtes&", "Multiples&", "TransactionsComparables&", "AutresCapitalisations&", "GordonShapiroBates&", "Goodwill&", "PatrimonialAncAncc&", "ChoixMéthodes&", "SynthèseValorisations&", "APNNE&", "Hist.Refer&"
};
String str;
Directory.CreateDirectory(this.IndexDirectoryPath + "/" + BookName);
String divfilePath = this.IndexDirectoryPath + "/" + this.BookName + "/" + this.BookName + ".index";
CreateDivFileSheets(divfilePath);
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
app.DisplayAlerts = false;
Excel.Workbook xlWorkBook;
if (state)
{
xlWorkBook = app.Workbooks.Open("D:\\ptw\\prefaceNPS.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
else
{
xlWorkBook = app.Workbooks.Open("D:\\ptw\\prefaceNP.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
for (int i = 0; i < sheets.Length; ++i)
{
str = sheets[i].Replace("#", ".");
//if (str.Remove(str.Length - 1, 1) == "Historique" || str.Remove(str.Length - 1, 1) == "Comptes annuels" || str.Remove(str.Length - 1, 1) == "P")
//{
createindexFiles(str.Remove(str.Length - 1, 1), xlWorkBook, state);
//}
}
xlWorkBook.Close();
app.Quit();
releaseObject(xlWorkBook);
releaseObject(app);
}
/// <summary>
/// 创建Excel
/// </summary>
/// <param name="title">标题</param>
/// <param name="fileName">文件名</param>
/// <param name="sheetNames">工作表名</param>
public void CreateExcel(string title, string FilePath, string sheetNames, System.Data.DataTable dt)
{
////待生成的文件名称
//FileInfo fi = new FileInfo(FilePath);
//if (fi.Exists) //判断文件是否已经存在,如果存在就删除!
//{
// fi.Delete();
//}
if (sheetNames != null && sheetNames != "")
{
Excel.Application m_Excel = new Excel.ApplicationClass(); //创建一个Excel对象(同时启动EXCEL.EXE进程)
m_Excel.SheetsInNewWorkbook = 1; //工作表的个数
Excel._Workbook m_Book = (Excel._Workbook)(m_Excel.Workbooks.Add(Missing.Value)); //添加新工作簿
Excel.Worksheet m_Sheet = (Excel.Worksheet)m_Book.Worksheets[1]; //定义ws为工作文档中的第一个sheet
ListToSheet(title, dt, m_Sheet, m_Book, 0);
#region 保存Excel,清除进程
//屏蔽覆盖和询问框
m_Excel.DisplayAlerts = false;
m_Excel.AlertBeforeOverwriting = false;
m_Book.SaveAs(FilePath, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
m_Book.Close(false, Missing.Value, Missing.Value);
m_Excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Excel);
m_Book = null;
m_Sheet = null;
m_Excel = null;
#endregion
}
}
/// <summary>
/// How to create Excel file from XML
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button6_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
DataSet ds = new DataSet();
XmlReader xmlFile;
int i = 0;
int j = 0;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xmlFile = XmlReader.Create("Product.xml", new XmlReaderSettings());
ds.ReadXml(xmlFile);
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
}
}
xlWorkBook.SaveAs("D:\\Codes\\C#\\XmlDemo1\\XmlDemo1\\bin\\Debug\\xml2excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
MessageBox.Show("Done .. ");
}
private void saveAsExcel_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog savedialog = new SaveFileDialog();
savedialog.Filter = "Excel|*.xlsx|所有文件|*.*";
savedialog.DefaultExt = "xlsx";
savedialog.AddExtension = true;
savedialog.OverwritePrompt = true;
savedialog.Title = "导出";
savedialog.ValidateNames = true;
if (savedialog.ShowDialog().Value)
{
MSEXCEL.Application excelApp;
MSEXCEL.Workbook excelDoc;
excelApp = new MSEXCEL.ApplicationClass();
excelDoc = excelApp.Workbooks.Add(System.Type.Missing);
MSEXCEL.Worksheet excelWS = (MSEXCEL.Worksheet)excelDoc.Worksheets[1];
for (int i = 0; i < tbl.Rows.Count; i++)
{
for (int j = 0; j < tbl.Columns.Count; j++)
{
excelWS.Cells[i + 1, j + 1] = tbl.Rows[i][j].ToString();
}
}
excelDoc.SaveAs(savedialog.FileName);
excelDoc.Close();
excelApp.Quit();
MessageBox.Show("文件已导出");
}
}
private void sideBar_BtnDataFormat_Click(object sender, EventArgs e)
{
//Microsoft.Office.Interop.Excel.Application
//Object filename = "页岩气选区评价系统软件使用说明.docx";
try
{
Object filefullname = System.Windows.Forms.Application.StartupPath + "\\Standard Data Format_Example.xlsx";
if (!File.Exists((string)filefullname))
{
MessageBox.Show("源文件不存在或已被删除。", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
MsExcel.Application excel = new MsExcel.ApplicationClass(); //引用Excel对象
MsExcel.Workbook book = excel.Application.Workbooks.Add(filefullname);
//引用Excel工作簿
excel.Visible = true; //使Excel可视
}
catch
{
MessageBox.Show("Office程序调用出错，请确认是否安装了Office2013或更新的版本。", "信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
/// <summary>
/// 构造函数，将一个已有Excel工作簿作为模板（模板化导入数据），并指定模板类型
/// Created:20170531(ChengMengjia)
/// </summary>
/// <param name="templetFilePath"></param>
/// <param name="Type"></param>
public ExcelHelper(string templetFilePath, int templetType)
{
if (string.IsNullOrEmpty(templetFilePath))
{
throw new Exception("Excel模板文件路径不能为空！");
}
if (!File.Exists(templetFilePath))
{
throw new Exception("指定路径的Excel模板文件不存在！");
}
this.templetFile = templetFilePath;
excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false; //是否需要显示提示
excelApp.AlertBeforeOverwriting = false; //是否弹出提示覆盖
//打开模板文件，得到WorkBook对象
workBook = excelApp.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing);
//得到WorkSheet对象
workSheet = workBook.Worksheets[1];
}
/// <summary>
/// DataGridView导出Excel
/// </summary>
/// <param name="strCaption">Excel文件中的标题</param>
/// <param name="myDGV">DataGridView 控件</param>
/// <returns>0:成功;1:DataGridView中无记录;2:Excel无法启动;9999:异常错误</returns>
public int ExportToExcel(string strCaption, DataGridView myDGV)
{
int result = 9999;
//保存
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
//saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
if (saveFileDialog.FileName == "")
{
MessageBox.Show("请输入保存文件名！");
saveFileDialog.ShowDialog();
}
// 列索引，行索引，总列数，总行数
int intColumsCount = 0;
for (int i = 0; i < myDGV.ColumnCount; i++)
{
if (myDGV.Columns[i].Visible)
{
intColumsCount++;
}
}
int ColIndex = 0;
int dgColIndex = 0;
int RowIndex = 0;
int ColCount = intColumsCount;
int RowCount = myDGV.RowCount;
if (myDGV.RowCount == 0)
{
result = 1;
}
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
result = 2;
}
try
{
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //标题所占的单元格数与DataGridView中的列数相同
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = strCaption;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, ColCount];
//获取列标题
foreach (DataGridViewColumn col in myDGV.Columns)
{
if (col.Visible)
{
objData[RowIndex, ColIndex++] = col.HeaderText;
}
}
// 获取数据
for (RowIndex = 1; RowIndex <= RowCount; RowIndex++)
{
dgColIndex = 0;
for (ColIndex = 0; ColIndex < ColCount; ColIndex++)
{
for (; dgColIndex < myDGV.Columns.Count; dgColIndex++)
{
if (!myDGV.Columns[dgColIndex].Visible)
{
continue;
}
else
{
if (myDGV[dgColIndex, RowIndex - 1].ValueType == typeof(string) ||
myDGV[dgColIndex, RowIndex - 1].ValueType == typeof(DateTime)) //这里就是验证DataGridView单元格中的类型,如果是string或是DataTime类型,则在放入缓存时在该内容前加入" ";
{
if (myDGV.Rows[RowIndex - 1].Cells[dgColIndex] is DataGridViewCheckBoxCell)
{
DataGridViewCheckBoxCell dc = new DataGridViewCheckBoxCell();
bool isChecked = Convert.ToBoolean(dc.Value);
if (isChecked)
{
objData[RowIndex, ColIndex] = "是";
}
else
{
objData[RowIndex, ColIndex] = "否";
}
}
else
{
objData[RowIndex, ColIndex] = "'" + myDGV[dgColIndex, RowIndex - 1].FormattedValue;
}
//objData[RowIndex, ColIndex] = "'" + myDGV[dgColIndex, RowIndex - 1].Value;
}
else
{
//if (myDGV.Rows[RowIndex].Cells[dgColIndex] is DataGridViewCheckBoxCell)
//{
// DataGridViewCheckBoxCell dc = new DataGridViewCheckBoxCell();
// bool isChecked=Convert.ToBoolean(dc.Value);
// if (isChecked)
// objData[RowIndex, ColIndex] = "是";
// else
// objData[RowIndex, ColIndex] = "否";
//}
//else
objData[RowIndex, ColIndex] = myDGV[dgColIndex, RowIndex - 1].Value;
}
dgColIndex++;
break;
}
}
}
System.Windows.Forms.Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, ColCount]);
range.Value2 = objData;
xlBook.Saved = true;
xlBook.SaveCopyAs(saveFileDialog.FileName);
}
catch (Exception err)
{
result = 9999;
}
finally
{
xlApp.Quit();
GC.Collect(); //强制回收
mes.Show("导出成功!");
}
//返回值
result = 0;
}
return(result);
}
public static string CreateEmailXlsFile(System.Data.DataTable dtSkpFld, System.Data.DataTable dt5, System.Data.DataTable dt3, System.Data.DataTable dt4)
{
//this.Cursor = Cursors.WaitCursor;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
/*
* string EmailBody1 = "";// HeaderDT.Rows[0]["EMAIL_HEADER"].ToString() + "\r\n";
* for (int i = 0; i < ValuesDT.Columns.Count; i++)
* {
* EmailBody1 += ValuesDT.Rows[0][i].ToString() + HeaderDT.Rows[0]["EMAIL_FILE_SEPARATOR"].ToString();
* }
* // EmailBody1 += "\r\n" + HeaderDT.Rows[0]["EMAIL_FOOTER"].ToString();
*/
string EmailBody1 = ""; //= dt3.Rows[0]["EMAIL_HEADER"].ToString() + dt3.Rows[0]["EMAIL_FILE_SEPARATOR"].ToString() ;
int i, c = 0;
for (int k = 0; k < dtSkpFld.Rows.Count; k++)
{
for (i = 0; i < dt4.Rows.Count; i++)
{
string temp = "";
if (dtSkpFld.Rows[k]["col"].ToString() == dt4.Rows[i]["col"].ToString())
{
if (dt4.Rows[i]["description"].ToString().ToUpper().Contains("DATE") && dt3.Rows[0]["DATE_FORMAT"].ToString().Trim() != "" && dt5.Rows[0][i].ToString().Trim() != "")
{
string date = Convert.ToDateTime(dt5.Rows[0][i].ToString()).ToString(dt3.Rows[0]["DATE_FORMAT"].ToString().Trim());
excel.Cells[1, c + 1] = date;
c++;
}
else if (dt4.Rows[i]["description"].ToString().ToUpper().Contains("AMOUNT") && dt5.Rows[0][i].ToString().Trim() != "")
{
if (dt3.Rows[0]["THOUSAND_SEPARATOR"].ToString() == "1" && dt3.Rows[0]["SHOW_DECIMAL"].ToString() == "1")
{
string amount = Convert.ToDouble(dt5.Rows[0][i].ToString()).ToString("#,#.00");
excel.Cells[1, c + 1] = amount;
c++;
}
else if (dt3.Rows[0]["THOUSAND_SEPARATOR"].ToString() == "1" && dt3.Rows[0]["SHOW_DECIMAL"].ToString() == "0")
{
string amount = Convert.ToDouble(dt5.Rows[0][i].ToString()).ToString("#,#");
excel.Cells[1, c + 1] = amount;
c++;
}
else if (dt3.Rows[0]["THOUSAND_SEPARATOR"].ToString() == "0" && dt3.Rows[0]["SHOW_DECIMAL"].ToString() == "1")
{
string amount = Convert.ToDouble(dt5.Rows[0][i].ToString()).ToString("#.00");
excel.Cells[1, i + 1] = amount;
c++;
}
else
{
string amount = Convert.ToDouble(dt5.Rows[0][i].ToString()).ToString("#");
excel.Cells[1, c + 1] = amount;
c++;
}
}
else
{
excel.Cells[1, c + 1] = dt5.Rows[0][i].ToString();
c++;
}
}
else if (dtSkpFld.Rows[k]["col"].ToString() == "SKIP_FIELD")
{
if (dt4.Rows[i]["COL_LENGTH"] != System.DBNull.Value)
{
if (Convert.ToInt32(dtSkpFld.Rows[k]["COL_LENGTH"]) > 0)
{
if (dtSkpFld.Rows[k]["FIX_WORD"].ToString() != "")
{
temp = dtSkpFld.Rows[k]["FIX_WORD"].ToString().PadRight(Convert.ToInt32(dtSkpFld.Rows[k]["COL_LENGTH"]));
excel.Cells[1, c + 1] = temp;
i = dt4.Rows.Count;
c++;
}
else
{
temp = temp.PadRight(Convert.ToInt32(dtSkpFld.Rows[k]["COL_LENGTH"]));
excel.Cells[1, c + 1] = temp;
i = dt4.Rows.Count;
c++;
}
}
}
else
{
if (dtSkpFld.Rows[k]["FIX_WORD"].ToString() != "")
{
excel.Cells[1, c + 1] = dtSkpFld.Rows[k]["FIX_WORD"].ToString();
i = dt4.Rows.Count;
c++;
}
else
{
excel.Cells[1, c + 1] = "";
i = dt4.Rows.Count;
c++;
}
}
//temp = Convert.ToInt32(dt4.Rows[i]["COL_LENGTH"]) > 0 ? dt5.Rows[j][i].ToString().PadRight(Convert.ToInt32(dt4.Rows[i]["COL_LENGTH"])) : dt5.Rows[j][i].ToString().Substring(0, Convert.ToInt32(dt4.Rows[i]["COL_LENGTH"]));
//EmailBody += "\n<tr><td><b>" + dtSkpFld.Rows[k]["col"].ToString() + "</b></td><td><b>" + temp + "</b></td></tr>";
}
}
}
string RetValue;
RetValue = DateTime.Now.ToString("dd-MM-yyyy_HHmmss");
string FilePath = AppDomain.CurrentDomain.BaseDirectory + RetValue + ".XLS";
if (File.Exists(FilePath))
{
File.Delete(FilePath);
worksheet.SaveAs(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//this.Cursor = Cursors.Arrow;
excel.Quit();
}
else
{
worksheet.SaveAs(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//this.Cursor = Cursors.Arrow;
excel.Quit();
}
return(FilePath);
}
public static void CreateFTPXlsFile(System.Data.DataTable dtSkpFld, System.Data.DataTable dt5, System.Data.DataTable dt3, System.Data.DataTable dt4, string FilePath, int j)
{
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
//= dt3.Rows[0]["EMAIL_HEADER"].ToString() + dt3.Rows[0]["EMAIL_FILE_SEPARATOR"].ToString() ;
int i, c = 0;
for (int k = 0; k < dtSkpFld.Rows.Count; k++)
{
for (i = 0; i < dt4.Rows.Count; i++)
{
string temp = "";
if (dtSkpFld.Rows[k]["col"].ToString() == dt4.Rows[i]["col"].ToString())
{
if (dt4.Rows[i]["description"].ToString().ToUpper().Contains("DATE") && dt3.Rows[0]["DATE_FORMAT"].ToString().Trim() != "" && dt5.Rows[0][i].ToString().Trim() != "")
{
string date = Convert.ToDateTime(dt5.Rows[0][i].ToString()).ToString(dt3.Rows[0]["DATE_FORMAT"].ToString().Trim());
excel.Cells[1, c + 1] = date;
c++;
}
else if (dt4.Rows[i]["description"].ToString().ToUpper().Contains("AMOUNT") && dt5.Rows[0][i].ToString().Trim() != "")
{
if (dt3.Rows[0]["THOUSAND_SEPARATOR"].ToString() == "1" && dt3.Rows[0]["SHOW_DECIMAL"].ToString() == "1")
{
string amount = Convert.ToDouble(dt5.Rows[0][i].ToString()).ToString("#,#.00");
excel.Cells[1, c + 1] = amount;
c++;
}
else if (dt3.Rows[0]["THOUSAND_SEPARATOR"].ToString() == "1" && dt3.Rows[0]["SHOW_DECIMAL"].ToString() == "0")
{
string amount = Convert.ToDouble(dt5.Rows[0][i].ToString()).ToString("#,#");
excel.Cells[1, c + 1] = amount;
c++;
}
else if (dt3.Rows[0]["THOUSAND_SEPARATOR"].ToString() == "0" && dt3.Rows[0]["SHOW_DECIMAL"].ToString() == "1")
{
string amount = Convert.ToDouble(dt5.Rows[0][i].ToString()).ToString("#.00");
excel.Cells[1, i + 1] = amount;
c++;
}
else
{
string amount = Convert.ToDouble(dt5.Rows[0][i].ToString()).ToString("#");
excel.Cells[1, c + 1] = amount;
c++;
}
}
else
{
excel.Cells[1, c + 1] = dt5.Rows[0][i].ToString();
c++;
}
}
else if (dtSkpFld.Rows[k]["col"].ToString() == "SKIP_FIELD")
{
if (dt4.Rows[i]["COL_LENGTH"] != System.DBNull.Value)
{
if (Convert.ToInt32(dtSkpFld.Rows[k]["COL_LENGTH"]) > 0)
{
if (dtSkpFld.Rows[k]["FIX_WORD"].ToString() != "")
{
temp = dtSkpFld.Rows[k]["FIX_WORD"].ToString().PadRight(Convert.ToInt32(dtSkpFld.Rows[k]["COL_LENGTH"]));
excel.Cells[1, c + 1] = temp;
i = dt4.Rows.Count;
c++;
}
else
{
temp = temp.PadRight(Convert.ToInt32(dtSkpFld.Rows[k]["COL_LENGTH"]));
excel.Cells[1, c + 1] = temp;
i = dt4.Rows.Count;
c++;
}
}
}
else
{
if (dtSkpFld.Rows[k]["FIX_WORD"].ToString() != "")
{
excel.Cells[1, c + 1] = dtSkpFld.Rows[k]["FIX_WORD"].ToString();
i = dt4.Rows.Count;
c++;
}
else
{
excel.Cells[1, c + 1] = "";
i = dt4.Rows.Count;
c++;
}
}
//temp = Convert.ToInt32(dt4.Rows[i]["COL_LENGTH"]) > 0 ? dt5.Rows[j][i].ToString().PadRight(Convert.ToInt32(dt4.Rows[i]["COL_LENGTH"])) : dt5.Rows[j][i].ToString().Substring(0, Convert.ToInt32(dt4.Rows[i]["COL_LENGTH"]));
//EmailBody += "\n<tr><td><b>" + dtSkpFld.Rows[k]["col"].ToString() + "</b></td><td><b>" + temp + "</b></td></tr>";
}
}
}
//for (int i = 0; i < dt2.Rows.Count; i++)
//{
// excel.Cells[1, i + 1] = dt3.Rows[j][i].ToString() + dt1.Rows[0]["FTP_FILE_SEPARATOR"].ToString();
//}
if (File.Exists(FilePath))
{
File.Delete(FilePath);
worksheet.SaveAs(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excel.Quit();
}
else
{
worksheet.SaveAs(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excel.Quit();
}
}
private void lnkexporttoexcel_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
Excel.Range cells = xlWorkSheet.Cells;
for (i = 1; i <= gdvmeapresult.ColumnCount; i++)
{
string column = gdvmeapresult.Columns[i - 1].Name;
xlWorkSheet.Cells[2, i + 1] = column;
cells = (Excel.Range)xlWorkSheet.Cells[2, i + 1];
cells.Font.Bold = true;
cells.Borders.Weight = Excel.XlBorderWeight.xlMedium;
cells.ColumnWidth = 15;
}
for (i = 2; i <= gdvmeapresult.RowCount + 1; i++)
{
for (j = 2; j <= gdvmeapresult.ColumnCount + 1; j++)
{
DataGridViewCell cell = gdvmeapresult[j - 2, i - 2];
xlWorkSheet.Cells[i + 1, j] = Convert.ToString(cell.Value);
////format cells
if (j > 2)
{
Color bgc = Color.Pink;
Color fgc = Color.Black;
if (cell.Value.ToString() == "-")//data not available
{
bgc = Color.Gray;
}
else
{
decimal mapevalue = decimal.Parse(cell.Value.ToString());
if (mapevalue > 25)
{
bgc = Color.Red; //Color.Black;//(204, 51, 51);
}
if (mapevalue < -25)
{
bgc = Color.Blue;//(114, 154, 210);
}
if (mapevalue <= 25 && mapevalue >= -25)
{
bgc = Color.White;
}
}
((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Interior.Color = System.Drawing.ColorTranslator.ToOle(bgc);
((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Font.Color = System.Drawing.ColorTranslator.ToOle(fgc);
}
((Excel.Range)xlWorkSheet.Cells[i + 1, j]).Borders.Weight = Excel.XlBorderWeight.xlThin;
//end cell format
}
}
//last row range
Excel.Range range = xlWorkSheet.get_Range(xlWorkSheet.Cells[i + 1, 2], xlWorkSheet.Cells[i + 1, gdvmeapresult.ColumnCount + 1]);
range.Merge(true);
range.RowHeight = 40;
range.WrapText = true;
xlWorkSheet.Cells[i + 1, 2] = "Note: Red represents underforecasts (>25%), Blue represents overforecasts (<-25%)," +
"Gray represents insufficient data to complete the forecast, and no field color represents an accurate forecast, within 25%.";
//header
Excel.Range header = xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 1]);
header = header.EntireRow;
for (int no = 0; no < 5; no++)
{
header.Insert(Excel.XlInsertShiftDirection.xlShiftDown, misValue);
}
header = xlWorkSheet.get_Range(xlWorkSheet.Cells[2, 2], xlWorkSheet.Cells[2, gdvmeapresult.ColumnCount + 1]);
header.Value = "Forecast MAPE Result";
header.Merge(true);
header.Font.Underline = true;
header.Font.Bold = true;
header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
header.RowHeight = 40;
header.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.Cells[3, 2] = "Forecast Id : ";
xlWorkSheet.Cells[3, 3] = forecastInfo.ForecastNo;
xlWorkSheet.Cells[3, 4] = "Methodology : ";
xlWorkSheet.Cells[3, 5] = forecastInfo.Methodology;
xlWorkSheet.Cells[3, 6] = "Wastage % : ";
xlWorkSheet.Cells[3, 7] = forecastInfo.Westage.ToString();
xlWorkSheet.Cells[4, 2] = "Start Date : ";
xlWorkSheet.Cells[4, 3] = forecastInfo.StartDate.ToShortDateString();
xlWorkSheet.Cells[4, 4] = "Data Usage : ";
xlWorkSheet.Cells[4, 5] = forecastInfo.DataUsage;
xlWorkSheet.Cells[4, 6] = "Add By % : ";
xlWorkSheet.Cells[4, 7] = forecastInfo.Scaleup.ToString();
xlWorkSheet.Cells[5, 2] = "Period : ";
xlWorkSheet.Cells[5, 3] = forecastInfo.Period;
xlWorkSheet.Cells[5, 4] = "Regression Type : ";
xlWorkSheet.Cells[5, 5] = forecastInfo.Method;
xlWorkSheet.Cells[5, 6] = "Extension Period : ";
xlWorkSheet.Cells[5, 7] = forecastInfo.Extension.ToString();
//header end
saveFileDialog1.FileName = forecastInfo.ForecastNo + ".xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
xlWorkBook.SaveAs(saveFileDialog1.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
LqtUtil.releaseObject(xlWorkSheet);
LqtUtil.releaseObject(xlWorkBook);
LqtUtil.releaseObject(xlApp);
MessageBox.Show("Exported Successfully!");
}
}
//------------------------------------------------------------------------------
// Explicit Activation
// This entry point is used to activate the application explicitly
//------------------------------------------------------------------------------
public static int Main(string[] args)
{
int retValue = 0;
try
{
theProgram = new Program();
Part workPart = theSession.Parts.Work;
Part displayPart = theSession.Parts.Display;
bool status;
DefineVariable.Is_Local = Environment.GetEnvironmentVariable("UGII_ENV_FILE");
if (DefineVariable.Is_Local != "")
{
//取得本機ShopDoc.xls路徑
DefineVariable.SelfCheckPath = string.Format(@"{0}\{1}\{2}", Path.GetDirectoryName(displayPart.FullPath), "MODEL", "SelfCheck.xls");
}
//取得料號
string PartNo = Path.GetFileNameWithoutExtension(displayPart.FullPath);
//取得日期
string CurrentDate = DateTime.Now.ToShortDateString();
int SheetCount = 0;
NXOpen.Tag[] SheetTagAry = null;
theUfSession.Draw.AskDrawings(out SheetCount, out SheetTagAry);
DefineVariable.DicDimenData = new Dictionary <string, TextData>();
for (int i = 0; i < SheetCount; i++)
{
//打開Sheet並記錄所有OBJ
NXOpen.Drawings.DrawingSheet CurrentSheet = (NXOpen.Drawings.DrawingSheet)NXObjectManager.Get(SheetTagAry[i]);
CurrentSheet.Open();
if (i == 0)
{
//記錄第一張Sheet
DefineVariable.FirstDrawingSheet = CurrentSheet;
}
DisplayableObject[] SheetObj = CurrentSheet.View.AskVisibleObjects();
foreach (DisplayableObject singleObj in SheetObj)
{
TextData cTextData = new TextData();
string singleObjType = singleObj.GetType().ToString();
string SelfCheck_Gauge = "", BallonNum = "", Frequency = "", Location = "";
string[] mainText;
string[] dualText;
#region 取SelfCheck共用屬性(泡泡值、檢具名稱、檢驗頻率、泡泡所在區域)，如果都沒屬性就找下一個
try
{
SelfCheck_Gauge = singleObj.GetStringAttribute(CaxME.DimenAttr.IPQC_Gauge);
BallonNum = singleObj.GetStringAttribute(CaxME.DimenAttr.BallonNum);
Frequency = singleObj.GetStringAttribute(CaxME.DimenAttr.IPQC_Freq);
Location = singleObj.GetStringAttribute(CaxME.DimenAttr.BallonLocation);
}
catch (System.Exception ex)
{
SelfCheck_Gauge = "";
}
if (SelfCheck_Gauge == "")
{
continue;
}
#endregion
#region 紀錄共用屬性(泡泡值、檢具名稱、檢驗頻率、泡泡所在區域)
//取得泡泡值
cTextData.BallonNum = BallonNum;
//取得檢具名稱
cTextData.Gauge = SelfCheck_Gauge;
//取得檢驗頻率
cTextData.Frequency = Frequency;
//取得泡泡所在區域
cTextData.Location = Location;
#endregion
if (singleObjType == "NXOpen.Annotations.VerticalDimension")
{
#region VerticalDimension取Text
cTextData.Type = "NXOpen.Annotations.VerticalDimension";
NXOpen.Annotations.VerticalDimension temp = (NXOpen.Annotations.VerticalDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.PerpendicularDimension")
{
#region PerpendicularDimension取Text
cTextData.Type = "NXOpen.Annotations.PerpendicularDimension";
NXOpen.Annotations.PerpendicularDimension temp = (NXOpen.Annotations.PerpendicularDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.MinorAngularDimension")
{
#region MinorAngularDimension取Text
cTextData.Type = "NXOpen.Annotations.MinorAngularDimension";
NXOpen.Annotations.MinorAngularDimension temp = (NXOpen.Annotations.MinorAngularDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.RadiusDimension")
{
#region MinorAngularDimension取Text
cTextData.Type = "NXOpen.Annotations.RadiusDimension";
NXOpen.Annotations.RadiusDimension temp = (NXOpen.Annotations.RadiusDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.HorizontalDimension")
{
#region HorizontalDimension取Text
cTextData.Type = "NXOpen.Annotations.HorizontalDimension";
NXOpen.Annotations.HorizontalDimension temp = (NXOpen.Annotations.HorizontalDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.IdSymbol")
{
#region IdSymbol取Text
cTextData.Type = "NXOpen.Annotations.IdSymbol";
NXOpen.Annotations.IdSymbol temp = (NXOpen.Annotations.IdSymbol)singleObj;
#endregion
}
else if (singleObjType == "NXOpen.Annotations.Note")
{
#region Note取Text
cTextData.Type = "NXOpen.Annotations.Note";
NXOpen.Annotations.Note temp = (NXOpen.Annotations.Note)singleObj;
cTextData.MainText = temp.GetText()[0];
#endregion
}
else if (singleObjType == "NXOpen.Annotations.DraftingFcf")
{
#region DraftingFcf取Text
NXOpen.Annotations.DraftingFcf temp = (NXOpen.Annotations.DraftingFcf)singleObj;
CaxME.FcfData sFcfData = new CaxME.FcfData();
CaxME.GetFcfData(temp, out sFcfData);
cTextData.Type = "NXOpen.Annotations.DraftingFcf";
cTextData.Characteristic = sFcfData.Characteristic;
cTextData.ZoneShape = sFcfData.ZoneShape;
cTextData.ToleranceValue = sFcfData.ToleranceValue;
cTextData.MaterialModifier = sFcfData.MaterialModifier;
cTextData.PrimaryDatum = sFcfData.PrimaryDatum;
cTextData.PrimaryMaterialModifier = sFcfData.PrimaryMaterialModifier;
cTextData.SecondaryDatum = sFcfData.SecondaryDatum;
cTextData.SecondaryMaterialModifier = sFcfData.SecondaryMaterialModifier;
cTextData.TertiaryDatum = sFcfData.TertiaryDatum;
cTextData.TertiaryMaterialModifier = sFcfData.TertiaryMaterialModifier;
#endregion
}
else if (singleObjType == "NXOpen.Annotations.Label")
{
#region Label取Text
cTextData.Type = "NXOpen.Annotations.Label";
NXOpen.Annotations.Label temp = (NXOpen.Annotations.Label)singleObj;
cTextData.MainText = temp.GetText()[0];
#endregion
}
else if (singleObjType == "NXOpen.Annotations.DraftingDatum")
{
#region DraftingDatum取Text
cTextData.Type = "NXOpen.Annotations.DraftingDatum";
NXOpen.Annotations.DraftingDatum temp = (NXOpen.Annotations.DraftingDatum)singleObj;
#endregion
}
else if (singleObjType == "NXOpen.Annotations.DiameterDimension")
{
#region DiameterDimension取Text
cTextData.Type = "NXOpen.Annotations.DiameterDimension";
NXOpen.Annotations.DiameterDimension temp = (NXOpen.Annotations.DiameterDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.AngularDimension")
{
#region AngularDimension取Text
cTextData.Type = "NXOpen.Annotations.AngularDimension";
NXOpen.Annotations.AngularDimension temp = (NXOpen.Annotations.AngularDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.CylindricalDimension")
{
#region CylindricalDimension取Text
cTextData.Type = "NXOpen.Annotations.CylindricalDimension";
NXOpen.Annotations.CylindricalDimension temp = (NXOpen.Annotations.CylindricalDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
if (temp.ToleranceType.ToString() == "BilateralTwoLines")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
if (temp.ToleranceType.ToString() == "UnilateralAbove")
{
cTextData.TolType = "UnilateralAbove";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = "0";
}
if (temp.ToleranceType.ToString() == "UnilateralBelow")
{
cTextData.TolType = "UnilateralBelow";
cTextData.UpperTol = "0";
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.ChamferDimension")
{
#region ChamferDimension取Text
cTextData.Type = "NXOpen.Annotations.ChamferDimension";
NXOpen.Annotations.ChamferDimension temp = (NXOpen.Annotations.ChamferDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0] + "X" + "45" + "<$s>";
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
if (temp.ToleranceType.ToString() == "BilateralTwoLines")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
if (temp.ToleranceType.ToString() == "UnilateralAbove")
{
cTextData.TolType = "UnilateralAbove";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = "0";
}
if (temp.ToleranceType.ToString() == "UnilateralBelow")
{
cTextData.TolType = "UnilateralBelow";
cTextData.UpperTol = "0";
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
#endregion
}
//計算泡泡總數
DefineVariable.BallonCount++;
DefineVariable.DicDimenData[BallonNum] = cTextData;
}
}
//設定輸出路徑--Local
//string[] FolderFile = System.IO.Directory.GetFileSystemEntries(Path.GetDirectoryName(displayPart.FullPath), "*.xls");
//string OutputPath = string.Format(@"{0}\{1}", Path.GetDirectoryName(displayPart.FullPath),
// Path.GetFileNameWithoutExtension(displayPart.FullPath) + "_" + "SelfCheck" + "_" + (FolderFile.Length + 1) + ".xls");
//設定輸出路徑--Server
string OperNum = Regex.Replace(Path.GetFileNameWithoutExtension(displayPart.FullPath).Split('_')[1], "[^0-9]", "");
string Local_Folder_OIS = string.Format(@"{0}\{1}\{2}", Path.GetDirectoryName(displayPart.FullPath), "OP" + OperNum, "OIS");
if (!File.Exists(Local_Folder_OIS))
{
System.IO.Directory.CreateDirectory(Local_Folder_OIS);
}
string[] FolderFile = System.IO.Directory.GetFileSystemEntries(Local_Folder_OIS, "*.xls");
List <string> ListFolderFileWithIPQC = new List <string>();
foreach (string i in FolderFile)
{
if (i.Contains("SelfCheck"))
{
ListFolderFileWithIPQC.Add(i);
}
}
string OutputPath = string.Format(@"{0}\{1}", Local_Folder_OIS,
Path.GetFileNameWithoutExtension(displayPart.FullPath) + "_" + "SelfCheck" + "_" + (ListFolderFileWithIPQC.Count + 1) + ".xls");
//檢查PC有無Excel在執行
foreach (var item in Process.GetProcesses())
{
if (item.ProcessName == "EXCEL")
{
CaxLog.ShowListingWindow("請先關閉所有Excel再重新執行輸出，如沒有EXCEL在執行，請開啟工作管理員關閉背景EXCEL");
return(retValue);
}
else
{
continue;
}
}
Excel.ApplicationClass x = new Excel.ApplicationClass();
Excel.Workbook book = null;
Excel.Worksheet sheet = null;
Excel.Range oRng = null;
try
{
x.Visible = false;
if (DefineVariable.Is_Local != "")
{
if (File.Exists(DefineVariable.SelfCheckPath))
{
book = x.Workbooks.Open(DefineVariable.SelfCheckPath);
}
else
{
book = x.Workbooks.Open(@"D:\SelfCheck.xls");
}
}
else
{
book = x.Workbooks.Open(@"D:\SelfCheck.xls");
}
sheet = (Excel.Worksheet)book.Sheets[1];
//由檢驗總數開啟符合總數的頁數
int needSheetNo = (DefineVariable.BallonCount / 11);
int needSheetNo_Reserve = (DefineVariable.BallonCount % 11);
if (needSheetNo_Reserve != 0)
{
needSheetNo++;
}
for (int i = 1; i < needSheetNo; i++)
{
sheet.Copy(System.Type.Missing, x.Workbooks[1].Worksheets[1]);
}
//更改每一個Sheet的名稱與頁數
for (int i = 0; i < book.Worksheets.Count; i++)
{
sheet = (Excel.Worksheet)book.Sheets[i + 1];
if (i == 0 && book.Worksheets.Count > 1)
{
sheet.Name = PartNo;
oRng = (Excel.Range)sheet.Cells[4, 5];
oRng.Value = oRng.Value.ToString().Replace("1/1", "1/" + (book.Worksheets.Count).ToString());
}
else
{
sheet.Name = PartNo + "(" + (i + 1) + ")";
oRng = (Excel.Range)sheet.Cells[4, 5];
string temp = (i + 1).ToString();
oRng.Value = oRng.Value.ToString().Replace("1/1", temp + "/" + (book.Worksheets.Count).ToString());
}
}
//填表
int ExcelSequenceNo = -1;
for (int i = 1; i < 1000; i++)
{
ExcelSequenceNo++;
TextData cTextData;
DefineVariable.DicDimenData.TryGetValue(i.ToString(), out cTextData);
if (cTextData == null)
{
ExcelSequenceNo--;
continue;
}
RowColumn sRowColumn;
DefineVariable.GetExcelRowColumn(ExcelSequenceNo, out sRowColumn);
int currentSheet_Value = (ExcelSequenceNo / 11);
int currentSheet_Reserve = (ExcelSequenceNo % 11);
if (currentSheet_Value == 0)
{
sheet = (Excel.Worksheet)book.Sheets[1];
}
else
{
sheet = (Excel.Worksheet)book.Sheets[currentSheet_Value + 1];
}
oRng = (Excel.Range)sheet.Cells;
if (cTextData.Type == "NXOpen.Annotations.DraftingFcf")
{
#region DraftingFcf填資料
if (cTextData.Characteristic != "")
{
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = DefineVariable.GetCharacteristicSymbol(cTextData.Characteristic);
//oRng[sRowColumn.CharacteristicRow, sRowColumn.CharacteristicColumn] = DefineVariable.GetCharacteristicSymbol(cTextData.Characteristic);
}
if (cTextData.ZoneShape != "")
{
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + DefineVariable.GetZoneShapeSymbol(cTextData.ZoneShape);
//oRng[sRowColumn.ZoneShapeRow, sRowColumn.ZoneShapeColumn] = DefineVariable.GetZoneShapeSymbol(cTextData.ZoneShape);
}
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.ToleranceValue;
//oRng[sRowColumn.ToleranceValueRow, sRowColumn.ToleranceValueColumn] = cTextData.ToleranceValue;
if (cTextData.MaterialModifier != "" & cTextData.MaterialModifier != "None")
{
string ValueStr = cTextData.MaterialModifier;
if (ValueStr == "LeastMaterialCondition")
{
ValueStr = "l";
}
else if (ValueStr == "MaximumMaterialCondition")
{
ValueStr = "m";
}
else if (ValueStr == "RegardlessOfFeatureSize")
{
ValueStr = "s";
}
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + ValueStr;
//oRng[sRowColumn.MaterialModifierRow, sRowColumn.MaterialModifierColumn] = ValueStr;
}
//Primary
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.PrimaryDatum;
//oRng[sRowColumn.PrimaryDatumRow, sRowColumn.PrimaryDatumColumn] = cTextData.PrimaryDatum;
if (cTextData.PrimaryMaterialModifier != "" & cTextData.PrimaryMaterialModifier != "None")
{
string ValueStr = cTextData.PrimaryMaterialModifier;
if (ValueStr == "LeastMaterialCondition")
{
ValueStr = "l";
}
else if (ValueStr == "MaximumMaterialCondition")
{
ValueStr = "m";
}
else if (ValueStr == "RegardlessOfFeatureSize")
{
ValueStr = "s";
}
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + ValueStr;
//oRng[sRowColumn.PrimaryMaterialModifierRow, sRowColumn.PrimaryMaterialModifierColumn] = ValueStr;
}
//Secondary
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.SecondaryDatum;
//oRng[sRowColumn.SecondaryDatumRow, sRowColumn.SecondaryDatumColumn] = cTextData.SecondaryDatum;
if (cTextData.SecondaryMaterialModifier != "" & cTextData.SecondaryMaterialModifier != "None")
{
string ValueStr = cTextData.SecondaryMaterialModifier;
if (ValueStr == "LeastMaterialCondition")
{
ValueStr = "l";
}
else if (ValueStr == "MaximumMaterialCondition")
{
ValueStr = "m";
}
else if (ValueStr == "RegardlessOfFeatureSize")
{
ValueStr = "s";
}
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + ValueStr;
//oRng[sRowColumn.SecondaryMaterialModifierRow, sRowColumn.SecondaryMaterialModifierColumn] = ValueStr;
}
//Tertiary
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.TertiaryDatum;
//oRng[sRowColumn.TertiaryDatumRow, sRowColumn.TertiaryDatumColumn] = cTextData.TertiaryDatum;
if (cTextData.TertiaryMaterialModifier != "" & cTextData.TertiaryMaterialModifier != "None")
{
string ValueStr = cTextData.TertiaryMaterialModifier;
if (ValueStr == "LeastMaterialCondition")
{
ValueStr = "l";
}
else if (ValueStr == "MaximumMaterialCondition")
{
ValueStr = "m";
}
else if (ValueStr == "RegardlessOfFeatureSize")
{
ValueStr = "s";
}
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + ValueStr;
//oRng[sRowColumn.TertiaryMaterialModifierRow, sRowColumn.TertiaryMaterialModifierColumn] = ValueStr;
}
#endregion
}
else if (cTextData.Type == "NXOpen.Annotations.Label")
{
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = cTextData.MainText;
//((Range)oRng[sRowColumn.MainTextRow, sRowColumn.MainTextColumn]).Interior.ColorIndex = 50;
}
else
{
#region Dimension填資料
if (cTextData.BeforeText != null)
{
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + DefineVariable.GetGDTWord(cTextData.BeforeText);
//oRng[sRowColumn.BeforeTextRow, sRowColumn.BeforeTextColumn] = DefineVariable.GetGDTWord(cTextData.BeforeText);
}
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + DefineVariable.GetGDTWord(cTextData.MainText);
//oRng[sRowColumn.MainTextRow, sRowColumn.MainTextColumn] = DefineVariable.GetGDTWord(cTextData.MainText);
//Range ab = (Range)oRng[sRowColumn.MainTextRow, sRowColumn.MainTextColumn];
//ab.Interior.ColorIndex = 39;
if (cTextData.UpperTol != "" & cTextData.TolType == "BilateralOneLine")
{
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + "±";
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.UpperTol;
string MaxMinStr = "(" + (Convert.ToDouble(cTextData.MainText) + Convert.ToDouble(cTextData.UpperTol)).ToString() + "-" + (Convert.ToDouble(cTextData.MainText) - Convert.ToDouble(cTextData.UpperTol)).ToString() + ")";
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + MaxMinStr;
//oRng[sRowColumn.ToleranceSymbolRow, sRowColumn.ToleranceSymbolColumn] = "±";
//oRng[sRowColumn.UpperTolRow, sRowColumn.UpperTolColumn] = cTextData.UpperTol;
}
else if (cTextData.UpperTol != "" & cTextData.TolType == "BilateralTwoLines")
{
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + "+";
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.UpperTol;
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + "/";
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.LowerTol;
string MaxMinStr = "(" + (Convert.ToDouble(cTextData.MainText) + Convert.ToDouble(cTextData.UpperTol)).ToString() + "-" + (Convert.ToDouble(cTextData.MainText) + Convert.ToDouble(cTextData.LowerTol)).ToString() + ")";
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + MaxMinStr;
}
else if (cTextData.UpperTol != "" & cTextData.TolType == "UnilateralAbove")
{
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + "+";
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.UpperTol;
}
else if (cTextData.UpperTol != "" & cTextData.TolType == "UnilateralBelow")
{
//oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + "-";
oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn] = ((Excel.Range)oRng[sRowColumn.DimensionRow, sRowColumn.DimensionColumn]).Value + cTextData.LowerTol;
}
#endregion
}
#region 檢具、頻率、Max、Min、泡泡、泡泡位置、料號、日期
oRng[sRowColumn.GaugeRow, sRowColumn.GaugeColumn] = cTextData.Gauge;
oRng[sRowColumn.FrequencyRow, sRowColumn.FrequencyColumn] = cTextData.Frequency;
oRng[sRowColumn.BallonRow, sRowColumn.BallonColumn] = cTextData.BallonNum;
oRng[sRowColumn.LocationRow, sRowColumn.LocationColumn] = cTextData.Location;
oRng[sRowColumn.PartNoRow, sRowColumn.PartNoColumn] = PartNo;
oRng[sRowColumn.DateRow, sRowColumn.DateColumn] = CurrentDate;
#endregion
}
book.SaveAs(OutputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
book.Close(Type.Missing, Type.Missing, Type.Missing);
x.Quit();
//切回第一張Sheet
DefineVariable.FirstDrawingSheet.Open();
UI.GetUI().NXMessageBox.Show("SelfCheck", NXMessageBox.DialogType.Information, "輸出完成");
theProgram.Dispose();
}
catch (System.Exception ex)
{
book.SaveAs(OutputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
book.Close(Type.Missing, Type.Missing, Type.Missing);
x.Quit();
}
}
catch (NXOpen.NXException ex)
{
// ---- Enter your exception handling code here -----
}
return(retValue);
}
private void Design_IRC_Abutment_Bridges_Box_Type()
{
string file_path = Get_Project_Folder();
//file_path = Path.Combine(file_path, "BoQ_Bridges_Box_Type.xlsx");
file_path = Path.Combine(file_path, "IRC Abutment Design Box Type.xlsx");
string copy_path = file_path;
//file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC Abutment Design.xlsx");
file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC ABUTMENT Design_Box_Type.xlsx");
if (File.Exists(file_path))
{
File.Copy(file_path, copy_path, true);
}
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.ApplicationClass();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(copy_path, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["DLSUP"];
//Excel.Range formatRange;
//formatRange = myExcelWorksheet.get_Range("b" + (dgv_box_gen_data.RowCount + 8), "L" + (dgv_box_gen_data.RowCount + 8));
//formatRange.Interior.Color = System.Drawing.
//ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
List <string> list = new List <string>();
#region Section Input
myExcelWorksheet.get_Range("A9").Formula = txt_sec_B1.Text;
myExcelWorksheet.get_Range("B9").Formula = txt_sec_B2.Text;
myExcelWorksheet.get_Range("C9").Formula = txt_sec_B3.Text;
myExcelWorksheet.get_Range("E9").Formula = txt_sec_B4.Text;
myExcelWorksheet.get_Range("D15").Formula = txt_sec_B5.Text;
myExcelWorksheet.get_Range("G17").Formula = txt_sec_B6.Text;
myExcelWorksheet.get_Range("A17").Formula = txt_sec_H1.Text;
myExcelWorksheet.get_Range("E12").Formula = txt_sec_H2.Text;
myExcelWorksheet.get_Range("G12").Formula = txt_sec_H3.Text;
myExcelWorksheet.get_Range("I12").Formula = txt_sec_H4.Text;
myExcelWorksheet.get_Range("D13").Formula = txt_sec_H5.Text;
myExcelWorksheet.get_Range("E20").Formula = txt_sec_H6.Text;
#endregion Section Input
DataGridView dgv = dgv_sidl;
int rindx = 0;
#region SIDL Input
string v1 = "";
double n, w, d, uu;
myExcelWorksheet.get_Range("L118").Formula = txt_sidl_spc1.Text;
myExcelWorksheet.get_Range("M118").Formula = txt_sidl_spc2.Text;
for (int i = 0; i < dgv.RowCount; i++)
{
n = MyList.StringToDouble(dgv[2, i].Value.ToString(), 0.0);
w = MyList.StringToDouble(dgv[3, i].Value.ToString(), 0.0);
d = MyList.StringToDouble(dgv[4, i].Value.ToString(), 0.0);
uu = MyList.StringToDouble(dgv[5, i].Value.ToString(), 0.0);
myExcelWorksheet.get_Range("D" + (i + 117)).Formula = dgv[2, i].Value.ToString();
myExcelWorksheet.get_Range("E" + (i + 117)).Formula = dgv[3, i].Value.ToString();
myExcelWorksheet.get_Range("F" + (i + 117)).Formula = dgv[4, i].Value.ToString();
myExcelWorksheet.get_Range("G" + (i + 117)).Formula = dgv[5, i].Value.ToString();
try
{
if ((n * w * d * uu) == 0.0)
{
myExcelWorksheet.get_Range("H" + (i + 117)).Formula = dgv[6, i].Value.ToString();
}
}
catch (Exception ex0)
{
//throw;
}
}
#endregion SIDL Input
double L = MyList.StringToDouble(txt_Ana_L.Text, 0.0);
double og = MyList.StringToDouble(txt_Ana_og.Text, 0.0);
double eff_L = (L - 2 * og);
myExcelWorksheet.get_Range("B153").Formula = txt_Ana_og.Text;
myExcelWorksheet.get_Range("C152").Formula = (eff_L / 4.0).ToString("f3");
myExcelWorksheet.get_Range("D160").Formula = eff_L.ToString("f3");
#region LL
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["LL"];
myExcelWorksheet.get_Range("A9").Formula = og.ToString();
myExcelWorksheet.get_Range("I10").Formula = eff_L.ToString();
myExcelWorksheet.get_Range("S9").Formula = og.ToString();
#endregion LL
#region Earth pr
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Earth pr"];
dgv = dgv_earth_pressure;
for (int i = 0; i < dgv.RowCount; i++)
{
myExcelWorksheet.get_Range("F" + (i + 7)).Formula = dgv[1, i].Value.ToString();
}
#endregion Earth pr
#region Base Pressure
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Base Pressure"];
rindx = 0;
dgv = dgv_base_pressure;
List <string> ldbl = new List <string>();
for (int i = 0; i < dgv.RowCount; i++)
{
try
{
if (dgv[1, i].Value.ToString() != "")
{
ldbl.Add(dgv[1, i].Value.ToString());
}
}
catch (Exception exx) { }
}
rindx = 0;
for (int i = 7; i < 55; i++)
{
if (i == 18 ||
i == 19 ||
i == 25 ||
(i >= 28 && i <= 30) ||
i == 35 ||
i == 44 ||
i == 47)
{
continue;
}
else
{
myExcelWorksheet.get_Range("F" + i).Formula = ldbl[rindx++].ToString();
}
}
#endregion Earth pr
rindx = 0;
myExcelWorkbook.Save();
releaseObject(myExcelWorkbook);
iapp.Excel_Open_Message();
}
static public bool exportDataToExcel(string tieude, DataTable dt, string PathFile, string name, string dienthoai, string dc)
{
bool result = false;
//khoi tao cac doi tuong Com Excel de lam viec
Excel.ApplicationClass xlApp;
Excel.Worksheet xlSheet;
Excel.Workbook xlBook;
//doi tuong Trống để thêm vào xlApp sau đó lưu lại sau
object missValue = System.Reflection.Missing.Value;
//khoi tao doi tuong Com Excel moi
xlApp = new Excel.ApplicationClass();
xlBook = xlApp.Workbooks.Add(missValue);
//su dung Sheet dau tien de thao tac
xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
//không cho hiện ứng dụng Excel lên để tránh gây đơ máy
xlApp.Visible = false;
int socot = 8;
int sohang = dt.Rows.Count;
int i, j;
decimal tongsoluong = 0;
decimal tonggiagoc = 0;
decimal tongthanhtien = 0;
//SaveFileDialog f = new SaveFileDialog();
//f.Filter = "Excel file (*.xls)|*.xls";
//if (f.ShowDialog() == DialogResult.OK)
//{
//set thuoc tinh cho tieu de
xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1").Merge(false);
Excel.Range caption = xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1");
caption.Select();
caption.FormulaR1C1 = tieude;
//căn lề cho tiêu đề
caption.HorizontalAlignment = Excel.Constants.xlCenter;
caption.Font.Bold = true;
caption.VerticalAlignment = Excel.Constants.xlCenter;
caption.Font.Size = 15;
//màu nền cho tiêu đề
caption.Interior.ColorIndex = 20;
caption.RowHeight = 30;
Excel.Range ICIM = xlSheet.get_Range("A2", Convert.ToChar(socot + 65) + "5");
ICIM.Select();
xlSheet.get_Range("A2", Convert.ToChar(socot + 65) + "2").Merge(false);
xlSheet.get_Range("A3", Convert.ToChar(socot + 65) + "3").Merge(false);
xlSheet.get_Range("A4", Convert.ToChar(socot + 65) + "4").Merge(false);
xlSheet.Cells[2, 1] = "Họ tên KH: " + name;
xlSheet.Cells[3, 1] = "Điện thoại: " + dienthoai;
xlSheet.Cells[4, 1] = "Địa chỉ: " + dc;
ICIM.Font.Bold = true;
ICIM.Font.Size = 12;
ICIM.Font.Name = "Times New Roman";
ICIM.Interior.ColorIndex = 19;
ICIM.HorizontalAlignment = Excel.Constants.xlLeft;
//set thuoc tinh cho cac header
Excel.Range header = xlSheet.get_Range("A5", Convert.ToChar(socot + 65) + "5");
header.Select();
header.Columns.AutoFit();
foreach (Excel.Range column in header.Columns)
{
column.ColumnWidth = (double)column.ColumnWidth + 10;
if (column.Column == 4)
{
column.ColumnWidth = 30;
}
if (column.Column == 3)
{
}
}
header.HorizontalAlignment = Excel.Constants.xlCenter;
header.Font.Bold = true;
header.Font.Size = 10;
int rowtemp = sohang + 5;
//điền tiêu đề cho các cột trong file excel
//for (i = 0; i < socot; i++)
xlSheet.Cells[5, 2] = "SKU";
xlSheet.Cells[5, 3] = "UPC";
xlSheet.Cells[5, 4] = "Tên sản phẩm";
xlSheet.Cells[5, 5] = "Số lượng";
xlSheet.Cells[5, 6] = "Giá gốc";
xlSheet.Cells[5, 7] = "Giá khuyến mãi";
xlSheet.Cells[5, 8] = "Thành tiền";
xlSheet.Cells[5, 9] = "Ghi chú";
//dien cot stt
xlSheet.Cells[5, 1] = "STT";
header.Interior.ColorIndex = 15;
//sua lai format excel
//for (i = 0; i < sohang; i++)
for (i = 0; i < sohang; i++)
{
xlSheet.Cells[i + 6, 1] = i + 1;
}
//dien du lieu vao sheet
for (i = 0; i < sohang; i++)
{
for (j = 0; j < socot; j++)
{
if (j >= 4 && j <= 6)
{
((Excel.Range)xlSheet.Cells[i + 6, j + 2]).NumberFormat = "#,##0.00_);(#,##0.00)";
xlSheet.Cells[i + 6, j + 2] = dt.Rows[i][j];
}
if (j == 1)
{
((Excel.Range)xlSheet.Cells[i + 6, j + 2]).NumberFormat = "##0_);(##0)";
xlSheet.Cells[i + 6, j + 2] = dt.Rows[i][j];
}
else
{
xlSheet.Cells[i + 6, j + 2] = "" + Convert.ToString(dt.Rows[i][j]);
}
}
tongsoluong += Convert.ToDecimal(dt.Rows[i]["SoLuong"]);
tongthanhtien += Convert.ToDecimal(dt.Rows[i]["ThanhTien"]);
tonggiagoc += Convert.ToDecimal(dt.Rows[i]["GiaGoc"]);
}
//autofit độ rộng cho các cột
//sua lai format excel
//for (i = 0; i < sohang; i++)
for (i = 0; i < socot; i++)
{
((Excel.Range)xlSheet.Cells[1, i + 1]).EntireColumn.AutoFit();
}
int temp = sohang + 6;
Excel.Range foodter = xlSheet.get_Range("A" + temp, "I" + temp);
foodter.Select();
foodter.Font.Size = 10;
foodter.Interior.ColorIndex = 48;
xlSheet.Cells[temp, 5] = tongsoluong;
((Excel.Range)xlSheet.Cells[temp, 5]).NumberFormat = "#,##0.00_);(#,##0.00)";
xlSheet.Cells[temp, 6] = tonggiagoc;
((Excel.Range)xlSheet.Cells[temp, 6]).NumberFormat = "#,##0.00_);(#,##0.00)";
xlSheet.Cells[temp, 8] = tongthanhtien;
((Excel.Range)xlSheet.Cells[temp, 8]).NumberFormat = "#,##0.00_);(#,##0.00)";
//xlSheet.get_Range("A" + sohang + 3, Convert.ToChar(socot + 65) + (sohang + 3).ToString()).Merge(false);
//save file
xlBook.SaveAs(PathFile, Excel.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, Excel.XlSaveAsAccessMode.xlNoChange, missValue, missValue, missValue, missValue, missValue);
xlBook.Close(true, missValue, missValue);
xlApp.Quit();
// release cac doi tuong COM
releaseObject(xlSheet);
releaseObject(xlBook);
releaseObject(xlApp);
result = true;
//}
return(result);
}
public void GetDataFromMeters(Form1 frm)
{
try
{
//Load tags
//loadTags();
//Load Serial port data
loadSerialPortDetails();
//Get No of type of meters
loadNoOfDifferentMeterTypes();
//create new excel application
Excel.Application excel_app = new Excel.ApplicationClass();
excel_app.Visible = false;
StreamWriter file = new StreamWriter("output.txt");
string newfile = System.IO.Directory.GetCurrentDirectory() + "\\Template.xlsx";
//File.Copy(".//Template.xlsx", newfile);
Excel.Workbook workbook = excel_app.Workbooks.Open(newfile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
string sheet_name = "";
if (_isEnergy)
{
sheet_name = "Energy";
}
else
{
sheet_name = "All_data";
}
Excel.Worksheet sheet = FindSheet(workbook, sheet_name);
if (sheet == null)
{
// Add the worksheet at the end.
throw FileNotFoundException();
}
if (_isEnergy)
{
sheet.Cells[5, 1] = DateTime.Now.ToString("dd-MMM-yyyy");
sheet.Cells[5, 2] = DateTime.Now.ToString("HH:mm");
}
//Get Meter Details from different type of meters
for (int i = 1; i <= _meterTypes; i++)
{
_currentMeterType = i;
//Load device model
_modelNo = getDeviceModel();
//Load slave id ranges of the meter
loadMeterRange();
//Load Memory address of modbus registers for Ir,Iy,Ib,Vr,Vy,Vb,KWH,MVAR,MW
loadMemAddress();
//Load Meter Ratio of individual meter and fetch data from meters
for (int j = _start; j <= _end; j++)
{
//Load sheet
//Update UI regarding meter
frm.lblMeterNo.Text = "Currently reading meter no " + j.ToString();
//Load meter Ratios
// LoadMeterRatios(j);
//Get data from meters
file.WriteLine("Meter ID: " + j.ToString());
file.WriteLine(System.Environment.NewLine);
int m = 0;
foreach (var address in _memAddress)
{
//Updating UI regarding Tag
frm.lblTag.Text = "Currently reading Tag " + _tags[m].ToString();
m++;
//Constructing request header
Byte[] requestwithoutcrc = new Byte[6];
Byte[] requestwithcrc = new Byte[8];
//first byte is the id of the meter
//in this scenario j variable holds meter id
requestwithoutcrc[0] = Convert.ToByte(j);
//second byte is the function code of modbus
//As we are trying to read input registers function code is 4
requestwithoutcrc[1] = 4;
//The consecutive two bytes stores the register start address
//address[0] holds the register start address in int we are converting this to 16bit binary using convert2bitarray function and this is further converted to two 8 bit integers
Byte[] registerstartaddress = convert2int8(convert2bitarray(address[0], 16), 2);
for (int k = registerstartaddress.Length - 1, l = 0; k >= 0; k--, l++)
{
requestwithoutcrc[l + 2] = registerstartaddress[k];
}
//The consecutive two bytes stores the registers length
//address[1] holds the value ie., no registers needed from starting address this is also converted to two 8 bit integers
//response length variable holds no of bytes the output response will be
int responselength = address[1];
Byte[] response = new byte[responselength * 2 + 5];
Byte[] responsedata = new byte[responselength * 2];
Byte[] length = convert2int8(convert2bitarray(address[1], 16), 2);
for (int k = length.Length - 1, l = 0; k >= 0; k--, l++)
{
requestwithoutcrc[l + 4] = length[k];
}
//Till this point we have constructed the modbus request modbus request as crc is little indian in modbus
//The loop will calculate crc and appends crc bytes in little endian mode
byte[] crcbytes = calculatecrc(requestwithoutcrc);
requestwithoutcrc.CopyTo(requestwithcrc, 0);
for (int k = 0; k < crcbytes.Length; k++)
{
requestwithcrc[k + 6] = crcbytes[k];
}
_comport.Open();
_comport.DiscardInBuffer();
_comport.Write(requestwithcrc, 0, requestwithcrc.Length);
Thread.Sleep(1000);
if (_comport.BytesToRead == response.Length)
{
_comport.Read(response, 0, response.Length);
}
_comport.Close();
file.Write("Request: ");
foreach (byte byterequestdata in requestwithcrc)
{
file.Write(Convert.ToInt16(byterequestdata).ToString() + " ");
}
file.WriteLine(System.Environment.NewLine);
file.Write("Response: ");
foreach (byte byteresponsedata in response)
{
file.Write(Convert.ToInt16(byteresponsedata).ToString() + " ");
}
file.WriteLine(System.Environment.NewLine);
Array.Copy(response, 3, responsedata, 0, 4);
Array.Reverse(responsedata);
frm.lblValue.Text = _tags[m - 1].ToString() + " : " + BitConverter.ToInt32(responsedata, 0).ToString();
if (_isEnergy)
{
sheet.Cells[5, address[2] + j] = BitConverter.ToInt32(responsedata, 0).ToString();
}
else
{
sheet.Cells[j + 4, address[2]] = BitConverter.ToInt32(responsedata, 0).ToString();
}
}
}
}
file.Close();
workbook.Close(true, newfile, Type.Missing);
excel_app.Quit();
}
catch (Exception ex)
{
throw ex;
}
}
// 在此添加你的代码...
public DataSet ImportProductCategoryFromExcel(string fileName)
{
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = CREATE_ERROR;
return(null);
}
Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
}
catch
{
_ReturnStatus = -1;
_ReturnMessage = IMPORT_ERROR;
return(null);
}
int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
}
workbook.Close(null, null, null);
xlApp.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
DataSet ds = new DataSet();
string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
OleDbDataAdapter da;
for (int i = 1; i <= n; i++)
{
string sql = "select * from [" + SheetSet[i - 1] + "$] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[i - 1]);
da.Dispose();
}
conn.Close();
conn.Dispose();
}
return(ds);
}
private static void ToExcel(DataGridView ExportGrid, string p_ReportName)
{
//如果网格尚未数据绑定
if (ExportGrid == null)
{
return;
}
// 列索引，行索引
int colIndex = 0;
int rowIndex = 0;
int rowIndex1 = 0;
int j;
//总可见列数，总可见行数
//int colCount = ExportGrid.Columns.GetColumnCount(DataGridViewElementStates.Visible);
//int rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible);
int rowCount = 0;
int colCount = SelectedColumns.Count;
if (PrintAllRows) //打印全部
{
rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible);
}
else //打印选择
{
rowCount = ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Selected);
}
//如果DataGridView中没有行，返回
if (rowCount == 0)
{
return;
}
//保存对话框
if (!SaveFileDialog(p_ReportName + " " + System.DateTime.Now.ToString("yyyy-MM-dd")))
{
return;
}
// 创建Excel对象
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel Error", "System Information");
return;
}
// 创建Excel工作薄
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
// 设置标题，实测中发现执行设置字体大小和将字体设置为粗体的语句耗时较长，故注释掉了
string[] sTitle = p_ReportName.Split(new char[1] {
';'
});
xlSheet.Name = sTitle[0];
Microsoft.Office.Interop.Excel.Range range, range1;
//Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
//range.MergeCells = true;
//xlApp.ActiveCell.FormulaR1C1 = sTitle[0];
//xlApp.ActiveCell.Font.Size = 20;
//xlApp.ActiveCell.Font.Bold = true;
//xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
// 单据汇总
// 创建缓存数据
//object[,] objData = new object[rowCount + 1 + sTitle.Length, colCount];
//for (j = 1; j < sTitle.Length; j++)
//{
// //Microsoft.Office.Interop.Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[rowIndex, 1], xlApp.Cells[rowIndex, colCount]);
// //range1.MergeCells = true;
// objData[rowIndex, 0] = sTitle[j];
// rowIndex++;
//}
//objData[rowIndex, 0] = "";
//rowIndex++;
object[,] objData = new object[rowCount + 1, colCount];
// 获取列标题，隐藏的列不处理
for (int i = 0; i < ExportGrid.ColumnCount; i++)
{
if (!PrintDGV.SelectedColumns.Contains(ExportGrid.Columns[i].HeaderText))
{
continue;
}
if (ExportGrid.Columns[i].Visible)
{
objData[rowIndex, colIndex++] = ExportGrid.Columns[i].HeaderText;
}
}
// 获取数据，隐藏的列的数据忽略
for (int i = 1; i <= ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible); i++)
{
if (!PrintAllRows && !ExportGrid.Rows[i - 1].Selected)
{
continue;
}
rowIndex++;
colIndex = 0;
for (j = 0; j < ExportGrid.ColumnCount; j++)
{
if (!PrintDGV.SelectedColumns.Contains(ExportGrid.Columns[j].HeaderText))
{
continue;
}
if (ExportGrid.Columns[j].Visible)
{
if (ExportGrid[j, i - 1].Value != null)
{
objData[rowIndex, colIndex++] = ExportGrid[j, i - 1].Value.ToString();
if (ExportGrid[j, i - 1].Style.BackColor == Color.LightGray)
{
int kkk = 0;
}
}
else
{
objData[rowIndex, colIndex++] = "";
}
}
}
//System.Windows.Forms.Application.DoEvents();
}
// 写入Excel
//xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
//range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowCount + +1 + sTitle.Length, colCount]);
range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowCount + +1, colCount]);
range.Value2 = objData;
//颜色设置
rowIndex1++;
for (int i = 1; i <= ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible); i++)
{
if (!PrintAllRows && !ExportGrid.Rows[i - 1].Selected)
{
continue;
}
rowIndex1++;
colIndex = 0;
for (j = 0; j < ExportGrid.ColumnCount; j++)
{
if (!PrintDGV.SelectedColumns.Contains(ExportGrid.Columns[j].HeaderText))
{
continue;
}
if (ExportGrid.Columns[j].Visible)
{
if (ExportGrid[j, i - 1].Value != null)
{
//objData[rowIndex, colIndex++] = ExportGrid[j, i - 1].Value.ToString();
//颜色设置
colIndex++;
if (ExportGrid[j, i - 1].Style.BackColor == Color.LightGray)
{
range1 = xlSheet.get_Range(xlApp.Cells[rowIndex1, colIndex], xlApp.Cells[rowIndex1, colIndex]);
range1.Interior.ColorIndex = 15;
}
if (ExportGrid[j, i - 1].Style.BackColor == Color.Pink)
{
range1 = xlSheet.get_Range(xlApp.Cells[rowIndex1, colIndex], xlApp.Cells[rowIndex1, colIndex]);
range1.Interior.ColorIndex = 22;
}
if (ExportGrid[j, i - 1].Style.BackColor == Color.LightGreen)
{
range1 = xlSheet.get_Range(xlApp.Cells[rowIndex1, colIndex], xlApp.Cells[rowIndex1, colIndex]);
range1.Interior.ColorIndex = 43;
}
if (ExportGrid[j, i - 1].Style.BackColor == Color.LightBlue)
{
range1 = xlSheet.get_Range(xlApp.Cells[rowIndex1, colIndex], xlApp.Cells[rowIndex1, colIndex]);
range1.Interior.ColorIndex = 34;
}
if (ExportGrid[j, i - 1].Style.BackColor == Color.Gray)
{
range1 = xlSheet.get_Range(xlApp.Cells[rowIndex1, colIndex], xlApp.Cells[rowIndex1, colIndex]);
range1.Interior.ColorIndex = 16;
}
if (ExportGrid[j, i - 1].Style.BackColor == Color.LightYellow)
{
range1 = xlSheet.get_Range(xlApp.Cells[rowIndex1, colIndex], xlApp.Cells[rowIndex1, colIndex]);
range1.Interior.ColorIndex = 44;
}
if (ExportGrid[j, i - 1].Style.BackColor == Color.Orange)
{
range1 = xlSheet.get_Range(xlApp.Cells[rowIndex1, colIndex], xlApp.Cells[rowIndex1, colIndex]);
range1.Interior.ColorIndex = 46;
}
}
}
}
System.Windows.Forms.Application.DoEvents();
}
// 保存
try
{
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
//xlApp.Visible = true;
xlBook.Saved = true;
xlBook.SaveCopyAs(FullFileName);
MessageBox.Show("Output Finish!", "Information");
}
catch
{
MessageBox.Show("Error！", "Information");
//return false;
}
finally
{
xlApp.Quit();
GC.Collect();
//KillProcess("excel");
}
//return true;
}
private static int numObjects = 0; // calculated in a loop
static void Main(string[] args)
{
////////////////////////////////////////////////////////////////////
// get input file //
////////////////////////////////////////////////////////////////////
string inputFile;
string suffPattern = "\\.(DISPLAY|display|XML|xml)$"; // only works with these types of files
if (args.Length != 1)
{
Console.Error.WriteLine("Error: requires one parameter");
return;
}
if (!System.IO.File.Exists(args[0]))
{ // check that file exists
Console.Error.WriteLine("Error: file not found");
return;
}
if (!Regex.IsMatch(args[0], suffPattern))
{
Console.Error.WriteLine("Error: no suffix match");
return;
}
inputFile = String.Copy(args[0]);
outputFile = Regex.Replace(inputFile, suffPattern, ".xls");
////////////////////////////////////////////////////////////////////
// set up xml //
////////////////////////////////////////////////////////////////////
System.Xml.XmlTextReader xmlreader;
// o reads a stream (can't go backwards in the stream)
// o doesn't load the whole document/tree, so is relatively fast
// o does not validate the xml
xmlreader = new XmlTextReader(inputFile);
xmlreader.WhitespaceHandling = WhitespaceHandling.None; // do not consider whitespace as xml nodes
////////////////////////////////////////////////////////////////////
// set up excel //
////////////////////////////////////////////////////////////////////
// open the Excel application
Excel.Application excelApp = new Excel.ApplicationClass();
// turn off alerts
excelApp.DisplayAlerts = false;
// msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel._application.displayalerts(VS.80).aspx
// set number of sheets
if (doView2)
{
excelApp.SheetsInNewWorkbook = 2;
}
else
{
excelApp.SheetsInNewWorkbook = 1;
}
// open a new blank workbook
Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(missing);
// create a Sheets object that holds the Worksheets within the workbook
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
// get an individual sheet
Excel.Worksheet view1Worksheet = (Excel.Worksheet)excelSheets.get_Item("Sheet1");
view1Worksheet.Name = "EVSview1";
// create view2Worksheet even though we may not use it
Excel.Worksheet view2Worksheet = null;
if (doView2)
{
view2Worksheet = (Excel.Worksheet)excelSheets.get_Item("Sheet2");
view2Worksheet.Name = "EVSview2";
}
////////////////////////////////////////////////////////////////////
// write "Trial Name" section //
////////////////////////////////////////////////////////////////////
// we can use hard-coded cell locations for this section because we know the format
// (i.e., there are only two columns and three rows of output)
// "Trial" node
excelCell = (Excel.Range)view1Worksheet.get_Range("A1", "A1"); // access individual cell
excelCell.Value2 = "Trial Name";
excelCell.Font.Bold = true;
excelCell = (Excel.Range)view1Worksheet.get_Range("B1", "B1");
xmlreader.Read(); // get first node
while (xmlreader.Name != "Trial")
{
xmlreader.Read(); // scroll thru until "Trial" node
}
if (xmlreader.Name != "Trial") // we should be at "Trial" node now
{
Console.Error.WriteLine("xmlParseError: Looking for Trial node. Found" + xmlreader.Name);
return;
}
excelCell.Value2 = xmlreader.GetAttribute("name"); // write trial_name to B1
// "Display" node
xmlreader.Read(); // move to "Display" node
if (xmlreader.Name != "Display")
{
Console.Error.WriteLine("xmlParseError: Looking for Display node. Found" + xmlreader.Name);
return;
}
excelCell = (Excel.Range)view1Worksheet.get_Range("A2", "A2");
excelCell.Value2 = "DisplayX";
excelCell = (Excel.Range)view1Worksheet.get_Range("B2", "B2");
excelCell.Value2 = xmlreader.GetAttribute("X");
excelCell = (Excel.Range)view1Worksheet.get_Range("A3", "A3");
excelCell.Value2 = "DisplayY";
excelCell = (Excel.Range)view1Worksheet.get_Range("B3", "B3");
excelCell.Value2 = xmlreader.GetAttribute("Y");
////////////////////////////////////////////////////////////////////
// write "Condition" section //
////////////////////////////////////////////////////////////////////
excelCell = (Excel.Range)view1Worksheet.get_Range("A5", "A5");
excelCell.Value2 = "Condition Info";
excelCell.Font.Bold = true;
rowNumber = 5; // from this point, rows and columns are not hard-coded
// the next node is Condition
xmlreader.Read();
if (xmlreader.Name != "Condition")
{
Console.Error.WriteLine("xmlParseError: Looking for Condition node. Found" + xmlreader.Name);
return;
}
// we are now at "Condition" node
// o we're going to loop through attributes (instead of accessing them via hard-coded names)
// o for future, this means that attributes can be added without changing this section of code
int numAttr = xmlreader.AttributeCount; // for v1, the count is 12
xmlreader.MoveToFirstAttribute(); // go to first attribute
for (int i = 0; i < numAttr; i++)
{
rowNumber++; // increment row number
excelCell = getCell(view1Worksheet, stringA, rowNumber);
excelCell.Value2 = xmlreader.Name; // write attribute name
excelCell = excelCell.Next; // move to the right
excelCell.Value2 = xmlreader.Value; // write attribute value
xmlreader.MoveToNextAttribute();
}
// at this point, we're still in the condition node, and are going to read the object nodes
// skip two lines for spacing
rowNumber += 2;
////////////////////////////////////////////////////////////////////
// write "Object Info" section //
////////////////////////////////////////////////////////////////////
excelCell = getCell(view1Worksheet, stringA, rowNumber);
excelCell.Value2 = "Object Info";
excelCell.Font.Bold = true;
xmlreader.Read(); // move to first object node
if (xmlreader.Name != "Obj")
{
Console.Error.WriteLine("xmlParseError: Looking for Obj node (sub-node of Condition). Found" + xmlreader.Name);
return;
}
// iterate over the Object nodes (this is also where we calculate the numObjects value)
while (xmlreader.Name == "Obj")
{
numObjects++;
rowNumber++;
excelCell = getCell(view1Worksheet, stringA, rowNumber);
excelCell.Value2 = xmlreader.GetAttribute("name");
excelCell = excelCell.Next; // move to the right
if (xmlreader.GetAttribute("IsTargetObject") == "1") //0=no, 1=yes
{
excelCell.Value2 = "TARGET";
}
else
{
excelCell.Value2 = "not target";
}
excelCell = excelCell.Next;
excelCell.Value2 = xmlreader.GetAttribute("ObjectFilePath");
xmlreader.Read();
}
// at this point, we are at the </Condition> node
// skip two more lines for spacing
rowNumber += 2;
////////////////////////////////////////////////////////////////////
// write the timestamp header rows in view1 //
////////////////////////////////////////////////////////////////////
// calculate how many columns we will need for the TimeStamp header row:
// [first column] + [object/gaze labels] + [X and Y label for each object/gaze] + [n spacers]
// TimeStamp row + (n objects + 1 gaze)(X + Y columns) + (n blank columns)
// => 1 + (n + 1 )(2) + n
// => 1 + (n+1)(2) + n
// => 1 + (2n + 2) + n
// => 1 + 2n + 2 + n
// => 3 + 3n
// => (n+1)3 columns
//
// format of columns:
// timestamp gazeX gazeY blank obj1X obj1Y blank ... objnX objnY
int numColumns = (numObjects + 1) * 3; // 3(n+1)
// vars for formatting the first row
Excel.Range mergeRange;
Excel.Range mergeFrom;
Excel.Range mergeTo;
Excel.Range highlightRange;
Excel.Range highlightFrom;
Excel.Range highlightTo;
// write the timestamp-label cell
excelCell = getCell(view1Worksheet, stringA, rowNumber);
timeStampLineNum = rowNumber; // need this number for insertion later
excelCell.Value2 = "TimeStamp";
excelCell.Font.Bold = true;
timestampCell = excelCell; // save location of TimeStamp for insertion later
highlightFrom = excelCell; // save location of TimeStamp for highlighting now
// write and merge the gaze-label cells
excelCell = excelCell.Next;
excelCell.Value2 = "gaze";
mergeFrom = excelCell; // the "X" col
excelCell = excelCell.Next; // the "Y" col
mergeTo = excelCell;
mergeRange = (Excel.Range)view1Worksheet.get_Range(mergeFrom, mergeTo);
mergeRange.MergeCells = true;
mergeRange.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelCell = excelCell.Next.Next; // first Next for spacer; second for moving to initial obj-label cell
// write and merge the object-label cells
for (int n = 1; n < numObjects + 1; n++)
{
excelCell.Value2 = "obj" + n;
mergeFrom = excelCell; // the "X" col
excelCell = excelCell.Next; // the "Y" col
mergeTo = excelCell;
excelCell = excelCell.Next.Next; // spacer
mergeRange = (Excel.Range)view1Worksheet.get_Range(mergeFrom, mergeTo);
mergeRange.MergeCells = true;
mergeRange.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
// kluge! we're now two cells too far for highlighting
excelCell = excelCell.Previous.Previous;
highlightTo = excelCell;
highlightRange = (Excel.Range)view1Worksheet.get_Range(highlightFrom, highlightTo);
highlightRange.Interior.Color = (222 << 16) | (222 << 8) | 222; // 222 = shade of grey
//next line (labelled with X/Y)
rowNumber++;
excelCell = getCell(view1Worksheet, stringB, rowNumber); // start in column B
for (int j = 0; j < numObjects + 1; j++)
{
excelCell.Value2 = "X";
excelCell = excelCell.Next;
excelCell.Value2 = "Y";
excelCell = excelCell.Next.Next; // spacer
}
// now we center the X/Y labels
excelCell.EntireRow.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
////////////////////////////////////////////////////////////////////
// write the timestamp header rows in view2 //
////////////////////////////////////////////////////////////////////
if (doView2)
{
// headers cells on this worksheet can be hard-coded because they have standard locations
excelCellView2 = getCell(view2Worksheet, "A", 1);
excelCellView2.Value2 = "TimeStamp";
excelCellView2.Font.Bold = true;
excelCellView2.Font.Underline = true;
excelCellView2.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelCellView2 = getCell(view2Worksheet, "B", 1);
excelCellView2.Value2 = "Object";
excelCellView2.Font.Bold = true;
excelCellView2.Font.Underline = true;
excelCellView2.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelCellView2 = getCell(view2Worksheet, "C", 1);
excelCellView2.Value2 = "X";
excelCellView2.Font.Bold = true;
excelCellView2.Font.Underline = true;
excelCellView2.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelCellView2 = getCell(view2Worksheet, "D", 1);
excelCellView2.Value2 = "Y";
excelCellView2.Font.Bold = true;
excelCellView2.Font.Underline = true;
excelCellView2.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
rowNumberView2 = 2; // object locations start on next line
}
////////////////////////////////////////////////////////////////////
// write the timestamps (onto both sheets) //
////////////////////////////////////////////////////////////////////
// recall that we are at the </Condition> node - we want to move to <TrialStart>
xmlreader.Read();
if (xmlreader.Name != "TrialStart")
{
Console.Error.WriteLine("xmlParseError: Looking for TrialStart. Found" + xmlreader.Name);
return;
}
while (xmlreader.Name != "SubjectTargetStart") // go thru nodes until SubjectTargetStart is reached
{
rowNumber++;
xmlreader.Read(); // move to Time node
if (xmlreader.Name == "Time")
{
// o loop over "Time" nodes
// o the "if Time" statement is present to skip over the </TrialStart> and <SubjectTargetStart> nodes.
// If those nodes were not present, we wouldn't need the "if Time" statement
// (or we would error-check with an "if !Time" statement)
excelCell = getCell(view1Worksheet, stringA, rowNumber);
excelCell.Value2 = xmlreader.GetAttribute("stamp"); // write time to the cell
// for view2
if (doView2)
{
excelCellView2 = getCell(view2Worksheet, stringA, rowNumberView2);
excelCellView2.Value2 = xmlreader.GetAttribute("stamp");
excelCellView2 = excelCellView2.Next;
}
xmlreader.Read(); // move to first obj node
// (this just allows us to put the Read() at the bottom of the while loop)
if (xmlreader.Name != "obj")
{
Console.Error.WriteLine("xmlParseError: Looking for obj (sub-node of Time). Found" + xmlreader.Name);
return;
}
// loop over the obj nodes
while (xmlreader.NodeType != XmlNodeType.EndElement)
{
// o loop over the obj nodes
// o we can end the loop with EndElement because obj nodes do not have an ending element
// but the Time node does (</Time>)
// View2 needs obj name for each iteration, but View1 doesn't
if (doView2)
{
excelCellView2.Value2 = xmlreader.GetAttribute("name");
}
excelCell = excelCell.Next; // move to the right
if (doView2)
{
excelCellView2 = excelCellView2.Next;
}
excelCell.Value2 = xmlreader.GetAttribute("x");
if (doView2)
{
excelCellView2.Value2 = xmlreader.GetAttribute("x");
}
excelCell = excelCell.Next; // move to the right
if (doView2)
{
excelCellView2 = excelCellView2.Next;
}
excelCell.Value2 = xmlreader.GetAttribute("y");
if (doView2)
{
excelCellView2.Value2 = xmlreader.GetAttribute("y");
}
excelCell = excelCell.Next; // move to the right (the "spacer" column)
if (doView2)
{
rowNumberView2++; // view2 has objects on different rows
}
if (doView2)
{
excelCellView2 = getCell(view2Worksheet, stringB, rowNumberView2);
}
xmlreader.Read(); // move to next node (which will be either <obj> or </Time>
}
// after the while loop, we are at a </Time> node
}
if (doView2)
{
rowNumberView2 += 2; // extra spacing on view2
}
}
// at this point, we are at <SubjectTargetStart>
////////////////////////////////////////////////////////////////////
// insert user selection above timestamps //
////////////////////////////////////////////////////////////////////
int linenumForWriting = timeStampLineNum;
if (xmlreader.Name != "SubjectTargetStart")
{
Console.Error.WriteLine("xmlParseError: Looking for SubjectTargetStart. Found" + xmlreader.Name);
return;
}
// insert "User Selected" Label/row
Excel.Range timestampRow = timestampCell.EntireRow; // select TimeStamp-label row
timestampRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing); // move the row down (insert line above)
timeStampLineNum++; // the TimeStamp line has moved down one row
Excel.Range currWritingCell = getCell(view1Worksheet, stringA, linenumForWriting);
currWritingCell.Value2 = "User Selected";
currWritingCell.Font.Bold = true;
linenumForWriting++;
// move to first Obj node
xmlreader.Read();
if (xmlreader.Name != "Obj")
{
Console.Error.WriteLine("xmlParseError: Looking for Obj (sub-node of SubjectTargetStart). Found" + xmlreader.Name);
return;
}
while (xmlreader.NodeType != XmlNodeType.EndElement)
{
timestampCell = getCell(view1Worksheet, stringA, timeStampLineNum); // where TimeStamp-label is currently
timestampRow = timestampCell.EntireRow;
timestampRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
timeStampLineNum++; // TimeStamp-label row has moved down
// write attribute where TimeStamp-label used to be
currWritingCell = getCell(view1Worksheet, stringA, linenumForWriting);
currWritingCell.Value2 = xmlreader.GetAttribute("name");
currWritingCell.Font.Bold = false;
linenumForWriting++;
xmlreader.Read(); // move to next node
}
// at this point, we are at </SubjectTargetStart>
// insert final row for spacing
timestampCell = getCell(view1Worksheet, stringA, timeStampLineNum);
timestampRow = timestampCell.EntireRow;
timestampRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
timeStampLineNum++;
////////////////////////////////////////////////////////////////////
// resize column A on both sheets //
////////////////////////////////////////////////////////////////////
excelCell = (Excel.Range)view1Worksheet.get_Range("A1", missing);
excelCell.ColumnWidth = firstColWidth;
if (doView2)
{
excelCellView2 = (Excel.Range)view2Worksheet.get_Range("A1", missing);
}
if (doView2)
{
excelCellView2.ColumnWidth = firstColWidth;
}
////////////////////////////////////////////////////////////////////
// save doc & close Excel //
////////////////////////////////////////////////////////////////////
excelWorkbook.SaveAs(outputFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// parameters description at msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel._workbook.saveas(VS.80).aspx
excelApp.Quit();
} // end main()
private void gridControl1_DoubleClick(object sender, EventArgs e)
{
DevExpress.XtraGrid.Views.Grid.ViewInfo.GridHitInfo hi =
gridView1.CalcHitInfo((sender as System.Windows.Forms.Control).PointToClient(System.Windows.Forms.Control.MousePosition));
DataRow FocusRow;
if (hi.RowHandle >= 0)
{
FocusRow = gridView1.GetDataRow(hi.RowHandle);
string strType = FocusRow.ItemArray[3].ToString();
string strPath = FocusRow.ItemArray[4].ToString();
if (strType == "OUTLOOK")
{
try
{
Email.Application oApp = new Email.Application();
Email._MailItem oMailItem = (Email._MailItem)oApp.CreateItemFromTemplate(strPath, Type.Missing);
//oMailItem.Subject = "abc";
oMailItem.Display(false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (strType == "WORD")
{
try
{
Word.Application wordApp;
Word.Document doc;
wordApp = new Word.ApplicationClass();
wordApp.Visible = true;
object fileName = strPath;
object missing = Type.Missing;
object fReadOnly = false;
doc = wordApp.Documents.Open(ref fileName,
ref missing, ref fReadOnly, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing);
//doc.Activate();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (strType == "EXCEL")
{
try
{
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.Workbook workBook = oExcel.Workbooks.Open(strPath, 0, true, 5, null, null, true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);
//Excel.Worksheet ws = (Excel.Worksheet)oExcel.ActiveSheet;
//ws.Activate();
//ws.get_Range("A1", "IV65536").Font.Size = 8;
oExcel.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (strType == "PDF")
{
ACMS.ACMSStaff.To_Do_List.frmPDFviewer frm = new ACMS.ACMSStaff.To_Do_List.frmPDFviewer(strPath);
frm.Show();
}
else if (strType == "VIDEO")
{
ACMS.ACMSStaff.To_Do_List.frmVideoPlayer frmPlayer = new ACMS.ACMSStaff.To_Do_List.frmVideoPlayer(strPath);
frmPlayer.Show();
}
}
else if (gridView1.FocusedRowHandle >= 0)
{
FocusRow = gridView1.GetDataRow(gridView1.FocusedRowHandle);
string strType = FocusRow.ItemArray[3].ToString();
string strPath = FocusRow.ItemArray[4].ToString();
//ACMS.ACMSStaff.WorkFlow.MyCustomEditForm frm = new ACMS.ACMSStaff.WorkFlow.MyCustomEditForm((int)FocusRow.ItemArray[0], oUser.NDepartmentID());
//frm.Show();
}
}
public void losowanie52(List <Record> formatka, List <Format52> ilosci, int kolumny, int wiersze)
{
//var excelFile = Path.GetFullPath(@"D:\Program Files\P_Olton\SpotFinder\Test1.xlsx");
var excelFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "FOLDERdamiana", "FOLDERwewnatrzFolderuDamiana");
Excel.Application excel = new Excel.ApplicationClass();
excel.DisplayAlerts = false;
Excel.Workbook wb = excel.Workbooks.Add(Missing.Value);
Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
ws.Name = "Test";
ws.Cells[1, 1] = "LP";
ws.Cells[1, 2] = "MIASTO";
ws.Cells[1, 3] = "Adres";
ws.Cells[1, 4] = "FIRMA";
ws.Cells[1, 5] = "Nr tablicy";
ws.Cells[1, 6] = "Forma";
ws.Cells[1, 7] = "X";
ws.Cells[1, 8] = "Y";
ws.Cells[1, 9] = "Nr tablicy z formatem";
ws.Cells[1, 10] = "kategoria";
ws.Cells[1, 11] = "Typ reklamy";
ws.Cells[1, 12] = "test";
//trzeba bedzie pokombinowac z napisanem metody która zwroci wartosc z bezposrednio z klasy
//
//ws.Cells[2, 1];
int zmienna = 1;
var cos = formatka.GetRange(2, 1);
var test = formatka[1].adres;
int j = 2;
for (int i = 0; i <= wiersze - 2; i++)
{
ws.Cells[j, 1] = formatka[i].LP;
ws.Cells[j, 2] = formatka[i].miasto;
ws.Cells[j, 3] = formatka[i].adres;
ws.Cells[j, 4] = formatka[i].miasto;
ws.Cells[j, 5] = formatka[i].nr_Tablicy;
ws.Cells[j, 6] = formatka[i].forma;
ws.Cells[j, 7] = formatka[i].latitude_X;
ws.Cells[j, 8] = formatka[i].longitude_Y;
ws.Cells[j, 9] = formatka[i].nr_tab_Format;
ws.Cells[j, 10] = formatka[i].kategoria;
ws.Cells[j, 11] = "Typ reklamy";
ws.Cells[j, 12] = "test";
j++;
}
int licznik = 0;
int los;
string typRek;
j = 2;
Random rnd = new Random(); //potrzebne do wylosowania typu reklamy
///////////////////////////////////////////////////////////////////////////////////////////////////////
for (int i = 0; i <= wiersze - 2; i++)
{
if (formatka[i].forma == "5x2")//losowanie dla 5x2
{
for (;;)
{
licznik++;
typRek = Randv2.losowacz52(los = rnd.Next(1, 19), ilosci, formatka, i);
if (typRek == "again")
{
}
else
{
break;
}
if (FormatCheck.check52(ilosci) == "empty")
{
typRek = "brak_pozycji";
break;
}
if (licznik > 2000)
{
System.Console.WriteLine("KRECE SIE TU NA 52 " + licznik);
}
}
ws.Cells[j, 11] = typRek;
j++;
}
else if (formatka[i].forma == "6x3")// losowanie dla 6x3
{
for (;;)
{
typRek = Randv3.losowacz63(los = rnd.Next(1, 19), ilosci, formatka, i);
if (typRek == "again")
{
}
else
{
break;
}
if (FormatCheck.check63(ilosci) == "empty")
{
typRek = "brak_pozycji";
break;
}
if (licznik > 2000)
{
System.Console.WriteLine("KRECE SIE TU NA 63 " + licznik);
}
}
ws.Cells[j, 11] = typRek;
j++;
}
else if (formatka[i].forma == "12x3" || formatka[i].forma == "12x4")// losowanie dla 6x3
{
for (;;)
{
typRek = Randv4.losowacz1234(los = rnd.Next(1, 19), ilosci, formatka, i);
if (typRek == "again")
{
}
else
{
break;
}
if (FormatCheck.check1234(ilosci) == "empty")
{
typRek = "brak_pozycji";
break;
}
if (licznik > 2000)
{
System.Console.WriteLine("KRECE SIE TU NA 1234 " + licznik);
}
}
ws.Cells[j, 11] = typRek;
j++;
}
else
{
j++;
}
}
/* foreach (var a in ilosci)
* {
* Console.WriteLine(a.tv_Sam+a.tv_LG+a.tv_Sony+a.tv_Sha+a.laptop+a.tel_Sam+a.tel_Mot+a.pra_Sam+a.pra_Whi+a.kuc_Ami+a.lod_Sam+a.lod_Bek+a.susz+a.oczysz+a.odk+a.eksp+a.szczot+a.paro);
*
* }
*/
//ustawiony jest tryb odczytu (Patrz ponizej)'
wb.SaveAs(excelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
Killprocess kill = new Killprocess();
kill.btnExport_Click();
}
private void Design_IRC_Abutment_Bridges_Girder_Type()
{
string file_path = Get_Project_Folder();
//file_path = Path.Combine(file_path, "BoQ_Bridges_Box_Type.xlsx");
file_path = Path.Combine(file_path, "IRC Abutment Design Girder Type.xlsx");
string copy_path = file_path;
file_path = Path.Combine(Application.StartupPath, @"DESIGN\Abutment\Abutment Design IRC\IRC ABUTMENT Design_Girder_Type.xlsx");
if (File.Exists(file_path))
{
File.Copy(file_path, copy_path, true);
}
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.ApplicationClass();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(copy_path, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["DLSUP"];
//Excel.Range formatRange;
//formatRange = myExcelWorksheet.get_Range("b" + (dgv_box_gen_data.RowCount + 8), "L" + (dgv_box_gen_data.RowCount + 8));
//formatRange.Interior.Color = System.Drawing.
//ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
List <string> list = new List <string>();
#region Section Input
myExcelWorksheet.get_Range("B4").Formula = txt_Ana_L.Text;
myExcelWorksheet.get_Range("B5").Formula = txt_Ana_og.Text;
myExcelWorksheet.get_Range("B6").Formula = txt_total_weight.Text;
myExcelWorksheet.get_Range("B7").Formula = txt_total_sidl.Text;
#endregion Section Input
DataGridView dgv = dgv_sidl;
int rindx = 0;
#region SIDL Input
//for (int i = 0; i < dgv.RowCount; i++)
//{
// myExcelWorksheet.get_Range("E" + (i + 117)).Formula = dgv[2, i].Value.ToString();
// myExcelWorksheet.get_Range("F" + (i + 117)).Formula = dgv[3, i].Value.ToString();
// myExcelWorksheet.get_Range("G" + (i + 117)).Formula = dgv[4, i].Value.ToString();
//}
#endregion SIDL Input
#region Earth pr
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Earth pr"];
dgv = dgv_earth_pressure;
for (int i = 0; i < dgv.RowCount; i++)
{
myExcelWorksheet.get_Range("F" + (i + 7)).Formula = dgv[1, i].Value.ToString();
}
#endregion Earth pr
#region Base Pressure
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Base Pressure"];
rindx = 0;
dgv = dgv_base_pressure;
List <string> ldbl = new List <string>();
for (int i = 0; i < dgv.RowCount; i++)
{
try
{
if (dgv[1, i].Value.ToString() != "")
{
ldbl.Add(dgv[1, i].Value.ToString());
}
}
catch (Exception exx) { }
}
rindx = 0;
for (int i = 7; i < 55; i++)
{
if (i == 18 ||
i == 19 ||
i == 25 ||
(i >= 28 && i <= 30) ||
i == 35 ||
i == 44 ||
i == 47)
{
continue;
}
else
{
myExcelWorksheet.get_Range("F" + i).Formula = ldbl[rindx++].ToString();
}
}
#endregion Earth pr
rindx = 0;
myExcelWorkbook.Save();
releaseObject(myExcelWorkbook);
iapp.Excel_Open_Message();
}
/// <summary>
/// 把Excel文件转换成PDF格式文件
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns>true=转换成功</returns>
public static bool XLSConvertToPDF(string sourcePath, string targetPath)
{
// ConvertExcelPDF(sourcePath, targetPath);
bool result = false;
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Excel.ApplicationClass application = null;
Excel.Workbook workBook = null;
try
{
application = new Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
#region 设置打印参数
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in workBook.Sheets)
{
sh.PageSetup.Zoom = false;
sh.PageSetup.FitToPagesTall = 1;
sh.PageSetup.FitToPagesWide = 1;
sh.PageSetup.TopMargin = 0;
sh.PageSetup.BottomMargin = 0;
sh.PageSetup.LeftMargin = 0;
sh.PageSetup.RightMargin = 0;
sh.PageSetup.CenterHorizontally = true;
}
#endregion
workBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch (Exception ex)
{
result = false;
//Logger.WriteLog("Service Error:Excel转pdf" + ex.ToString(), "Log\\ServiceInfoError.txt", true);
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
private void Pier_Process_Design_BS()
{
string file_path = Path.Combine(iApp.LastDesignWorkingFolder, Title);
if (iApp.user_path != "")
{
file_path = Path.Combine(iApp.user_path, Title);
}
if (!Directory.Exists(file_path))
{
Directory.CreateDirectory(file_path);
}
//file_path = Path.Combine(file_path, "RCC Cantilever Abutment Design");
//if (!Directory.Exists(file_path)) Directory.CreateDirectory(file_path);
file_path = Path.Combine(file_path, "Pier with open foundation.xlsx");
//file_path = Path.Combine(file_path, "BoQ_Flyover_ROB_RUBs.xlsx");
//file_path = Path.Combine(file_path, "BoQ for " + cmb_boq_item.Text + ".xlsx");
string copy_path = file_path;
file_path = Path.Combine(Application.StartupPath, @"DESIGN\Pier\Pier Design Limit State\Pier with open foundation BS.xlsx");
if (File.Exists(file_path))
{
File.Copy(file_path, copy_path, true);
}
else
{
MessageBox.Show(file_path + " file not found.");
return;
}
iApp.Excel_Open_Message();
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
myExcelApp = new Excel.ApplicationClass();
myExcelApp.Visible = true;
//myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
//myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorkbook = myExcelWorkbooks.Open(copy_path, 0, false, 5, "2011ap", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.Sheets["Design Data"];
List <TextBox> All_Data = Get_TextBoxes();
//Excel.Range formatRange;
//formatRange = myExcelWorksheet.get_Range("b" + (dgv.RowCount + after_indx), "L" + (dgv.RowCount + after_indx));
//formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
List <double> data = new List <double>();
try
{
string kStr = "";
foreach (var item in All_Data)
{
kStr = item.Name.Replace("txt_des_", "");
//myExcelWorksheet.get_Range("E53").Formula = data[rindx++].ToString();
myExcelWorksheet.get_Range(kStr).Formula = item.Text;
}
#region Input 2
//myExcelWorksheet.get_Range("K78").Formula = data[rindx++].ToString();
//myExcelWorksheet.get_Range("K81").Formula = data[rindx++].ToString();
#endregion Input 2
}
catch (Exception exx) { }
myExcelWorkbook.Save();
releaseObject(myExcelWorkbook);
//iApp.Excel_Open_Message();
}
//------------------------------------------------------------------------------
// Explicit Activation
// This entry point is used to activate the application explicitly
//------------------------------------------------------------------------------
public static int Main(string[] args)
{
int retValue = 0;
try
{
theProgram = new Program();
Session theSession = Session.GetSession();
Part workPart = theSession.Parts.Work;
Part displayPart = theSession.Parts.Display;
DefineVariable.Is_Local = Environment.GetEnvironmentVariable("UGII_ENV_FILE");
if (DefineVariable.Is_Local != "")
{
//取得本機"FAI.xls路徑
DefineVariable.FAIPath = string.Format(@"{0}\{1}\{2}", Path.GetDirectoryName(displayPart.FullPath), "MODEL", "FAI.xls");
}
//取得料號
string PartNo = Path.GetFileNameWithoutExtension(displayPart.FullPath);
//取得零件名稱
string PartDescription = "";
try
{
PartDescription = displayPart.GetStringAttribute("PARTDESCRIPTIONPOS");
}
catch (System.Exception ex)
{
PartDescription = "";
}
int SheetCount = 0;
NXOpen.Tag[] SheetTagAry = null;
theUfSession.Draw.AskDrawings(out SheetCount, out SheetTagAry);
DefineVariable.DicDimenData = new Dictionary <string, TextData>();
for (int i = 0; i < SheetCount; i++)
{
//打開Sheet並記錄所有OBJ
NXOpen.Drawings.DrawingSheet CurrentSheet = (NXOpen.Drawings.DrawingSheet)NXObjectManager.Get(SheetTagAry[i]);
CurrentSheet.Open();
if (i == 0)
{
//記錄第一張Sheet
DefineVariable.FirstDrawingSheet = CurrentSheet;
}
DisplayableObject[] SheetObj = CurrentSheet.View.AskVisibleObjects();
foreach (DisplayableObject singleObj in SheetObj)
{
TextData cTextData = new TextData();
string singleObjType = singleObj.GetType().ToString();
string FAI_Gauge = "", BallonNum = "", Frequency = "", Location = "";
string[] mainText;
string[] dualText;
#region 取FAI共用屬性(泡泡值、檢具名稱、檢驗頻率、泡泡所在區域)，如果都沒屬性就找下一個
try
{
FAI_Gauge = singleObj.GetStringAttribute(CaxME.DimenAttr.FAI_Gauge);
BallonNum = singleObj.GetStringAttribute(CaxME.DimenAttr.BallonNum);
Frequency = singleObj.GetStringAttribute(CaxME.DimenAttr.FAI_Freq);
Location = singleObj.GetStringAttribute(CaxME.DimenAttr.BallonLocation);
}
catch (System.Exception ex)
{
FAI_Gauge = "";
}
if (FAI_Gauge == "")
{
continue;
}
#endregion
#region 紀錄共用屬性(泡泡值、檢具名稱、檢驗頻率、泡泡所在區域)
//取得泡泡值
cTextData.BallonNum = BallonNum;
//取得檢具名稱
cTextData.Gauge = FAI_Gauge;
//取得檢驗頻率
cTextData.Frequency = Frequency;
//取得泡泡所在區域
cTextData.Location = Location;
#endregion
if (singleObjType == "NXOpen.Annotations.VerticalDimension")
{
#region VerticalDimension取Text
cTextData.Type = "NXOpen.Annotations.VerticalDimension";
NXOpen.Annotations.VerticalDimension temp = (NXOpen.Annotations.VerticalDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.PerpendicularDimension")
{
#region PerpendicularDimension取Text
cTextData.Type = "NXOpen.Annotations.PerpendicularDimension";
NXOpen.Annotations.PerpendicularDimension temp = (NXOpen.Annotations.PerpendicularDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.MinorAngularDimension")
{
#region MinorAngularDimension取Text
cTextData.Type = "NXOpen.Annotations.MinorAngularDimension";
NXOpen.Annotations.MinorAngularDimension temp = (NXOpen.Annotations.MinorAngularDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.RadiusDimension")
{
#region MinorAngularDimension取Text
cTextData.Type = "NXOpen.Annotations.RadiusDimension";
NXOpen.Annotations.RadiusDimension temp = (NXOpen.Annotations.RadiusDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.HorizontalDimension")
{
#region HorizontalDimension取Text
cTextData.Type = "NXOpen.Annotations.HorizontalDimension";
NXOpen.Annotations.HorizontalDimension temp = (NXOpen.Annotations.HorizontalDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
if (temp.ToleranceType.ToString() == "BilateralTwoLines")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
if (temp.ToleranceType.ToString() == "UnilateralAbove")
{
cTextData.TolType = "UnilateralAbove";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = "0";
}
if (temp.ToleranceType.ToString() == "UnilateralBelow")
{
cTextData.TolType = "UnilateralBelow";
cTextData.UpperTol = "0";
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.IdSymbol")
{
#region IdSymbol取Text
cTextData.Type = "NXOpen.Annotations.IdSymbol";
NXOpen.Annotations.IdSymbol temp = (NXOpen.Annotations.IdSymbol)singleObj;
#endregion
}
else if (singleObjType == "NXOpen.Annotations.Note")
{
#region Note取Text
cTextData.Type = "NXOpen.Annotations.Note";
NXOpen.Annotations.Note temp = (NXOpen.Annotations.Note)singleObj;
//判斷是否由CAX產生的Note
string createby = "";
try
{
createby = temp.GetStringAttribute("Createby");
}
catch (System.Exception ex)
{
createby = "";
}
if (createby == "")
{
string tempStr = temp.GetText()[0].Replace("<F2>", "");
tempStr = tempStr.Replace("<F>", "");
cTextData.MainText = tempStr;
}
else
{
cTextData.MainText = temp.GetText()[0];
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.DraftingFcf")
{
#region DraftingFcf取Text
NXOpen.Annotations.DraftingFcf temp = (NXOpen.Annotations.DraftingFcf)singleObj;
CaxME.FcfData sFcfData = new CaxME.FcfData();
CaxME.GetFcfData(temp, out sFcfData);
cTextData.Type = "NXOpen.Annotations.DraftingFcf";
cTextData.Characteristic = sFcfData.Characteristic;
cTextData.ZoneShape = sFcfData.ZoneShape;
cTextData.ToleranceValue = sFcfData.ToleranceValue;
cTextData.MaterialModifier = sFcfData.MaterialModifier;
cTextData.PrimaryDatum = sFcfData.PrimaryDatum;
cTextData.PrimaryMaterialModifier = sFcfData.PrimaryMaterialModifier;
cTextData.SecondaryDatum = sFcfData.SecondaryDatum;
cTextData.SecondaryMaterialModifier = sFcfData.SecondaryMaterialModifier;
cTextData.TertiaryDatum = sFcfData.TertiaryDatum;
cTextData.TertiaryMaterialModifier = sFcfData.TertiaryMaterialModifier;
#endregion
}
else if (singleObjType == "NXOpen.Annotations.Label")
{
#region Label取Text
cTextData.Type = "NXOpen.Annotations.Label";
NXOpen.Annotations.Label temp = (NXOpen.Annotations.Label)singleObj;
cTextData.MainText = temp.GetText()[0];
#endregion
}
else if (singleObjType == "NXOpen.Annotations.DraftingDatum")
{
#region DraftingDatum取Text
cTextData.Type = "NXOpen.Annotations.DraftingDatum";
NXOpen.Annotations.DraftingDatum temp = (NXOpen.Annotations.DraftingDatum)singleObj;
#endregion
}
else if (singleObjType == "NXOpen.Annotations.DiameterDimension")
{
#region DiameterDimension取Text
cTextData.Type = "NXOpen.Annotations.DiameterDimension";
NXOpen.Annotations.DiameterDimension temp = (NXOpen.Annotations.DiameterDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.AngularDimension")
{
#region AngularDimension取Text
cTextData.Type = "NXOpen.Annotations.AngularDimension";
NXOpen.Annotations.AngularDimension temp = (NXOpen.Annotations.AngularDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.CylindricalDimension")
{
#region CylindricalDimension取Text
cTextData.Type = "NXOpen.Annotations.CylindricalDimension";
NXOpen.Annotations.CylindricalDimension temp = (NXOpen.Annotations.CylindricalDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0];
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
if (temp.ToleranceType.ToString() == "BilateralTwoLines")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
if (temp.ToleranceType.ToString() == "UnilateralAbove")
{
cTextData.TolType = "UnilateralAbove";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = "0";
}
if (temp.ToleranceType.ToString() == "UnilateralBelow")
{
cTextData.TolType = "UnilateralBelow";
cTextData.UpperTol = "0";
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
#endregion
}
else if (singleObjType == "NXOpen.Annotations.ChamferDimension")
{
#region ChamferDimension取Text
cTextData.Type = "NXOpen.Annotations.ChamferDimension";
NXOpen.Annotations.ChamferDimension temp = (NXOpen.Annotations.ChamferDimension)singleObj;
temp.GetDimensionText(out mainText, out dualText);
if (mainText.Length > 0)
{
cTextData.MainText = mainText[0] + "X" + "45" + "<$s>";
}
if (temp.GetAppendedText().GetBeforeText().Length > 0)
{
cTextData.BeforeText = temp.GetAppendedText().GetBeforeText()[0].ToString();
}
if (temp.GetAppendedText().GetAfterText().Length > 0)
{
cTextData.AfterText = temp.GetAppendedText().GetAfterText()[0].ToString();
}
if (temp.GetAppendedText().GetAboveText().Length > 0)
{
cTextData.AboveText = temp.GetAppendedText().GetAboveText()[0].ToString();
}
if (temp.GetAppendedText().GetBelowText().Length > 0)
{
cTextData.BelowText = temp.GetAppendedText().GetBelowText()[0].ToString();
}
if (temp.ToleranceType.ToString() == "BilateralOneLine")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = (-1 * temp.UpperMetricToleranceValue).ToString();
}
if (temp.ToleranceType.ToString() == "BilateralTwoLines")
{
cTextData.TolType = "BilateralOneLine";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
if (temp.ToleranceType.ToString() == "UnilateralAbove")
{
cTextData.TolType = "UnilateralAbove";
cTextData.UpperTol = temp.UpperMetricToleranceValue.ToString();
cTextData.LowerTol = "0";
}
if (temp.ToleranceType.ToString() == "UnilateralBelow")
{
cTextData.TolType = "UnilateralBelow";
cTextData.UpperTol = "0";
cTextData.LowerTol = temp.LowerMetricToleranceValue.ToString();
}
#endregion
}
//計算泡泡總數
DefineVariable.BallonCount++;
DefineVariable.DicDimenData[BallonNum] = cTextData;
}
}
//設定輸出路徑--Local
//string[] FolderFile = System.IO.Directory.GetFileSystemEntries(Path.GetDirectoryName(displayPart.FullPath), "*.xls");
//string OutputPath = string.Format(@"{0}\{1}", Path.GetDirectoryName(displayPart.FullPath),
// Path.GetFileNameWithoutExtension(displayPart.FullPath) + "_" + "IPQC" + "_" + (FolderFile.Length + 1) + ".xls");
//設定輸出路徑--Server
string OperNum = Regex.Replace(Path.GetFileNameWithoutExtension(displayPart.FullPath).Split('_')[1], "[^0-9]", "");
string Local_Folder_OIS = string.Format(@"{0}\{1}\{2}", Path.GetDirectoryName(displayPart.FullPath), "OP" + OperNum, "OIS");
if (!File.Exists(Local_Folder_OIS))
{
System.IO.Directory.CreateDirectory(Local_Folder_OIS);
}
string[] FolderFile = System.IO.Directory.GetFileSystemEntries(Local_Folder_OIS, "*.xls");
List <string> ListFolderFileWithFAI = new List <string>();
foreach (string i in FolderFile)
{
if (i.Contains("FAI"))
{
ListFolderFileWithFAI.Add(i);
}
}
string OutputPath = string.Format(@"{0}\{1}", Local_Folder_OIS,
Path.GetFileNameWithoutExtension(displayPart.FullPath) + "_" + "FAI" + "_" + (ListFolderFileWithFAI.Count + 1) + ".xls");
//檢查PC有無Excel在執行
foreach (var item in Process.GetProcesses())
{
if (item.ProcessName == "EXCEL")
{
CaxLog.ShowListingWindow("請先關閉所有Excel再重新執行輸出，如沒有EXCEL在執行，請開啟工作管理員關閉背景EXCEL");
return(retValue);
}
else
{
continue;
}
}
Excel.ApplicationClass x = new Excel.ApplicationClass();
Excel.Workbook book = null;
Excel.Worksheet sheet = null;
Excel.Range oRng = null;
try
{
x.Visible = false;
if (DefineVariable.Is_Local != "")
{
if (File.Exists(DefineVariable.FAIPath))
{
book = x.Workbooks.Open(DefineVariable.FAIPath);
}
else
{
book = x.Workbooks.Open(@"D:\FAI.xls");
}
}
else
{
book = x.Workbooks.Open(@"D:\FAI.xls");
}
//取得第一頁sheet
sheet = (Excel.Worksheet)book.Sheets[1];
#region 處理第一頁
oRng = (Excel.Range)sheet.Cells;
oRng[10, 1] = PartNo;
oRng[10, 2] = PartDescription;
#endregion
//取得第二頁sheet
sheet = (Excel.Worksheet)book.Sheets[2];
#region 處理第二頁
oRng = (Excel.Range)sheet.Cells;
oRng[10, 1] = PartNo;
oRng[10, 2] = PartDescription;
#endregion
//取得第三頁sheet
sheet = (Excel.Worksheet)book.Sheets[3];
#region 處理第三頁
oRng = (Excel.Range)sheet.Cells;
oRng[10, 1] = PartNo;
oRng[10, 5] = PartDescription;
//Insert所需欄位並填入資料
int CurrentRow = 16, BallonColumn = 1, LocationColumn = 2, DimenColumn = 4, GaugeColumn = 6;
for (int i = 1; i < DefineVariable.BallonCount; i++)
{
oRng = (Excel.Range)sheet.Range["A17"].EntireRow;
oRng.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
}
//填表
for (int i = 1; i < 1000; i++)
{
TextData cTextData;
DefineVariable.DicDimenData.TryGetValue(i.ToString(), out cTextData);
if (cTextData == null)
{
continue;
}
oRng = (Excel.Range)sheet.Cells;
//取得Row,Column
CurrentRow = CurrentRow + 1;
if (cTextData.Type == "NXOpen.Annotations.DraftingFcf")
{
#region DraftingFcf填資料
if (cTextData.Characteristic != "")
{
oRng[CurrentRow, DimenColumn] = DefineVariable.GetCharacteristicSymbol(cTextData.Characteristic);
//oRng[sRowColumn.CharacteristicRow, sRowColumn.CharacteristicColumn] = DefineVariable.GetCharacteristicSymbol(cTextData.Characteristic);
}
if (cTextData.ZoneShape != "")
{
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + DefineVariable.GetZoneShapeSymbol(cTextData.ZoneShape);
//oRng[sRowColumn.ZoneShapeRow, sRowColumn.ZoneShapeColumn] = DefineVariable.GetZoneShapeSymbol(cTextData.ZoneShape);
}
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.ToleranceValue;
//oRng[sRowColumn.ToleranceValueRow, sRowColumn.ToleranceValueColumn] = cTextData.ToleranceValue;
if (cTextData.MaterialModifier != "" & cTextData.MaterialModifier != "None")
{
string ValueStr = cTextData.MaterialModifier;
if (ValueStr == "LeastMaterialCondition")
{
ValueStr = "l";
}
else if (ValueStr == "MaximumMaterialCondition")
{
ValueStr = "m";
}
else if (ValueStr == "RegardlessOfFeatureSize")
{
ValueStr = "s";
}
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + ValueStr;
//oRng[sRowColumn.MaterialModifierRow, sRowColumn.MaterialModifierColumn] = ValueStr;
}
//Primary
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.PrimaryDatum;
//oRng[sRowColumn.PrimaryDatumRow, sRowColumn.PrimaryDatumColumn] = cTextData.PrimaryDatum;
if (cTextData.PrimaryMaterialModifier != "" & cTextData.PrimaryMaterialModifier != "None")
{
string ValueStr = cTextData.PrimaryMaterialModifier;
if (ValueStr == "LeastMaterialCondition")
{
ValueStr = "l";
}
else if (ValueStr == "MaximumMaterialCondition")
{
ValueStr = "m";
}
else if (ValueStr == "RegardlessOfFeatureSize")
{
ValueStr = "s";
}
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + ValueStr;
//oRng[sRowColumn.PrimaryMaterialModifierRow, sRowColumn.PrimaryMaterialModifierColumn] = ValueStr;
}
//Secondary
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.SecondaryDatum;
//oRng[sRowColumn.SecondaryDatumRow, sRowColumn.SecondaryDatumColumn] = cTextData.SecondaryDatum;
if (cTextData.SecondaryMaterialModifier != "" & cTextData.SecondaryMaterialModifier != "None")
{
string ValueStr = cTextData.SecondaryMaterialModifier;
if (ValueStr == "LeastMaterialCondition")
{
ValueStr = "l";
}
else if (ValueStr == "MaximumMaterialCondition")
{
ValueStr = "m";
}
else if (ValueStr == "RegardlessOfFeatureSize")
{
ValueStr = "s";
}
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + ValueStr;
//oRng[sRowColumn.SecondaryMaterialModifierRow, sRowColumn.SecondaryMaterialModifierColumn] = ValueStr;
}
//Tertiary
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.TertiaryDatum;
//oRng[sRowColumn.TertiaryDatumRow, sRowColumn.TertiaryDatumColumn] = cTextData.TertiaryDatum;
if (cTextData.TertiaryMaterialModifier != "" & cTextData.TertiaryMaterialModifier != "None")
{
string ValueStr = cTextData.TertiaryMaterialModifier;
if (ValueStr == "LeastMaterialCondition")
{
ValueStr = "l";
}
else if (ValueStr == "MaximumMaterialCondition")
{
ValueStr = "m";
}
else if (ValueStr == "RegardlessOfFeatureSize")
{
ValueStr = "s";
}
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + ValueStr;
//oRng[sRowColumn.TertiaryMaterialModifierRow, sRowColumn.TertiaryMaterialModifierColumn] = ValueStr;
}
#endregion
}
else if (cTextData.Type == "NXOpen.Annotations.Label")
{
oRng[CurrentRow, DimenColumn] = cTextData.MainText;
//((Range)oRng[sRowColumn.MainTextRow, sRowColumn.MainTextColumn]).Interior.ColorIndex = 50;
}
else
{
#region Dimension填資料
if (cTextData.BeforeText != null)
{
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + DefineVariable.GetGDTWord(cTextData.BeforeText);
//oRng[sRowColumn.BeforeTextRow, sRowColumn.BeforeTextColumn] = DefineVariable.GetGDTWord(cTextData.BeforeText);
}
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + DefineVariable.GetGDTWord(cTextData.MainText);
//oRng[sRowColumn.MainTextRow, sRowColumn.MainTextColumn] = DefineVariable.GetGDTWord(cTextData.MainText);
//Range a = (Range)oRng[sRowColumn.MainTextRow, sRowColumn.MainTextColumn];
//a.Interior.ColorIndex = 39;
if (cTextData.UpperTol != "" & cTextData.TolType == "BilateralOneLine")
{
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + "±";
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.UpperTol;
string MaxMinStr = "(" + (Convert.ToDouble(cTextData.MainText) + Convert.ToDouble(cTextData.UpperTol)).ToString() + "-" + (Convert.ToDouble(cTextData.MainText) - Convert.ToDouble(cTextData.UpperTol)).ToString() + ")";
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + MaxMinStr;
//oRng[sRowColumn.ToleranceSymbolRow, sRowColumn.ToleranceSymbolColumn] = "±";
//oRng[sRowColumn.UpperTolRow, sRowColumn.UpperTolColumn] = cTextData.UpperTol;
}
else if (cTextData.UpperTol != "" & cTextData.TolType == "BilateralTwoLines")
{
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + "+";
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.UpperTol;
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + "/";
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.LowerTol;
string MaxMinStr = "(" + (Convert.ToDouble(cTextData.MainText) + Convert.ToDouble(cTextData.UpperTol)).ToString() + "-" + (Convert.ToDouble(cTextData.MainText) + Convert.ToDouble(cTextData.LowerTol)).ToString() + ")";
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + MaxMinStr;
}
else if (cTextData.UpperTol != "" & cTextData.TolType == "UnilateralAbove")
{
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + "+";
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.UpperTol;
}
else if (cTextData.UpperTol != "" & cTextData.TolType == "UnilateralBelow")
{
//oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + "-";
oRng[CurrentRow, DimenColumn] = ((Excel.Range)oRng[CurrentRow, DimenColumn]).Value + cTextData.LowerTol;
}
#endregion
}
#region 檢具、頻率、Max、Min、泡泡、泡泡位置、料號、日期
oRng[CurrentRow, GaugeColumn] = cTextData.Gauge;
//oRng[sRowColumn.FrequencyRow, sRowColumn.FrequencyColumn] = cTextData.Frequency;
oRng[CurrentRow, BallonColumn] = cTextData.BallonNum;
oRng[CurrentRow, LocationColumn] = cTextData.Location;
//oRng[sRowColumn.PartNoRow, sRowColumn.PartNoColumn] = PartNo;
//oRng[sRowColumn.DateRow, sRowColumn.DateColumn] = CurrentDate;
#endregion
}
#endregion
book.SaveAs(OutputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
book.Close(Type.Missing, Type.Missing, Type.Missing);
x.Quit();
//切回第一張Sheet
DefineVariable.FirstDrawingSheet.Open();
UI.GetUI().NXMessageBox.Show("FAI", NXMessageBox.DialogType.Information, "輸出完成");
}
catch (System.Exception ex)
{
book.SaveAs(OutputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
book.Close(Type.Missing, Type.Missing, Type.Missing);
x.Quit();
}
}
catch (NXOpen.NXException ex)
{
// ---- Enter your exception handling code here -----
}
theProgram.Dispose();
return(retValue);
}
public bool uploadInfo()
{
bool rtn = false;
string code = "";
bool isCorrect = false;
string errormsg = "";
string name = "";
if (File.Exists(testFile))
{
if (File.Exists(sourceFile))
{
File.Delete(sourceFile);
}
File.Copy(testFile, sourceFile);
FileInfo fileInfo = new FileInfo(sourceFile);
//有最新文件，开始上传数据
//先检查数据源中的校验码是否正确
Excel.ApplicationClass excel = new Excel.ApplicationClass();
//MessageBox.Show(excel.Version);
excel.Visible = false;
excel.Workbooks.Open(sourceFile, Type.Missing, Type.Missing, Type.Missing, "halleluja", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet)excel.Worksheets[2];
name = ((Excel.Range)ws.Cells[3, 3]).Text.ToString();
sourceFileName = name;
excel.Quit();
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
if (Patient.w_code != "")
{
code = Patient.w_code;
verifyCode = code;
try
{
string strOrder = webService.isExistOrder(code, CurrentUser.UserName);
if (strOrder.Length > 2)
{
vjList = getValidate(strOrder);
if (vjList.name.Length > 0)
{
isCorrect = true;
}
string pType = vjList.peopleType;
//if (pType == "1")
//{
// isAdult = false;
//}
//else
//{
// isAdult = true;
//}
}
else if (strOrder == "-1")
{
errormsg = "验证码不存在，或者被使用,";
}
else if (strOrder == "-2")
{
errormsg = "门店用户未设置所属门店,";
}
else if (strOrder == "-3")
{
errormsg = "订单预约门店与使用门店不同,";
}
else if (strOrder == "-4")
{
errormsg = "订单状态不可用,";
}
}
catch (Exception ex)
{
string er = ex.ToString();
LogHelper.WriteLog(ex.ToString());
}
}
else
{
errormsg = "验证码录入有误,";
}
//上传文件到服务器
//verifyCode = "hahahaha";
FTPUpLoad();
//有更新文件，但是文件有问题
if (!isCorrect)
{
File.Delete(sourceFile);
//SetValue("ChangeTime", latestTime);
LogHelper.WriteLog(sourceFileName + "-" + errormsg + "文件存在问题，云端处理数据失败！");
//XtraMessageBox.Show(sourceFileName + "-" + errormsg + "云端处理数据失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
//SetValue("ChangeTime", latestTime);
LogHelper.WriteLog("开始云端处理数据");
//copyTemp();
copyStream();
//上传数据
//Thread thdSub = new Thread(new ThreadStart(ThreadFun));
//thdSub.Start();
rtn = ThreadFun();
}
return(rtn);
}
public List <Company> ExcelReader()
{
string path = AppDomain.CurrentDomain.BaseDirectory + "CompaniesBB.xlsx";
System.Diagnostics.Debug.WriteLine(path);
//Instance reference for Excel Application
Excel.Application objXL = null;
//Workbook refrence
List <Company> companies = new List <Company>();
objXL = new Excel.ApplicationClass();
//Adding WorkBook
System.Globalization.CultureInfo oldCI;
oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("sv-SE");
Excel.Workbook objWB = objXL.Workbooks.Open(path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, Excel.XlCorruptLoad.xlExtractData);
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
try
{
//Instancing Excel using COM services
foreach (Excel.Worksheet objSHT in objWB.Worksheets)
{
int rows = objSHT.UsedRange.Rows.Count;
int cols = objSHT.UsedRange.Columns.Count;
for (int r = 2; r <= rows; r++)
{
Company company = new Company();
company.ID_bb = (objSHT.Cells[r, 1] as Excel.Range).Value?.ToString();
company.CompanyName = (objSHT.Cells[r, 2] as Excel.Range).Value?.ToString();
company.Verksamhet_BB = (objSHT.Cells[r, 3] as Excel.Range).Value?.ToString();
company.ParVAT = (objSHT.Cells[r, 5] as Excel.Range).Value?.ToString();
company.Phone = (objSHT.Cells[r, 6] as Excel.Range).Value?.ToString();
company.StreetAddress2 = (objSHT.Cells[r, 8] as Excel.Range).Value?.ToString();
company.City2_BB = (objSHT.Cells[r, 9] as Excel.Range).Value?.ToString();
company.PostalCode2_BB = (objSHT.Cells[r, 10] as Excel.Range).Value?.ToString();
company.Fax_BB = (objSHT.Cells[r, 11] as Excel.Range).Value?.ToString();
company.Ansvarig_BB = (objSHT.Cells[r, 14] as Excel.Range).Value?.ToString();
company.HS_Owner = (objSHT.Cells[r, 14] as Excel.Range).Value?.ToString();
company.Employees = (objSHT.Cells[r, 16] as Excel.Range).Value?.ToString();
company.StreetAddress = (objSHT.Cells[r, 17] as Excel.Range).Value?.ToString();
company.Postal = (objSHT.Cells[r, 18] as Excel.Range).Value?.ToString();
company.City = (objSHT.Cells[r, 19] as Excel.Range).Value?.ToString();
company.Kundnr_BB = (objSHT.Cells[r, 24] as Excel.Range).Value?.ToString();
company.Par_Kalla_BB = (objSHT.Cells[r, 25] as Excel.Range).Value?.ToString();
company.StateRegion = (objSHT.Cells[r, 27] as Excel.Range).Value?.ToString();
company.Turnover = (objSHT.Cells[r, 30] as Excel.Range).Value?.ToString();
company.Orgnr_BB = (objSHT.Cells[r, 31] as Excel.Range).Value?.ToString();
company.ParID_BB = (objSHT.Cells[r, 49] as Excel.Range).Value?.ToString();
company.SNI_kod_BB = (objSHT.Cells[r, 56] as Excel.Range).Value?.ToString();
company.Website_URL = (objSHT.Cells[r, 61] as Excel.Range).Value?.ToString();
company.Description = (objSHT.Cells[r, 66] as Excel.Range).Value?.ToString();
company.ArbetsStalleNr_BB = (objSHT.Cells[r, 67] as Excel.Range).Value?.ToString();
company.Type = "Customer";
companies.Add(company);
}
}
//Closing workbook
objWB.Close();
//Closing excel application
objXL.Quit();
}
catch (Exception ex)
{
string mess = ex.Message;
objWB.Saved = true;
//Closing work book
objWB.Close();
//Closing excel application
objXL.Quit();
//Response.Write("Illegal permission");
}
return(companies);
}
/// <summary>
/// this functio create a excel file with the values from a table
/// </summary>
public static string Export(string path, System.Data.DataTable dt)
{
string output = "OK";
try
{
//create a new instance of excel file
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
//first let's extract the columns and create a little header
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "@";
//worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "#";
worksheet.Activate();
int horizontal = 1;
int vertical = 1;
foreach (DataColumn dc in dt.Columns)
{
excel.Cells[vertical, horizontal] = dc.ColumnName;
horizontal++;
}
//go to next row
vertical++;
foreach (DataRow row in dt.Rows)
{
horizontal = 1;
foreach (DataColumn column in dt.Columns)
{
excel.Cells[vertical, horizontal] = row[column];
horizontal++;
}
vertical++;
}
//save the excel file
worksheet.Name = "PROROGHE Non Lavorate";
//DirectoryInfo dr = new DirectoryInfo(path);
//dr.Create();
FileInfo fi = new FileInfo(path);
if (fi.Exists == true)
{
fi.Delete();
}
worksheet.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Workbooks.Close();
excel.Quit();
}
catch (Exception ex)
{
output = ex.Message + "\n" + ex.StackTrace;
}
return(output);
}
