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