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