private void ExportarDataGridViewExcel(DataGridView grd) { SaveFileDialog fichero = new SaveFileDialog(); fichero.Filter = "Excel (*.xls)|*.xls"; if (fichero.ShowDialog() == DialogResult.OK) { Microsoft.Office.Interop.Excel.Application aplicacion; Microsoft.Office.Interop.Excel.Workbook libros_trabajo; Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo; aplicacion = new Microsoft.Office.Interop.Excel.Application(); libros_trabajo = aplicacion.Workbooks.Add(); hoja_trabajo = (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1); //Recorremos el DataGridView rellenando la hoja de trabajo for (int i = 0; i < grd.Rows.Count ; i++) { for (int j = 0; j < grd.Columns.Count; j++) { hoja_trabajo.Cells[i + 1, j + 1] = grd.Rows[i].Cells[j].Value.ToString(); } } libros_trabajo.SaveAs(fichero.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal); libros_trabajo.Close(true); aplicacion.Quit(); } }
private void btn_Excel_Click(object sender, EventArgs e) { if (dgv_Info.Rows.Count == 0)//判断是否有数据 return;//返回 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 excel.Application.Workbooks.Add(true);//在Excel中添加一个工作簿 excel.Visible = true;//设置Excel显示 //生成字段名称 for (int i = 0; i < dgv_Info.ColumnCount; i++) { excel.Cells[1, i + 1] = dgv_Info.Columns[i].HeaderText;//将数据表格控件中的列表头填充到Excel中 } //填充数据 for (int i = 0; i < dgv_Info.RowCount - 1; i++)//遍历数据表格控件的所有行 { for (int j = 0; j < dgv_Info.ColumnCount; j++)//遍历数据表格控件的所有列 { if (dgv_Info[j, i].ValueType == typeof(string))//判断遍历到的数据是否是字符串类型 { excel.Cells[i + 2, j + 1] = "'" + dgv_Info[j, i].Value.ToString();//填充Excel表格 } else { excel.Cells[i + 2, j + 1] = dgv_Info[j, i].Value.ToString();//填充Excel表格 } } } }
/* * The method getTable opens a file, gets the first worksheet in the workbook, and fills the datatable Table with its contents. */ public void setup() { OpenFileDialog openFile = new OpenFileDialog(); openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); openFile.Filter = "Excel Workbook|*.xls"; if (openFile.ShowDialog() == true) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet); wb = app.Workbooks.Open(openFile.FileName); Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets.get_Item(1); if (wb != null) // If there is a worksheet { if (ws.UsedRange != null) // if the worksheet is not empty { Table = toDataTable(ws); } } app.Visible = true; } }
void CreateExcelFile() { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Workbooks.Add(); excel.Visible = true; excel.DisplayAlerts = false; excel.Range["A1"].Value = bankDataTable.Columns[0].ColumnName; excel.Range["B1"].Value = bankDataTable.Columns[1].ColumnName; excel.Range["C1"].Value = bankDataTable.Columns[2].ColumnName; excel.Range["D1"].Value = bankDataTable.Columns[3].ColumnName; excel.Range["E1"].Value = bankDataTable.Columns[4].ColumnName; excel.Range["F1"].Value = bankDataTable.Columns[5].ColumnName; // set date and time format for column F excel.Range["F1", "F99"].NumberFormat = "M/D/YYYY H:MM AM/PM"; // set width for column F excel.Range["F1"].EntireColumn.ColumnWidth = 17; // shamelessly stolen from Terri int j = 2; foreach (DataRow row in bankDataTable.Rows) { int i = 65; foreach( var item in row.ItemArray) { char c1 = (char)i++; string cell = c1 + j.ToString(); excel.Range[cell].Value = item.ToString(); } j++; } }
private void btn_Gather_Click(object sender, EventArgs e) { object missing = System.Reflection.Missing.Value;//定义object缺省值 string[] P_str_Names = txt_MultiExcel.Text.Split(',');//存储所有选择的Excel文件名 string P_str_Name = "";//存储遍历到的Excel文件名 List<string> P_list_SheetNames = new List<string>();//实例化泛型集合对象,用来存储工作表名称 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 //打开指定的Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表 for (int i = 0; i < P_str_Names.Length - 1; i++)//遍历所有选择的Excel文件名 { P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名 //指定要复制的工作簿 Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名 for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表 { //指定要复制的工作表 Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]];//创建新工作表 TempWorksheet.Copy(missing, newWorksheet);//将工作表内容复制到目标工作表中 } Tempworkbook.Close(false, missing, missing);//关闭临时工作簿 } workbook.Save();//保存目标工作簿 workbook.Close(false, missing, missing);//关闭目标工作簿 MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); CloseProcess("EXCEL");//关闭所有Excel进程 }
private void importButton_Click(object sender, RoutedEventArgs e) { var app = new Microsoft.Office.Interop.Excel.Application(); var workbook = app.Workbooks.Add(); var worksheet = workbook.Worksheets[1]; worksheet.Cells[1, 1] = "Ім'я"; worksheet.Cells[1, 2] = "Дата і час"; worksheet.Cells[1, 3] = "Кількість помилок"; worksheet.Cells[1, 4] = "Витрачений час"; worksheet.Cells[1, 5] = "Результат"; using (var context = new Model.DB()) { var results = context.ConfusedLinesTestResults.ToList(); for (int i = 0; i < results.Count(); i++) { worksheet.Cells[i + 2, 1] = results[i].Name; worksheet.Cells[i + 2, 2] = results[i].Date; worksheet.Cells[i + 2, 3] = results[i].ErrorsCount; worksheet.Cells[i + 2, 4] = results[i].Time; worksheet.Cells[i + 2, 5] = results[i].Result; } } app.Visible = true; }
public static void Init() { lock (Lock) { _excelApp = new Microsoft.Office.Interop.Excel.Application(); } }
public static void print(DataGridView dataGridView1,string tableName) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();//Создание объекта Excel ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 15; ExcelApp.Cells[1, 1] = tableName;//Передаём имя таблицы ExcelApp.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; for (int i = 1; i < dataGridView1.Columns.Count; i++)//Заполняем названия столбцов { ExcelApp.Cells[2, i] = dataGridView1.Columns[i].HeaderText; ExcelApp.Cells[2, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; } for (int i = 1; i < dataGridView1.ColumnCount; i++)//Заполняем таблицу { for (int j = 0; j < dataGridView1.RowCount; j++) { try { ExcelApp.Cells[j + 3, i] = (dataGridView1[i, j].Value).ToString(); ExcelApp.Cells[j + 3, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; } catch { } } } ExcelApp.Visible = true;//Открываем Excel }
public void exportarDatosExcel() { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.Sheets[1]; worksheet = workbook.ActiveSheet; worksheet.Name = "Receive Payment"; //Se le manda a la cabezera los datos de las tabla worksheet.Cells[1, 1] = "Date"; worksheet.Cells[1, 2] = "Num-Referent"; worksheet.Cells[1, 3] = "Receive From"; worksheet.Cells[1, 4] = "Amount"; for (int i = 0; i < tblReceive.Items.Count; i++) { ReceivePayment rpaymentcheck = (ReceivePayment)tblReceive.Items.GetItemAt(i); worksheet.Cells[i + 3, 1] = rpaymentcheck.Date; worksheet.Cells[i + 3, 2] = rpaymentcheck.RefNumber; worksheet.Cells[i + 3, 3] = rpaymentcheck.Customer; worksheet.Cells[i + 3, 4] = rpaymentcheck.Amount; } }
static void DisplayInExcel(IEnumerable<dynamic> entities) { var excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Visible = true; excelApp.Workbooks.Add(); dynamic workSheet = excelApp.ActiveSheet; workSheet.Cells[1, "A"] = "Header A"; workSheet.Cells[1, "B"] = "Header B"; var row = 1; foreach (var item in entities) { row++; workSheet.Cells[row, "A"] = item.ColumnA; workSheet.Cells[row, "B"] = item.ColumnB; } workSheet.Columns[1].AutoFit(); workSheet.Columns[2].AutoFit(); }
public static bool DataTableExportAsExcel(DataTable dt, string FileFullPath) { if (!System.IO.Directory.Exists(Path.GetDirectoryName(FileFullPath))) { CreatePath(FileFullPath); } Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application(); excelApplication.EnableEvents = false; excelApplication.Application.DisplayAlerts = false; excelApplication.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet; excelApplication.Visible = false; int nRowIndex = 0; int nColumnIndex = 0; int ColumnCount = dt.Columns.Count; int RowCount = dt.Rows.Count; object[,] strArr = new object[RowCount + 1, ColumnCount]; foreach (DataColumn col in dt.Columns) { strArr[nRowIndex, nColumnIndex] = col.ColumnName; ++nColumnIndex; } ++nRowIndex; nColumnIndex = 0; foreach (DataRow row in dt.Rows) { for (int i = 0; i < ColumnCount; i++) { strArr[nRowIndex, nColumnIndex] = row[i].ToString(); ++nColumnIndex; } ++nRowIndex; nColumnIndex = 0; } string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(ColumnCount, RowCount + 1); Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1", strExcelMaxColumnIndex); myRange.get_Resize(RowCount + 1, ColumnCount); try { myRange.Value2 = strArr; myRange.Columns.AutoFit(); myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { return false; } excelApplication.Quit(); killexcel(excelApplication); GC.Collect(); return true; }
private void btn_Read_Click(object sender, EventArgs e) { int P_int_Count = 0;//记录正在读取的行数 string P_str_Line, P_str_Content = "";//记录读取行的内容及遍历到的内容 List<string> P_str_List = new List<string>();//存储读取的所有内容 StreamReader SReader = new StreamReader(txt_Txt.Text, Encoding.Default);//实例化流读取对象 while ((P_str_Line = SReader.ReadLine()) != null)//循环读取文本文件中的每一行 { P_str_List.Add(P_str_Line);//将读取到的行内容添加到泛型集合中 P_int_Count++;//使当前读取行数加1 } Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 object missing = System.Reflection.Missing.Value;//获取缺少的object类型值 //打开指定的Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet newWorksheet;//声明工作表对象 for (int i = 0; i < P_str_List.Count; i++)//遍历泛型集合 { P_str_Content = P_str_List[i];//记录遍历到的值 newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表 newWorksheet.Cells[1, 1] = P_str_Content;//直接将遍历到的内容添加到工作表中 } excel.Application.DisplayAlerts = false;//不显示提示对话框 workbook.Save();//保存工作表 workbook.Close(false, missing, missing);//关闭工作表 MessageBox.Show("已经将文本文件的内容分解到了Excel的不同数据表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public void exportExcel(DataGridView table) { if (table.Columns.Count > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); int _columns = 0; int _rows = 1; foreach (DataGridViewColumn column in table.Columns) { excel.Cells[1, ++_columns] = column.Name; } foreach (DataGridViewRow row in table.Rows) { _columns = 0; _rows++; foreach (DataGridViewColumn column in table.Columns) { excel.Cells[_rows, ++_columns] = row.Cells[column.Name].Value; } } excel.Visible = true; } }
public List<string> GetExcelSheets(string excelFileName) { Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workBookObject = null; workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets; // get the first and only worksheet from the collection of worksheets List<string> sheetNames = new List<string>(); Regex regSheetName = new Regex("Sheet\\d+"); foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) { if (!regSheetName.IsMatch(sheet.Name)) { sheetNames.Add(sheet.Name); } Marshal.ReleaseComObject(sheet); } excelFileObject.Quit(); Marshal.ReleaseComObject(sheets); Marshal.ReleaseComObject(workBookObject); Marshal.ReleaseComObject(excelFileObject); return sheetNames; }
public ExcelWriter(string _fileName) { fileName = _fileName; if(File.Exists(FilePath)) throw new ApplicationException("File already exists: " + FilePath); File.Create(FilePath); app = new Microsoft.Office.Interop.Excel.Application(); Console.Error.WriteLine("Connected to Excel"); wbs = app.Workbooks; wb = wbs.Add(1); wb.Activate(); wss = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1); Console.Error.WriteLine("Excel Worksheet Initialized"); }
public void Print(YellowstonePathology.Business.Search.ReportSearchList caseList, string description, DateTime printDate) { Microsoft.Office.Interop.Excel.Application xlApp; xlApp = new Microsoft.Office.Interop.Excel.Application(); xlApp.Visible = false; Microsoft.Office.Interop.Excel.Workbook wb = xlApp.Workbooks.Add(@"\\CFileServer\documents\ReportTemplates\MolecularTesting\CaseList.xlt"); Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1]; ws.Cells[3, 1] = "Batch: " + description + " - " + printDate.ToShortDateString(); int rowPosition = 6; for (int i = caseList.Count - 1; i > -1; i--) { ws.Cells[rowPosition, 1] = caseList[i].ReportNo; ws.Cells[rowPosition, 2] = caseList[i].PanelSetName; ws.Cells[rowPosition, 3] = caseList[i].PatientName; ws.Cells[rowPosition, 4] = caseList[i].PhysicianName + " - " + caseList[i].ClientName; ws.Cells[rowPosition, 5] = caseList[i].OrderedBy; rowPosition++; } Object oMissing = Type.Missing; Object oFalse = false; ws.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wb.Close(oFalse, oMissing, oMissing); xlApp.Quit(); }
public void Dispose() { excelApp.Quit(); Marshal.ReleaseComObject(excelApp); Marshal.FinalReleaseComObject(excelApp); excelApp = null; }
public void insertBTN(String query, String path) { sqlCon.ConnectionString = conn; sqlCon.Open(); SqlDataAdapter da = new SqlDataAdapter(query, sqlCon); System.Data.DataTable dtMainSQLData = new System.Data.DataTable(); da.Fill(dtMainSQLData); DataColumnCollection dcCollection = dtMainSQLData.Columns; // Export Data into EXCEL Sheet Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Application.Workbooks.Add(Type.Missing); // ExcelApp.Cells.CopyFromRecordset(objRS); for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++) { for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++) { if (i == 1) { ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString(); } else ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString(); } } ExcelApp.ActiveWorkbook.SaveCopyAs(path + "\\Results.xlsx"); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); }
private void btn_Read_Click(object sender, EventArgs e) { int P_int_Count=0;//记录正在读取的行数 string P_str_Line, P_str_Content = "";//记录读取行的内容及遍历到的内容 List<string> P_str_List = new List<string>();//存储读取的所有内容 StreamReader SReader = new StreamReader(txt_Txt.Text, Encoding.Default);//实例化流读取对象 while ((P_str_Line = SReader.ReadLine()) != null)//循环读取文本文件中的每一行 { P_str_List.Add(P_str_Line);//将读取到的行内容添加到泛型集合中 P_int_Count++;//使当前读取行数加1 } Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 object missing = System.Reflection.Missing.Value;//获取缺少的object类型值 //打开指定的Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing); excel.Application.DisplayAlerts = false;//不显示提示对话框 for (int i = 0; i < P_str_List.Count; i++)//遍历泛型集合 { P_str_Content = P_str_List[i];//记录遍历到的值 if (Regex.IsMatch(P_str_Content, "^[0-9]*[1-9][0-9]*$"))//判断是否是数字 newWorksheet.Cells[i + 1, 1] = Convert.ToDecimal(P_str_Content).ToString("¥00.00");//格式化为货币格式,再添加到工作表中 else newWorksheet.Cells[i + 1, 1] = P_str_Content;//直接将遍历到的内容添加到工作表中 } workbook.Save();//保存工作表 workbook.Close(false, missing, missing);//关闭工作表 MessageBox.Show("已经将文本文件内容成功导入Excel工作表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public void ConvertCsvToExcel_MicrosoftOfficeInteropExcel() { StringBuilder content = new StringBuilder(); content.AppendLine("param1\tparam2\tstatus"); content.AppendLine("0.5\t10\tpassed"); content.AppendLine("10\t20\tfail"); using (TemporaryFile xlsxFile = new TemporaryFile(".xlsx")) { Clipboard.SetText(content.ToString()); Microsoft.Office.Interop.Excel.Application xlexcel; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlexcel = new Microsoft.Office.Interop.Excel.Application(); // for excel visibility //xlexcel.Visible = true; // Creating a new workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); // Putting Sheet 1 as the sheet you want to put the data within xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1); // creating the range Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range) xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.Paste(CR, false); xlWorkSheet.SaveAs(xlsxFile.FileName); xlexcel.Quit(); Console.WriteLine("Created file {0}", xlsxFile.FileName); } }
public static void OpenExcelDocs2(string filename, double[] content) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象 Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(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); //引用Excel工作簿 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ; //引用Excel工作页面 excel.Visible = false; sheet.Cells[24, 3] = content[1]; sheet.Cells[25, 3] = content[0]; book.Save(); book.Close(Type.Missing, Type.Missing, Type.Missing); 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(); //sheet = null; //book = null; //excel = null; //不能杀死进程 //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); //可以释放对象,但是不能杀死进程 //System.Runtime.InteropServices.Marshal.ReleaseComObject(book); //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); }
/// <summary> /// 加载数据同时保存数据到指定位置 /// </summary> /// <param name="obj"></param> private void AddData(FarPoint.Win.Spread.FpSpread obj) { wait = new WaitDialogForm("", "正在加载数据, 请稍候..."); try { //打开Excel表格 //清空工作表 fpSpread1.Sheets.Clear(); obj.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls"); PF.SpreadRemoveEmptyCells(obj); //this.AddCellChanged(); //this.barEditItem2.Visibility = DevExpress.XtraBars.BarItemVisibility.Never; //S4_2_1.AddBarEditItems(this.barEditItem2, this.barEditItem1, this); } catch (System.Exception e) { //如果打开出错则重新生成并保存 LoadData(); //判断文件夹是否存在,不存在则创建 if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls")) { Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls"); } //保存EXcel文件 obj.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet); // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls"; workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.WrapText = true; //保护工作表 ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); } wait.Close(); }
public static void Main(string[] args) { PowerMILL.Application pMAppliacation = (PowerMILL.Application) System.Runtime.InteropServices.Marshal.GetActiveObject("PowerMill.Application"); Microsoft.Office.Interop.Excel.Application exApplication = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook exWorkbook = exApplication.Workbooks.Open(@"e:\ExTest.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t",false, false, 0, true, 1, 0); Microsoft.Office.Interop.Excel._Worksheet exWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)exWorkbook.Sheets[1]; Microsoft.Office.Interop.Excel.Range exRange = exWorksheet.UsedRange; int rowCount = exRange.Rows.Count; int colCount = exRange.Columns.Count; for (int i = 1; i <= rowCount; i++) { for (int j = 1; j <= colCount; j++) { string valueForPM = null; try { valueForPM = (string)(exRange.Cells[i, j] as Microsoft.Office.Interop.Excel.Range).Value.ToString(); } catch {} if (valueForPM != null) { pMAppliacation.DoCommand(@"MESSAGE INFO """+ valueForPM +@""""); } } } }
public byte[] Convert() { var excel = new Application(); if (File.Exists(FileToSave)) { File.Delete(FileToSave); } try { excel.Workbooks.Open(Filename: FullFilePath); excel.Visible = false; if (excel.Workbooks.Count > 0) { var wsEnumerator = excel.ActiveWorkbook.Worksheets.GetEnumerator(); object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml; while (wsEnumerator.MoveNext()) { var wsCurrent = (Microsoft.Office.Interop.Excel.Worksheet)wsEnumerator.Current; wsCurrent.SaveAs(Filename: FileToSave, FileFormat: format); break; } excel.Workbooks.Close(); } } finally { excel.Application.Quit(); } return base.ReadConvertedFile(); }
public void CreateExcelFile() { app = new Microsoft.Office.Interop.Excel.Application(); workbook = app.Workbooks.Add(1); worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1]; AddExcelProcessTolist(); }
private void button2_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook; Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet; ////Книга. //ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value); ////Таблица. //ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); ExcelApp.Workbooks.Open(Application.StartupPath + "\\result.xlsx"); for (int j = 0; j < dataGridView1.ColumnCount; j++) { ExcelApp.Cells[3, j + 1] = dataGridView1.Columns[j].HeaderText; } for (int i = 0; i < dataGridView1.Rows.Count; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { ExcelApp.Cells[i + 4, j + 1] = dataGridView1.Rows[i].Cells[j].Value; } } //Вызываем нашу созданную эксельку. ExcelApp.Visible = true; ExcelApp.UserControl = true; }
private void btn_Export_Click(object sender, EventArgs e) { object missing = System.Reflection.Missing.Value;//声明object缺省值 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 //打开Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表 Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application();//实例化Access对象 worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[cbox_SheetName.Text]);//获取选择的工作表 worksheet.Move(workbook.Sheets[1], missing);//将选择的工作表作为第一个工作表 object P_obj_Name = (object)worksheet.Name;//获取工作表名称 excel.DisplayAlerts = false;//设置Excel保存时不显示对话框 workbook.Save();//保存工作簿 CloseProcess("EXCEL");//关闭所有Excel进程 object P_obj_Excel = (object)txt_Path.Text;//记录Excel文件路径 try { access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库 //将Excel指定工作表中的数据导入到Access中 access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_obj_Excel, true, missing, missing); access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);//关闭并保存Access数据库文件 CloseProcess("MSACCESS");//关闭所有Access数据库进程 MessageBox.Show("已经将Excel的" + cbox_SheetName.Text + "工作表中的数据导入到Access数据库中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch { MessageBox.Show("Access数据库中已经存在该表!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
public ExcelFile(string filePath) { _application = new Microsoft.Office.Interop.Excel.Application(); _workbook = _application.Workbooks.Open(filePath); _worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets.Item[1]; _filename = filePath; _currentSheetNumber = 1; }
/* private void loadKeyTable(Microsoft.Office.Interop.Excel.Worksheet sheet) { foreach (string key in keyList_) { for (int i = 1; i < 100; i++) { Microsoft.Office.Interop.Excel.Range rgn = sheet.Cells[3, i]; dynamic val = rgn.Value2; string title = Convert.ToString(val); int indexof = title.IndexOf(key); if (indexof != -1) { keyTable_.Add(key, i); break; } } } } */ public DataExcelManager(Microsoft.Office.Interop.Excel.Application app, string path, string sheet) { excelApp_ = app; excelPath_ = path; sheetName_ = sheet; //keyList_ = new string[] { "Collection Retail", "Market", "Product reference", "Product Reference Name", "Product color code", "Product color", "Product size", "YEN", "Note" }; //keyTable_ = new Hashtable(); }
//导出 private void barBtnDaochuExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = ""; saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { fname = saveFileDialog1.FileName; try { fpSpread1.SaveExcel(fname); //以下是打开文件设表格自动换行 // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws = null; Microsoft.Office.Interop.Excel.Range range = null; excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= workBook.Worksheets.Count; i++) { ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i]; //取消保护工作表 ws.Unprotect(Missing.Value); //有数据的行数 int row = ws.UsedRange.Rows.Count; //有数据的列数 int col = ws.UsedRange.Columns.Count; //创建一个区域 range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]); //设区域内的单元格自动换行 range.WrapText = true; //保护工作表 //ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } //保存工作簿 workBook.Save(); //关闭工作簿 excelApp.Workbooks.Close(); if (MessageBox.Show("导出成功,是否打开该文档?","询问",MessageBoxButtons.YesNo,MessageBoxIcon.Question) != DialogResult.Yes) return; System.Diagnostics.Process.Start(fname); } catch { MessageBox.Show("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。"); return; } } }
private void OutputExecelFromDataTable(DataTable dt, string EXCEL_SAVE_PATH) { if (dt.Rows.Count > 0) {// Excelを参照設定する必要があります // [参照の追加],[COM],[Microsoft Excel *.* Object Library] // Imports Microsoft.Office.Interop (必要) // Imports System.Runtime.InteropServices (必要) //Excel出力 // EXCEL関連オブジェクトの定義 // Excelを起動する Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application(); objExcel.Visible = true; try { //待機状態 Cursor.Current = Cursors.WaitCursor; // ブック(ファイル)を開く Microsoft.Office.Interop.Excel.Workbook objWorkBook = objExcel.Workbooks.Open(EXCEL_SAVE_PATH); try { // 現在日時を取得 string timestanpText = DateTime.Now.ToString(" yyyy/MM/dd HH:mm"); //bbl.GetDate(); int count = 0; int currentRow = (int)ERow.DetailStartRow; int page = 1; string breakKey = ""; foreach (DataRow row in dt.Rows) { if (breakKey != "" && breakKey != row["VendorCD"].ToString()) { count = DATA_COUNT; } if (count >= DATA_COUNT) { //行1からGYO_COUNTまでの行をこぴーする objWorkBook.Sheets[1].Range("1:" + GYO_COUNT).Copy(objWorkBook.Sheets[1].Range("A" + (GYO_COUNT * page + 1))); //Detail部をクリアする currentRow = (int)ERow.DetailStartRow + page * GYO_COUNT; objWorkBook.Sheets[1].Range("A" + currentRow + ":BZ" + (currentRow + DATA_COUNT - 1)).ClearContents(); page++; count = 0; } if (count == 0) { breakKey = row["VendorCD"].ToString(); if (page == 1) { objWorkBook.Sheets[1].Cells[2, 64] = timestanpText; objWorkBook.Sheets[1].Cells[(int)ERow.Print1, (int)ECell.Print1] = row["Print1"].ToString(); objWorkBook.Sheets[1].Cells[(int)ERow.Print2, (int)ECell.Print2] = row["Print2"].ToString(); objWorkBook.Sheets[1].Cells[(int)ERow.Print3, (int)ECell.Print3] = row["Print3"].ToString(); objWorkBook.Sheets[1].Cells[(int)ERow.Print4, (int)ECell.Print4] = row["Print4"].ToString(); objWorkBook.Sheets[1].Cells[(int)ERow.ZIP, (int)ECell.ZIP] = row["ZIP"].ToString(); objWorkBook.Sheets[1].Cells[(int)ERow.CompanyName, (int)ECell.CompanyName] = row["CompanyName"].ToString(); objWorkBook.Sheets[1].Cells[(int)ERow.TEL, (int)ECell.TEL] = row["TEL"].ToString(); } objWorkBook.Sheets[1].Cells[(int)ERow.VendorName + (page - 1) * GYO_COUNT, (int)ECell.VendorName] = row["VendorName"].ToString(); objWorkBook.Sheets[1].Cells[(int)ERow.OrderPerson + (page - 1) * GYO_COUNT, (int)ECell.OrderPerson] = row["OrderPerson"].ToString(); } objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaseDate] = row["PurchaseDate"].ToString(); objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.VendorDeliveryNo] = row["VendorDeliveryNo"].ToString(); objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.MakerItem] = row["MakerItem"].ToString(); objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.ColorName] = row["ColorName"].ToString() + Environment.NewLine + row["SizeName"].ToString(); objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaserUnitPrice] = row["PurchaserUnitPrice"].ToString(); objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.OrderUnitPrice] = row["OrderUnitPrice"].ToString(); objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaseSu] = row["PurchaseSu"].ToString(); currentRow++; count++; } //// エクセル表示 //objExcel.Visible = true; objWorkBook.Save(); // クローズ objWorkBook.Close(false); objExcel.Quit(); } finally { if (objWorkBook != null) { try { //objWorkBook.Close(); } finally { // EXCEL解放 System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkBook); objWorkBook = null /* TODO Change to default(_) if this is not a reference type */; } } } } finally { if (objExcel != null) { try { //objExcel.Quit(); } finally { // EXCEL解放 System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel); objExcel = null /* TODO Change to default(_) if this is not a reference type */; } } //元に戻す Cursor.Current = Cursors.Default; } } }
/// <summary> /// FUNCTION FOR EXPORT TO EXCEL /// </summary> /// <param name="dataTable"></param> /// <param name="worksheetName"></param> /// <param name="saveAsLocation"></param> /// <returns></returns> public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType) { Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook excelworkBook; Microsoft.Office.Interop.Excel.Worksheet excelSheet; Microsoft.Office.Interop.Excel.Range excelCellrange; try { // Start Excel and get Application object. excel = new Microsoft.Office.Interop.Excel.Application(); // for making Excel visible excel.Visible = false; excel.DisplayAlerts = false; // Creation a new Workbook excelworkBook = excel.Workbooks.Add(Type.Missing); // Workk sheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; excelSheet.Name = worksheetName; excelSheet.Cells[1, 1] = ReporType; excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString(); // loop through each row and add values to our sheet int rowcount = 2; foreach (DataRow datarow in dataTable.Rows) { rowcount += 1; for (int i = 1; i <= dataTable.Columns.Count; i++) { // on the first iteration we add the column headers if (rowcount == 3) { excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName; excelSheet.Cells.Font.Color = System.Drawing.Color.Black; } excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString(); //for alternate rows if (rowcount > 3) { if (i == dataTable.Columns.Count) { if (rowcount % 2 == 0) { excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false); } } } } } // now we resize the columns excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); excelworkBook.SaveAs(saveAsLocation); excelworkBook.Close(); excel.Quit(); ShowSavedExcelFile(saveAsLocation); return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } finally { excelSheet = null; excelCellrange = null; excelworkBook = null; } }
private void expExportToExcel_Expanded(object sender, RoutedEventArgs e) { int intRowCounter; int intRowNumberOfRecords; int intColumnCounter; int intColumnNumberOfRecords; // Creating a Excel object. Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; try { expExportToExcel.IsExpanded = false; worksheet = workbook.ActiveSheet; worksheet.Name = "OpenOrders"; int cellRowIndex = 1; int cellColumnIndex = 1; intRowNumberOfRecords = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Rows.Count; intColumnNumberOfRecords = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Columns.Count; for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Columns[intColumnCounter].ColumnName; cellColumnIndex++; } cellRowIndex++; cellColumnIndex = 1; //Loop through each row and read value from each column. for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++) { for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Rows[intRowCounter][intColumnCounter].ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } //Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.FilterIndex = 1; saveDialog.ShowDialog(); workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } catch (System.Exception ex) { TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // My Tickets // Export To Excel " + ex.Message); MessageBox.Show(ex.ToString()); } finally { excel.Quit(); workbook = null; excel = null; } }
static void TaskMethod(object title) { lock (taskMethodLocj) { DataGridView dataGridView1 = InDataWindow.InData_.dataGridView1; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 //写入标题 for (int i = 0; i < dataGridView1.ColumnCount; i++) { worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; } //写入数值 for (int r = 0; r < dataGridView1.Rows.Count; r++) { for (int i = 0; i < dataGridView1.ColumnCount; i++) { if (i == 7) { worksheet.Cells[r + 2, i + 1] = "'" + dataGridView1.Rows[r].Cells[i].Value; } else { worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value; } } Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应 // if (Microsoft.Office.Interop.cmbxType.Text != "Notification") // { // Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]); // rg.NumberFormat = "00000000"; // } if (title.ToString() != "") { try { workbook.Saved = true; workbook.SaveCopyAs(title.ToString()); //fileSaved = true; } catch (Exception ex) { //fileSaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } } //else //{ // fileSaved = false; //} xlApp.Quit(); GC.Collect(); //强行销毁 // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL MessageBox.Show("导出文件成功", "提示", MessageBoxButtons.OK); } }
private void btn_exportExcel_Click(object sender, EventArgs e) { btn_exportExcel.Enabled = false; if (grdvw_SN_data.Rows.Count > 1) { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; // see the excel sheet behind the program Microsoft.Office.Interop.Excel.Range aRange; //app.Visible = true; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; // changing the name of active sheet //worksheet.Name = "Exported from gridview"; //worksheet.AllocatedRange.AutoFitColumns(); //worksheet.AllocatedRange.AutoFitRows(); // storing header part in Excel SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "XLS (*.xlsx)|*.xlsx"; sfd.FileName = "Output.xlsx"; //string filename1 = @"D:\temp\test.csv"; bool fileError = false; if (sfd.ShowDialog() == DialogResult.OK) { if (File.Exists(sfd.FileName)) { try { File.Delete(sfd.FileName); } catch (IOException ex) { fileError = true; MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message); } } } if (!fileError) { try { string CsvFpath = sfd.FileName; for (int l = 1; l < grdvw_SN_data.Columns.Count + 1; l++) { worksheet.Cells[1, l] = grdvw_SN_data.Columns[l - 1].HeaderText; } int i = 0, j = 0; // storing Each row and column value to excel sheet for (i = 0; i < grdvw_SN_data.Rows.Count - 1; i++) { for (j = 0; j < grdvw_SN_data.Columns.Count; j++) { //app.Range["G"].NumberFormat = app.Range["A" + (i + 2)].NumberFormat = "@"; app.Range["E" + (i + 2)].NumberFormat = "@"; app.Range["F" + (i + 2)].NumberFormat = "0"; app.Range["G" + (i + 2)].NumberFormat = "@"; app.Range["J" + (i + 2)].NumberFormat = "0"; app.Range["K" + (i + 2)].NumberFormat = "@"; worksheet.Cells[i + 2, j + 1] = grdvw_SN_data.Rows[i].Cells[j].Value.ToString(); } } aRange = worksheet.get_Range("A2", "N" + (i + 1)); aRange.Columns.AutoFit(); // CellRange cRange = worksheet.Range[] aRange.Borders.LineStyle = LineStyleType.Dashed; //aRange.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None; //aRange.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None; // save the application workbook.SaveAs(CsvFpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Exit from the application MessageBox.Show("Data Exported Successfully !!!", "Info"); app.Quit(); } catch (Exception ex) { MessageBox.Show("Error :" + ex.Message); } } } else { MessageBox.Show("No Record To Export !!!", "Info"); } btn_exportExcel.Enabled = true; }
private bool ExportDataToExcel() { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); try { // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; // see the excel sheet behind the program app.Visible = true; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; // changing the name of active sheet worksheet.Name = cmbBillingCycle.Text; // storing header part in Excel for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value == null ? string.Empty : dataGridView1.Rows[i].Cells[j].Value.ToString(); } } var outputPath = Properties.Settings.Default.OutputFolderPath; outputPath += cmbAccountName.Text + "\\" + cmbYear.Text + "\\" + cmbBillingCycle.Text + "\\"; var path = string.Empty; if (errorMsgs.Count() > 0) { outputPath = outputPath + "ErrorReport\\"; CreateFolderStructure(outputPath); path = outputPath + "Error.xls"; } else { CreateFolderStructure(outputPath); path = outputPath + cmbBillingCycle.Text + ".xls"; } // save the application workbook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //workbook.Close(); workbook.Close(0); //app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); // Exit from the application return(true); } catch { return(false); } finally { workbook.Close(Type.Missing, Type.Missing, Type.Missing); app.Quit(); if (workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); } //app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); } }
public ActionResult UploadFileAjax(string accion, string fec_informe, string fec_informe_hasta) { try { for (int i = 0; i < Request.Files.Count; i++) { var fileContent = Request.Files[i]; if (fileContent != null && fileContent.ContentLength > 0) { string fileCliente = Path.GetFileName(fileContent.FileName); string fileExtension = Path.GetExtension(fileContent.FileName); string fileDestino = string.Empty; string fileDestinoDBS = string.Empty; int pid = 0; if (fileExtension != ".xlsx" && fileExtension != ".xls") { return(Json( new Response { Status = HttpStatusCode.BadRequest, Message = "El archivo debe ser tipo Excel (.xlsx,.xls)", }, JsonRequestBehavior.AllowGet)); } // get a stream var stream = fileContent.InputStream; var nomenclatura = "BAL_Car_" + DateTime.Now.ToString("yyMMddhhmmss") + ".xlsx"; if (HttpContext.IsDebuggingEnabled) { fileDestino = Path.Combine(@"M:\MVC\com\xlsd", nomenclatura); fileDestinoDBS = fileDestino.Replace(@"M:\MVC\com\xlsd", xlsPathDES); } else { fileDestino = Path.Combine(Server.MapPath("~/XlsD"), nomenclatura); fileDestinoDBS = fileDestino.Replace(Server.MapPath("~/XlsD"), xlsPathPRO); } var mensajeError = string.Empty; if (accion == "CARGA") { using (var fileStream = System.IO.File.Create(fileDestinoDBS)) { stream.CopyTo(fileStream); } } // Formateando excel a 2 decimales //Grabacion de temporal en sql var cod_unidad_negocio = string.Empty; if (Session["cod_unidad_negocio"] != null) { cod_unidad_negocio = Session["cod_unidad_negocio"].ToString(); } try { var excelApp = new Microsoft.Office.Interop.Excel.Application(); //CreateObject("Excel.Application") excelApp.Application.Visible = false; excelApp.DisplayAlerts = false; var excelWorkbook = excelApp.Workbooks.Open(Filename: fileDestinoDBS, ReadOnly: false); GetWindowThreadProcessId(excelApp.Hwnd, out pid); try { excelWorkbook.Sheets["BD Balmet"].Range("F6:EO500").NumberFormat = "0.0000000000000000"; } catch (Exception exc) { mensajeError = "ERROR: En la ejecución de Excel " + exc.Message; } //Close Excel excelWorkbook.Close(SaveChanges: true); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); excelWorkbook = null; excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; var archivoDestino = fileDestinoDBS.Replace(@"\", "/"); try { //procesoExcel.Kill(); Process.GetProcessById(pid).Kill(); } catch (Exception) { } } catch (Exception ex) { System.Threading.Thread.Sleep(1000); //Espera un segundo para intentarlo de nuevo mensajeError += " Servicio de Excel en estos momentos se encuentra sin recursos.\r Por favor intentelo mas tarde."; } DateTime dtfecInforme = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", CultureInfo.InvariantCulture); DateTime dtfecInformeHasta = DateTime.ParseExact(fec_informe_hasta, "dd/MM/yyyy", CultureInfo.InvariantCulture); var execute = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "CARGA", 0, dtfecInforme, dtfecInformeHasta); if (execute.tipo == "ERROR") { return(Json( new Response { Status = HttpStatusCode.BadRequest, Message = execute.mensaje.Replace("\n", "<br>") }, JsonRequestBehavior.AllowGet)); } else if (execute.tipo == "SUCCESS") { execute = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "START", 0, dtfecInforme, dtfecInformeHasta); if (execute.tipo == "ERROR") { return(Json( new Response { Status = HttpStatusCode.BadRequest, Message = execute.mensaje, Kind = execute.tipo, Id = execute.Id.ToString() }, JsonRequestBehavior.AllowGet)); } else if (execute.tipo == "WARNING") { return(Json( new Response { Status = HttpStatusCode.BadRequest, Message = execute.mensaje, Kind = execute.tipo, Id = execute.Id.ToString(), Aux = nomenclatura, Aux2 = fileDestinoDBS, Aux3 = fileDestino }, JsonRequestBehavior.AllowGet)); } else { execute = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "DETALLE", execute.Id, dtfecInforme, dtfecInformeHasta); if (execute.tipo == "ERROR") { return(Json( new Response { Status = HttpStatusCode.BadRequest, Message = execute.mensaje, Kind = execute.tipo, Id = execute.Id.ToString() }, JsonRequestBehavior.AllowGet)); } else { return(Json( new Response { Status = HttpStatusCode.OK, Message = execute.mensaje }, JsonRequestBehavior.AllowGet)); } } } } } } catch (Exception e) { return(Json( new Response { Status = HttpStatusCode.BadRequest, Message = "El archivo no se cargó. " + e.Message, }, JsonRequestBehavior.AllowGet)); } return(Json( new Response { Status = HttpStatusCode.BadRequest, Message = "No se puede continuar por errores en el modelo", Errors = ModelState.Values.SelectMany(x => x.Errors).Select(x => x.ErrorMessage) }, JsonRequestBehavior.AllowGet)); }
public static void Run() { #region kill any Excel process before run the app Process[] prs = Process.GetProcesses(); foreach (Process pr in prs) { if (pr.ProcessName == "EXCEL") { pr.Kill(); } } #endregion try { // Create a new FileSystemWatcher and set its properties. FileSystemWatcher watcher = new FileSystemWatcher(); watcher.Path = ConfigurationManager.AppSettings["RootFolder"]; /* Watch for changes in LastAccess and LastWrite times, and the renaming of files or directories. */ watcher.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite | NotifyFilters.FileName | NotifyFilters.DirectoryName; // Only watch xls files. watcher.Filter = "*.xls*"; // Add event handlers. // Begin watching. watcher.EnableRaisingEvents = true; #region open and work with excel file foreach (string file in Directory.EnumerateFiles(ConfigurationManager.AppSettings["RootFolder"], "*.xls")) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file); Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; inputFileName = Path.GetFileNameWithoutExtension(file); ArrayList vagonsList = new ArrayList(); Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //create COM-object Excel excel.Visible = false; //make excel process invisible excel.Workbooks.Add(Type.Missing); //add workbook excel.SheetsInNewWorkbook = 1; //number of pages in workbook Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks[1]; //get link on 1st book Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Worksheets.get_Item(1); //get link on 1st page sheet.Name = "Report" + " " + DateTime.Now.ToString("dd.MM.yy"); sheet.Cells[1, 1] = "№ КОНТЕЙНЕРА"; sheet.Cells[1, 2] = "СТАНЦ"; sheet.Cells[1, 3] = "ОПЕР"; sheet.Cells[1, 4] = "ДАТА"; sheet.Cells[1, 5] = "ВРЕМЯ"; sheet.Cells[1, 6] = "СОСТ"; sheet.Cells[1, 7] = "N ОТПР"; sheet.Cells[1, 8] = "N ВАГОНА"; for (int i = 1; i <= rowCount; i++) { for (int j = 1; j <= colCount; j++) { if (j == 1) { if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null) { //add useful things here! vagonsList.Add(xlRange.Cells[i, j].Value2.ToString()); } } } } ConsoleColor color = Console.ForegroundColor; Console.ForegroundColor = ConsoleColor.DarkYellow; Console.WriteLine($"Start processing file {inputFileName}."); Console.ForegroundColor = color; for (int i = 0; i < vagonsList.Count; i++) { try { int list = vagonsList.Count; var container = vagonsList[i]; if (vagonsList != null) { //Console.WriteLine($"Start processing file {inputFileName}."); File.WriteAllText(ConfigurationManager.AppSettings["querryFolder"] + "01" + 11 + "000" + ".000", string.Format("(:217 0:1680 {0}:)", container)); Console.WriteLine($"Now processing: {container}"); System.Threading.Thread.Sleep(9900); } else { break; } string answerFileName = "01" + 11 /*15й порт Левитан, 11 мой*/ + "2400"; DirectoryInfo dirConcentratorPath = new DirectoryInfo(ConfigurationManager.AppSettings["answerFolder"]); FileInfo[] fileInDir = dirConcentratorPath.GetFiles(answerFileName + "*.*"); foreach (FileInfo foundFile in fileInDir) { string fullName = foundFile.FullName; var lines = File.ReadAllLines(foundFile.FullName, Encoding.GetEncoding(866)); sheet.get_Range("E2", string.Format("E{0}", vagonsList.Count)).NumberFormat = "@"; string text = ""; // search key variable using (StreamReader sr = new StreamReader(foundFile.FullName, Encoding.GetEncoding(866))) { text = sr.ReadToEnd(); Regex regexNoInfo = new Regex("[Н][Е][Т]\\s[А-Я]{10}"); foreach (var line in lines) { var NoInfoMatches = regexNoInfo.Matches(line); if (NoInfoMatches.Count > 0) { var res = NoInfoMatches[0].Value; string noinfo = "-"; sheet.Cells[i + 2, 1].Value = container; sheet.Cells[i + 2, 2].Value = noinfo; sheet.Cells[i + 2, 3].Value = noinfo; sheet.Cells[i + 2, 4].Value = noinfo; sheet.Cells[i + 2, 5].Value = noinfo; sheet.Cells[i + 2, 6].Value = noinfo; sheet.Cells[i + 2, 7].Value = noinfo; sheet.Cells[i + 2, 8].Value = noinfo; break; } else { continue; } } Regex regexOkInfo = new Regex(@"([О][П][Е][Р][А][Ц][И][И]\s[С]\s[К])"); foreach (var line in lines) { var okInfoMatches = regexOkInfo.Matches(line); if (okInfoMatches.Count > 0) { string tempAnswer = lines[lines.Length - 4]; string pattern = @"([A-Я]{4})|([А-Я]{3}.)|(\d\d[.]\d\d[.]\d\d)|([А-Я]{3}.)|(\d{2}[-]\d{2})|(\d{8})|(\d{6}?)"; Regex rgx = new Regex(pattern); MatchCollection matchList = Regex.Matches(tempAnswer, pattern); var results = matchList.Cast <Match>().Select(match => match.Value).ToList(); var station = matchList[0].Value; var operation = matchList[1].Value; var dt = matchList[2].Value; DateTime dateOfOperation = Convert.ToDateTime(dt); string timeOfOperation = matchList[3].Value; var state = matchList[4].Value; if (results.Count == 5) { sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column sheet.Cells[i + 2, 2].Value = station; sheet.Cells[i + 2, 3].Value = operation; sheet.Cells[i + 2, 4].Value = dateOfOperation; sheet.Cells[i + 2, 5].Value = timeOfOperation; sheet.Cells[i + 2, 6].Value = state; string noinfo = "-"; sheet.Cells[i + 2, 7].Value = noinfo; sheet.Cells[i + 2, 8].Value = noinfo; } else if (results.Count == 6) { var otpravka = matchList[5].Value; sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column sheet.Cells[i + 2, 2].Value = station; sheet.Cells[i + 2, 3].Value = operation; sheet.Cells[i + 2, 4].Value = dateOfOperation; sheet.Cells[i + 2, 5].Value = timeOfOperation; sheet.Cells[i + 2, 6].Value = state; sheet.Cells[i + 2, 7].Value = otpravka; string noinfo = "-"; sheet.Cells[i + 2, 8].Value = noinfo; } else { var otpravka = matchList[5].Value; var vagon = matchList[6].Value; #endregion sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column sheet.Cells[i + 2, 2].Value = station; sheet.Cells[i + 2, 3].Value = operation; sheet.Cells[i + 2, 4].Value = dateOfOperation; sheet.Cells[i + 2, 5].Value = timeOfOperation; sheet.Cells[i + 2, 6].Value = state; sheet.Cells[i + 2, 7].Value = otpravka; sheet.Cells[i + 2, 8].Value = vagon; } } else { continue; } } } File.Delete(foundFile.FullName); } } catch (Exception e) { foreach (Process pr in prs) { if (pr.ProcessName == "EXCEL" && pr.ProcessName == "FastCustom") { pr.Kill(); } } //this will use the "fileLogger" logger from our NLog.config file Logger logger = LogManager.GetLogger("fileLogger"); //add custom message and pass in the exception logger.Error(e, "Need fix some problems!"); } } #region Formatting excel var formatTable = vagonsList.Count + 1; // fix applying formatting for last string sheet.get_Range("B1", string.Format("H{0}", formatTable)).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //aligment center Microsoft.Office.Interop.Excel.Range chartRange; chartRange = sheet.get_Range("a1", "h1"); foreach (Microsoft.Office.Interop.Excel.Range cells in chartRange.Cells) { cells.BorderAround2(); } #endregion try { sheet.Columns.AutoFit(); // autofit string reportName = inputFileName + " от " + DateTime.Now.ToString("dd.MM.yyyy, HH-mm"); workbook.SaveAs(ConfigurationManager.AppSettings["reportFolder"] + reportName + ".xlsx"); excel.Workbooks.Close(); excel.Quit(); Console.ForegroundColor = ConsoleColor.DarkGreen; Console.WriteLine($"Processing file {inputFileName} is completed."); Console.ForegroundColor = color; } catch (System.Runtime.InteropServices.COMException) { foreach (Process pr in prs) { if (pr.ProcessName == "EXCEL") { pr.Kill(); } } } //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); //Console.WriteLine("Press \'q\' to quit the console."); //while (Console.Read() != 'q') ; } //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); } #region clean all excel processes again just in case catch (Exception e) { foreach (Process pr in prs) { if (pr.ProcessName == "EXCEL" && pr.ProcessName == "FastCustom") { pr.Kill(); } } //this will use the "fileLogger" logger from our NLog.config file Logger logger = LogManager.GetLogger("fileLogger"); //add custom message and pass in the exception logger.Error(e, "Need fix some problems!"); } #endregion }
public string CreatePdfFromExcel(int id = 0) { try { string workbookPath = @"C:\Users\fwang\source\Workspaces\Workspace\RiskNet\Dashboard\DashboardBranch\Presentation\Nop.Web\ExportPdfFiles\Temp"; var directory = new DirectoryInfo(workbookPath); if (id != 0) { foreach (FileInfo myFile in directory.GetFiles()) { try { myFile.Delete(); } catch (Exception ex) { } } return(""); } foreach (FileInfo myFile in directory.GetFiles("*.xlsx").OrderByDescending(f => f.LastWriteTime)) { using (new Impersonator("fwang", "mobius", "Yibing9!")) { Microsoft.Office.Interop.Excel.Application excelApplication; Microsoft.Office.Interop.Excel.Workbook excelWorkbook; excelApplication = new Microsoft.Office.Interop.Excel.Application(); excelApplication.ScreenUpdating = false; excelApplication.DisplayAlerts = false; excelWorkbook = excelApplication.Workbooks.Open(myFile.FullName); Microsoft.Office.Interop.Excel.Worksheet ws = excelWorkbook.Sheets[1]; ws.PageSetup.Zoom = false; ws.PageSetup.FitToPagesTall = false; ws.PageSetup.FitToPagesWide = 1; try { excelWorkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, myFile.FullName + Guid.NewGuid().ToString() + ".pdf"); } catch (System.Exception ex) { } finally { excelWorkbook.Close(); excelApplication.Quit(); excelApplication = null; excelWorkbook = null; } } } }catch (Exception ex) { string s = ex.Message + " " + ex.StackTrace; return(s); } return("test"); MySimpleClass o = new MySimpleClass(); MySimpleClass o2 = new MySimpleClass(); o.StringVal1 = "Test test"; o.StringVal2 = "test2"; try { this.AutoMapper(o, o2); }catch (Exception ex) { StackTrace st = new StackTrace(true); string stackIndent = ""; for (int i = 0; i < st.FrameCount; i++) { // Note that at this level, there are four // stack frames, one for each method invocation. StackFrame sf = st.GetFrame(i); Console.WriteLine(); Console.WriteLine(stackIndent + " Method: {0}", sf.GetMethod()); Console.WriteLine(stackIndent + " File: {0}", sf.GetFileName()); Console.WriteLine(stackIndent + " Line Number: {0}", sf.GetFileLineNumber()); stackIndent += " "; } throw ex; } return(o2.StringVal1); }
private void ExcelCreation(DataSet dtDataByfilter) { try { dtDataByfilter.Tables[0].TableName = "Target"; // dtDataByfilter.Tables[1].TableName = "Incentive Plan"; // dtDataByfilter.Tables[2].TableName = "Report"; string myServerPath = Server.MapPath("~/ExcelTemplate/SalePurchase-Template.xlsx"); //var excel = new Microsoft.Office.Interop.Excel.Application(); //var workbook = excel.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //excel.Visible = true; //excel.Worksheets.Add(dtDataByfilter.Tables[0], "Target1"); Microsoft.Office.Interop.Excel.Workbook wbExiting = excel.Workbooks.Open(myServerPath); Microsoft.Office.Interop.Excel.Worksheet sh = wbExiting.Sheets[1]; if (dtDataByfilter != null && dtDataByfilter.Tables[0].Rows.Count > 0) { for (int i = 0; i < dtDataByfilter.Tables[0].Rows.Count; i++) { sh.Cells[i + 2, 1] = dtDataByfilter.Tables[0].Rows[i][0]; sh.Cells[i + 2, 2] = dtDataByfilter.Tables[0].Rows[i][1]; sh.Cells[i + 2, 3] = dtDataByfilter.Tables[0].Rows[i][2]; sh.Cells[i + 2, 4] = dtDataByfilter.Tables[0].Rows[i][3]; sh.Cells[i + 2, 5] = dtDataByfilter.Tables[0].Rows[i][4]; sh.Cells[i + 2, 6] = dtDataByfilter.Tables[0].Rows[i][5]; sh.Cells[i + 2, 7] = dtDataByfilter.Tables[0].Rows[i][6]; sh.Cells[i + 2, 8] = dtDataByfilter.Tables[0].Rows[i][7]; sh.Cells[i + 2, 9] = dtDataByfilter.Tables[0].Rows[i][8]; } } string strUniqueFileName = string.Empty; strUniqueFileName = Session["USERID"].ToString(); string myServerPath1 = Server.MapPath("~/ExcelTemplate/" + strUniqueFileName + ".xlsx"); FileInfo file = new FileInfo(myServerPath1); if (file.Exists) { file.Delete(); } wbExiting.SaveAs(Server.MapPath("~/ExcelTemplate/" + strUniqueFileName + ".xlsx"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excel.DisplayAlerts = false; wbExiting.Close(false, Type.Missing, Type.Missing); excel.Quit(); FileInfo file1 = new FileInfo(myServerPath1); if (file1.Exists) { Response.Clear(); Response.ClearHeaders(); Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=SalePurchase.xlsx"); Response.AddHeader("Content-Type", "application/Excel"); Response.ContentType = "application/vnd.xls"; Response.AddHeader("Content-Length", file1.Length.ToString()); Response.WriteFile(file1.FullName); // file.Delete(); Response.End(); } else { Response.Write("This file does not exist."); } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); } }
protected List <lhh_LoginList_info> getlist() { try { Local_IP(); InfoList = new List <lhh_LoginList_info>(); System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); string[] ob = Regex.Split(ZFCEPath, @"\\", RegexOptions.IgnoreCase); //string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "辣皇后\\user control.xlsx"); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook analyWK = excelApp.Workbooks.Open(ZFCEPath, Type.Missing, true, Type.Missing, "htc", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)analyWK.Worksheets["配置"]; Microsoft.Office.Interop.Excel.Range rng; rng = WS.get_Range(WS.Cells[2, 7], WS.Cells[WS.UsedRange.Rows.Count, 10]); int rowCount = WS.UsedRange.Rows.Count - 2; object[,] o = new object[1, 1]; o = (object[, ])rng.Value2; clsCommHelp.CloseExcel(excelApp, analyWK); for (int i = 1; i <= rowCount; i++) { // bgWorker.ReportProgress(0, "读入数据中 : " + i.ToString() + "/" + rowCount.ToString()); lhh_LoginList_info temp = new lhh_LoginList_info(); #region 基础信息 if (o[i, 1] != null) { temp.loginid = o[i, 1].ToString().Trim(); } if (o[i, 2] != null) { temp.pwd = o[i, 2].ToString().Trim(); } if (o[i, 3] != null) { temp.startTime = o[i, 3].ToString().Trim(); } if (o[i, 4] != null) { temp.endTime = Convert.ToDateTime(o[i, 4].ToString()); } #endregion InfoList.Add(temp); } return(InfoList); } catch (Exception ex) { MessageBox.Show("表格存在异常,请参照使用说明修改:" + ex.Message); throw ex; } }
private void ReadDataForAccount(string SelectedAccount) { try { List <int> rownumbers = new List <int>(); //specify the file name where its actually exist var readOutputfile = Properties.Settings.Default.OutputFolderPath; string filepath = string.Empty; int rows_count = 0; Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); try { DirectoryInfo dirInfo = new DirectoryInfo(readOutputfile + SelectedAccount + "//"); var files = dirInfo.GetFiles(); int counter = 1; foreach (FileInfo fileInfo in files) { filepath = readOutputfile + SelectedAccount + "\\" + fileInfo.Name; Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath); try { // statement get the workbookname string ExcelWorkbookname = WB.Name; // statement get the worksheet count int worksheetcount = WB.Worksheets.Count; Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[1]; // statement get the firstworksheetname Microsoft.Office.Interop.Excel.Range range = wks.UsedRange; if (counter == 1) { rows_count = range.Rows.Count; } string firstworksheetname = wks.Name; if (counter == 1) { CreateColumnsForGrid(null); dataGridView1.Rows.Add(rows_count - 17); //statement get the first cell value for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { dataGridView1.Columns[i - 1].HeaderText = wks.Cells[1, i].Text; } } if (counter > 1) { dataGridView1.Columns.Add(fileInfo.Name.Replace(fileInfo.Extension, ""), fileInfo.Name.Replace(fileInfo.Extension, "")); } // storing Each row and column value to excel sheet if (counter == 1) { for (int i = 0; i < rows_count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, j + 1].Text; decimal result; if (decimal.TryParse(dataGridView1.Rows[i].Cells[j].Value.ToString(), out result)) { if (!rownumbers.Exists(a => a == i)) { rownumbers.Add(i); } } } } } else { for (int i = 0; i < rows_count - 1; i++) { for (int j = dataGridView1.Columns.Count - 1; j < dataGridView1.Columns.Count; j++) { dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, 4].Text; } } } counter++; } catch (Exception e) { MessageBox.Show("Issue occured.." + e.Message); } finally { if (WB != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(WB); } } } DisplayAverageData(rownumbers); MessageBox.Show("Data read successfully"); } catch (Exception ex) { // MessageBox.Show("Something went wrong.." + ex.Message); } finally { //oExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); } } catch (Exception ex) { MessageBox.Show("There was a problem reading the excel sheet, please try again in sometime! " + ex.Message); } }
private void ReadDataForAccountAndYear(string SelectedAccount, string selectedYear) { try { var readOutputfile = Properties.Settings.Default.OutputFolderPath; DirectoryInfo dirInfo = new DirectoryInfo(readOutputfile + SelectedAccount + "//" + selectedYear + "//"); if (dirInfo.Exists) { var directs = dirInfo.GetDirectories(); if (directs.Length > 0) { var messageResult = MessageBox.Show("Do you like to load all months data.", "Month Data Alert", MessageBoxButtons.YesNo); if (messageResult == System.Windows.Forms.DialogResult.Yes) { int counter = 1; foreach (var directory in directs) { List <int> rownumbers = new List <int>(); //specify the file name where its actually exist string filepath = string.Empty; int rows_count = 0; try { var files = directory.GetFiles(); foreach (FileInfo fileInfo in files) { if (!fileInfo.Name.StartsWith("~")) { filepath = directory.FullName + "\\" + fileInfo.Name; Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath); // statement get the workbookname string ExcelWorkbookname = WB.Name; // statement get the worksheet count int worksheetcount = WB.Worksheets.Count; Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[1]; try { // statement get the firstworksheetname Microsoft.Office.Interop.Excel.Range range = wks.UsedRange; rows_count = range.Rows.Count; string firstworksheetname = wks.Name; if (counter == 1) { CreateColumnsForGrid(null); dataGridView1.Rows.Add(rows_count - 17); //statement get the first cell value for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { dataGridView1.Columns[i - 1].HeaderText = wks.Cells[1, i].Text; } } if (counter > 1) { dataGridView1.Columns.Add(fileInfo.Name.Replace(fileInfo.Extension, ""), fileInfo.Name.Replace(fileInfo.Extension, "")); } // storing Each row and column value to excel sheet if (counter == 1) { for (int i = 0; i < rows_count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, j + 1].Text; decimal result; if (decimal.TryParse(dataGridView1.Rows[i].Cells[j].Value.ToString(), out result)) { if (!rownumbers.Exists(a => a == i)) { rownumbers.Add(i); } } } } } else { for (int i = 0; i < rows_count - 1; i++) { for (int j = dataGridView1.Columns.Count - 1; j < dataGridView1.Columns.Count; j++) { dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, 4].Text; } } } counter++; } catch (Exception e) { MessageBox.Show("Issue occured.." + e.Message); } finally { if (wks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wks); wks = null; } if (WB != null) { WB.Close(false, null, null); System.Runtime.InteropServices.Marshal.ReleaseComObject(WB); WB = null; } if (oExcel != null) { oExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); oExcel = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } } } DisplayAverageData(rownumbers); } catch (Exception ex) { // MessageBox.Show("Something went wrong.." + ex.Message); } } MessageBox.Show("Data read successfully"); } } } } catch (Exception ex) { MessageBox.Show("There was a problem reading the excel sheet, please try again in sometime! " + ex.Message); } }
private void ExportToExcel(DataTable DataTable, string ExcelFilePath = null) { try { int ColumnsCount; if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0) { throw new Exception("ExportToExcel: Null or empty input table!\n"); } // load excel, and create a new workbook Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbooks.Add(); // single worksheet Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet; //object[] Header = new object[ColumnsCount]; //// column headings //for (int i = 0; i < ColumnsCount; i++) // Header[i] = DataTable.Columns[i].ColumnName; //Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range) // (Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount])); //HeaderRange.Value = Header; //HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); //HeaderRange.Font.Bold = true; // DataCells int RowsCount = DataTable.Rows.Count; object[,] Cells = new object[RowsCount, ColumnsCount]; for (int j = 0; j < RowsCount; j++) { for (int i = 0; i < ColumnsCount; i++) { Cells[j, i] = DataTable.Rows[j][i]; } } Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount, ColumnsCount])).Value = Cells; //Worksheet.Columns.AutoFit();//Extend column width if necessary // check fielpath if (ExcelFilePath != null && ExcelFilePath != "") { try { Worksheet.Cells.NumberFormat = "@";//Type of all cells set to text Worksheet.SaveAs(ExcelFilePath); Excel.Quit(); MessageBox.Show("Excel file saved!"); } catch (Exception ex) { throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n" + ex.Message); } } else // no filepath is given { Excel.Visible = true; } } catch (Exception ex) { WriteLog(ex.ToString()); throw new Exception("ExportToExcel: \n" + ex.Message); } }
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\WerkStudent\Mail\Gen_Open_Fin_kel"); Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; double cF = 1 / 0.3048; //Get UI document UIDocument uidoc = commandData.Application.ActiveUIDocument; //Get document Document doc = uidoc.Document; string Level_1 = (String)xlRange.Cells[4, "A"].Value2; //Create levels Level Level1 = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Levels) .WhereElementIsNotElementType().Cast <Level>().First(x => x.Name == Level_1); Level Level2 = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Levels) .WhereElementIsNotElementType().Cast <Level>().First(x => x.Name == "Level 2"); string ty1 = (String)xlRange.Cells[3, "A"].Value2; var FloorType = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Floors) .WhereElementIsElementType().Cast <FloorType>().First(x => x.Name == ty1); //var Floor = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_FloorsCut) // .WhereElementIsElementType().Cast<Floor>().First(x => x.Name == ty1); int points = Convert.ToInt32(xlRange.Cells[2, "C"].value2); string keller = (String)xlRange.Cells[2, "F"].value2; int geschoss = Convert.ToInt32(xlRange.Cells[3, "F"].value2); List <XYZ> pointlistkf = new List <XYZ>(); List <XYZ> pointlistgf = new List <XYZ>(); List <XYZ> pointlistufs = new List <XYZ>(); for (int i = 5; i <= points + 4; i++) { if (keller == "Ja") { XYZ ptskf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, -3.06 * cF); pointlistkf.Add(ptskf); XYZ ptsgf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, 0 * cF); pointlistgf.Add(ptsgf); for (int j = 0; j < geschoss; j++) { XYZ ptsufs = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, (xlRange.Cells[i, "D"].value2 + (j * 3.06)) * cF); pointlistufs.Add(ptsufs); } } else { XYZ ptsgf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, 0 * cF); pointlistgf.Add(ptsgf); for (int j = 0; j < geschoss; j++) { XYZ ptsufs = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, (xlRange.Cells[i, "D"].value2 + (j * 3.06)) * cF); pointlistufs.Add(ptsufs); } } } List <Line> concurveskf = new List <Line>(); List <Line> concurvesgf = new List <Line>(); for (int i = 0; i < pointlistkf.Count; i++) { if (i < pointlistkf.Count - 1) { Line Alllines = Line.CreateBound(pointlistkf.ElementAt(i), pointlistkf.ElementAt(i + 1)); concurveskf.Add(Alllines); } else { Line Alllines = Line.CreateBound(pointlistkf.ElementAt(pointlistkf.Count - 1), pointlistkf.ElementAt(0)); concurveskf.Add(Alllines); } } for (int i = 0; i < pointlistgf.Count; i++) { if (i < pointlistgf.Count - 1) { Line Alllines = Line.CreateBound(pointlistgf.ElementAt(i), pointlistgf.ElementAt(i + 1)); concurvesgf.Add(Alllines); } else { Line Alllines = Line.CreateBound(pointlistgf.ElementAt(pointlistgf.Count - 1), pointlistgf.ElementAt(0)); concurvesgf.Add(Alllines); } } List <Line> concurvesuf = new List <Line>(); List <Line> concurvesuf1 = new List <Line>(); List <Line> concurvesuf2 = new List <Line>(); switch (geschoss) { case 1: break; case 2: for (int i = 0; i < 4; i++) { if (i < 3) { Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1)); concurvesuf.Add(Alllines); } else { Line Alllines = Line.CreateBound(pointlistufs.ElementAt(pointlistufs.Count - 1), pointlistufs.ElementAt(0)); concurvesuf.Add(Alllines); } } break; case 3: for (int i = 0; i < 4; i++) { if (i < 3) { Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1)); Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 5)); concurvesuf.Add(Alllines); concurvesuf1.Add(Alllines1); } else { Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(0)); Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 1)); concurvesuf.Add(Alllines); concurvesuf1.Add(Alllines1); } } break; case 4: for (int i = 0; i < 4; i++) { if (i < 3) { Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1)); Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 5)); Line Alllines2 = Line.CreateBound(pointlistufs.ElementAt(i + 8), pointlistufs.ElementAt(i + 9)); concurvesuf.Add(Alllines); concurvesuf1.Add(Alllines1); concurvesuf2.Add(Alllines2); } else { Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(0)); Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 1)); Line Alllines2 = Line.CreateBound(pointlistufs.ElementAt(i + 8), pointlistufs.ElementAt(i + 5)); concurvesuf.Add(Alllines); concurvesuf1.Add(Alllines1); concurvesuf2.Add(Alllines2); } } break; } List <Curve> licurvekf = new List <Curve>(); List <Curve> licurvegf = new List <Curve>(); for (int i = 0; i < concurveskf.Count; i++) { licurvekf.Add(concurveskf.ElementAt(i)); } for (int i = 0; i < concurvesgf.Count; i++) { licurvegf.Add(concurvesgf.ElementAt(i)); } List <Curve> licurveuf = new List <Curve>(); for (int i = 0; i < concurvesuf.Count; i++) { licurveuf.Add(concurvesuf.ElementAt(i)); } List <Curve> licurveuf1 = new List <Curve>(); for (int i = 0; i < concurvesuf1.Count; i++) { licurveuf1.Add(concurvesuf1.ElementAt(i)); } List <Curve> licurveuf2 = new List <Curve>(); for (int i = 0; i < concurvesuf2.Count; i++) { licurveuf2.Add(concurvesuf2.ElementAt(i)); } // Making a curve in a loop List <CurveLoop> loops = new List <CurveLoop>(); CurveLoop crvloop0 = CurveLoop.Create(licurvekf); loops.Add(crvloop0); CurveLoop crvloop = CurveLoop.Create(licurvegf); loops.Add(crvloop); CurveLoop crvloop1 = CurveLoop.Create(licurveuf); loops.Add(crvloop1); CurveLoop crvloop2 = CurveLoop.Create(licurveuf1); loops.Add(crvloop2); CurveLoop crvloop3 = CurveLoop.Create(licurveuf2); loops.Add(crvloop3); List <CurveArray> curArri = new List <CurveArray>(); for (int i = 0; i < loops.Count; i++) { CurveArray curArr = new CurveArray(); foreach (Curve c in loops[i]) { //Put the curves to curve array curArr.Append(c); } curArri.Add(curArr); } // List <XYZ> opointlist = new List <XYZ>(); for (int i = 3; i <= 6; i++) { XYZ opoints = new XYZ(xlRange.Cells[i, "H"].value2 * cF, xlRange.Cells[i, "I"].value2 * cF, xlRange.Cells[i, "J"].value2 * cF); opointlist.Add(opoints); } List <Line> opline = new List <Line>(); for (int i = 0; i < opointlist.Count; i++) { if (i < opointlist.Count - 1) { Line oline = Line.CreateBound(opointlist.ElementAt(i), opointlist.ElementAt(i + 1)); opline.Add(oline); } else { Line oline = Line.CreateBound(opointlist.ElementAt(3), opointlist.ElementAt(0)); opline.Add(oline); } } List <Curve> ocurve = new List <Curve>(); for (int i = 0; i < opline.Count; i++) { ocurve.Add(opline.ElementAt(i)); } CurveLoop crvvloopopen = CurveLoop.Create(ocurve); CurveArray curvopen = new CurveArray(); foreach (Curve co in crvvloopopen) { //Put the curves to curve array curvopen.Append(co); } // try { Floor FL; List <Floor> stories = new List <Floor>(); if (keller == "Ja") { for (int i = 0; i <= geschoss; i++) { using (Transaction trans = new Transaction(doc, "Bungalow Floors")) { trans.Start(); FL = doc.Create.NewFloor(curArri[i], FloorType, Level1, false); stories.Add(FL); trans.Commit(); } } } else { for (int i = 0; i < geschoss; i++) { using (Transaction trans = new Transaction(doc, "Bungalow Floors")) { trans.Start(); FL = doc.Create.NewFloor(curArri[i], FloorType, Level1, false); stories.Add(FL); trans.Commit(); } } } for (int i = 1; i < stories.Count; i++) { using (Transaction trans1 = new Transaction(doc, "Bung open")) { trans1.Start(); //var Open = doc.Create.NewFloor(curArr1, FloorType, Level2, false); doc.Create.NewOpening(stories[i], curvopen, true); trans1.Commit(); } } return(Result.Succeeded); } catch (Exception e) { message = e.Message; return(Result.Failed); } }
private void Export(DataTable dt, string sheetName, string title) { //Tạo các đối tượng Excel Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks oBooks; Microsoft.Office.Interop.Excel.Sheets oSheets; Microsoft.Office.Interop.Excel.Workbook oBook; Microsoft.Office.Interop.Excel.Worksheet oSheet; //Tạo mới một Excel WorkBook oExcel.Visible = true; oExcel.DisplayAlerts = false; oExcel.Application.SheetsInNewWorkbook = 1; oBooks = oExcel.Workbooks; oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing)); oSheets = oBook.Worksheets; oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1); oSheet.Name = sheetName; try { // Tạo phần đầu nếu muốn Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "O1"); head.MergeCells = true; head.Value2 = title; head.Font.Bold = true; head.Font.Name = "Tahoma"; head.Font.Size = "18"; head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo tiêu đề cột Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3"); cl1.Value2 = "Mã KH"; cl1.ColumnWidth = 10.5; Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3"); cl2.Value2 = "Họ KH"; cl2.ColumnWidth = 20.0; Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3"); cl3.Value2 = "Tên KH"; cl3.ColumnWidth = 20.0; Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3"); cl4.Value2 = "Giới tính"; cl4.ColumnWidth = 10.0; Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3"); cl5.Value2 = "Ngày sinh"; cl5.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3"); cl6.Value2 = "Điện thoại"; cl6.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3"); cl7.Value2 = "Địa chỉ"; cl7.ColumnWidth = 25.0; Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3"); cl8.Value2 = "CMND"; cl8.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range cl9 = oSheet.get_Range("I3", "I3"); cl9.Value2 = "Ngày mua"; cl9.ColumnWidth = 15; Microsoft.Office.Interop.Excel.Range cl10 = oSheet.get_Range("J3", "J3"); cl10.Value2 = "Sổ BH"; cl10.ColumnWidth = 10.0; Microsoft.Office.Interop.Excel.Range cl11 = oSheet.get_Range("K3", "K3"); cl11.Value2 = "Tên xe"; cl11.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range cl12 = oSheet.get_Range("L3", "L3"); cl12.Value2 = "Biển số"; cl12.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range cl13 = oSheet.get_Range("M3", "M3"); cl13.Value2 = "Số khung"; cl13.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range cl14 = oSheet.get_Range("N3", "N3"); cl14.Value2 = "Số máy"; cl14.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range cl15 = oSheet.get_Range("O3", "O3"); cl15.Value2 = "Loại KH"; cl15.ColumnWidth = 10.0; Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "O3"); rowHead.Font.Bold = true; // Kẻ viền rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Thiết lập màu nền rowHead.Interior.ColorIndex = 15; rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable, // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần. object[,] arr = new object[dt.Rows.Count, dt.Columns.Count]; //Chuyển dữ liệu từ DataTable vào mảng đối tượng for (int r = 0; r < dt.Rows.Count; r++) { DataRow dr = dt.Rows[r]; for (int c = 0; c < dt.Columns.Count; c++) { arr[r, c] = dr[c]; } } //Thiết lập vùng điền dữ liệu int rowStart = 4; int columnStart = 1; int rowEnd = rowStart + dt.Rows.Count - 1; int columnEnd = dt.Columns.Count; // Ô bắt đầu điền dữ liệu Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart]; // Ô kết thúc điền dữ liệu Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd]; // Lấy về vùng điền dữ liệu Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2); //Điền dữ liệu vào vùng đã thiết lập range.Value2 = arr; // Kẻ viền range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Căn giữa cột STT Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart]; Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3); oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); } }
private void btnExportXL_Click(object sender, RoutedEventArgs e) { try { dt = ((DataView)dgVariationReport.ItemsSource).ToTable(); if (dt.Rows.Count > 0) { Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog(); dlg.InitialDirectory = Environment.CurrentDirectory; dlg.Title = "Variation Report"; dlg.DefaultExt = ".xlsx"; dlg.Filter = "XL files|*.xls;*.xlsx|All files|*.*"; dlg.FileName = "VariationReport"; if (dlg.ShowDialog() == true) { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; worksheet.Name = "Variation Report"; worksheet.Cells[3, 4] = "VARIATION REPORT "; worksheet.Cells[3, 4].Font.Bold = true; for (int i = 1; i < dt.Columns.Count + 1; i++) { if (i == 3) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Member", dtpFromDate.SelectedDate); } else if (i == 4) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Amount", dtpFromDate.SelectedDate); } else if (i == 5) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Member", dtpFromDate.SelectedDate); } else if (i == 6) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Amount", dtpFromDate.SelectedDate); } else if (i == 7) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Member", dtpFromDate.SelectedDate); } else if (i == 8) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Amount", dtpFromDate.SelectedDate); } else if (i == 9) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Member", dtpToDate.SelectedDate); } else if (i == 10) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Amount", dtpToDate.SelectedDate); } else if (i == 11) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Member", dtpToDate.SelectedDate); } else if (i == 12) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Amount", dtpToDate.SelectedDate); } else if (i == 13) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Member", dtpToDate.SelectedDate); } else if (i == 14) { worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Amount", dtpToDate.SelectedDate); } else if (i != 1) { worksheet.Cells[5, i + 2] = dt.Columns[i - 1].ColumnName; } worksheet.Cells[5, i + 2].Font.Bold = true; } for (int i = 0; i < dt.Rows.Count - 1; i++) { for (int j = 0; j < dt.Columns.Count; j++) { if (j != 0) { worksheet.Cells[i + 6, j + 3] = dt.Rows[i][j].ToString(); } } } workbook.SaveAs(dlg.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); app.Quit(); MessageBox.Show("Exported Sucessfully !", "Sucess"); } } else { MessageBox.Show("No Data Found !", "Empty"); } } catch (Exception ex) { ExceptionLogging.SendErrorToText(ex); } }
public void Export(DataTable dt, string sheetName, string title) { //Tạo các đối tượng Excel Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks oBooks; Microsoft.Office.Interop.Excel.Sheets oSheets; Microsoft.Office.Interop.Excel.Workbook oBook; Microsoft.Office.Interop.Excel.Worksheet oSheet; //Tạo mới một Excel WorkBook oExcel.Visible = true; oExcel.DisplayAlerts = false; oExcel.Application.SheetsInNewWorkbook = 1; oBooks = oExcel.Workbooks; oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing)); oSheets = oBook.Worksheets; oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1); oSheet.Name = sheetName; // Tạo phần đầu Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "H1"); head.MergeCells = true; head.Value2 = title; head.Font.Bold = true; head.Font.Name = "Tahoma"; head.Font.Size = "18"; head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo tiêu đề cột Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3"); cl1.Value2 = "Mã nhân viên"; cl1.ColumnWidth = 10.5; Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3"); cl2.Value2 = "Tên Nhân viên"; cl2.ColumnWidth = 25.0; Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3"); cl3.Value2 = "Giới tính"; cl3.ColumnWidth = 10.0; Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3"); cl4.Value2 = "Ngày sinh"; cl4.ColumnWidth = 13.0; Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3"); cl5.Value2 = "Mail"; cl5.ColumnWidth = 25.0; Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3"); cl6.Value2 = "Số điện thoại"; cl6.ColumnWidth = 20.0; Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3"); cl7.Value2 = "Địa chỉ"; cl7.ColumnWidth = 30.0; Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3"); cl8.Value2 = "Vị trí"; cl8.ColumnWidth = 13.0; Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "H3"); rowHead.Font.Bold = true; // Kẻ viền rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Thiết lập màu nền rowHead.Interior.ColorIndex = 15; rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable, // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần. object[,] arr = new object[dt.Rows.Count, dt.Columns.Count]; //Chuyển dữ liệu từ DataTable vào mảng đối tượng for (int r = 0; r < dt.Rows.Count; r++) { DataRow dr = dt.Rows[r]; for (int c = 0; c < dt.Columns.Count; c++) { string tmp = dr[c].ToString(); arr[r, c] = tmp; } } //Thiết lập vùng điền dữ liệu int rowStart = 4; int columnStart = 1; int rowEnd = rowStart + dt.Rows.Count - 1; int columnEnd = dt.Columns.Count; // Ô bắt đầu điền dữ liệu Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart]; // Ô kết thúc điền dữ liệu Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd]; // Lấy về vùng điền dữ liệu Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2); //Điền dữ liệu vào vùng đã thiết lập range.Value2 = arr; // Kẻ viền range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Căn giữa cột STT Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart]; Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3); oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // releaseObject(oSheet); releaseObject(oBook); releaseObject(oSheets); releaseObject(oBooks); releaseObject(oExcel); }
public ActionResult RunXlsCampania(string cod_unidad_negocio, string fec_informe, string campania, int batch_desde, int batch_hasta) { if (Session["cod_unidad_negocio"] != null) { cod_unidad_negocio = Session["cod_unidad_negocio"].ToString(); } var mensajeError = string.Empty; var tablero = string.Empty; var codUnidadNegocio = cod_unidad_negocio; var fecInforme = fec_informe; var batchDesde = batch_desde; var batchHasta = batch_hasta; var resultado = string.Empty; var mensaje = string.Empty; var archivoDestino = string.Empty; int pid = 0; //Identificar el archivo a descargar, incluyendo la ruta de acceso. var excelFuente = string.Empty; var excelDestino = string.Empty; var fileDestino = string.Empty; excelFuente = Server.MapPath("~/xlso/KPI_PI_Campañas.xlsm"); DateTime?dtfecInforme = null; if (!(fec_informe == "" || fec_informe == string.Empty)) { dtfecInforme = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", CultureInfo.InvariantCulture); //Cambiamos el nombre del archivo Destino fileDestino = "Campaña" + "_" + campania + "_" + codUnidadNegocio + "_" + dtfecInforme.Value.ToString("yyMMdd") + DateTime.Now.ToString("hhmmsss") + ".xlsx"; } else { fileDestino = "Campaña" + "_" + campania + "_" + codUnidadNegocio + "_" + batchDesde + "_" + batch_hasta + "_" + DateTime.Now.ToString("hhmmsss") + ".xlsx"; } excelDestino = Server.MapPath("~/xlsd/" + fileDestino); try { var excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Application.Visible = false; excelApp.DisplayAlerts = false; var excelWorkbook = excelApp.Workbooks.Open(Filename: excelFuente, ReadOnly: true); GetWindowThreadProcessId(excelApp.Hwnd, out pid); excelWorkbook.Sheets["Registro"].Range("A2").Value = codUnidadNegocio; excelWorkbook.Sheets["Registro"].Range("B2").Value = campania; var fecExcel = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", null).ToString("MM/dd/yyyy"); excelWorkbook.Sheets["Registro"].Range("C2").Value = fecExcel; excelWorkbook.Sheets["Registro"].Range("D2").Value = excelDestino; excelWorkbook.Sheets["Registro"].Range("E2").Value = batchDesde; excelWorkbook.Sheets["Registro"].Range("F2").Value = batchHasta; try { mensajeError = excelApp.Run("BtnRegistro_Carga"); } catch (Exception) { mensajeError = "ERROR: En la ejecución de Excel"; } if (mensajeError == null) { mensajeError = ""; } mensajeError.Replace("'", "´"); mensajeError.Replace(((char)34).ToString(), "´"); mensajeError.Replace(((char)13).ToString(), "\r"); mensajeError.Replace(((char)10).ToString(), "\r"); //Close Excel excelWorkbook.Close(SaveChanges: false); excelWorkbook = null; excelApp.Quit(); archivoDestino = excelDestino.Replace(@"\", "/"); try { //procesoExcel.Kill(); Process.GetProcessById(pid).Kill(); } catch (Exception) { } } catch (Exception ex) { System.Threading.Thread.Sleep(1000); //Espera un segundo para intentarlo de nuevo mensajeError += " Servicio de Excel en estos momentos se encuentra sin recursos.\r Por favor intentelo mas tarde."; try { //procesoExcel.Kill(); Process.GetProcessById(pid).Kill(); } catch (Exception) { } } if (mensajeError == "") { return(Json( new Response { Status = HttpStatusCode.OK, Message = archivoDestino }, JsonRequestBehavior.AllowGet)); } return(Json( new Response { Status = HttpStatusCode.BadRequest, Message = mensajeError }, JsonRequestBehavior.AllowGet)); }
public int print(bool b) { // 打开一个Excel进程 Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); // 利用这个进程打开一个Excel文件 //Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\CSBag\SOP-MFG-109-R01A 产品内包装记录.xlsx"); Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\8 SOP-MFG-412-R01A 底封机运行记录"); // 选择一个Sheet,注意Sheet的序号是从1开始的 Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[wb.Worksheets.Count]; // 修改Sheet中某行某列的值 fill_excel(my, wb); //"生产指令-步骤序号- 表序号 /&P" my.PageSetup.RightFooter = Instruction + "-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码 if (b) { //true->预览 // 设置该进程是否可见 oXL.Visible = true; // 让这个Sheet为被选中状态 my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能 return(0); } else { int pageCount = 0; bool isPrint = true; //false->打印 try { // 设置该进程是否可见 //oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet // 直接用默认打印机打印该Sheet my.PrintOut(); } catch { isPrint = false; } finally { if (isPrint) { //写日志 string log = "=====================================\n"; log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + ":" + mySystem.Parameter.userName + " 打印文档\n"; dt记录.Rows[0]["日志"] = dt记录.Rows[0]["日志"].ToString() + log; bs记录.EndEdit(); da记录.Update((DataTable)bs记录.DataSource); } // 关闭文件,false表示不保存 pageCount = wb.ActiveSheet.PageSetup.Pages.Count; wb.Close(false); // 关闭Excel进程 oXL.Quit(); // 释放COM资源 Marshal.ReleaseComObject(wb); Marshal.ReleaseComObject(oXL); wb = null; oXL = null; } return(pageCount); } }
public static void ExportXLSX(DataGridView dgv, int[] x, int[] y, string[] vanban) { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); //Khởi tạo WorkBook Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); //Khởi tạo WorkSheet Microsoft.Office.Interop.Excel._Worksheet worksheet = null; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; app.Visible = true; //Đổ dữ liệu vào Sheet for (int i = 0; i < vanban.Length; i++) { worksheet.Cells[x[i], y[i]] = vanban[i]; } int dongbatdau = 7; for (int i = 0; i < dgv.Columns.Count; i++) { worksheet.Cells[dongbatdau, i + 1] = dgv.Columns[i].HeaderText; } for (int i = 0; i < dgv.Rows.Count; i++) { for (int j = 0; j < dgv.Columns.Count; j++) { worksheet.Cells[i + 1 + dongbatdau, j + 1] = dgv.Rows[i].Cells[j].Value.ToString(); } } worksheet.Range["A" + dongbatdau, "G" + dongbatdau].Font.Bold = true; //đậm tiêu đề cột worksheet.Range["A1", "J100"].Font.Name = "Times New Roman"; worksheet.Range["A1", "G2"].Font.Bold = true; worksheet.Range["A2", "G2"].Font.Italic = true; worksheet.Range["A2", "A2"].Font.Bold = false; worksheet.Range["C4"].Font.Bold = true; worksheet.Range["C4"].Font.Size = 18; //kẻ viền worksheet.Range["A" + (dongbatdau), Convert.ToChar(dgv.Columns.Count + 65 - 1).ToString() + (dongbatdau + dgv.Rows.Count)].Borders.LineStyle = 1; int ii = 20; worksheet.Range["A1"].ColumnWidth = ii; worksheet.Range["B1"].ColumnWidth = ii; worksheet.Range["C1"].ColumnWidth = ii; worksheet.Range["D1"].ColumnWidth = ii; worksheet.Range["E1"].ColumnWidth = ii; worksheet.Range["F1"].ColumnWidth = ii; worksheet.Range["G1"].ColumnWidth = ii; //tính ttoongr worksheet.Cells[dongbatdau + dgv.Rows.Count + 1, dgv.Columns.Count] = string.Format("=SUM({0}:{1})", Convert.ToChar(dgv.Columns.Count + 64).ToString() + (dongbatdau + 1), Convert.ToChar(dgv.Columns.Count + 64).ToString() + (dongbatdau + dgv.Rows.Count) ); worksheet.Cells[dongbatdau + dgv.Rows.Count + 1, dgv.Columns.Count - 1] = "Tổng"; }
private void buttonStart_Click(object sender, EventArgs e) { if (textBoxPath.Text != "") { this.label1.Visible = true; this.pictureBox1.Visible = true; this.label1.Text = "正在整理中......"; this.pictureBox1.Image = Resources._123; List <String> list = Director(textBoxPath.Text); foreach (var path in list) { CrawlData(path); } } try { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = true; //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错 Microsoft.Office.Interop.Excel.Workbook xBook = excel.Workbooks.Add(Missing.Value); Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1]; xSheet.Cells[1, 1] = "ip地址"; xSheet.Cells[1, 2] = "漏洞名称"; xSheet.Cells[1, 3] = "详细描述"; xSheet.Cells[1, 4] = "加固建议"; xSheet.Cells[1, 5] = "风险端口"; xSheet.Cells[1, 6] = "危险程度"; List <string> loopholeInfoDicKey = new List <string>(loopholeInfoDic.Keys); for (int i = 0; i < loopholeInfoDicKey.Count; i++) { for (int j = 0; j < 6; j++) { if (j == 2) { if (loopholeInfoDic[loopholeInfoDicKey[i]].Count > 4) { xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][4]; } } else if (j == 3) { if (loopholeInfoDic[loopholeInfoDicKey[i]].Count > 4) { xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][5]; } } else if (j == 4) { xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][2]; } else if (j == 5) { xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][3]; } else { xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][j]; } } } excel.DisplayAlerts = false; string saveName = GetSystemSecond().ToString(); string savePath = Application.StartupPath + "\\" + saveName + ".xlsx"; xBook.SaveAs(savePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); xSheet = null; xBook = null; excel.Quit(); excel = null; GC.Collect();//如果不使用这条语句会导致excel进程无法正常退出,使用后正常退出 } catch (Exception ex) { MessageBox.Show("保存excel出错: " + ex.Message); Close(); } this.label1.Text = "整理完成!"; this.pictureBox1.Image = Resources._234; }
private void button1_Click(object sender, EventArgs e) { List <int> id_select = new List <int>(); SqlConnection connecting = new SqlConnection(Program.connection); connecting.Open(); string contact = "SELECT ID FROM [Documents] WHERE TypeDoc='" + CbTypeDoc.Text + "' AND DateIssued>='" + dateTimeOt.Value + "' AND DateIssued<='" + dateTimeDo.Value + "'"; SqlCommand cmd = new SqlCommand(contact, connecting); SqlDataReader dataReader = cmd.ExecuteReader(); try { if (dataReader.HasRows) // если есть данные { while (dataReader.Read()) { id_select.Add(Convert.ToInt32(dataReader["ID"])); } } nomera = new int[id_select.Count]; int k = 0; foreach (int i in id_select) { nomera[k] = i; k++; } } catch (Exception ex) { MessageBox.Show(ex.Message); } connecting.Close(); try { //Объявляем приложение Microsoft.Office.Interop.Excel.Application ex = new Microsoft.Office.Interop.Excel.Application(); //Отобразить Excel //Количество листов в рабочей книге ex.SheetsInNewWorkbook = 1; //Добавить рабочую книгу Microsoft.Office.Interop.Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing); //Получаем первый лист документа (счет начинается с 1) Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)ex.Worksheets.get_Item(1); string rootFolder = @"D:\"; //Путь string fileName = $"ExportUsers{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"; // название файла чтобы не повторялось FileInfo file = new FileInfo(rootFolder + fileName); //создаем файл // Microsoft.Office.Interop.Excel.Application package = new Microsoft.Office.Interop.Excel.Application(); // package.Visible = true; // string rootFolder = @"D:\"; //Путь // string fileName = "shab"; // // FileInfo file = new FileInfo(rootFolder + fileName); var excelcells = worksheet.get_Range("A1", "Q1"); excelcells.Merge(Type.Missing); excelcells.Value = "Выписка о выданных документах типа'" + CbTypeDoc.Text + "'"; excelcells.get_Range("A1", "Q1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; var excelcells1 = worksheet.get_Range("A2", "Q2"); excelcells1.Merge(Type.Missing); excelcells1.Value = "Период: от '" + dateTimeOt.Value.ToShortDateString() + "' до '" + dateTimeDo.Value.ToShortDateString() + "'"; excelcells1.get_Range("A2", "Q2").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; // ExcelWorksheet worksheet = package.Worksheets.Add("Дипломы"); //Необязательно worksheet.Cells[3, 1].Value = "Вид документа"; worksheet.Cells[3, 2].Value = "Статус документа"; worksheet.Cells[3, 3].Value = "Подтверждение утраты"; worksheet.Cells[3, 4].Value = "Подтверждение обмена"; worksheet.Cells[3, 5].Value = "Подтверждение уничтожения"; worksheet.Cells[3, 6].Value = "Серия документа"; worksheet.Cells[3, 7].Value = "Номер документа"; worksheet.Cells[3, 8].Value = "Дата выдачи документа"; worksheet.Cells[3, 9].Value = "Регистрационный номер"; worksheet.Cells[3, 10].Value = "Дополнительная профессиональная программа"; worksheet.Cells[3, 11].Value = "Наименование дополнительной профессиональной программы"; worksheet.Cells[3, 12].Value = "Дата начала обучения"; worksheet.Cells[3, 13].Value = "Дата окончания обучения"; worksheet.Cells[3, 14].Value = "Фамилия слушателя"; worksheet.Cells[3, 15].Value = "Имя слушателя"; worksheet.Cells[3, 16].Value = "Отчество слушателя"; worksheet.Cells[3, 17].Value = "Дата рождения"; SqlConnection connect = new SqlConnection(Program.connection); connect.Open(); //выбираем статус документа // SqlCommand statusdoc = new SqlCommand("SELECT StatusDoc FROM [Documents] WHERE ID =" + nomera[1], connect); // statusdoc.ExecuteScalar(); // MessageBox.Show(Convert.ToString(statusdoc.ExecuteScalar())); for (int u = 0; u < nomera.Length; u++) { //выбираем статус документа SqlCommand statusdoc = new SqlCommand("SELECT StatusDoc FROM [Documents] WHERE ID =" + nomera[u], connect); statusdoc.ExecuteScalar(); //выбираем утрату документа SqlCommand utrata = new SqlCommand("SELECT Utrata FROM [Documents] WHERE ID =" + nomera[u], connect); utrata.ExecuteScalar(); //выбираем обмен документа SqlCommand obmen = new SqlCommand("SELECT Obmen FROM [Documents] WHERE ID =" + nomera[u], connect); obmen.ExecuteScalar(); //выбираем уничтожение документа SqlCommand unichtog = new SqlCommand("SELECT Unichtog FROM [Documents] WHERE ID =" + nomera[u], connect); unichtog.ExecuteScalar(); //выбираем серию документа SqlCommand seriesdoc = new SqlCommand("SELECT SeriesDoc FROM [Documents] WHERE ID =" + nomera[u], connect); seriesdoc.ExecuteScalar(); //выбираем номер документа SqlCommand numberdoc = new SqlCommand("SELECT NumberDoc FROM [Documents] WHERE ID =" + nomera[u], connect); numberdoc.ExecuteScalar(); //выбираем регистрационный номер документа SqlCommand regnumber = new SqlCommand("SELECT RegNumber FROM [Documents] WHERE ID =" + nomera[u], connect); regnumber.ExecuteScalar(); //выбираем дату выдачи документа SqlCommand dateissued = new SqlCommand("SELECT DateIssued FROM [Documents] WHERE ID =" + nomera[u], connect); dateissued.ExecuteScalar(); //Выбираем группу к которой привязан курс SqlCommand gr = new SqlCommand("SELECT [Group] FROM [Documents] WHERE ID=" + nomera[u], connect); gr.ExecuteScalar(); //выбираем ID курса по названию группы SqlCommand course = new SqlCommand("SELECT IDCourse FROM [Group] WHERE NameGroup ='" + gr.ExecuteScalar() + "'", connect); course.ExecuteScalar(); //выбираем тип программы( повышение квалификации..) SqlCommand nametype = new SqlCommand("SELECT NameType FROM [TypeProg] WHERE ID=(SELECT Type FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "')", connect); nametype.ExecuteScalar(); //выбираем количество часов в курсе SqlCommand kolchas = new SqlCommand("SELECT KolChas FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "'", connect); kolchas.ExecuteScalar(); //выбираем наименование программы (медицинская оптика...) SqlCommand nameprog = new SqlCommand("SELECT NameProg FROM [Program] WHERE ID=(SELECT Program FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "')", connect); nameprog.ExecuteScalar(); //выбираем ID курса SqlCommand idcourse = new SqlCommand("SELECT Course FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "'", connect); idcourse.ExecuteScalar(); //выбираем наименование курса SqlCommand namecourse = new SqlCommand("SELECT NameCourse FROM [Course] WHERE IDCourse='" + idcourse.ExecuteScalar() + "'", connect); namecourse.ExecuteScalar(); //выбираем дату начала курса SqlCommand datestart = new SqlCommand("SELECT DateStart FROM [NewCourse] WHERE ID=" + course.ExecuteScalar(), connect); datestart.ExecuteScalar(); //выбираем дату окончания курса SqlCommand dateend = new SqlCommand("SELECT DateEnd FROM [NewCourse] WHERE ID=" + course.ExecuteScalar(), connect); dateend.ExecuteScalar(); //выбираем id студента SqlCommand student = new SqlCommand("SELECT IdStudent FROM [Documents] WHERE ID=" + nomera[u], connect); student.ExecuteScalar(); //выбираем фамилию студента SqlCommand surname = new SqlCommand("SELECT Surname FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect); surname.ExecuteScalar(); //выбираем имя студента SqlCommand name = new SqlCommand("SELECT Name FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect); name.ExecuteScalar(); //выбираем отчество студента SqlCommand patronimyc = new SqlCommand("SELECT Patronymic FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect); patronimyc.ExecuteScalar(); //выбираем дату рождения студента SqlCommand dateofbirth = new SqlCommand("SELECT DateOfBirth FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect); dateofbirth.ExecuteScalar(); worksheet.Cells[u + 4, 1].Value = CbTypeDoc.Text; worksheet.Cells[u + 4, 2].Value = statusdoc.ExecuteScalar(); worksheet.Cells[u + 4, 3].Value = utrata.ExecuteScalar(); worksheet.Cells[u + 4, 4].Value = obmen.ExecuteScalar(); worksheet.Cells[u + 4, 5].Value = unichtog.ExecuteScalar(); worksheet.Cells[u + 4, 6].Value = seriesdoc.ExecuteScalar(); worksheet.Cells[u + 4, 7].Value = numberdoc.ExecuteScalar(); worksheet.Cells[u + 4, 8].Value = dateissued.ExecuteScalar(); worksheet.Cells[u + 4, 9].Value = regnumber.ExecuteScalar(); worksheet.Cells[u + 4, 10].Value = nametype.ExecuteScalar(); worksheet.Cells[u + 4, 11].Value = nameprog.ExecuteScalar(); worksheet.Cells[u + 4, 12].Value = datestart.ExecuteScalar(); worksheet.Cells[u + 4, 13].Value = dateend.ExecuteScalar(); worksheet.Cells[u + 4, 14].Value = surname.ExecuteScalar(); worksheet.Cells[u + 4, 15].Value = name.ExecuteScalar(); worksheet.Cells[u + 4, 16].Value = patronimyc.ExecuteScalar(); worksheet.Cells[u + 4, 17].Value = dateofbirth.ExecuteScalar(); } var oRng = worksheet.get_Range("A1", "Q1"); oRng.EntireColumn.AutoFit(); // закрываем соединение с БД //ex.Visible = true; // worksheet.SaveAs("otchet"); SaveFileDialog sfd = new SaveFileDialog(); //выбираем путь для сохранения sfd.InitialDirectory = @"D:\SDO\otchet\"; string path = ""; if (sfd.ShowDialog() == DialogResult.OK) { path = sfd.FileName; worksheet.SaveAs(path); MessageBox.Show("Файл сохранен"); } connect.Close(); MessageBox.Show("Файл сформирован"); } catch (Exception ex) { MessageBox.Show("ошибка" + ex); } }
private void BtnExport_Click(object sender, EventArgs e) { try { if (dgvMonthlyReports.Rows.Count != 0) { SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "Excel Files (*.xls)|*.xls"; saveFileDialog1.FileName = "Doctor Wise Summary(" + DateTime.Now.ToString("dd-MM-yy h.mm.ss tt") + ").xls"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { PathName = saveFileDialog1.FileName; Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 20; int count = dgvMonthlyReports.Columns.Count; ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, count]].Merge(); ExcelApp.Cells[1, 1] = "DOCTOR WISE SUMMARY"; ExcelApp.Cells[1, 1].HorizontalAlignment = HorizontalAlignment.Center; ExcelApp.Cells[1, 1].Font.Size = 12; ExcelApp.Cells[1, 1].Interior.Color = Color.FromArgb(153, 204, 255); ExcelApp.Columns.ColumnWidth = 20; ExcelApp.Cells[2, 1] = "From Date"; ExcelApp.Cells[2, 1].Font.Size = 10; ExcelApp.Cells[2, 2] = dptMonthly_From.Value.ToString("dd-MM-yyyy"); ExcelApp.Cells[2, 2].Font.Size = 10; ExcelApp.Cells[3, 1] = "To Date"; ExcelApp.Cells[3, 1].Font.Size = 10; ExcelApp.Cells[3, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ExcelApp.Cells[3, 2] = dptMonthly_To.Value.ToString("dd-MM-yyyy"); ExcelApp.Cells[3, 2].Font.Size = 10; ExcelApp.Cells[4, 1] = "Running Date"; ExcelApp.Cells[4, 1].Font.Size = 10; ExcelApp.Cells[4, 2] = DateTime.Now.ToString("dd-MM-yyyy"); ExcelApp.Cells[4, 2].Font.Size = 10; ExcelApp.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; for (int i = 1; i < dgvMonthlyReports.Columns.Count + 1; i++) { ExcelApp.Cells[5, i] = dgvMonthlyReports.Columns[i - 1].HeaderText; ExcelApp.Cells[5, i].ColumnWidth = 25; ExcelApp.Cells[5, i].EntireRow.Font.Bold = true; ExcelApp.Cells[5, i].Interior.Color = Color.FromArgb(0, 102, 204); ExcelApp.Cells[5, i].Font.Size = 10; ExcelApp.Cells[5, i].Font.Name = "Segoe UI"; ExcelApp.Cells[5, i].Font.Color = Color.FromArgb(255, 255, 255); ExcelApp.Cells[5, i].Interior.Color = Color.FromArgb(0, 102, 204); } for (int i = 0; i <= dgvMonthlyReports.Rows.Count; i++) { try { for (int j = 0; j < dgvMonthlyReports.Columns.Count; j++) { ExcelApp.Cells[i + 6, j + 1] = dgvMonthlyReports.Rows[i].Cells[j].Value.ToString(); ExcelApp.Cells[i + 6, j + 1].BorderAround(true); ExcelApp.Cells[i + 6, j + 1].Borders.Color = Color.FromArgb(0, 102, 204); ExcelApp.Cells[i + 6, j + 1].Font.Size = 8; } } catch { } } ExcelApp.ActiveWorkbook.SaveAs(PathName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); checkStr = "1"; MessageBox.Show("Successfully Exported to Excel", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else { MessageBox.Show("No records found,please change the date and try again!..", "Failed ", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error!...", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> /// 导出Excel 的方法 /// </summary> private void tslExport_Excel(string fileName, DataGridView myDGV) { string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) { return; //被点了取消 } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1 Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", "Z" + (myDGV.SelectedRows.Count + 10)); //把Execl设置问文本格式 range.NumberFormatLocal = "@"; //写入标题 for (int i = 1; i < myDGV.ColumnCount; i++) { worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText; } //写入数值 int s = 0; for (int r = 0; r < myDGV.Rows.Count; r++) { if (Convert.ToBoolean(myDGV.Rows[r].Cells[0].Value)) { for (int i = 1; i < myDGV.ColumnCount; i++) { worksheet.Cells[s + 2, i] = myDGV.Rows[r].Cells[i].Value; } System.Windows.Forms.Application.DoEvents(); s++; } } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]); rang.NumberFormat = "000000000000"; if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); //fileSaved = true; } catch { //fileSaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开!\n"); } } //else //{ // fileSaved = false; //} xlApp.Quit(); GC.Collect();//强行销毁 MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK); }
private void ExportToExcel(DataTable dt) { Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application(); try { object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet(); osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"]; int columnsCount; if (dt == null || (columnsCount = dt.Columns.Count) == 0) { throw new Exception("ExportToExcel: Null or empty input table!\n"); } object[] Header = new object[columnsCount]; for (int i = 0; i < 7; i++) { Header[i] = dt.Columns[i].ColumnName; } Header[6] = string.Empty; Header[7] = "Total Work Time"; Header[8] = string.Empty; int hours = 6; for (var i = 6; i <= columnsCount - 1; i++) { Header[i] = hours + "h"; hours++; } Microsoft.Office.Interop.Excel.Range HeaderRange = osheet.get_Range((Microsoft.Office.Interop.Excel.Range) (osheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range) (osheet.Cells[1, columnsCount - 2])); HeaderRange.Value = Header; HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); HeaderRange.Font.Bold = true; int rowsCount = dt.Rows.Count; object[,] Cells = new object[rowsCount, columnsCount]; for (int j = 0; j < rowsCount; j++) { for (int i = 0; i < columnsCount - 2; i++) { Cells[j, i] = dt.Rows[j][i]; } } osheet.get_Range((Microsoft.Office.Interop.Excel.Range)(osheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range) (osheet.Cells[rowsCount + 1, columnsCount])).Value = Cells; oexcel.Columns.AutoFit(); string filePath = string.Empty; var save_file_dialog = new SaveFileDialog(); save_file_dialog.FileName = "Efficiency " + DateTime.Now.Date.ToString("dd'-'MM'-'yyyy"); save_file_dialog.DefaultExt = ".xlsx"; save_file_dialog.Filter = "Excel File|*.xlsx|All Files|*.*"; if (save_file_dialog.ShowDialog() == DialogResult.OK) { filePath = save_file_dialog.FileName; oexcel.ActiveWorkbook.SaveCopyAs(filePath); oexcel.ActiveWorkbook.Saved = true; oexcel.Quit(); } save_file_dialog.Dispose(); } catch (Exception ex) { throw new Exception("ExportToExcel: \n" + ex.Message); } }
private void ExportDepartmentToExcel() { int intRowCounter; int intRowNumberOfRecords; int intColumnCounter; int intColumnNumberOfRecords; // Creating a Excel object. Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; try { worksheet = workbook.ActiveSheet; worksheet.Name = "OpenOrders"; int cellRowIndex = 1; int cellColumnIndex = 1; intRowNumberOfRecords = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Rows.Count; intColumnNumberOfRecords = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Columns.Count; for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Columns[intColumnCounter].ColumnName; cellColumnIndex++; } cellRowIndex++; cellColumnIndex = 1; //Loop through each row and read value from each column. for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++) { for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++) { worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Rows[intRowCounter][intColumnCounter].ToString(); cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } //Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.FilterIndex = 1; saveDialog.ShowDialog(); workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } catch (System.Exception ex) { TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Blue Jay ERP // Design Employee Productivity // Export Department to Excel " + ex.Message); MessageBox.Show(ex.ToString()); } finally { excel.Quit(); workbook = null; excel = null; } }
private void Export2Excel() { DateTime tgl; var excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Visible = true; Microsoft.Office.Interop.Excel.Workbook excelbk = excelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelbk.Worksheets["Sheet1"]; int worksheetcol = WORKSHEETSTARTCOL; int jmlCol = dgvGrid.Columns.Count; for (int colCount = 0; colCount < jmlCol; colCount++) { Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet1.Cells[WORKSHEETSTARTROW, worksheetcol]; if (colCount > 1 && colCount < (jmlCol - JML_COL_TETAP)) { try { tgl = DateTime.Parse(dgvGrid.Columns[colCount].Name); xlRange.Value2 = tgl.ToString("dd"); //xlRange.Value2 = tgl.ToString("dd-MMM-yyyy") + "(" + tgl.DayOfWeek.ToString().ToUpper().Substring(0, 3) + ")"; } catch { } } else { xlRange.Value2 = dgvGrid.Columns[colCount].HeaderText; } worksheetcol += 1; } int worksheetRow = WORKSHEETSTARTROW + 1; for (int rowCount = 0; rowCount < dgvGrid.Rows.Count; rowCount++) { if (dgvGrid.Rows[rowCount].IsNewRow) { continue; } worksheetcol = WORKSHEETSTARTCOL; for (int colCount = 0; colCount < jmlCol; colCount++) { Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet1.Cells[worksheetRow, worksheetcol]; xlRange.Value2 = dgvGrid.Rows[rowCount].Cells[colCount].Value.ToString(); //xlRange.Font.Background = dataGridView1.Rows[rowCount].Cells[colCount].Style.BackColor; //xlRange.Font.Color = dgvGrid.Rows[rowCount].Cells[colCount].Style.ForeColor.ToArgb(); if (colCount > 1 && colCount < (jmlCol - JML_COL_TETAP)) { if (chkUseColor.Checked) { xlRange.Value2 = ""; xlRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(dgvGrid.Rows[rowCount].Cells[colCount].Style.BackColor); } else { switch (dgvGrid.Rows[rowCount].Cells[colCount].Value.ToString()) { case "0": xlRange.Value2 = STAT_ALPA; break; case "1": xlRange.Value2 = STAT_KERJA; break; case "2": xlRange.Value2 = STAT_TIDAKABSEN; break; case "3": xlRange.Value2 = STAT_TELAT; break; case "4": xlRange.Value2 = STAT_PULANGCEPAT; break; case "5": xlRange.Value2 = STAT_OFF; break; case "6": xlRange.Value2 = STAT_IJIN; break; case "7": xlRange.Value2 = STAT_OFFMASUK; break; case "8": xlRange.Value2 = STAT_IJIN; break; } } } //if (dgvGrid.Rows[rowCount].Cells[colCount].Style.Font != null) //{ // xlRange.Font.Bold = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Bold; // xlRange.Font.Italic = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Italic; // xlRange.Font.Underline = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Underline; // xlRange.Font.FontStyle = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.FontFamily; //} worksheetcol += 1; } worksheetRow += 1; } }
public void ExportToExcel(List <PrintList> print) { // Load Excel application Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); // Create empty workbook excel.Workbooks.Add(); // Create Worksheet from active sheet Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet; // I created Application and Worksheet objects before try/catch, // so that i can close them in finnaly block. // It's IMPORTANT to release these COM objects!! try { // ------------------------------------------------ // Creation of header cells // ------------------------------------------------ workSheet.Cells[1, "A"] = translationText.Get("excelA"); workSheet.Cells[1, "B"] = translationText.Get("excelB"); workSheet.Cells[1, "C"] = translationText.Get("excelC"); workSheet.Cells[1, "D"] = translationText.Get("excelD"); workSheet.Cells[1, "E"] = translationText.Get("excelE"); // ------------------------------------------------ // Populate sheet with some real data from "cars" list // ------------------------------------------------ int row = 2; // start row (in row 1 are header cells) foreach (PrintList prn in print) { workSheet.Cells[row, "A"] = prn.EmployeeName; workSheet.Cells[row, "B"] = prn.DateTime; workSheet.Cells[row, "C"] = prn.CtrlID; workSheet.Cells[row, "D"] = prn.Ordinal; workSheet.Cells[row, "E"] = prn.DepartmentName; row++; } // Apply some predefined styles for data to look nicely :) workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); // Define filename string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)); // Save this data as a file workSheet.SaveAs(fileName); // Display SUCCESS message Console.WriteLine(string.Format("The file '{0}' is saved successfully!", fileName)); //MessageBox.Show(string.Format("The file '{0}' is saved successfully!", fileName)); } catch (Exception exception) { Console.WriteLine("Exception", "There was a PROBLEM saving Excel file!\n" + exception.Message); //MessageBox.Show("Exception", "There was a PROBLEM saving Excel file!\n" + exception.Message, MessageBoxButtons.OK, essageBoxIcon.Error); } finally { // Quit Excel application excel.Quit(); // Release COM objects (very important!) if (excel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); } if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); } // Empty variables excel = null; workSheet = null; // Force garbage collector cleaning GC.Collect(); } }
public void KetXuatLuongNV(DataTable dt, string SheetName, string title) { //Tạo các đối tượng trong Excel Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks oBooks; Microsoft.Office.Interop.Excel.Sheets oSheets; Microsoft.Office.Interop.Excel.Workbook book; Microsoft.Office.Interop.Excel.Worksheet sheet; //tạo mới 1 Exel WorkBook oExcel.Visible = true; oExcel.DisplayAlerts = false; oExcel.Application.SheetsInNewWorkbook = 1; oBooks = oExcel.Workbooks; book = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing)); oSheets = book.Worksheets; sheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1); sheet.Name = SheetName; //tạo phần đầu nếu muốn Microsoft.Office.Interop.Excel.Range head = sheet.get_Range("A1", "I1"); head.MergeCells = true; head.Value2 = title; head.Font.Bold = true; head.Font.Name = "Tahoma"; head.Font.Size = "18"; head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //Tạo tiêu đề cột Microsoft.Office.Interop.Excel.Range cl1 = sheet.get_Range("A3", "A3"); cl1.Value2 = "Mã NV"; cl1.ColumnWidth = 18.5; Microsoft.Office.Interop.Excel.Range cl2 = sheet.get_Range("B3", "B3"); cl2.Value2 = "Họ tên"; cl2.ColumnWidth = 25.5; Microsoft.Office.Interop.Excel.Range cl3 = sheet.get_Range("C3", "C3"); cl3.Value2 = "HSL"; cl3.ColumnWidth = 25.5; Microsoft.Office.Interop.Excel.Range cl4 = sheet.get_Range("D3", "D99"); cl4.Value2 = ""; cl4.NumberFormat = "dd/MM/yyyy"; cl4.ColumnWidth = 33.5; Microsoft.Office.Interop.Excel.Range cl5 = sheet.get_Range("E3", "E3"); cl5.Value2 = "BHXH"; cl5.ColumnWidth = 16; Microsoft.Office.Interop.Excel.Range cl6 = sheet.get_Range("F3", "F3"); cl6.Value2 = "BHYT"; cl6.ColumnWidth = 16.0; Microsoft.Office.Interop.Excel.Range cl7 = sheet.get_Range("G3", "G3"); cl7.Value2 = "BHTN"; cl7.ColumnWidth = 16; Microsoft.Office.Interop.Excel.Range cl8 = sheet.get_Range("H3", "H3"); cl8.Value2 = "Tổng phụ cấp"; cl8.ColumnWidth = 16; Microsoft.Office.Interop.Excel.Range cl9 = sheet.get_Range("I3", "I3"); cl9.Value2 = "Lương thực lãnh"; cl9.ColumnWidth = 30; Microsoft.Office.Interop.Excel.Range rowHead = sheet.get_Range("A3", "I3"); rowHead.Font.Bold = true; //kẻ viền rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; //thiết lập màu nền rowHead.Interior.ColorIndex = 15; rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //tạo mảng đối tượng để lưu trữ toàn bộ dữ liệu trong Database //vì dữ liệu đc gán vào các Cell trong Excel phải thông qua object thuần object[,] arr = new object[dt.Rows.Count, dt.Columns.Count]; //chuyển dữ liệu từ Database vào mảng đối tượng for (int r = 0; r < dt.Rows.Count; r++) { DataRow dr = dt.Rows[r]; for (int c = 0; c < dt.Columns.Count; c++) { arr[r, c] = dr[c]; } } //thiết lập vùng điền dữ liệu int rowStart = 4; int columnStart = 1; int rowEnd = rowStart + dt.Rows.Count - 1; int columnEnd = dt.Columns.Count; //Ô bắt đầu điền dữ liệu Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowStart, columnStart]; //Ô kết thúc điền dữ liệu Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowEnd, columnEnd]; //Lấy về vùng điền dữ liệu Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(c1, c2); //Điền dữ liệu vào vùng đã thiết lập range.Value2 = arr; // Kẻ viền range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Căn giữa cột STT Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowEnd, columnStart]; Microsoft.Office.Interop.Excel.Range c4 = sheet.get_Range(c1, c3); sheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; }