private static void PresentResult(Query query, Report report, int profileCounter, int cellOffset) { // This method shall include a verification of empty columns. If the user has selected more than one profile // the second query shall be presented on the right of the first query result in Excel. Microsoft.Office.Interop.Excel.Application currentApp = GA_Excel2007.Globals.ThisAddIn.Application; Worksheet activeSheet = currentApp.ActiveSheet as Worksheet; if (currentApp.ActiveSheet != null) { int dataLength = 2; if (report.Data != null) { dataLength = report.Data.GetLength(1); } int activeRow = currentApp.ActiveCell.Row; int activeColumn = currentApp.ActiveCell.Column + cellOffset; /*if (currentApp.ActiveCell.Cells.Value2 != null) * { * activeColumn = ActiveColumn(profileCounter, dataLength); * }*/ object[] queryInformation = GetQueryInformation(query, report, profileCounter); int infoRows = queryInformation.GetLength(0); if (report.Data != null) { dataLength = report.Data.GetLength(1); Range dataRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows + 1, activeColumn], activeSheet.Cells[activeRow + infoRows + report.Data.GetLength(0), activeColumn + report.Data.GetLength(1) - 1]); dataRange.Value2 = report.Data; Range headerRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows, activeColumn], activeSheet.Cells[activeRow + infoRows, activeColumn + report.Headers.GetLength(1) - 1]); headerRange.Value2 = report.Headers; headerRange.Font.Bold = true; } Range queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn], activeSheet.Cells[activeRow, activeColumn + dataLength - 1]); queryInformationRange.Font.Italic = true; queryInformationRange.MergeCells = true; queryInformationRange.Borders.Weight = XlBorderWeight.xlThin; queryInformationRange.Value2 = queryInformation; //int height = (int)queryInformationRange.Height; queryInformationRange.RowHeight = height; } }
// These examples are in Named and Optional, main topic. Can be deleted here. static void TestExcelCom() { var excelApp = new Microsoft.Office.Interop.Excel.Application(); var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1; // Using named and optional arguments, you can supply arguments for // only the parameters for which you do not want to use the default // value, and omit arguments for the other parameters. In the following // call, a value is sent for only one parameter. excelApp.get_Range("A1", "B4").AutoFormat(Format: myFormat); // In Visual C# 2008 and earlier versions, you need to supply an argument for // every parameter. The following call supplies a value for the first // parameter, and sends a placeholder value for the other six. The // default values are used for those parameters. excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); }
//Función para exportar a excel private void exportarAExcel(DataGridView dgvCierre) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); int iIndiceColumna = 0; excel.Columns.ColumnWidth = 20; excel.Cells[1, 1] = "INFORME DE VENTAS POR CATEGORIAS"; excel.Cells[2, 1] = "DESDE " + sfechaInicio + " A " + sfechaFin; foreach (DataGridViewColumn col in dgvCierre.Columns) { iIndiceColumna++; if (iIndiceColumna == 1) { excel.Cells[1, iIndiceColumna].ColumnWidth = 40; } excel.Cells[5, iIndiceColumna] = col.HeaderText; excel.Cells[5, iIndiceColumna].Interior.Color = Color.Yellow; } int iIndiceFila = 5; foreach (DataGridViewRow row in dgvCierre.Rows) { iIndiceFila++; iIndiceColumna = 0; foreach (DataGridViewColumn col in dgvCierre.Columns) { iIndiceColumna++; excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value; excel.Cells[iIndiceFila + 1, iIndiceColumna].BorderAround(); } } excel.get_Range("A5", "C5").BorderAround(); excel.Visible = true; }
//生成Excel private bool ExportToExcel(DataTable dt) { bool result = false; this.UseWaitCursor = true; if (dt.Rows.Count > 65536) { MessageBox.Show("數據記錄太多(最多不能超過65536條),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return result; } Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { labMsg.Text = "Excel正在匯出中…………"; if (xlApp == null) { throw new Exception("无法创建Excel对象,可能您的机器未安装Excel"); } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(true); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "過膠機生產日報表"; //worksheet.QueryTables.Add #region worksheet的格式 worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印 worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印 worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效 worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁 worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中 worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102); worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126); worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126); xlApp.Visible = false; #endregion #region 列名 #region 循環填充列名,很慢,已經REMARK //for (i = 1; i <= dt.Columns.Count - 1; i++) //{ // if (i == 11 || i == 12 || i == 13) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Substring(8, 3); // } // else if (i == 14 || i == 15 || i == 16 || i == 17) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim().Substring(8); // } // else if (i >= 23 && i <= 31) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim(); // } // else // { // MergeColumn(xlApp, xlApp.Cells[RowIndex, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex, i] = dt.Columns[i].ColumnName; // } //} #endregion int RowIndex = 2; MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 30], "過膠機生產日報表", 16, 65535, 0); //大標題 MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 30], "常用物料使用情況", 12, 65535, 0); RowIndex = RowIndex + 1; MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "上工序次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "上工序廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "累計數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "級別", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 23], "物料編號", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "物料名稱", 12, 65535, 35); MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "採購單號", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "生產批次", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "實際用量", 12, 65535, 8.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "單位", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "寬度(mm)", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "長度(m)", 12, 65535, 9); #endregion #region 填充數據 向Excel中逐行逐列写入表格中的数据 RowIndex = RowIndex + 1; //數據開始的行號 int ExcelCurrectRow = RowIndex; //當前行 int dtRowNO; int realRowNO = 0;//dt實際行 int dtKeyRowNO = RowIndex;//NO:4 int megreCount = 0; int dtMetrialRowNO = 23;//物料編號所在列 int tempRow; int tempCol; #region 較慢的填充數據方法 已經REMARK //for (int row = 0; row < dt.Rows.Count; row++) //{ // //DataRow[] rows = dt.Select(dt.Rows[row][0].ToString()); // for (int col = 1; col < dt.Columns.Count; col++) // { // if (row > 0) //班次內工程ID相等 col22之後不合併 // { // if (dt.Rows[row - 1][0].ToString() == dt.Rows[row][0].ToString() && col < 22) // { // CellStyle(xlApp, xlApp.Cells[currectRow, col], xlApp.Cells[RowIndex, col], 10, -4142, true); // } // else // { // CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false); // xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString(); // if (col < 22) currectRow = RowIndex; // } // } // else // { // CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false); // xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString(); // } // } // RowIndex++; // //if (RowIndex == 15) goto range; // for 測試 //} #endregion DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" }); for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++) { DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' "); megreCount = currectKeyRow.Length; for (tempRow = 0; tempRow < megreCount; tempRow++) { if (tempRow == 0) { for (tempCol = 1; tempCol < dtMetrialRowNO; tempCol++) //先合併物料編號之前的單元格 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true); } realRowNO += megreCount; } for (tempCol = dtMetrialRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false); } dtKeyRowNO++; } //dtKeyRowNO++; //if (realRowNO > dt.Rows.Count) break; /* * DataTable pjsf_iPJSFID = dt.DefaultView.ToTable(true, new string[] { "pjsf_iPJSFID" }); List<Thread> threads = new List<Thread>(); for (dtRowNO = 0; dtRowNO < pjsf_iPJSFID.Rows.Count; dtRowNO++) { */ } #endregion #region 設置Sheet邊框 //range: Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]); SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; #endregion Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]); DataFormat.Font.Name = "楷体"; DataFormat.Font.Size = 10; Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]); IntFormat.NumberFormatLocal = "0.00"; #region savecode Object missing = Missing.Value; SaveFileDialog saveForm = new SaveFileDialog(); saveForm.Filter = "Execl files (*.xls)|*.xls"; saveForm.FilterIndex = 0; saveForm.FileName = "過膠機生產日報表"; if (saveForm.ShowDialog() == DialogResult.OK) { string fileName = saveForm.FileName; workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } #endregion result = true; } catch (Exception ex) { throw ex; } finally { labMsg.Text = ""; this.UseWaitCursor = false; xlApp.Quit(); IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法 int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); System.GC.Collect(); } labMsg.Text = ""; return result; }
private void btnToExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excel = null; try { //DataGridView沒有資料就不執行 if (this.dgvShowData.Rows.Count <= 1) { MessageBox.Show("沒有可滙出的資料!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } //new 出一個Excel excel = new Microsoft.Office.Interop.Excel.Application(); //看的到Excel在工作 excel.Visible = true; //新增加一工作簿 excel.Application.Workbooks.Add(true); //寫入欄位名稱 for (int i = 0; i < dgvShowData.Columns.Count; i++) { excel.Cells[1, i + 1] = dgvShowData.Columns[i].HeaderText; } //把DataGridView資料寫到Excel for (int i = 0; i < dgvShowData.Rows.Count - 1; i++) { for (int j = 0; j < dgvShowData.Columns.Count; j++) { if (dgvShowData[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dgvShowData[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dgvShowData[j, i].Value.ToString(); } } } //設定滙出後,欄位寛度自動配合資料調整 excel.Cells.EntireRow.AutoFit(); //自動調整列高 excel.Cells.EntireColumn.AutoFit(); excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; excel.get_Range("A1").Font.Color = Color.White; excel.get_Range("A1").Interior.Color = Color.DodgerBlue; excel.get_Range("B1").Font.Color = Color.White; excel.get_Range("B1").Interior.Color = Color.DodgerBlue; excel.get_Range("C1").Font.Color = Color.White; excel.get_Range("C1").Interior.Color = Color.DodgerBlue; excel.get_Range("D1").Font.Color = Color.White; excel.get_Range("D1").Interior.Color = Color.DodgerBlue; excel.get_Range("E1").Font.Color = Color.White; excel.get_Range("E1").Interior.Color = Color.DodgerBlue; excel.get_Range("F1").Font.Color = Color.White; excel.get_Range("F1").Interior.Color = Color.DodgerBlue; excel.get_Range("G1").Font.Color = Color.White; excel.get_Range("G1").Interior.Color = Color.DodgerBlue; excel.get_Range("H1").Font.Color = Color.White; excel.get_Range("H1").Interior.Color = Color.DodgerBlue; excel.get_Range("I1").Font.Color = Color.White; excel.get_Range("I1").Interior.Color = Color.DodgerBlue; excel.get_Range("J1").Font.Color = Color.White; excel.get_Range("J1").Interior.Color = Color.DodgerBlue; excel.get_Range("K1").Font.Color = Color.White; excel.get_Range("K1").Interior.Color = Color.DodgerBlue; excel.get_Range("L1").Font.Color = Color.White; excel.get_Range("L1").Interior.Color = Color.DodgerBlue; excel.get_Range("M1").Font.Color = Color.White; excel.get_Range("M1").Interior.Color = Color.DodgerBlue; excel.get_Range("N1").Font.Color = Color.White; excel.get_Range("N1").Interior.Color = Color.DodgerBlue; excel.get_Range("O1").Font.Color = Color.White; excel.get_Range("O1").Interior.Color = Color.DodgerBlue; //設置禁止彈出覆蓋或儲存的彈跳視窗 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; MessageBox.Show("已成功輸出至Excel!", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { //釋放資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); } }
private static void PresentResultUpdate(Query query, Report report, int profileCounter) { Microsoft.Office.Interop.Excel.Application currentApp = GA_Excel2007.Globals.ThisAddIn.Application; Worksheet activeSheet = currentApp.ActiveSheet as Worksheet; if (currentApp.ActiveSheet != null) { int activeColumn = query.Column; int activeRow = query.Row; string addressLocal = ""; string addressHelper = ""; object[] queryInformation = GetQueryInformation(query, report, profileCounter); int infoRows = queryInformation.GetLength(0); int dataLength = dataLength = report.Data.GetLength(1); Range queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn], activeSheet.Cells[activeRow, activeColumn + dataLength - 1]); // addressLocal = queryInformationRange.get_Address().Replace("$", ""); string[] adLocal = addressLocal.Split(':'); // Looks after other reports in the same column. foreach (string address in _addressQueries) { addressHelper = address.Replace("$", ""); // If there exist a report above this report then check where that report's data range ends. if (addressHelper.First().Equals(adLocal[0].First())) { addressHelper = addressHelper.Substring(1, addressHelper.Length - 1); int addressHelperInt = Int32.Parse(addressHelper); int addressLocalInt = Int32.Parse(adLocal[0].Substring(1, adLocal[0].Length - 1)); if (addressHelperInt > addressLocalInt) { activeRow = addressHelperInt + 2; queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn], activeSheet.Cells[activeRow, activeColumn + dataLength - 1]); } } } ((Style)queryInformationRange.Style).WrapText = false; queryInformationRange.Font.Italic = true; queryInformationRange.MergeCells = true; queryInformationRange.Borders.Weight = XlBorderWeight.xlThin; queryInformationRange.Value2 = queryInformation; if (report.Data != null) { dataLength = report.Data.GetLength(1); Range dataRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows + 1, activeColumn], activeSheet.Cells[activeRow + infoRows + report.Data.GetLength(0), activeColumn + report.Data.GetLength(1) - 1]); dataRange.Value2 = report.Data; // Saves the last cell in this particular data range. string endDataRow = ""; endDataRow = adLocal[0].First() + (activeRow + infoRows + report.Data.GetLength(0)).ToString(); _addressQueries.Add(endDataRow); Range headerRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows, activeColumn], activeSheet.Cells[activeRow + infoRows, activeColumn + report.Headers.GetLength(1) - 1]); headerRange.Value2 = report.Headers; headerRange.Font.Bold = true; } } }
private void btnSendToExcel_Click(object sender, EventArgs e) { Excel.Application excel = null; try { //DataGridView沒有資料就不執行 if (this.dgvReferOrderAll.Rows.Count <= 1) { MessageBox.Show("沒有可滙出的資料!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } string Date = DateTime.Now.ToString("yyyy-MM-dd"); //設定滙出後的存檔路徑(儲存在桌面) string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\BentoReport_" + Date + ".xls"; //new 出一個Excel excel = new Microsoft.Office.Interop.Excel.Application(); //看的到Excel在工作 excel.Visible = false; //新增加一工作簿 excel.Application.Workbooks.Add(true); PGB pgb = new PGB(); pgb.progressBar1.Minimum = 0; pgb.progressBar1.Maximum = dgvReferOrderAll.Rows.Count - 1; pgb.progressBar1.Step = 1; pgb.progressBar1.Value = 0; pgb.progressBar1.ForeColor = Color.Pink; pgb.progressBar1.Style = ProgressBarStyle.Continuous; pgb.Show(); //寫入欄位名稱 for (int i = 0; i < dgvReferOrderAll.Columns.Count; i++) { excel.Cells[1, i + 1] = dgvReferOrderAll.Columns[i].HeaderText; } //把DataGridView資料寫到Excel for (int i = 0; i < dgvReferOrderAll.Rows.Count - 1; i++) { pgb.progressBar1.Value++; for (int j = 0; j < dgvReferOrderAll.Columns.Count; j++) { if (dgvReferOrderAll[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dgvReferOrderAll[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dgvReferOrderAll[j, i].Value.ToString(); } } //設定欄位靠右 excel.get_Range("A" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign. xlHAlignRight; excel.get_Range("C" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign. xlHAlignRight; //設定欄位顏色 excel.get_Range("A" + (i + 2)).Interior.Color = Color.Pink; excel.get_Range("B" + (i + 2)).Interior.Color = Color.Pink; excel.get_Range("C" + (i + 2)).Interior.Color = Color.Pink; excel.get_Range("D" + (i + 2)).Interior.Color = Color.Pink; //設定欄位框線 excel.get_Range("A" + (i + 2)).Borders.LineStyle = 1; excel.get_Range("B" + (i + 2)).Borders.LineStyle = 1; excel.get_Range("C" + (i + 2)).Borders.LineStyle = 1; excel.get_Range("D" + (i + 2)).Borders.LineStyle = 1; } /* 先將DataGridView的Rows總數給變數aa,以利後續透過aa+1的方式來新增要加入的資料 (將DataGridView資料轉至Excel後,在Rows下方插入TextBox.Text) */ int aa = dgvReferOrderAll.Rows.Count + 1; excel.Cells[aa, 1] = "[葷]數量:"; excel.Cells[aa, 2] = txtTotalOrder0.Text; excel.Cells[aa, 3] = "[素]數量:"; excel.Cells[aa, 4] = txtTotalOrder1.Text; //設定欄位靠右 excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; //設定欄位顏色 excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple; excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple; //設定欄位字體顏色 excel.get_Range("A" + aa).Font.Color = Color.Snow; excel.get_Range("B" + aa).Font.Color = Color.Snow; excel.get_Range("C" + aa).Font.Color = Color.Snow; excel.get_Range("D" + aa).Font.Color = Color.Snow; //設定欄位字體為粗體 excel.get_Range("A" + aa).Font.Bold = true; excel.get_Range("B" + aa).Font.Bold = true; excel.get_Range("C" + aa).Font.Bold = true; excel.get_Range("D" + aa).Font.Bold = true; aa = aa + 1; excel.Cells[aa, 1] = "[葷]金額:"; excel.Cells[aa, 2] = txtTotalPrice0.Text; excel.Cells[aa, 3] = "[素]金額:"; excel.Cells[aa, 4] = txtTotalPrice1.Text; excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple; excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple; excel.get_Range("A" + aa).Font.Color = Color.Snow; excel.get_Range("B" + aa).Font.Color = Color.Snow; excel.get_Range("C" + aa).Font.Color = Color.Snow; excel.get_Range("D" + aa).Font.Color = Color.Snow; excel.get_Range("A" + aa).Font.Bold = true; excel.get_Range("B" + aa).Font.Bold = true; excel.get_Range("C" + aa).Font.Bold = true; excel.get_Range("D" + aa).Font.Bold = true; aa = aa + 1; excel.Cells[aa, 3] = "總金額:"; excel.Cells[aa, 4] = txtTotalPriceAll.Text; excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid; excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple; excel.get_Range("C" + aa).Font.Color = Color.Snow; excel.get_Range("D" + aa).Font.Color = Color.Snow; excel.get_Range("C" + aa).Font.Bold = true; excel.get_Range("D" + aa).Font.Bold = true; //設定滙出後,欄位寛度自動配合資料調整 excel.Cells.EntireRow.AutoFit(); //自動調整列高 excel.Cells.EntireColumn.AutoFit(); //將所有欄位做垂直置中 excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; //將A1~D1的欄位做水平置中 excel.get_Range("A1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.get_Range("B1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.get_Range("C1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excel.get_Range("D1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //設定A1~D1欄位框線 excel.get_Range("A1").Borders.LineStyle = 1; excel.get_Range("B1").Borders.LineStyle = 1; excel.get_Range("C1").Borders.LineStyle = 1; excel.get_Range("D1").Borders.LineStyle = 1; excel.get_Range("A1").Font.Color = Color.White; excel.get_Range("A1").Interior.Color = Color.DodgerBlue; excel.get_Range("B1").Font.Color = Color.White; excel.get_Range("B1").Interior.Color = Color.DodgerBlue; excel.get_Range("C1").Font.Color = Color.White; excel.get_Range("C1").Interior.Color = Color.DodgerBlue; excel.get_Range("D1").Font.Color = Color.White; excel.get_Range("D1").Interior.Color = Color.DodgerBlue; /* excel.get_Range("E1").Font.Color = Color.White; excel.get_Range("E1").Interior.Color = Color.DodgerBlue; excel.get_Range("F1").Font.Color = Color.White; excel.get_Range("F1").Interior.Color = Color.DodgerBlue; excel.get_Range("G1").Font.Color = Color.White; excel.get_Range("G1").Interior.Color = Color.DodgerBlue; excel.get_Range("H1").Font.Color = Color.White; excel.get_Range("H1").Interior.Color = Color.DodgerBlue; excel.get_Range("I1").Font.Color = Color.White; excel.get_Range("I1").Interior.Color = Color.DodgerBlue; excel.get_Range("J1").Font.Color = Color.White; excel.get_Range("J1").Interior.Color = Color.DodgerBlue; excel.get_Range("K1").Font.Color = Color.White; excel.get_Range("K1").Interior.Color = Color.DodgerBlue; excel.get_Range("L1").Font.Color = Color.White; excel.get_Range("L1").Interior.Color = Color.DodgerBlue; excel.get_Range("M1").Font.Color = Color.White; excel.get_Range("M1").Interior.Color = Color.DodgerBlue; excel.get_Range("N1").Font.Color = Color.White; excel.get_Range("N1").Interior.Color = Color.DodgerBlue; excel.get_Range("O1").Font.Color = Color.White; excel.get_Range("O1").Interior.Color = Color.DodgerBlue; */ //設置禁止彈出覆蓋或儲存的彈跳視窗 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; //將檔案儲存到SaveFile指定的位置 excel.ActiveWorkbook.SaveCopyAs(SaveFilePath); pgb.Close(); MessageBox.Show("已成功滙出Excel檔!" + Environment.NewLine + "檔案儲存在您電腦的桌面,檔名:BentoReport_" + Date + ".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message); } //關閉工作簿和結束Excel程式 excel.Workbooks.Close(); excel.Quit(); //釋放資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); }
private void button2_Click(object sender, EventArgs e) { Excel.Application ex = new Microsoft.Office.Interop.Excel.Application(); ex.Visible = true; ex.SheetsInNewWorkbook = 12; Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing); ex.DisplayAlerts = false; var currentYear1 = DateTime.Now.Year; for (int i = 0; i < 12; i++) { Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(i + 1); sheet.StandardWidth = 3; sheet.Name = months[i]; sheet.Cells[1, 1] = String.Format("Номер отеля | День"); if (months[i] == "01" || months[i] == "03" || months[i] == "05" || months[i] == "07" || months[i] == "08" || months[i] == "10" || months[i] == "12") { for (int j = 2; j < 33; j++) { sheet.Cells[j] = String.Format("{0}", j - 1); } for (int j = 2; j < 17; j++) { sheet.Cells[j, 1] = String.Format("{0}", j - 1); } } if (months[i] == "02") { for (int j = 2; j < 31; j++) { sheet.Cells[j] = String.Format("{0}", j - 1); } for (int j = 2; j < 17; j++) { sheet.Cells[j, 1] = String.Format("{0}", j - 1); } } if (months[i] == "04" || months[i] == "06" || months[i] == "09" || months[i] == "11") { for (int j = 2; j < 32; j++) { sheet.Cells[j] = String.Format("{0}", j - 1); } for (int j = 2; j < 17; j++) { sheet.Cells[j, 1] = String.Format("{0}", j - 1); } } } SQLiteConnection sql = new SQLiteConnection(@"Data Source=D:\VS\Diplom\Guest.sqlite"); sql.Open(); int count = 0; SQLiteCommand sc4 = new SQLiteCommand("select count (*) as '" + count + "'from Гости", sql); //кол-во строк в таблице count = Convert.ToInt32(sc4.ExecuteScalar()); int[] b = new int[count]; //получение id int o = 0; SQLiteCommand sc3 = new SQLiteCommand("select id from Гости", sql); var mas1 = sc3.ExecuteReader(); while (mas1.Read()) { b[o] = Convert.ToInt32(mas1["id"]); o++; } string Date1, Date2, Number = ""; for (int j = 0; j < count; j++) { SQLiteCommand sc = new SQLiteCommand("select Дата_Заезда from Гости where id='" + b[j] + "'", sql); SQLiteCommand sc1 = new SQLiteCommand("select Дата_Выезда from Гости where id='" + b[j] + "'", sql); SQLiteCommand sc2 = new SQLiteCommand("select Номер_Комнаты from Гости where id= '" + b[j] + "'", sql); Date1 = sc.ExecuteScalar().ToString(); Date2 = sc1.ExecuteScalar().ToString(); Number = sc2.ExecuteScalar().ToString(); var currentYear = DateTime.Now.Date; string[] words = Date1.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries); //разделение даты string Day = words[0]; string Month = words[1]; string Year = words[2]; words = Date2.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries); string Day1 = words[0]; string Month1 = words[1]; string Year1 = words[2]; Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(Month); if (listBox2.SelectedItem.ToString() == currentYear1.ToString()) { if (Month == Month1) { ((Excel.Worksheet)ex.Application.ActiveWorkbook.Sheets[Month]).Select(); Excel.Range range2 = ex.get_Range(ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day) + 1], ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day1) + 1]); range2.Interior.Color = ColorTranslator.ToOle(Color.Blue); } else { string h = Day1; for (int a = Convert.ToInt32(Month); a <= Convert.ToInt32(Month1); a++) { if (a != Convert.ToInt32(Month1)) { if (a == 01 || a == 03 || a == 05 || a == 07 || a == 08 || a == 10 || a == 12) { Day1 = "31"; } else if (a == 02) { Day1 = "29"; } else if (a == 04 || a == 06 || a == 09 || a == 11) { Day1 = "30"; } } ((Excel.Worksheet)ex.Application.ActiveWorkbook.Sheets[a]).Select(); Excel.Range range2 = ex.get_Range(ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day) + 1], ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day1) + 1]); range2.Interior.Color = ColorTranslator.ToOle(Color.Blue); Day1 = h; } } } } sql.Close(); }
// Клик по кнопке составить расписание private void createSchedule_Click(object sender, RoutedEventArgs e) { this.IsEnabled = false; this.Cursor = Cursors.Wait; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); if (excel == null) { MessageBox.Show("EXCEL не может быть запущен. Убедитесь, что у вас установлен пакет MS Office.", "Excel Error", MessageBoxButton.OK, MessageBoxImage.Error); return; } //excel.Visible = true; Excel.Workbook wb = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; ws.Name = "Расписание"; LessonsSchedule[] rings = context.lessonsSchedule .OrderBy(lesson => lesson.numberLesson) .ToArray(); int column = 2; for (int row = 2; row < (App.days.Length) * rings.Length;) { foreach (LessonsSchedule ring in rings) { ws.Cells[column, 2] = ring.numberLesson + " пара" + "\n" + ring.beginTime + " - " + ring.endTime; column++; } excel.get_Range(String.Format("A{0}:A{1}", row, column - 1), Type.Missing).Merge(Type.Missing); ws.Cells[row, 1] = App.days[(row - 2) / rings.Length]; row += rings.Length; } // Список всех преподователей из таблицы правил int[] teachers = context.regulation .Join( context.teacher, reg => reg.idTeacher, teach => teach.id, (reg, teach) => new { reg.idTeacher, teach.lastname } ) .Distinct() .OrderBy(x => x.lastname) .Select(x => x.idTeacher) .ToArray(); //Количество недель в семестре int weeksCount; int lectures; // Количество часов на лекции int practices; // Количество часов на практику int laboratorys; // КОличествво часов на лабораторные int maxLessons; // Максимальное число пар за день int currentTeacherNumber = 2; // Номер преподователя, нужен для заполнения excel string lessonType; // Тип лекции (л. пр. лаб.) List <Regulation> deleteReg = new List <Regulation>(); List <int> tempLessons = new List <int>(); foreach (int teacherID in teachers) { ///////////////////\\\\\\\\\\\\\\\\\\\\ System.Diagnostics.Debug.WriteLine("------------------------------------------------------------------>"); ///////////////////\\\\\\\\\\\\\\\\\\\\ currentTeacherNumber++; // Текущий преподователь Teacher currentTeacher = context.teacher .Where(id => id.id == teacherID) .Single(); ///////////////////\\\\\\\\\\\\\\\\\\\\ System.Diagnostics.Debug.WriteLine("Преподователь: ID = {0}, Фамилия = {1}", currentTeacher.id, currentTeacher.lastname); ///////////////////\\\\\\\\\\\\\\\\\\\\ ws.Cells[1, currentTeacherNumber] = currentTeacher.lastname; // Все правила текущего преподователя, отсортированных по дням недели List <Regulation> teacherFromReg = context.regulation .Where(id => id.idTeacher == teacherID) .OrderBy(d => d.day) .ToList(); // Рабочий план текущего преподователя WorkPlan[] workPlan = context.workPlan .Where(id => id.idTeacher == teacherID) .ToArray(); foreach (WorkPlan plan in workPlan) { // Количество дней в семестре TimeSpan daysOfSemester = DateTime.Parse(plan.endDate) - DateTime.Parse(plan.beginDate); //Количество недель в семестре weeksCount = daysOfSemester.Days / 7; // Количество часов на лекции в неделю у данного преподователя на данный предмет lectures = plan.lecturesTime / weeksCount; // Количество часов на практику в неделю у данного преподователя на данный предмет practices = plan.practiceTime / weeksCount; // Количество часов на лабораторные в неделю у данного преподователя на данный предмет laboratorys = plan.laboratoryTime / weeksCount; ///////////////////\\\\\\\\\\\\\\\\\\\\ System.Diagnostics.Debug.WriteLine("Количество недель в семестре {0}", weeksCount); System.Diagnostics.Debug.WriteLine("Рабочий план НА СЕМЕСТР: ID = {0}, Лекций = {1}, Практик = {2}, Лаб = {3}", plan.id, plan.lecturesTime, plan.practiceTime, plan.laboratoryTime); System.Diagnostics.Debug.WriteLine("Рабочий план НА НЕДЕЛЮ: ID = {0}, Лекций = {1}, Практик = {2}, Лаб = {3}", plan.id, lectures, practices, laboratorys); ///////////////////\\\\\\\\\\\\\\\\\\\\ // Удаляем использованные правила foreach (Regulation del in deleteReg) { teacherFromReg.Remove(del); } deleteReg.Clear(); foreach (Regulation reg in teacherFromReg) { maxLessons = reg.maxLesson; ///////////////////\\\\\\\\\\\\\\\\\\\\ System.Diagnostics.Debug.WriteLine(""); System.Diagnostics.Debug.WriteLine("Правило: ID = {0}, День недели = {1}, Занятия = {2}, Макс. занятий = {3}", reg.id, App.days[reg.day], reg.lessons, reg.maxLesson); ///////////////////\\\\\\\\\\\\\\\\\\\\ List <int> lessons = reg.lessons.Split(',') .Select(lesson => int.Parse(lesson)) .ToList(); tempLessons.Clear(); foreach (int lesson in lessons) { // Условие выхода из цикла if (maxLessons == 0) { // Добавляем запись правила в список, для удаления на следующей итерации deleteReg.Add(reg); break; } // Уменьшаем количество часов на занятия if (lectures > 0) { lectures--; lessonType = " л."; } else if (practices > 0) { practices--; lessonType = " пр."; } else if (laboratorys > 0) { laboratorys--; lessonType = " лаб."; } else { break; } maxLessons--; tempLessons.Add(lesson); // Возвращаем название предмета и его id var subject = context.workPlan .Where(currentPlan => currentPlan.id == plan.id) .Join( context.subject, currentPlan => currentPlan.idSubject, sub => sub.id, (currentPlan, sub) => new { sub.subjectName, sub.id } ) .Single(); // По id предмета возвращаем все аудитории, в которых он преподаётся string[] classes = context.subjectClasses .Join( context.classroom, sc => new { k1 = sc.idSubject, k2 = sc.idClassroom }, cl => new { k1 = subject.id, k2 = cl.id }, (sc, cl) => cl.className ) .ToArray(); ws.Cells[reg.day * rings.Length + 1 + lesson, currentTeacherNumber] = subject.subjectName + lessonType + "\n" + classes[new Random().Next(classes.Length)]; ////////////////lessons.Remove(lessons[lesson]); } ///////////////////\\\\\\\\\\\\\\\\\\\\ System.Diagnostics.Debug.WriteLine("Остаток НА НЕДЕЛЮ: Лекций: = {0}, Практических = {1}, Лабораторных = {2}", lectures, practices, laboratorys); ///////////////////\\\\\\\\\\\\\\\\\\\\ System.Diagnostics.Debug.WriteLine("Расписание: предмет = {0}, День недели = {1}, Занятия = {2}, Осталось свободных занятий = {3}", context.subject.Where(id => id.id == plan.idSubject).Select(n => n.subjectName).Single(), App.days[reg.day], String.Join(",", tempLessons .Select(x => x) .OrderBy(x => x) .ToArray()), maxLessons); ///////////////////\\\\\\\\\\\\\\\\\\\\ } } } /// ------- Цикл по составлению рассписания кончился ------------ /// Стили для excel // Преподаватели var teachersRange = ws.Range["C1", String.Format("{0}1", Convert.ToChar(67 + teachers.Length))]; // где 67 - код из ASCII буквы C teachersRange.Orientation = 90; teachersRange.WrapText = false; teachersRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; teachersRange.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom; teachersRange.Font.Size = 16; teachersRange.Font.Name = "Arial"; // Дни недели var daysRange = ws.Range["A2", String.Format("A{0}", rings.Length * App.days.Length)]; daysRange.Orientation = 90; daysRange.WrapText = false; daysRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; daysRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; daysRange.Font.Size = 16; daysRange.Font.Name = "Arial"; // Номера занятий var lessonRange = ws.Range["B2", String.Format("B{0}", rings.Length * App.days.Length + 1)]; lessonRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; lessonRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; lessonRange.Font.Size = 12; lessonRange.ColumnWidth = 15; // Расписание var scheduleRange = ws.Range["C2", String.Format("{0}{1}", Convert.ToChar(67 + teachers.Length), rings.Length * App.days.Length + 1)]; scheduleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; scheduleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; scheduleRange.Font.Size = 12; scheduleRange.WrapText = true; scheduleRange.Rows.AutoFit(); scheduleRange.ColumnWidth = 25; SaveFileDialog saveExcelFile = new SaveFileDialog(); saveExcelFile.FileName = "Расписание"; saveExcelFile.DefaultExt = ".xlsx"; saveExcelFile.Filter = "Книга Excel 2007 (*.xlsx)|*.xlsx|Книга Excel 2003 (*.xls)|*.xls"; // Сохранение файла и закрытие excel if ((bool)saveExcelFile.ShowDialog()) { wb.SaveAs(saveExcelFile.FileName); wb.Close(true); excel.Quit(); excel.DisplayAlerts = false; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); Process.Start(saveExcelFile.FileName); } this.IsEnabled = true; this.Cursor = null; }
//生成Excel private bool ExportToExcel(DataTable dt) { bool result = false; if (dt.Rows.Count > 65536) { MessageBox.Show("數據記錄太多(最多不能超過65536條),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return result; } Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { labMsg.Text = "Excel正在匯出中…………"; if (xlApp == null) { throw new Exception("无法创建Excel对象,可能您的机器未安装Excel"); } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(true); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "過膠機品質自檢日報表"; worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印 worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印 worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效 worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁 worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中 worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102); worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126); worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126); xlApp.Visible = false; #region 列名 int RowIndex = 2; MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 32], "過膠機品質自檢日報表", 16, 65535, 0); //大標題 MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 32], "當班品檢情況", 12, 65535, 0); RowIndex = RowIndex + 1; MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "累計數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "車速", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "溫度", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "壓力", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 23], xlApp.Cells[RowIndex, 23], "當班品檢總次數", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "第( )次品檢", 12, 65535, 11); MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "打卡數量", 12, 65535, 11); MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "抽檢數量", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "品檢問題", 12, 65535, 18); MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "次品數量", 12, 65535, 11); MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "廢品數量", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "問題處理辦法", 12, 65535, 24); MergeColumn(xlApp, xlApp.Cells[RowIndex, 31], xlApp.Cells[RowIndex, 31], "品檢開始時間", 12, 65535, 24); MergeColumn(xlApp, xlApp.Cells[RowIndex, 32], xlApp.Cells[RowIndex, 32], "品檢結束時間", 12, 65535, 24); #endregion #region 填充數據 向Excel中逐行逐列写入表格中的数据 RowIndex = RowIndex + 1; //數據開始的行號 int ExcelCurrectRow = RowIndex; //當前行 int dtRowNO; int realRowNO = 0;//dt實際行 int dtKeyRowNO = RowIndex;//NO:4 int megreCount = 0; int dtProblemRowNO = 24;//物料編號所在列 int tempRow; int tempCol; DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" }); for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++) { DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' "); megreCount = currectKeyRow.Length; for (tempRow = 0; tempRow < megreCount; tempRow++) { if (tempRow == 0) { for (tempCol = 1; tempCol < dtProblemRowNO; tempCol++) //先合併物料編號之前的單元格 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true); } realRowNO += megreCount; } for (tempCol = dtProblemRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false); } dtKeyRowNO++; } } #endregion #region 設置Sheet邊框 //range: Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]); SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; #endregion Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]); DataFormat.Font.Name = "楷体"; DataFormat.Font.Size = 10; Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]); IntFormat.NumberFormatLocal = "0.00"; #region savecode Object missing = Missing.Value; SaveFileDialog saveForm = new SaveFileDialog(); saveForm.Filter = "Execl files (*.xls)|*.xls"; saveForm.FilterIndex = 0; saveForm.FileName = "過膠機品質自檢日報表"; if (saveForm.ShowDialog() == DialogResult.OK) { string fileName = saveForm.FileName; workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } #endregion result = true; } catch (Exception ex) { throw; } finally { labMsg.Text = ""; xlApp.Quit(); IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法 int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); System.GC.Collect(); } labMsg.Text = ""; return result; }
public ActionResult DownLoadFile() { try { #region Exal byte[] bytes; string fileName = "", contentType = "", PaperPath = ""; DataSet dswordchargeinrow = new DataSet(); KeystoneProject.Buisness_Logic.Master.Bl_Services Bl_obj = new Buisness_Logic.Master.Bl_Services(); Services AddServiceMod = new Services(); AddServiceMod.StoreAllServices = Bl_obj.GetAllServicesExcelData(); AddServiceMod.dsServicesCharges = Bl_obj.GetAllWardName(); 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; Excel.Range chartRange; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"]; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; app.get_Range("A2", "S2").Locked = true; string WardNAme; //worksheet.Name = "Exported from Keystone"; int count = 0; for (int i = 1; i < AddServiceMod.StoreAllServices.Tables[0].Columns.Count + 1; i++) { worksheet.Cells[1, i] = AddServiceMod.StoreAllServices.Tables[0].Columns[i - 1].ColumnName; count = i; } int m = 0; for (int word = 0; word < AddServiceMod.dsServicesCharges.Tables[0].Rows.Count; word++) { worksheet.Cells[1, count + word] = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"]; // worksheet.get_Range(worksheet.Cells[1, word], worksheet.Cells[4, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange); WardNAme = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"].ToString() + ","; m++; } for (int i = 0; i < AddServiceMod.StoreAllServices.Tables[0].Rows.Count; i++) { for (int j = 0; j < AddServiceMod.StoreAllServices.Tables[0].Columns.Count; j++) { // if (j < 2) // { worksheet.Cells[i + 2, j + 1] = AddServiceMod.StoreAllServices.Tables[0].Rows[i][j].ToString(); // } if (AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"].ToString() != "") { dswordchargeinrow = Bl_obj.SelectServiceChargesbyID(Convert.ToInt16(AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"])); if (dswordchargeinrow.Tables[0].Rows.Count > 0) { foreach (DataRow drcharge in AddServiceMod.dsServicesCharges.Tables[0].Rows) { int charge = 0; foreach (DataRow dr in dswordchargeinrow.Tables[0].Rows) { string WardName = drcharge["WardName"].ToString(); string WardName1 = dr["WardName"].ToString(); if (drcharge["WardName"].ToString() == dr["WardName"].ToString()) { worksheet.Cells[i + 2, charge + 7] = dr["GeneralCharges"]; } charge++; } } } } } } #endregion Random obj = new Random(); int a = obj.Next(1, 300); string DateCode = "Service_Excel_Data" + Bl_obj.ReffrenceExcel(); fileName = Server.MapPath("~/") + "Service_Excel/" + DateCode; workbook.SaveAs(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, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); string Path = fileName + ".xlsx"; string filename = System.IO.Path.GetFileName(DateCode + ".xlsx"); // string fullpath = System.IO.Path.Combine(Path, fileName); contentType = MimeMapping.GetMimeMapping(Path); File(Path, contentType, filename); // File(Path, contentType, filename); return(new JsonResult { Data = "Done", JsonRequestBehavior = JsonRequestBehavior.AllowGet }); //fileName = "Service_Excel_Data" + Bl_obj.ReffrenceCode()+".xlsx"; } catch (Exception ex) { throw; } // return File(FileById.FileContent, "/MRDFiles/pdf", FileById.FileName); }