private void xl_Click(object sender, EventArgs e) { copyAlltoClipboard(); 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(); xlexcel.Visible = true; xlWorkBook = xlexcel.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //worksheet name xlWorkSheet.Name = "ParentsFeedbackReport"; //clipboard Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[2, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); //header string[] header = new string[2]; header[0] = "Question"; header[1] = "Average Grade"; xlWorkSheet.get_Range("B1", "C1").Value = header; //additionalComments xlWorkSheet.Cells[10, 2] = "Additional Comments"; xlWorkSheet.Cells[11, 2].Value = ParentsFreeTextByGroup.Text.Trim(); //GroupId xlWorkSheet.Cells[2, 7] = "GroupID: " + temp.getId().ToString(); //SetStyle xlWorkSheet.Columns[2].ColumnWidth = 50; xlWorkSheet.Columns[3].ColumnWidth = 50; xlWorkSheet.Columns[7].ColumnWidth = 30; xlWorkSheet.get_Range("B1", "C7").Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; xlWorkSheet.get_Range("B1", "C7").Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; Microsoft.Office.Interop.Excel.Style style = xlWorkBook.Styles.Add("NewStyle"); style.Font.Name = "Verdana"; style.Font.Size = 10; style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.GreenYellow); style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid; xlWorkSheet.get_Range("B1", "C1").Style = style; xlWorkSheet.Cells[10, 2].Style = style; xlWorkSheet.Cells[2, 7].Style = style; }
private void Button1_Click(object sender, EventArgs e) { if (cbxRegNo.SelectedIndex == -1) { MessageBox.Show("Please select a vehicle first."); } else { 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 = "Deliveries"; Microsoft.Office.Interop.Excel.Style myStyle1 = workbook.Styles.Add("myStyle1"); myStyle1.Font.Name = "Verdana"; myStyle1.Font.Size = 12; myStyle1.Font.Bold = true; myStyle1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); myStyle1.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid; Microsoft.Office.Interop.Excel.Style myStyle2 = workbook.Styles.Add("myStyle2"); myStyle2.Font.Name = "Verdana"; myStyle2.Font.Size = 11; myStyle2.Font.Bold = true; myStyle2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); myStyle2.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue); myStyle2.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid; worksheet.Cells[1, 1] = "R-Line Courier System"; worksheet.Cells[2, 1] = "Vehicle " + cbxRegNo.Text + " delivery information for " + (dateTimeDeliver.Value.ToString()).Substring(0, 10); worksheet.Cells[3, 1] = "Report dated " + DateTime.Now.ToString(); Microsoft.Office.Interop.Excel.Range formatRange; formatRange = worksheet.get_Range("a1", "c3"); formatRange.Style = "myStyle1"; formatRange = worksheet.get_Range("a5", "l5"); formatRange.Style = "myStyle2"; formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); worksheet.Columns[1].ColumnWidth = 20; worksheet.Columns[2].ColumnWidth = 20; worksheet.Columns[3].ColumnWidth = 25; worksheet.Columns[4].ColumnWidth = 20; worksheet.Columns[5].ColumnWidth = 25; worksheet.Columns[6].ColumnWidth = 20; worksheet.Columns[7].ColumnWidth = 20; worksheet.Columns[8].ColumnWidth = 20; worksheet.Columns[9].ColumnWidth = 35; worksheet.Columns[10].ColumnWidth = 35; worksheet.Columns[11].ColumnWidth = 35; worksheet.Columns[12].ColumnWidth = 20; //columns will start at 4 int counter = 4; for (int i = 1; i < dgvDeliveries.Columns.Count + 1; i++) { worksheet.Cells[1 + counter, i] = dgvDeliveries.Columns[i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < dgvDeliveries.Rows.Count - 1; i++) { for (int j = 0; j < dgvDeliveries.Columns.Count; j++) { worksheet.Cells[i + 2 + counter, j + 1] = dgvDeliveries.Rows[i].Cells[j].Value.ToString(); } } app.Quit(); } }
public void printPegawai(String filename) { try { excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; worKbooK = excel.Workbooks.Add(Type.Missing); worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet; worKsheeT.Name = "Report Pegawai"; worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 4]].Merge(); worKsheeT.Cells[1, 1] = "Laporan Pegawai"; worKsheeT.Cells.Font.Size = 15; worKsheeT.Cells[2, 1] = "Kode Pegawai"; worKsheeT.Cells[2, 2] = "Nama"; worKsheeT.Cells[2, 3] = "Tanggal Lahir"; worKsheeT.Cells[2, 4] = "Alamat"; worKsheeT.Cells[2, 5] = "Gaji"; worKsheeT.Cells[2, 6] = "Username"; worKsheeT.Cells[2, 7] = "Password"; worKsheeT.Cells[2, 8] = "IdJabatan"; //worKsheeT.Cells[2, 5] = "Kategori"; Microsoft.Office.Interop.Excel.Style style = excel.ActiveWorkbook.Styles.Add("NewStyle"); style.Font.Name = "Verdana"; style.Font.Size = 12; style.Font.Bold = true; style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Koneksi k = new Koneksi(); k.Connect(); int baris = 3; String q = "SELECT * FROM Pegawai"; MySqlCommand c = new MySqlCommand(q, k.KoneksiDB); MySqlDataReader rd = c.ExecuteReader(); while (rd.Read()) { worKsheeT.Cells[baris, 1] = rd["KodePegawai"]; worKsheeT.Cells[baris, 2] = rd["Nama"]; worKsheeT.Cells[baris, 3] = rd["TglLahir"]; worKsheeT.Cells[baris, 4] = rd["Alamat"]; worKsheeT.Cells[baris, 5] = rd["Gaji"]; worKsheeT.Cells[baris, 6] = rd["Username"]; worKsheeT.Cells[baris, 7] = rd["Password"]; worKsheeT.Cells[baris, 8] = rd["IdJabatan"]; /*if (worKsheeT.Cells[baris, 8] = "J1") * { * worKsheeT.Cells[baris, 8] = "Pegawai Pembelian"; * } * else if (worKsheeT.Cells[baris, 8] = "J2") * { * worKsheeT.Cells[baris, 8] = "Kasir"; * } * else if (worKsheeT.Cells[baris, 8] = "J3") * { * worKsheeT.Cells[baris, 8] = "Manager"; * }*/ baris = baris + 1; } Microsoft.Office.Interop.Excel.Range angkaStyles = excel.get_Range("A3:H" + baris); angkaStyles.NumberFormat = "##,#"; Microsoft.Office.Interop.Excel.Range rangeStyles = excel.get_Range("A1:H20"); Microsoft.Office.Interop.Excel.Range rangeStyles2 = excel.get_Range("A1:H2"); Microsoft.Office.Interop.Excel.Range rangeStyles3 = excel.get_Range("A1:H" + baris); rangeStyles2.Style = "NewStyle"; rangeStyles.Columns.AutoFit(); //rangeStyles.Value2 = "'Style Test"; //rangeStyles.Style = "NewStyle"; rangeStyles.Columns.AutoFit(); //rangeStyles3.BorderAround2(); rangeStyles3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; rangeStyles3.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; k.KoneksiDB.Close(); /* * MySqlConnection mc = new MySqlConnection(Koneksi.strCon); * MySqlCommand cmd = new MySqlCommand(q, mc); * MySqlDataReader rd = cmd.ExecuteReader(); * * * int barisS= 2; * while (rd.Read()) * { * worKsheeT.Cells[barisS, 1] = rd["KodeBarang"]; * worKsheeT.Cells[barisS, 2] = rd["NamaBarang"]; * worKsheeT.Cells[barisS, 3] = rd["HargaJual"]; * worKsheeT.Cells[barisS, 4] = rd["Stok"]; * barisS++; * //worKsheeT.Cells[2, 5] = "Kategori"; * }*/ //DateTime dt = DateTime.Now; worKbooK.SaveAs(filename); worKbooK.Close(); excel.Quit(); /*ProcessStartInfo info = new ProcessStartInfo(); * info.Verb = "print"; * info.FileName = @filename; * info.CreateNoWindow = true; * info.WindowStyle = ProcessWindowStyle.Hidden; * * Process p = new Process(); * p.StartInfo = info; * p.Start(); * * p.WaitForInputIdle(); * System.Threading.Thread.Sleep(3000);*/ //if (false == p.CloseMainWindow()) //p.Kill(); } catch (Exception ex) { } finally { worKsheeT = null; celLrangE = null; worKbooK = null; } }
//masih belum public void printLaporanPenjualan(String filename, String periode) { try { excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; worKbooK = excel.Workbooks.Add(Type.Missing); worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet; worKsheeT.Name = "Report"; worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 6]].Merge(); worKsheeT.Cells[1, 1] = "Laporan Penjualan"; worKsheeT.Cells.Font.Size = 15; worKsheeT.Cells[2, 1] = "Tanggal"; worKsheeT.Cells[2, 2] = "NoNota"; worKsheeT.Cells[2, 3] = "Nama Barang"; worKsheeT.Cells[2, 4] = "Jumlah"; worKsheeT.Cells[2, 5] = "Harga"; worKsheeT.Cells[2, 6] = "Total"; //worKsheeT.Cells[2, 5] = "Kategori"; Microsoft.Office.Interop.Excel.Style style = excel.ActiveWorkbook.Styles.Add("NewStyle"); style.Font.Name = "Verdana"; style.Font.Size = 12; style.Font.Bold = true; style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Koneksi k = new Koneksi(); k.Connect(); int baris = 3; String q = "SELECT DATE_FORMAT(Tanggal,'%d-%M-%Y') as Tanggal,b.Nama,nd.Jumlah,nd.Harga,(nd.Harga*nd.Jumlah) as Subtotal,nb.NoNota " + "FROM notajual nb " + "INNER JOIN notajualdetil nd ON (nb.NoNota=nd.NoNota) " + "INNER JOIN barang b ON (b.KodeBarang=nd.KodeBarang) " + "WHERE DATE_FORMAT(Tanggal,'%Y-%M')=?periode"; MySqlCommand c = new MySqlCommand(q, k.KoneksiDB); c.Parameters.Add(new MySqlParameter("periode", periode)); MySqlDataReader rd = c.ExecuteReader(); int total = 0; while (rd.Read()) { worKsheeT.Cells[baris, 1] = rd["Tanggal"]; worKsheeT.Cells[baris, 2] = rd["NoNota"]; worKsheeT.Cells[baris, 3] = rd["Nama"]; worKsheeT.Cells[baris, 4] = rd["Jumlah"]; worKsheeT.Cells[baris, 5] = rd["Harga"]; worKsheeT.Cells[baris, 6] = rd["Subtotal"]; total = total + Int32.Parse(rd["Subtotal"] + ""); baris = baris + 1; } worKsheeT.Cells[baris, 6] = total; Microsoft.Office.Interop.Excel.Range angkaStyles = excel.get_Range("E3:F" + baris); angkaStyles.NumberFormat = "##,#"; Microsoft.Office.Interop.Excel.Range rangeStyles = excel.get_Range("A1:F20"); Microsoft.Office.Interop.Excel.Range rangeStyles2 = excel.get_Range("A1:F2"); Microsoft.Office.Interop.Excel.Range rangeStyles3 = excel.get_Range("A1:F" + baris); rangeStyles2.Style = "NewStyle"; rangeStyles.Columns.AutoFit(); //rangeStyles.Value2 = "'Style Test"; //rangeStyles.Style = "NewStyle"; rangeStyles.Columns.AutoFit(); //rangeStyles3.BorderAround2(); rangeStyles3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; rangeStyles3.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; k.KoneksiDB.Close(); worKbooK.SaveAs(filename); worKbooK.Close(); excel.Quit(); ProcessStartInfo info = new ProcessStartInfo(); info.Verb = "print"; info.FileName = @filename; info.CreateNoWindow = true; info.WindowStyle = ProcessWindowStyle.Hidden; Process p = new Process(); p.StartInfo = info; p.Start(); p.WaitForInputIdle(); System.Threading.Thread.Sleep(3000); //if (false == p.CloseMainWindow()) //p.Kill(); } catch (Exception ex) { String msg = ex.Message; String abc = "aa"; } finally { worKsheeT = null; celLrangE = null; worKbooK = null; } }
private void Button1_Click(object sender, EventArgs e) { 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 = "Deliveries"; Microsoft.Office.Interop.Excel.Style myStyle1 = workbook.Styles.Add("myStyle1"); myStyle1.Font.Name = "Verdana"; myStyle1.Font.Size = 12; myStyle1.Font.Bold = true; myStyle1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); myStyle1.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid; Microsoft.Office.Interop.Excel.Style myStyle2 = workbook.Styles.Add("myStyle2"); myStyle2.Font.Name = "Verdana"; myStyle2.Font.Size = 11; myStyle2.Font.Bold = true; myStyle2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); myStyle2.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue); myStyle2.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid; worksheet.Cells[1, 1] = "R-Line Courier System"; String title = ""; if (rbDelivered.Checked) { title = "Delivered"; } if (rbUndelivered.Checked) { title = "Undelivered"; } worksheet.Cells[2, 1] = title + " parcels during the period " + (dateTimeFrom.Value.ToString()).Substring(0, 10) + " to " + (dateTimeTo.Value.ToString()).Substring(0, 10); worksheet.Cells[3, 1] = "Report dated " + DateTime.Now.ToString(); Microsoft.Office.Interop.Excel.Range formatRange; formatRange = worksheet.get_Range("a1", "c3"); formatRange.Style = "myStyle1"; formatRange = worksheet.get_Range("a5", "l5"); formatRange.Style = "myStyle2"; formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); worksheet.Columns[1].ColumnWidth = 15; worksheet.Columns[2].ColumnWidth = 15; worksheet.Columns[3].ColumnWidth = 25; worksheet.Columns[4].ColumnWidth = 20; worksheet.Columns[5].ColumnWidth = 15; worksheet.Columns[6].ColumnWidth = 20; worksheet.Columns[7].ColumnWidth = 20; worksheet.Columns[8].ColumnWidth = 20; worksheet.Columns[9].ColumnWidth = 30; worksheet.Columns[10].ColumnWidth = 30; worksheet.Columns[11].ColumnWidth = 35; worksheet.Columns[12].ColumnWidth = 20; int counter = 4; for (int i = 1; i < dvgReport.Columns.Count + 1; i++) { worksheet.Cells[1 + counter, i] = dvgReport.Columns[i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < dvgReport.Rows.Count - 1; i++) { for (int j = 0; j < dvgReport.Columns.Count; j++) { worksheet.Cells[i + 2 + counter, j + 1] = dvgReport.Rows[i].Cells[j].Value.ToString(); } } app.Quit(); }