private void btnExcelConvert_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); // System.Data.DataTable dat = (System.Data.DataTable)(dgvSaleBook.DataSource); DataTable dat = new DataTable(); foreach (DataGridViewColumn col in dgvSaleBook.Columns) { dat.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvSaleBook.Rows) { DataRow dRow = dat.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dat.Rows.Add(dRow); } dat.TableName = "Sale Book 2"; xlWorksheet.Name = dat.TableName; for (int j = 1; j < dat.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = dat.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < dat.Rows.Count; k++) { for (int l = 0; l < dat.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = dat.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btneExcelReport_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); DataTable dtAccount = new DataTable(); foreach (DataGridViewColumn col in dgvCompany.Columns) { dtAccount.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvCompany.Rows) { DataRow dRow = dtAccount.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dtAccount.Rows.Add(dRow); } dtAccount.Columns.Remove("dealerId"); dtAccount.Columns.Remove("जन्मदिनांक"); dtAccount.Columns.Remove("लिंग"); dtAccount.Columns.Remove("व्यापाऱ्याचा पत्ता"); dtAccount.Columns.Remove("ई-मेल"); dtAccount.TableName = "Dealer Report"; xlWorksheet.Name = dtAccount.TableName; for (int j = 1; j < dtAccount.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = dtAccount.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < dtAccount.Rows.Count; k++) { for (int l = 0; l < dtAccount.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = dtAccount.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnExport_Click(object sender, EventArgs e) { try { System.Data.DataTable ds = new System.Data.DataTable(); Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); foreach (DataGridViewColumn col in dgvStockRegister.Columns) { ds.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvStockRegister.Rows) { DataRow dRow = ds.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } ds.Rows.Add(dRow); } ds.Columns.Remove("प्रकार"); ds.Columns.Remove("कंपनी"); ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक"); ds.TableName = "Item Ledger Report"; xlWorksheet.Name = ds.TableName; for (int j = 1; j < ds.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < ds.Rows.Count; k++) { for (int l = 0; l < ds.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = ds.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ac) { MessageBox.Show(ac.Message); } }
private void btnExcelConvert_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataTable dat = (System.Data.DataTable)(dgvProftandLoss.DataSource); dat.TableName = "ProfitAndLossAC"; xlWorksheet.Name = dat.TableName; for (int j = 1; j < dat.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = dat.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < dat.Rows.Count; k++) { for (int l = 0; l < dat.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = dat.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void metroButton2_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); // Loop over DataTables in DataSet. System.Data.DataTable ds = (System.Data.DataTable)dgvStockRegister.DataSource; ds.TableName = "Stock Valuation"; Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); xlWorksheet.Name = ds.TableName; for (int j = 1; j < ds.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < ds.Rows.Count; k++) { for (int l = 0; l < ds.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = ds.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void btnExcelReport_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); // Loop over DataTables in DataSet. System.Data.DataTable ds = new DataTable(); foreach (DataGridViewColumn col in dgvAccountLedger.Columns) { ds.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvAccountLedger.Rows) { DataRow dRow = ds.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } ds.Rows.Add(dRow); } if (Utility.Langn == "English") { ds.TableName = "Villagewise Account Report"; } else { ds.TableName = "गावानुसार खाते उधारी रिपोर्ट"; } Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); xlWorksheet.Name = ds.TableName; for (int j = 1; j < ds.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < ds.Rows.Count; k++) { for (int l = 0; l < ds.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = ds.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnExcel_Click(object sender, EventArgs e) { try { pbar.Visible = true; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); // Loop over DataTables in DataSet. DataSet ds = new DataSet(); DataTable dtGST = new DataTable(); foreach (DataGridViewColumn col in dgvGST.Columns) { dtGST.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvGST.Rows) { DataRow dRow = dtGST.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dtGST.Rows.Add(dRow); } DataTable dtCate = new DataTable(); foreach (DataGridViewColumn col in dgvCategory.Columns) { dtCate.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvCategory.Rows) { DataRow dRow = dtCate.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dtCate.Rows.Add(dRow); } ds.Tables.Add(dtGST); ds.Tables.Add(dtCate); DataTableCollection collection = ds.Tables; for (int i = collection.Count; i > 0; i--) { Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataTable table = collection[i - 1]; xlWorksheet.Name = table.TableName; for (int j = 1; j < table.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < table.Rows.Count; k++) { for (int l = 0; l < table.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = table.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); } ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; pbar.Visible = false; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public override void CreateWorksheet(string worksheetname) { Microsoft.Office.Interop.Excel.Sheets sheets = xlWB.Worksheets; Microsoft.Office.Interop.Excel.Worksheet newWS = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); newWS.Name = worksheetname; ReleaseObject(sheets); ReleaseObject(newWS); }
private void btneExcelReport_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); DataTable dtAccount = (DataTable)grdCustomerDetails.DataSource; dtAccount.Columns["customerName"].ColumnName = "ग्राहकाचे नाव"; //dtAccount.Columns["isCustomerRetailer"].ColumnName = "ग्राहक/किररकोळ विक्रेता"; dtAccount.Columns["villageName"].ColumnName = "गाव"; // dtAccount.Columns["customerAddress"].ColumnName = "पत्ता"; dtAccount.Columns["customerMobileNo"].ColumnName = "मोबाईल क्र."; dtAccount.Columns["MobileTwo"].ColumnName = "मोबाईल क्र.2"; // dtAccount.Columns["MobileThree"].ColumnName = "मोबाईल क्र.3"; // dtAccount.Columns["genderId"].ColumnName = "लिंग"; //dtAccount.Columns["DOB"].ColumnName = "जन्मदिनांक"; // dtAccount.Columns["emailId"].ColumnName = "ई-मेल"; dtAccount.Columns["CustomerGSTNo"].ColumnName = "GST No"; dtAccount.Columns["contactPerson"].ColumnName = "संपर्क व्यक्ती"; dtAccount.Columns["contactNo"].ColumnName = "संपर्क मोबाईल नं."; dtAccount.Columns.Remove("customerId"); dtAccount.Columns.Remove("villageId"); dtAccount.Columns.Remove("isActive"); dtAccount.Columns.Remove("addedBy"); dtAccount.Columns.Remove("addedOn"); dtAccount.Columns.Remove("updateBy"); dtAccount.Columns.Remove("updateDate"); dtAccount.Columns.Remove("OtherNote"); dtAccount.Columns.Remove("DOB"); dtAccount.Columns.Remove("isCustomerRetailer"); dtAccount.Columns.Remove("customerAddress"); dtAccount.Columns.Remove("genderId"); dtAccount.Columns.Remove("emailId"); dtAccount.Columns.Remove("MobileThree"); dtAccount.TableName = "Customer Report"; xlWorksheet.Name = dtAccount.TableName; for (int j = 1; j < dtAccount.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = dtAccount.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < dtAccount.Rows.Count; k++) { for (int l = 0; l < dtAccount.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = dtAccount.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public string GerarFichas(string mes, string ano, List <System.Data.DataTable> data, List <string[]> colunas, String fileName) { string ret = "Relatório gerado com sucesso!"; try { Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet); Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel.Worksheet ws = null; if (data[6].Rows.Count > 0) { #region Ficha6F sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha6F"; for (int i = 1; i <= colunas[6].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[6][i - 1]; ws.Cells[5, i] = "(" + i + ")"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00"; for (int i = 0; i < data[6].Rows.Count; i++) { string data_formatada = data[6].Rows[i]["DT_DOC"].ToString(); ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4); ws.Cells[i + 6, 2] = data[6].Rows[i]["TIPO"]; ws.Cells[i + 6, 3] = data[6].Rows[i]["SER"]; ws.Cells[i + 6, 4] = data[6].Rows[i]["NUM_DOC"]; ws.Cells[i + 6, 5] = data[6].Rows[i]["COD_PART"]; ws.Cells[i + 6, 6] = data[6].Rows[i]["NUM_DECL_DESP_EXP"]; ws.Cells[i + 6, 7] = data[6].Rows[i]["VL_DOC"]; ws.Cells[i + 6, 8] = data[6].Rows[i]["VL_BC_ICMS"]; ws.Cells[i + 6, 9] = data[6].Rows[i]["VL_ICMS"]; ws.Cells[i + 6, 10] = data[6].Rows[i]["PCT_CRED_OUT"]; ws.Cells[i + 6, 11] = data[6].Rows[i]["VL_CRED_OUT"]; } for (int i = 1; i <= colunas[6].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Demonstrativo das Operações Não Geradoras de Crédito Acumulado"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } if (data[5].Rows.Count > 0) { #region Ficha6E sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha6E"; for (int i = 1; i <= colunas[5].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[5][i - 1]; if (i == 11) { ws.Cells[5, i] = "(" + i + ") = (9) * (10) / 100"; } else if (i == 14) { ws.Cells[5, i] = "(" + i + ") = (11) + (13)"; } else { ws.Cells[5, i] = "(" + i + ")"; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 14]).EntireColumn.NumberFormat = "#,##0.00"; for (int i = 0; i < data[5].Rows.Count; i++) { string data_formatada = data[5].Rows[i]["DT_DOC"].ToString(); ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4); ws.Cells[i + 6, 2] = data[5].Rows[i]["TIPO"]; ws.Cells[i + 6, 3] = data[5].Rows[i]["SER"]; ws.Cells[i + 6, 4] = data[5].Rows[i]["NUM_DOC"]; ws.Cells[i + 6, 5] = data[5].Rows[i]["COD_PART"]; ws.Cells[i + 6, 6] = data[5].Rows[i]["COD_ENQUADRAMENTO"]; ws.Cells[i + 6, 7] = data[5].Rows[i]["VL_DOC"]; ws.Cells[i + 6, 8] = data[5].Rows[i]["IVA"]; ws.Cells[i + 6, 9] = data[5].Rows[i]["CUSTO_EST"]; ws.Cells[i + 6, 10] = data[5].Rows[i]["PMC"]; ws.Cells[i + 6, 11] = data[5].Rows[i]["CRED_EST_IMP"]; ws.Cells[i + 6, 12] = data[5].Rows[i]["PCT_CRED_OUT"]; ws.Cells[i + 6, 13] = data[5].Rows[i]["VL_CRED_OUT"]; ws.Cells[i + 6, 14] = data[5].Rows[i]["VL_CRED_ACUM_GER"]; } for (int i = 1; i <= colunas[5].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso III - Operações sem Pagamento de Imposto - Demais Casos"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } if (data[4].Rows.Count > 0) { #region Ficha6D sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha6D"; for (int i = 1; i <= colunas[4].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[4][i - 1]; if (i == 12) { ws.Cells[5, i] = "(" + i + ") = (10) * (11)"; } else if (i == 16) { ws.Cells[5, i] = "(" + i + ") = (14) + (15)"; } else { ws.Cells[5, i] = "(" + i + ")"; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 10]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 12]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 15]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00"; for (int i = 0; i < data[4].Rows.Count; i++) { string data_formatada = data[4].Rows[i]["DT_DOC"].ToString(); ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4); ws.Cells[i + 6, 2] = data[4].Rows[i]["TIPO"]; ws.Cells[i + 6, 3] = data[4].Rows[i]["SER"]; ws.Cells[i + 6, 4] = data[4].Rows[i]["NUM_DOC"]; ws.Cells[i + 6, 5] = data[4].Rows[i]["COD_PART"]; ws.Cells[i + 6, 6] = data[4].Rows[i]["COD_ENQUADRAMENTO"]; ws.Cells[i + 6, 7] = (Convert.ToBoolean(data[4].Rows[i]["COMPROV_OP"])) ? "Sim" : "Não"; ws.Cells[i + 6, 8] = data[4].Rows[i]["VL_DOC"]; ws.Cells[i + 6, 9] = data[4].Rows[i]["IVA"]; ws.Cells[i + 6, 10] = data[4].Rows[i]["CUSTO_EST"]; ws.Cells[i + 6, 11] = data[4].Rows[i]["PMC"]; ws.Cells[i + 6, 12] = data[4].Rows[i]["CRED_EST_IMP"]; ws.Cells[i + 6, 13] = data[4].Rows[i]["PCT_CRED_OUT"]; ws.Cells[i + 6, 14] = data[4].Rows[i]["VL_CRED_OUT"]; ws.Cells[i + 6, 15] = data[4].Rows[i]["VL_ICMS_COMPR"]; ws.Cells[i + 6, 16] = data[4].Rows[i]["VL_CRED_ACUM_GER"]; } for (int i = 1; i <= colunas[4].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso III - Operações sem Pagamento de Imposto - Zona Franca de Manaus"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } if (data[3].Rows.Count > 0) { #region Ficha6B sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha6B"; for (int i = 1; i <= colunas[3].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[3][i - 1]; if (i == 13) { ws.Cells[5, i] = "(" + i + ") = (11) * (12)"; } else if (i == 16) { ws.Cells[5, i] = "(" + i + ") = (13) + (15)"; } else if (i == 17) { ws.Cells[5, i] = "(" + i + ") = (16) - (9)"; } else { ws.Cells[5, i] = "(" + i + ")"; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 13]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 17]).EntireColumn.NumberFormat = "#,##0.00"; for (int i = 0; i < data[3].Rows.Count; i++) { string data_formatada = data[3].Rows[i]["DT_DOC"].ToString(); ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4); ws.Cells[i + 6, 2] = data[3].Rows[i]["TIPO"]; ws.Cells[i + 6, 3] = data[3].Rows[i]["SER"]; ws.Cells[i + 6, 4] = data[3].Rows[i]["NUM_DOC"]; ws.Cells[i + 6, 5] = data[3].Rows[i]["COD_PART"]; ws.Cells[i + 6, 6] = data[3].Rows[i]["COD_ENQUADRAMENTO"]; ws.Cells[i + 6, 7] = data[3].Rows[i]["VL_DOC"]; ws.Cells[i + 6, 8] = data[3].Rows[i]["VL_BC_ICMS"]; ws.Cells[i + 6, 9] = data[3].Rows[i]["VL_ICMS"]; ws.Cells[i + 6, 10] = data[3].Rows[i]["IVA"]; ws.Cells[i + 6, 11] = data[3].Rows[i]["CUSTO_EST"]; ws.Cells[i + 6, 12] = data[3].Rows[i]["PMC"]; ws.Cells[i + 6, 13] = data[3].Rows[i]["CRED_EST_IMP"]; ws.Cells[i + 6, 14] = data[3].Rows[i]["PCT_CRED_OUT"]; ws.Cells[i + 6, 15] = data[3].Rows[i]["VL_CRED_OUT"]; ws.Cells[i + 6, 16] = data[3].Rows[i]["VL_TOTAL_ICMS"]; ws.Cells[i + 6, 17] = data[3].Rows[i]["VL_CRED_ACUM_GER"]; } for (int i = 1; i <= colunas[3].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso II - Operações com Redução de Base de Cálculo"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } if (data[2].Rows.Count > 0) { #region Ficha6A sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha6A"; for (int i = 1; i <= colunas[2].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[2][i - 1]; if (i == 13) { ws.Cells[5, i] = "(" + i + ") = (11) * (12)"; } else if (i == 16) { ws.Cells[5, i] = "(" + i + ") = (13) + (15)"; } else if (i == 17) { ws.Cells[5, i] = "(" + i + ") = (16) - (9)"; } else { ws.Cells[5, i] = "(" + i + ")"; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 13]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 17]).EntireColumn.NumberFormat = "#,##0.00"; for (int i = 0; i < data[2].Rows.Count; i++) { string data_formatada = data[2].Rows[i]["DT_DOC"].ToString(); ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4); ws.Cells[i + 6, 2] = data[2].Rows[i]["TIPO"]; ws.Cells[i + 6, 3] = data[2].Rows[i]["SER"]; ws.Cells[i + 6, 4] = data[2].Rows[i]["NUM_DOC"]; ws.Cells[i + 6, 5] = data[2].Rows[i]["COD_PART"]; ws.Cells[i + 6, 6] = data[2].Rows[i]["COD_ENQUADRAMENTO"]; ws.Cells[i + 6, 7] = data[2].Rows[i]["VL_DOC"]; ws.Cells[i + 6, 8] = data[2].Rows[i]["VL_BC_ICMS"]; ws.Cells[i + 6, 9] = data[2].Rows[i]["VL_ICMS"]; ws.Cells[i + 6, 10] = data[2].Rows[i]["IVA"]; ws.Cells[i + 6, 11] = data[2].Rows[i]["CUSTO_EST"]; ws.Cells[i + 6, 12] = data[2].Rows[i]["PMC"]; ws.Cells[i + 6, 13] = data[2].Rows[i]["CRED_EST_IMP"]; ws.Cells[i + 6, 14] = data[2].Rows[i]["PCT_CRED_OUT"]; ws.Cells[i + 6, 15] = data[2].Rows[i]["VL_CRED_OUT"]; ws.Cells[i + 6, 16] = data[2].Rows[i]["VL_TOTAL_ICMS"]; ws.Cells[i + 6, 17] = data[2].Rows[i]["VL_CRED_ACUM_GER"]; } for (int i = 1; i <= colunas[2].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso I - Operações com Aplicação de Alíquotas Diversificadas"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } if (data[1].Rows.Count > 0) { #region Ficha5D sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha5D"; for (int i = 1; i <= colunas[1].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[1][i - 1]; ws.Cells[5, i] = "(" + i + ")"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } for (int i = 0; i < data[1].Rows.Count; i++) { ws.Cells[i + 6, 1] = data[1].Rows[i]["COD_ENQUADRAMENTO"]; ws.Cells[i + 6, 2] = data[1].Rows[i]["COD_HIP_GER"]; ws.Cells[i + 6, 3] = data[1].Rows[i]["ANEXO"]; ws.Cells[i + 6, 4] = data[1].Rows[i]["ARTIGO"]; ws.Cells[i + 6, 5] = data[1].Rows[i]["INCISO"]; ws.Cells[i + 6, 6] = data[1].Rows[i]["ALINEA"]; ws.Cells[i + 6, 7] = data[1].Rows[i]["PARAGRAFO"]; ws.Cells[i + 6, 8] = data[1].Rows[i]["ITEM"]; ws.Cells[i + 6, 9] = data[1].Rows[i]["LETRA"]; ws.Cells[i + 6, 10] = data[1].Rows[i]["OBS"]; } for (int i = 1; i <= colunas[1].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Enquadramento Legal da Operação/Prestação Geradora do Crédito Acumulado do ICMS"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } if (data[0].Rows.Count > 0) { #region Ficha5C sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha5C"; for (int i = 1; i <= colunas[0].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[0][i - 1]; ws.Cells[5, i] = "(" + i + ")"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } for (int i = 0; i < data[0].Rows.Count; i++) { ws.Cells[i + 6, 1] = data[0].Rows[i]["COD_PART"]; ws.Cells[i + 6, 2] = data[0].Rows[i]["NOME"]; ws.Cells[i + 6, 3] = data[0].Rows[i]["COD_PAIS"]; ws.Cells[i + 6, 4] = data[0].Rows[i]["CNPJ"]; ws.Cells[i + 6, 5] = data[0].Rows[i]["IE"]; ws.Cells[i + 6, 6] = data[0].Rows[i]["END"]; ws.Cells[i + 6, 7] = data[0].Rows[i]["NUM"]; ws.Cells[i + 6, 8] = data[0].Rows[i]["COMPL"]; ws.Cells[i + 6, 9] = data[0].Rows[i]["BAIRRO"]; ws.Cells[i + 6, 10] = data[0].Rows[i]["MUN"]; ws.Cells[i + 6, 11] = data[0].Rows[i]["CEP"]; ws.Cells[i + 6, 12] = data[0].Rows[i]["UF"]; } for (int i = 1; i <= colunas[0].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Cadastro de Participantes de Operações e Prestações"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } wb.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wb.Close(); xla.Quit(); } catch (Exception ex) { ret = "Erro ao gerar relatório"; } return(ret); }
private void btneExcelReport_Click(object sender, EventArgs e) { try { DataTable dtAccount = new DataTable(); //Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //Microsoft.Office.Interop.Excel.Sheets xlSheets = null; //Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //xlSheets = ExcelApp.Sheets; Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetToUpload = default(Microsoft.Office.Interop.Excel.Worksheet); // xlWorkSheetToUpload = ExcelApp.Sheets["Sheet1"]; ExcelApp.Visible = true; Microsoft.Office.Interop.Excel.Sheets xlSheets = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorkSheetToUpload = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataSet gstR3B = gstR3BController.getGSTR3BReport(Convert.ToDateTime(dtpFrom.Value.ToShortDateString()), Convert.ToDateTime(dtpTo.Value.ToShortDateString()), Utility.FinancilaYearId, "GSTR3B"); dtAccount = gstR3B.Tables[0]; dtAccount.TableName = "GST 3B Report"; xlWorkSheetToUpload.Name = dtAccount.TableName; xlWorkSheetToUpload.Cells[8, 2] = "3.1 Details of Outward Supplies and inward supplies liable to reverse charge"; xlWorkSheetToUpload.Range["B8:G8"].MergeCells = true; int iRowCnt = 10; xlWorkSheetToUpload.Cells[iRowCnt - 1, 2] = "Nature of Supplies"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 3] = "Total Taxable Value"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 4] = "Integrated Tax"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 5] = "Central Tax"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 6] = "State UT Tax"; xlWorkSheetToUpload.Cells[iRowCnt - 1, 7] = "Cess"; // SHOW THE EXCEL SHEET. // SETTING IT VISIBLE WILL ALLOW YOU TO SEE HOW IT WRITES DATA TO EACH CELL. // Storing Each row and column value to excel sheet for (var i = 0; i <= dtAccount.Rows.Count - 1; i++) { xlWorkSheetToUpload.Cells[iRowCnt, 2] = dtAccount.Rows[i]["NatureofSupplies"]; xlWorkSheetToUpload.Cells[iRowCnt, 3] = dtAccount.Rows[i]["TotalTaxableValue"]; xlWorkSheetToUpload.Cells[iRowCnt, 4] = dtAccount.Rows[i]["IntegratedTax"]; xlWorkSheetToUpload.Cells[iRowCnt, 5] = dtAccount.Rows[i]["CentralTax"]; xlWorkSheetToUpload.Cells[iRowCnt, 6] = dtAccount.Rows[i]["StateUTTax"]; xlWorkSheetToUpload.Cells[iRowCnt, 7] = dtAccount.Rows[i]["Cess"]; iRowCnt = iRowCnt + 1; } xlWorkSheetToUpload.Cells[18, 2] = "4. Eligible ITC"; xlWorkSheetToUpload.Range["B18:F18"].MergeCells = true; xlWorkSheetToUpload.Cells[19, 2] = "Details"; xlWorkSheetToUpload.Cells[19, 3] = "Integrated Tax"; xlWorkSheetToUpload.Cells[19, 4] = "Central Tax"; xlWorkSheetToUpload.Cells[19, 5] = "State UT Tax"; xlWorkSheetToUpload.Cells[19, 6] = "Cess"; xlWorkSheetToUpload.Cells[20, 2] = "1"; xlWorkSheetToUpload.Cells[20, 3] = "2"; xlWorkSheetToUpload.Cells[20, 4] = "3"; xlWorkSheetToUpload.Cells[20, 5] = "4"; xlWorkSheetToUpload.Cells[20, 6] = "5"; // Storing Each row and column value to excel sheet int newRow = 21; for (var i = 0; i <= gstR3B.Tables[1].Rows.Count - 1; i++) { xlWorkSheetToUpload.Cells[newRow, 2] = gstR3B.Tables[1].Rows[i]["Details"]; xlWorkSheetToUpload.Cells[newRow, 3] = gstR3B.Tables[1].Rows[i]["IntegratedTax"]; xlWorkSheetToUpload.Cells[newRow, 4] = gstR3B.Tables[1].Rows[i]["CentralTax"]; xlWorkSheetToUpload.Cells[newRow, 5] = gstR3B.Tables[1].Rows[i]["StateUTTax"]; xlWorkSheetToUpload.Cells[newRow, 6] = gstR3B.Tables[1].Rows[i]["Cess"]; newRow = newRow + 1; } Microsoft.Office.Interop.Excel.Range formatRange; formatRange = xlWorkSheetToUpload.get_Range("B18:F18"); xlWorkSheetToUpload.get_Range("B8:G8").Font.Bold = true; xlWorkSheetToUpload.get_Range("B8:G8").Font.Size = 13; xlWorkSheetToUpload.get_Range("B9:G9").Font.Size = 11; xlWorkSheetToUpload.get_Range("B9:G9").Font.Bold = true; // formatRange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheetToUpload.Columns.AutoFit(); ExcelApp = null; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnPrint_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); // Loop over DataTables in DataSet. System.Data.DataTable ds = new DataTable(); foreach (DataGridViewColumn col in dgvPandingChalan.Columns) { ds.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvPandingChalan.Rows) { DataRow dRow = ds.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } ds.Rows.Add(dRow); } ds.Columns.Remove("salesChallanId"); if (rbDealer.Checked == true) { ds.TableName = "PendingPurchaseChalan"; } if (rbCustomer.Checked == true) { ds.TableName = "PendingSaleChalan"; } Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); xlWorksheet.Name = ds.TableName; for (int j = 1; j < ds.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < ds.Rows.Count; k++) { for (int l = 0; l < ds.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = ds.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void ExcelShow() { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); DataTable dtExcel = new DataTable(); if (cmbCustomerName.SelectedIndex > 0) { dtExcel = saleReportController.OneDaySaleReport(cmbCustomerName.SelectedIndex, Convert.ToDateTime(dtpFromDate.Value.ToShortDateString()), Convert.ToDateTime(dtpToDate.Value.ToShortDateString()), Utility.FinancilaYearId, 1); } else { dtExcel = saleReportController.OneDaySaleReport(cmbCustomerName.SelectedIndex, Convert.ToDateTime(dtpFromDate.Value.ToShortDateString()), Convert.ToDateTime(dtpToDate.Value.ToShortDateString()), Utility.FinancilaYearId, 2); } if (dtExcel.Rows.Count <= 0) { return; } if (dtExcel.Rows.Count > 0) { object qty = dtExcel.Compute("Sum(quantity)", string.Empty); object saleAmt = dtExcel.Compute("Sum(TotalBillAmt)", string.Empty); DataRow dr = dtExcel.NewRow(); dr["unitBy"] = "एकूण:"; dr["quantity"] = qty; dr["TotalBillAmt"] = Math.Round(Convert.ToDecimal(saleAmt), 2); dtExcel.Rows.Add(dr); } if (Utility.Langn == "English") { dtExcel.Columns["invoiceNo"].ColumnName = "Invoice No."; dtExcel.Columns["salesDate"].ColumnName = "Date"; dtExcel.Columns["customerName"].ColumnName = "Customer Name"; dtExcel.Columns["customerAddress"].ColumnName = "Customer Address"; dtExcel.Columns["itemName"].ColumnName = "Item Name"; dtExcel.Columns["unitBy"].ColumnName = "Unit By"; dtExcel.Columns["quantity"].ColumnName = "Quantity"; dtExcel.Columns["perQtysalePrice"].ColumnName = "Sale Price"; dtExcel.Columns["TotalBillAmt"].ColumnName = "Total Sale Amt"; } else { dtExcel.Columns["invoiceNo"].ColumnName = "बिल नं."; dtExcel.Columns["salesDate"].ColumnName = "दिनांक"; dtExcel.Columns["customerName"].ColumnName = "ग्राहकाचे नाव"; dtExcel.Columns["customerAddress"].ColumnName = "ग्राहकाचा पत्ता"; dtExcel.Columns["itemName"].ColumnName = "वस्तूचे नाव"; dtExcel.Columns["unitBy"].ColumnName = "पॅकिंग"; dtExcel.Columns["quantity"].ColumnName = "नग"; dtExcel.Columns["perQtysalePrice"].ColumnName = "विक्री रक्कम"; dtExcel.Columns["TotalBillAmt"].ColumnName = "एकूण सेल रक्कम"; } dtExcel.TableName = "One Day Sale Report"; xlWorksheet.Name = dtExcel.TableName; for (int j = 1; j < dtExcel.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = dtExcel.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < dtExcel.Rows.Count; k++) { for (int l = 0; l < dtExcel.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = dtExcel.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnExport_Click(object sender, EventArgs e) { try { System.Data.DataTable ds = new System.Data.DataTable(); Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); if (chkSummery.Checked == false) { foreach (DataGridViewColumn col in dgvStockRegister.Columns) { ds.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvStockRegister.Rows) { DataRow dRow = ds.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } ds.Rows.Add(dRow); } if (cmbMainCategory.Text == "खते") { ds.Columns[3].ColumnName = "कोणाकडून आले व कंपनी"; ds.Columns[5].ColumnName = "दिवसातील आवक"; ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक"); ds.Columns.Remove("बियाणाचे नाव"); ds.Columns.Remove("बियाणाचा प्रकार"); ds.TableName = "खते रजिस्टर"; } if (cmbMainCategory.Text == "किटकनाशके") { ds.Columns[3].ColumnName = "किटकनाशक कोणाकडून खरेदी केले त्याचे नाव व पावती क्र. व दिनांक"; ds.Columns[5].ColumnName = "खरेदी केलेला साठा"; ds.Columns.Remove("बियाणाचे नाव"); ds.Columns.Remove("बियाणाचा प्रकार"); ds.TableName = "किटकनाशके रजिस्टर"; } if (cmbMainCategory.Text == "बियाणे") { ds.Columns[3].ColumnName = "कोणाकडून आले"; ds.Columns[5].ColumnName = "किती आले"; ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक"); ds.TableName = "बियाणे रजिस्टर"; } } else { foreach (DataGridViewColumn col in dgvSummerysales.Columns) { ds.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvSummerysales.Rows) { DataRow dRow = ds.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } ds.Rows.Add(dRow); } ds.TableName = "विक्री रजिस्टर"; } xlWorksheet.Name = ds.TableName; for (int j = 1; j < ds.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < ds.Rows.Count; k++) { for (int l = 0; l < ds.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = ds.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ac) { MessageBox.Show(ac.Message); } }
public string GerarRelatorio(string mes, string ano, List <System.Data.DataTable> data, List <string[]> colunas, String fileName) { string ret = "Relatório gerado com sucesso!"; try { Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet); Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel.Worksheet ws = null; if (data[3].Rows.Count > 0) { #region Ficha6B sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha6B"; for (int i = 1; i <= colunas[3].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[3][i - 1]; if (i == 13) { ws.Cells[5, i] = "(" + i + ") = (11) * (12)"; } else if (i == 16) { ws.Cells[5, i] = "(" + i + ") = (13) + (15)"; } else if (i == 17) { ws.Cells[5, i] = "(" + i + ") = (16) - (9)"; } else { ws.Cells[5, i] = "(" + i + ")"; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 13]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 17]).EntireColumn.NumberFormat = "#,##0.00"; for (int i = 0; i < data[3].Rows.Count; i++) { string data_formatada = data[3].Rows[i]["DT_DOC"].ToString(); ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4); ws.Cells[i + 6, 2] = data[3].Rows[i]["TIPO"]; ws.Cells[i + 6, 3] = data[3].Rows[i]["SER"]; ws.Cells[i + 6, 4] = data[3].Rows[i]["NUM_DOC"]; ws.Cells[i + 6, 5] = data[3].Rows[i]["COD_PART"]; ws.Cells[i + 6, 6] = data[3].Rows[i]["COD_ENQUADRAMENTO"]; ws.Cells[i + 6, 7] = data[3].Rows[i]["VL_DOC"]; ws.Cells[i + 6, 8] = data[3].Rows[i]["VL_BC_ICMS"]; ws.Cells[i + 6, 9] = data[3].Rows[i]["VL_ICMS"]; ws.Cells[i + 6, 10] = data[3].Rows[i]["IVA"]; ws.Cells[i + 6, 11] = data[3].Rows[i]["CUSTO_EST"]; ws.Cells[i + 6, 12] = data[3].Rows[i]["PMC"]; ws.Cells[i + 6, 13] = data[3].Rows[i]["CRED_EST_IMP"]; ws.Cells[i + 6, 14] = data[3].Rows[i]["PCT_CRED_OUT"]; ws.Cells[i + 6, 15] = data[3].Rows[i]["VL_CRED_OUT"]; ws.Cells[i + 6, 16] = data[3].Rows[i]["VL_TOTAL_ICMS"]; ws.Cells[i + 6, 17] = data[3].Rows[i]["VL_CRED_ACUM_GER"]; } for (int i = 1; i <= colunas[3].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso II - Operações com Redução de Base de Cálculo"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } if (data[2].Rows.Count > 0) { #region Ficha6A sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = "Ficha6A"; for (int i = 1; i <= colunas[2].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@"; ws.Cells[4, i] = colunas[2][i - 1]; if (i == 13) { ws.Cells[5, i] = "(" + i + ") = (11) * (12)"; } else if (i == 16) { ws.Cells[5, i] = "(" + i + ") = (13) + (15)"; } else if (i == 17) { ws.Cells[5, i] = "(" + i + ") = (16) - (9)"; } else { ws.Cells[5, i] = "(" + i + ")"; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 13]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 17]).EntireColumn.NumberFormat = "#,##0.00"; for (int i = 0; i < data[2].Rows.Count; i++) { string data_formatada = data[2].Rows[i]["DT_DOC"].ToString(); ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4); ws.Cells[i + 6, 2] = data[2].Rows[i]["TIPO"]; ws.Cells[i + 6, 3] = data[2].Rows[i]["SER"]; ws.Cells[i + 6, 4] = data[2].Rows[i]["NUM_DOC"]; ws.Cells[i + 6, 5] = data[2].Rows[i]["COD_PART"]; ws.Cells[i + 6, 6] = data[2].Rows[i]["COD_ENQUADRAMENTO"]; ws.Cells[i + 6, 7] = data[2].Rows[i]["VL_DOC"]; ws.Cells[i + 6, 8] = data[2].Rows[i]["VL_BC_ICMS"]; ws.Cells[i + 6, 9] = data[2].Rows[i]["VL_ICMS"]; ws.Cells[i + 6, 10] = data[2].Rows[i]["IVA"]; ws.Cells[i + 6, 11] = data[2].Rows[i]["CUSTO_EST"]; ws.Cells[i + 6, 12] = data[2].Rows[i]["PMC"]; ws.Cells[i + 6, 13] = data[2].Rows[i]["CRED_EST_IMP"]; ws.Cells[i + 6, 14] = data[2].Rows[i]["PCT_CRED_OUT"]; ws.Cells[i + 6, 15] = data[2].Rows[i]["VL_CRED_OUT"]; ws.Cells[i + 6, 16] = data[2].Rows[i]["VL_TOTAL_ICMS"]; ws.Cells[i + 6, 17] = data[2].Rows[i]["VL_CRED_ACUM_GER"]; } for (int i = 1; i <= colunas[2].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit(); } ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso I - Operações com Aplicação de Alíquotas Diversificadas"; ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true; #endregion } wb.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wb.Close(); xla.Quit(); } catch (Exception ex) { ret = "Erro ao gerar relatório"; } return(ret); }
private void btneExcelReport_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); System.Data.DataSet ds = new DataSet(); ds = gstR1Controller.getGSTR1Report(Convert.ToDateTime(dtpFrom.Value.ToShortDateString()), Convert.ToDateTime(dtpTo.Value.ToShortDateString()), Utility.FinancilaYearId, "GSTR1"); DataTableCollection collection = ds.Tables; ds.Tables[0].TableName = "b2b"; ds.Tables[0].Columns["GSTIN_UINofRecipient"].ColumnName = "GSTIN/UIN of Recipient"; ds.Tables[0].Columns["InvoiceNumber"].ColumnName = "Invoice Number"; ds.Tables[0].Columns["Invoicedate"].ColumnName = "Invoice date"; ds.Tables[0].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[0].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[0].Columns["ReverseCharge"].ColumnName = "Reverse Charge"; ds.Tables[0].Columns["InvoiceType"].ColumnName = "Invoice Type"; ds.Tables[0].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN"; ds.Tables[0].Columns["Rate"].ColumnName = "Rate"; ds.Tables[0].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[0].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[1].TableName = "b2cl"; ds.Tables[1].Columns["InvoiceNumber"].ColumnName = "Invoice Number"; ds.Tables[1].Columns["Invoicedate"].ColumnName = "Invoice date"; ds.Tables[1].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[1].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[1].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN"; ds.Tables[1].Columns["Rate"].ColumnName = "Rate"; ds.Tables[1].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[1].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[2].TableName = "b2cs"; ds.Tables[2].Columns["Type"].ColumnName = "Type"; ds.Tables[2].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[2].Columns["Rate"].ColumnName = "Rate"; ds.Tables[2].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[2].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[2].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN"; ds.Tables[3].TableName = "cndr"; ds.Tables[3].Columns["GSTIN_UINofRecipient"].ColumnName = "GSTIN/UIN of Recipient"; ds.Tables[3].Columns["Invoice_AdvanceReceiptNumber"].ColumnName = "Invoice/Advance Receipt Number"; ds.Tables[3].Columns["Invoice_AdvanceReceiptdate"].ColumnName = "Invoice/Advance Receipt date"; ds.Tables[3].Columns["Note_RefundVoucherNumber"].ColumnName = "Note/Refund Voucher Number"; ds.Tables[3].Columns["Note_RefundVoucherdate"].ColumnName = "Note/Refund Voucher date"; ds.Tables[3].Columns["DocumentType"].ColumnName = "Document Type"; ds.Tables[3].Columns["ReasonForIssuingdocument"].ColumnName = "Reason For Issuing document"; ds.Tables[3].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[3].Columns["Note_RefundVoucherValue"].ColumnName = "Note/Refund Voucher Value"; ds.Tables[3].Columns["Rate"].ColumnName = "Rate"; ds.Tables[3].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[3].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[3].Columns["PreGST"].ColumnName = "Pre GST"; ds.Tables[4].TableName = "cndur"; ds.Tables[4].Columns["URType"].ColumnName = "UR Type"; ds.Tables[4].Columns["Note_RefundVoucherNumber"].ColumnName = "Note/Refund Voucher Number"; ds.Tables[4].Columns["Note_RefundVoucherdate"].ColumnName = "Note/Refund Voucher date"; ds.Tables[4].Columns["DocumentType"].ColumnName = "Document Type"; ds.Tables[4].Columns["Invoice_AdvanceReceiptNumber"].ColumnName = "Invoice/Advance Receipt Number"; ds.Tables[4].Columns["Invoice_AdvanceReceiptdate"].ColumnName = "Invoice/Advance Receipt date"; ds.Tables[4].Columns["ReasonForIssuingdocument"].ColumnName = "Reason For Issuing document"; ds.Tables[4].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[4].Columns["Note_RefundVoucherValue"].ColumnName = "Note/Refund Voucher Value"; ds.Tables[4].Columns["Rate"].ColumnName = "Rate"; ds.Tables[4].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[4].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[4].Columns["PreGST"].ColumnName = "Pre GST"; ds.Tables[5].TableName = "exp"; ds.Tables[5].Columns["ExportType"].ColumnName = "Export Type"; ds.Tables[5].Columns["InvoiceNumber"].ColumnName = "Invoice Number"; ds.Tables[5].Columns["Invoicedate"].ColumnName = "Invoice date"; ds.Tables[5].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[5].Columns["PortCode"].ColumnName = "Port Code"; ds.Tables[5].Columns["ShippingBillNumber"].ColumnName = "Shipping Bill Number"; ds.Tables[5].Columns["ShippingBillDate"].ColumnName = "Shipping Bill Date"; ds.Tables[5].Columns["Rate"].ColumnName = "Rate"; ds.Tables[5].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[6].TableName = "at"; ds.Tables[6].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[6].Columns["Rate"].ColumnName = "Rate"; ds.Tables[6].Columns["GrossAdvanceReceived"].ColumnName = "Gross Advance Received"; ds.Tables[6].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[7].TableName = "atadj"; ds.Tables[7].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[7].Columns["Rate"].ColumnName = "Rate"; ds.Tables[7].Columns["GrossAdvanceAdjusted"].ColumnName = "Gross Advance Adjusted"; ds.Tables[7].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[8].TableName = "exemp"; ds.Tables[8].Columns["Description"].ColumnName = "Description"; ds.Tables[8].Columns["NilRatedSupplies"].ColumnName = "Nil Rated Supplies"; ds.Tables[8].Columns["Exempted"].ColumnName = "Exempted (other than nil rated/non GST supply )"; ds.Tables[8].Columns["NonGSTsupplies"].ColumnName = "Non-GST supplies"; ds.Tables[9].TableName = "hsn"; ds.Tables[9].Columns["HSNCode"].ColumnName = "HSN"; ds.Tables[9].Columns["Description"].ColumnName = "Description"; ds.Tables[9].Columns["UQC"].ColumnName = "UQC"; ds.Tables[9].Columns["TotalQuantity"].ColumnName = "Total Quantity"; ds.Tables[9].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[9].Columns["IntegratedTaxAmount"].ColumnName = "Integrated Tax Amount"; ds.Tables[9].Columns["CentralTaxAmount"].ColumnName = "Central Tax Amount"; ds.Tables[9].Columns["State_UTTaxAmount"].ColumnName = "State/UT Tax Amount"; ds.Tables[9].Columns["CessAmount"].ColumnName = "Cess Amount"; for (int i = collection.Count; i > 0; i--) { Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataTable table = collection[i - 1]; xlWorksheet.Name = table.TableName; for (int j = 1; j < table.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < table.Rows.Count; k++) { for (int l = 0; l < table.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = table.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); } ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public string GerarLista(string[] anos, String fileName, string conexao) { string ret = "Lista gerada com sucesso!"; try { Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet); Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel.Worksheet ws = null; #region Lista List <DataTable> data = new List <DataTable>(); List <DataTable> dataTotal = new List <DataTable>(); List <string[]> colunas = new List <string[]>(); string[] nomesColunas = { "Data", "Número", "Série", "CFOP", "Nome", "CNPJ", "IE", "Valor Documento", "Base de Cálculo", "Alíquota", "Valor ICMS", "UF", "Hipótese de Geração" }; colunas.Add(nomesColunas); bool temLista = false; for (int k = 0; k < anos.Length; k++) { temLista = false; for (int i = 0; i < 12; i++) { string mes = ""; if (i < 9) { mes = "0" + (i + 1).ToString(); } else { mes = (i + 1).ToString(); } data.Add(Lista(mes, anos[k], conexao)); dataTotal.Add(ListaTotal(mes, anos[k], conexao)); if (data[i].Rows.Count > 0) { temLista = true; } } if (temLista) { sheets = wb.Sheets; ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); ws.Name = anos[k]; for (int i = 1; i <= colunas[0].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).EntireColumn.NumberFormat = "@"; ws.Cells[1, i] = colunas[0][i - 1]; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).BorderAround2(); ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Font.Color = System.Drawing.Color.DarkOrange; } ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 8]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 9]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 10]).EntireColumn.NumberFormat = "#,##0.00"; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 11]).EntireColumn.NumberFormat = "#,##0.00"; int count = 0; for (int j = 0; j < data.Count; j++) { if (data[j].Rows.Count > 0) { count += 2; for (int i = 0; i < data[j].Rows.Count; i++) { string data_formatada = data[j].Rows[i]["DT_DOC"].ToString(); ws.Cells[count, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4); ws.Cells[count, 2] = data[j].Rows[i]["NUM_DOC"]; ws.Cells[count, 3] = data[j].Rows[i]["SER"]; ws.Cells[count, 4] = data[j].Rows[i]["CFOP"]; ws.Cells[count, 5] = data[j].Rows[i]["NOME"]; ws.Cells[count, 6] = (data[j].Rows[i]["CNPJ"] == DBNull.Value) ? data[j].Rows[i]["CPF"] : data[j].Rows[i]["CNPJ"]; ws.Cells[count, 7] = data[j].Rows[i]["IE"]; ws.Cells[count, 8] = data[j].Rows[i]["VL_DOC"]; ws.Cells[count, 9] = data[j].Rows[i]["VL_BC_ICMS"]; ws.Cells[count, 10] = data[j].Rows[i]["ALIQ_ICMS"]; ws.Cells[count, 11] = data[j].Rows[i]["VL_ICMS"]; ws.Cells[count, 12] = data[j].Rows[i]["UF"]; string hip_ger = data[j].Rows[i]["COD_HIP_GER"].ToString(); switch (hip_ger) { case "1": hip_ger = "Operações interestaduais com alíquota 7%"; break; case "2": hip_ger = "Operações interestaduais com alíquota 12%"; break; case "5": hip_ger = "Outras"; break; case "6": hip_ger = "Redução de Base de Cálculo"; break; case "7": hip_ger = "Saídas sem pagamento de Imposto – Exportação"; break; case "9": hip_ger = "Saídas sem pagamento de Imposto – ZF Manaus"; break; case "10": hip_ger = "Saídas sem pagamento de Imposto – Diferimento"; break; case "11": hip_ger = "Saídas sem pagamento de Imposto – Isenção"; break; default: hip_ger = "Sem Hipótese de Geração"; break; } ws.Cells[count, 13] = hip_ger; count++; } ws.Cells[count, 8] = dataTotal[j].Rows[0]["VL_DOC"]; ws.Cells[count, 11] = dataTotal[j].Rows[0]["VL_ICMS"]; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[count, 8]).Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)ws.Cells[count, 11]).Font.Bold = true; } } for (int i = 1; i <= colunas[0].Length; i++) { ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).EntireColumn.AutoFit(); } } data.Clear(); dataTotal.Clear(); } #endregion wb.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wb.Close(); xla.Quit(); } catch (Exception ex) { ret = "Erro ao gerar lista"; } return(ret); }
private void btneExcelReport_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); System.Data.DataSet ds = new DataSet(); ds = gstR2Controller.getGSTR2Report(Convert.ToDateTime(dtpFrom.Value.ToShortDateString()), Convert.ToDateTime(dtpTo.Value.ToShortDateString()), Utility.FinancilaYearId, "GSTR2"); DataTableCollection collection = ds.Tables; ds.Tables[0].TableName = "b2b"; ds.Tables[0].Columns["GSTINofSupplier"].ColumnName = "GSTIN of Supplier"; ds.Tables[0].Columns["InvoiceNumber"].ColumnName = "Invoice Number"; ds.Tables[0].Columns["Invoicedate"].ColumnName = "Invoice date"; ds.Tables[0].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[0].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[0].Columns["ReverseCharge"].ColumnName = "Reverse Charge"; ds.Tables[0].Columns["InvoiceType"].ColumnName = "Invoice Type"; ds.Tables[0].Columns["Rate"].ColumnName = "Rate"; ds.Tables[0].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[0].Columns["IntegratedTaxPaid"].ColumnName = "Integrated Tax Paid"; ds.Tables[0].Columns["CentralTaxPaid"].ColumnName = "Central Tax Paid"; ds.Tables[0].Columns["StateUTTaxPaid"].ColumnName = "State/UT Tax Paid"; ds.Tables[0].Columns["CessPaid"].ColumnName = "Cess Paid"; ds.Tables[0].Columns["EligibilityForITC"].ColumnName = "Eligibility For ITC"; ds.Tables[0].Columns["AvailedITCIntegratedTax"].ColumnName = "Availed ITC Integrated Tax"; ds.Tables[0].Columns["AvailedITCCentralTax"].ColumnName = "Availed ITC Central Tax"; ds.Tables[0].Columns["AvailedITCStateUTTax"].ColumnName = "Availed ITC State/UT Tax"; ds.Tables[0].Columns["AvailedITCCess"].ColumnName = "Availed ITC Cess"; ds.Tables[1].TableName = "b2bur"; ds.Tables[1].Columns["SupplierName"].ColumnName = "Supplier Name"; ds.Tables[1].Columns["InvoiceNumber"].ColumnName = "Invoice Number"; ds.Tables[1].Columns["Invoicedate"].ColumnName = "Invoice date"; ds.Tables[1].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[1].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[1].Columns["SupplyType"].ColumnName = "Supply Type"; ds.Tables[1].Columns["Rate"].ColumnName = "Rate"; ds.Tables[1].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[1].Columns["IntegratedTaxPaid"].ColumnName = "Integrated Tax Paid"; ds.Tables[1].Columns["CentralTaxPaid"].ColumnName = "Central Tax Paid"; ds.Tables[1].Columns["StateUTTaxPaid"].ColumnName = "State/UT Tax Paid"; ds.Tables[1].Columns["CessPaid"].ColumnName = "Cess Paid"; ds.Tables[1].Columns["EligibilityForITC"].ColumnName = "Eligibility For ITC"; ds.Tables[1].Columns["AvailedITCIntegratedTax"].ColumnName = "Availed ITC Integrated Tax"; ds.Tables[1].Columns["AvailedITCCentralTax"].ColumnName = "Availed ITC Central Tax"; ds.Tables[1].Columns["AvailedITCStateUTTax"].ColumnName = "Availed ITC State/UT Tax"; ds.Tables[1].Columns["AvailedITCCess"].ColumnName = "Availed ITC Cess"; ds.Tables[2].TableName = "imps"; ds.Tables[2].Columns["InvoiceNumberofRegRecipient"].ColumnName = "Invoice Number of Reg Recipient"; ds.Tables[2].Columns["InvoiceDate"].ColumnName = "Invoice Date"; ds.Tables[2].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[2].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[2].Columns["Rate"].ColumnName = "Rate"; ds.Tables[2].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[2].Columns["IntegratedTaxPaid"].ColumnName = "Integrated Tax Paid"; ds.Tables[2].Columns["CessPaid"].ColumnName = "Cess Paid"; ds.Tables[2].Columns["EligibilityForITC"].ColumnName = "Eligibility For ITC"; ds.Tables[2].Columns["AvailedITCIntegratedTax"].ColumnName = "Availed ITC Integrated Tax"; ds.Tables[2].Columns["AvailedITCCess"].ColumnName = "Availed ITC Cess"; ds.Tables[3].TableName = "impg"; ds.Tables[3].Columns["PortCode"].ColumnName = "Port Code"; ds.Tables[3].Columns["BillOfEntryNumber"].ColumnName = "Bill Of Entry Number"; ds.Tables[3].Columns["BillOfEntryDate"].ColumnName = "Bill Of Entry Date"; ds.Tables[3].Columns["BillOfEntryValue"].ColumnName = "Bill Of Entry Value"; ds.Tables[3].Columns["Documenttype"].ColumnName = "Document type"; ds.Tables[3].Columns["Rate"].ColumnName = "Rate"; ds.Tables[3].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[3].Columns["IntegratedTaxPaid"].ColumnName = "Integrated Tax Paid"; ds.Tables[3].Columns["CessPaid"].ColumnName = "Cess Paid"; ds.Tables[3].Columns["EligibilityForITC"].ColumnName = "Eligibility For ITC"; ds.Tables[3].Columns["AvailedITCIntegratedTax"].ColumnName = "Availed ITC Integrated Tax"; ds.Tables[3].Columns["AvailedITCCess"].ColumnName = "Availed ITC Cess"; ds.Tables[4].TableName = "cdnr"; ds.Tables[4].Columns["GSTINofSupplier"].ColumnName = "GSTIN of Supplier"; ds.Tables[4].Columns["NoteRefundVoucherNumber"].ColumnName = "Note/Refund Voucher Number"; ds.Tables[4].Columns["NoteRefundVoucherdate"].ColumnName = "Note/Refund Voucher date"; ds.Tables[4].Columns["InvoiceAdvancePaymentVoucherNumber"].ColumnName = "Invoice/Advance Payment Voucher Number"; ds.Tables[4].Columns["InvoiceAdvancePaymentVoucherdate"].ColumnName = "Invoice/Advance Payment Voucher date"; ds.Tables[4].Columns["PreGST"].ColumnName = "Pre GST"; ds.Tables[4].Columns["DocumentType"].ColumnName = "Document Type"; ds.Tables[4].Columns["ReasonForIssuingdocument"].ColumnName = "Reason For Issuing document"; ds.Tables[4].Columns["SupplyType"].ColumnName = "Supply Type"; ds.Tables[4].Columns["NoteRefundVoucherValue"].ColumnName = "Note/Refund Voucher Value"; ds.Tables[4].Columns["Rate"].ColumnName = "Rate"; ds.Tables[4].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[4].Columns["IntegratedTaxPaid"].ColumnName = "Integrated Tax Paid"; ds.Tables[4].Columns["CentralTaxPaid"].ColumnName = "Central Tax Paid"; ds.Tables[4].Columns["StateUTTaxPaid"].ColumnName = "State/UT Tax Paid"; ds.Tables[4].Columns["CessPaid"].ColumnName = "Cess Paid"; ds.Tables[4].Columns["EligibilityForITC"].ColumnName = "Eligibility For ITC"; ds.Tables[4].Columns["AvailedITCIntegratedTax"].ColumnName = "Availed ITC Integrated Tax"; ds.Tables[4].Columns["AvailedITCCentralTax"].ColumnName = "Availed ITC Central Tax"; ds.Tables[4].Columns["AvailedITCStateUTTax"].ColumnName = "Availed ITC State/UT Tax"; ds.Tables[4].Columns["AvailedITCCess"].ColumnName = "Availed ITC Cess"; ds.Tables[5].TableName = "cndur"; ds.Tables[5].Columns["NoteRefundVoucherNumber"].ColumnName = "Note/Voucher Number"; ds.Tables[5].Columns["NoteRefundVoucherdate"].ColumnName = "Note/Voucher date"; ds.Tables[5].Columns["InvoiceAdvancePaymentVoucherNumber"].ColumnName = "Invoice/Advance Payment Voucher number"; ds.Tables[5].Columns["InvoiceAdvancePaymentVoucherdate"].ColumnName = "Invoice/Advance Payment Voucher date"; ds.Tables[5].Columns["PreGST"].ColumnName = "Pre GST"; ds.Tables[5].Columns["DocumentType"].ColumnName = "Document Type"; ds.Tables[5].Columns["ReasonForIssuingdocument"].ColumnName = "Reason For Issuing document"; ds.Tables[5].Columns["SupplyType"].ColumnName = "Supply Type"; ds.Tables[5].Columns["InvoiceType"].ColumnName = "Invoice Type"; ds.Tables[5].Columns["NoteRefundVoucherValue"].ColumnName = "Note/Voucher Value"; ds.Tables[5].Columns["Rate"].ColumnName = "Rate"; ds.Tables[5].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[5].Columns["IntegratedTaxPaid"].ColumnName = "Integrated Tax Paid"; ds.Tables[5].Columns["CentralTaxPaid"].ColumnName = "Central Tax Paid"; ds.Tables[5].Columns["StateUTTaxPaid"].ColumnName = "State/UT Tax Paid"; ds.Tables[5].Columns["CessPaid"].ColumnName = "Cess Paid"; ds.Tables[5].Columns["EligibilityForITC"].ColumnName = "Eligibility For ITC"; ds.Tables[5].Columns["AvailedITCIntegratedTax"].ColumnName = "Availed ITC Integrated Tax"; ds.Tables[5].Columns["AvailedITCCentralTax"].ColumnName = "Availed ITC Central Tax"; ds.Tables[5].Columns["AvailedITCStateUTTax"].ColumnName = "Availed ITC State/UT Tax"; ds.Tables[5].Columns["AvailedITCCess"].ColumnName = "Availed ITC Cess"; ds.Tables[6].TableName = "at"; ds.Tables[6].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[6].Columns["SupplyType"].ColumnName = "Supply Type"; ds.Tables[6].Columns["GrossAdvancePaid"].ColumnName = "Gross Advance Paid"; ds.Tables[6].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[7].TableName = "atadj"; ds.Tables[7].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[7].Columns["SupplyType"].ColumnName = "Supply Type"; ds.Tables[7].Columns["GrossAdvancePaidtobeAdjusted"].ColumnName = "Gross Advance Paid to be Adjusted"; ds.Tables[7].Columns["CessAdjusted"].ColumnName = "Cess Adjusted"; ds.Tables[8].TableName = "exemp"; ds.Tables[8].Columns["Description"].ColumnName = "Description"; ds.Tables[8].Columns["Compositiontaxableperson"].ColumnName = "Composition taxable person"; ds.Tables[8].Columns["NilRatedSupplies"].ColumnName = "Nil Rated Supplies"; ds.Tables[8].Columns["Exempted"].ColumnName = "Exempted (other than nil rated/non GST supply)"; ds.Tables[8].Columns["NonGSTsupplies"].ColumnName = "Non-GST supplies"; ds.Tables[9].TableName = "itcr"; ds.Tables[9].Columns["DescriptionforreversalofITC"].ColumnName = "Description for reversal of ITC"; ds.Tables[9].Columns["Tobeaddedorreducedfromoutputliability"].ColumnName = "To be added or reduced from output liability"; ds.Tables[9].Columns["ITCIntegratedTaxAmount"].ColumnName = "ITC Integrated Tax Amount"; ds.Tables[9].Columns["ITCCentralTaxAmount"].ColumnName = "ITC Central Tax Amount"; ds.Tables[9].Columns["ITCStateUTTaxAmount"].ColumnName = "ITC State/UT Tax Amount"; ds.Tables[9].Columns["ITCCessAmount"].ColumnName = "ITC Cess Amount"; ds.Tables[10].TableName = "hsnsum"; ds.Tables[10].Columns["HSN"].ColumnName = "HSN"; ds.Tables[10].Columns["Description"].ColumnName = "Description"; ds.Tables[10].Columns["UQC"].ColumnName = "UQC"; ds.Tables[10].Columns["TotalQuantity"].ColumnName = "Total Quantity"; ds.Tables[10].Columns["TotalValue"].ColumnName = "Total Value"; ds.Tables[10].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[10].Columns["IntegratedTaxAmount"].ColumnName = "Integrated Tax Amount"; ds.Tables[10].Columns["CentralTaxAmount"].ColumnName = "Central Tax Amount"; ds.Tables[10].Columns["StateUTTaxAmount"].ColumnName = "State/UT Tax Amount"; ds.Tables[10].Columns["CessAmount"].ColumnName = "Cess Amount"; for (int i = collection.Count; i > 0; i--) { Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataTable table = collection[i - 1]; xlWorksheet.Name = table.TableName; for (int j = 1; j < table.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < table.Rows.Count; k++) { for (int l = 0; l < table.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = table.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); } ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }