/// <summary>
/// 释放资源
/// </summary>
public void Quit()
{
if (WorkBook != null)
{
WorkBook.Close(null, null, null);
}
if (ExcelApp != null)
{
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
}
if (Range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(Range);
Range = null;
}
if (WorkSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkSheet);
WorkSheet = null;
}
if (WorkBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook);
WorkBook = null;
}
if (ExcelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
GC.Collect();
}
/// <summary>
/// The ExtractSheet.
/// </summary>
/// <param name="file">The file<see cref="string"/>.</param>
/// <param name="destination">The destination<see cref="string"/>.</param>
/// <param name="sheet">The sheet<see cref="string"/>.</param>
public static void ExtractSheet(string file, string destination, string sheet)
{
WorkBook workbook = WorkBook.Load(file);
WorkBook new_wb = WorkBook.Create();
var ws = workbook.GetWorkSheet(sheet);
try
{
ws.CopyTo(new_wb, "data");
}
catch (ArgumentException ae)
{
Console.Error.WriteLine("FAILED:\t" + destination);
return;
}
new_wb.SaveAs(destination + ".xls");
new_wb.Close();
workbook.Close();
Console.WriteLine("Finished:\t" + destination);
}
public static ObservableCollection <string> OpenExcelFile(string filepath)
{
if (FileUtil.DoesFileExists(filepath) == false)
{
return(null);
}
WorkBook workbook = WorkBook.Load(filepath);
WorkSheet sheet = workbook.WorkSheets.First();
ObservableCollection <string> cellData = new ObservableCollection <string>();
int rowCount = 0;
Range useableRange = sheet["A1:A10"];
foreach (var cell in useableRange)
{
if (cell.IsEmpty)
{
break;
}
cellData.Add(cell.StringValue);
rowCount++;
}
workbook.Close();
return(cellData);
}
public void Dispose()
{
//GC.Collect();
//GC.WaitForPendingFinalizers();
if (WorkSheet != null)
{
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(WorkSheet);
WorkSheet = null;
}
if (WorkBook != null)
{
WorkBook.Close();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(WorkBook);
WorkBook = null;
}
if (App != null)
{
App.Visible = true;
App.DisplayAlerts = true;
App.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(App);
App = null;
}
IsInit = false;
SheetName = null;
FilePath = null;
}
private void GeraRelExcel(IEnumerable <Relexcel> listrel)
{
Microsoft.Office.Interop.Excel.Application App; // Aplicação Excel
Microsoft.Office.Interop.Excel.Workbook WorkBook; // Pasta
Microsoft.Office.Interop.Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
var i = 1;
foreach (var rel in listrel)
{
WorkSheet.Cells[i, 1] = string.Format("{0}/{1}/{2}", rel.Data.Day.ToString("00"), rel.Data.Month.ToString("00"), rel.Data.Year.ToString("00"));
WorkSheet.Cells[i, 2] = string.Format("{0}:{1}", ((int)rel.Hora.TotalHours).ToString("00"), rel.Hora.Minutes.ToString("00"));
WorkSheet.Cells[i, 3] = rel.Os;
WorkSheet.Cells[i, 4] = rel.Descricao;
i += 1;
}
// salva o arquivo
WorkBook.SaveAs(TBArquivo.Text + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
}
/// <summary>
/// Exporta um DataGridView para uma planilha no excel.
/// </summary>
/// <param name="grid">Um DataGridView</param>
public static void ParaExcel(DataGridView grid)
{
Excel.Application App; // Aplicação Excel
Excel.Workbook WorkBook; // Pasta
Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (WorkBook.Worksheets.get_Item(1) as Excel.Worksheet);
for (int i = 0; i < grid.RowCount; i += 1)
{
for (int j = 0; j < grid.ColumnCount; j += 1)
{
DataGridViewCell cell = grid[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
using (var salvar = new SaveFileDialog()) {
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
if (salvar.ShowDialog() == DialogResult.OK)
{
WorkBook.SaveAs(salvar.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
}
WorkBook.Close(true, misValue, misValue);
App.Quit();
}
private void btnExportar_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(caminho) == false)
{
SaveFileDialog salvar = new SaveFileDialog();
// Aplicação Excel
Excel.Application App;
Excel.Workbook WorkBook;
Excel.Worksheet WorkSheet;
object misValor = System.Reflection.Missing.Value;
App = new Excel.Application();
WorkBook = App.Workbooks.Add(misValor);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
// Passa as celulas do DataGridView para a Pasta do Excel
for (i = 0; i <= dataGridView3.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView3.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView3[j, i];
WorkSheet.Cells[1, 1] = "Nr da Nota Fiscal";
WorkSheet.Cells[1, 2] = "Vl. da Nota F.";
WorkSheet.Cells[1, 3] = "Valor ICMS Calculado";
WorkSheet.Cells[1, 4] = "Valor Analisado";
WorkSheet.Cells[1, 5] = "Valor Recolher";
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
//Gera um nome para novo arquivo
Random numAleatorio = new Random();
int valorInteiro = numAleatorio.Next();
DateTime dataHoje = DateTime.Today;
string data = dataHoje.ToString("D");
string nomeArquivo = "Extrato-" + data + " " + valorInteiro + ".xls";
string[] pasta = { value, nomeArquivo };
string caminhoCompleto = Path.Combine(pasta);
// define algumas propriedades da caixa salvar
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog(); // mostra
//salva na pasta virtual escolhida
WorkBook.SaveAs(salvar.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValor, misValor, misValor, misValor,
Excel.XlSaveAsAccessMode.xlExclusive, misValor, misValor, misValor, misValor, misValor);
WorkBook.Close(true, misValor, misValor);
App.Quit();
}
else
{
MessageBox.Show("Selecione primeiro um arquivo Nota Fiscal *.xml");
}
}
//------------------------------------------------------------------------------------------------------
#endregion
#region /*--------- Close ---------*\
//------------------------------------------------------------------------------------------------------
/// <summary>
///
/// </summary>
/// <returns></returns>
//--------------------------------------------------------------------
public void Close()
{
WorkBook.Close(true, MisValue, MisValue);
Appilcation.Quit();
ReleaseObject(WorkSheet);
ReleaseObject(WorkBook);
ReleaseObject(Appilcation);
}
protected override void DoAction()
{
WorkBook.SaveAs(Filename);
if (KeepOpen)
{
return;
}
WorkBook.Close();
// I get an exception in PythonScope trying to set WorkBook to null, because the COM object already disconnected when we closed it
//WorkBook = null;
}
public void geraDocExcelgrid(DataGridView grid)
{
try
{
SaveFileDialog salvar = new SaveFileDialog();
Excel.Application App; // Aplicação Excel
Excel.Workbook WorkBook; // Pasta
Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (int c = 1; c < grid.Columns.Count + 1; c++)
{
WorkSheet.Cells[1, c] = grid.Columns[c - 1].HeaderText;
}
// passa as celulas do DataGridView para a Pasta do Excel
for (i = 0; i <= grid.RowCount - 1; i++)
{
for (j = 0; j <= grid.ColumnCount - 1; j++)
{
DataGridViewCell cell = grid[j, i];
WorkSheet.Cells[i + 2, j + 1] = cell.Value.ToString();
}
}
// define algumas propriedades da caixa salvar
salvar.Title = "Meu Titulo";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog(); // mostra
// salva o arquivo
WorkBook.SaveAs(salvar.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
MessageBox.Show("Exportado com sucesso!");
}
catch { }
}
//metodos
async Task ExportarExcel(ProgressBar progress, DataGridView dgv)
{
try
{
SaveFileDialog salvar = new SaveFileDialog();
Microsoft.Office.Interop.Excel.Application app;
Microsoft.Office.Interop.Excel.Workbook WorkBook;
Microsoft.Office.Interop.Excel.Worksheet WorkSheet;
object misValue = System.Reflection.Missing.Value;
app = new Microsoft.Office.Interop.Excel.Application();
WorkBook = app.Workbooks.Add(misValue);
WorkSheet = (Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
progress.Visible = true;
progress.Maximum = dgv.RowCount;
for (i = 0; i <= dgvPdtCompra.RowCount - 1; i++)
{
progress.Increment(1);
for (j = 0; j <= dgvPdtCompra.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgvPdtCompra[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
salvar.Title = "Salvar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
if (salvar.ShowDialog() == DialogResult.OK)
{
WorkBook.SaveAs(salvar.FileName, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
app.Quit(); // encerra o excel
MessageBox.Show("Exportado com sucesso!");
}
progress.Value = 0;
progress.Visible = false;
}
catch (Exception er)
{
MessageBox.Show(er.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
SaveFileDialog salvar = new SaveFileDialog();// novo
Excel.Application App; // Aplicação Excel
Excel.Workbook WorkBook; // Pasta
Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
// passa as celulas do DataGridView para a Pasta do Excel
for (i = 0; i <= tabela_prof.RowCount - 1; i++)
{
for (j = 0; j <= tabela_prof.ColumnCount - 1; j++)
{
DataGridViewCell cell = tabela_prof[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
// define algumas propriedades da caixa salvar
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog(); // mostra
// salva o arquivo
WorkBook.SaveAs(salvar.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
MessageBox.Show("Exportado com sucesso!");
}
catch (Exception erro)
{
//throw erro;
MessageBox.Show("Arquivo nao foi salvo");
}
}
public static bool CloseFile() //kod za zatvaranje fajla
{
try {
if (wb != null) //ako je neki wb ucitan, zatvori ga i postavi wb na null
{
wb.Close();
wb = null;
}
return(true);
}catch (Exception e) //hvatanje greske prilikom zatvaranje
{
Console.WriteLine(e.ToString());
Console.WriteLine("Doslo je do greske prilikom zatvaranja!");
return(false); //vratiti false ako dodje do greske
}
}
public static bool CloseFile()
{
try {
if (wb != null)
{
wb.Close();
wb = null;
}
return(true);
}catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Doslo je do greske prilikom zatvaranja!");
return(false);
}
}
private void btnExportar_Click(object sender, EventArgs e)
{
SaveFileDialog salvar = new SaveFileDialog();
Microsoft.Office.Interop.Excel.Application App;
Microsoft.Office.Interop.Excel.Workbook WorkBook;
Microsoft.Office.Interop.Excel.Worksheet WorkSheet;
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dgvProdutos.RowCount - 1; i++)
{
for (j = 0; j <= dgvProdutos.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgvProdutos[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
try
{
salvar.Title = "Exportar para Microsoft Office Excel";
salvar.FileName = "Chronos_Clientes_" + DateTime.Now.ToString("dd_MM_yyyy");
salvar.Filter = "Arquivo do Excel *.xls|*.xls";
salvar.ShowDialog();
WorkBook.SaveAs(salvar.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit();
MessageBox.Show("Exportado com sucesso!", "chronOS");
}
catch (Exception erro)
{
MessageBox.Show("Não foi possivel realizar a exportação." + Environment.NewLine + erro.Message.ToString(), "chronOS");
}
}
void ExportarExcel()
{
var salvar = new SaveFileDialog();
Excel_.Application App; // Aplicação Excel
Excel_.Workbook WorkBook; // Pasta
Excel_.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Excel_.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel_.Worksheet)WorkBook.Worksheets.get_Item(1);
var i = 0;
var j = 0;
// passa as celulas do DataGridView para a Pasta do Excel
for (i = 0; i <= Grid.RowCount - 1; i++)
{
for (j = 0; j <= Grid.ColumnCount - 1; j++)
{
var cell = Grid[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
// define algumas propriedades da caixa salvar
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog(); // mostra
// salva o arquivo
WorkBook.SaveAs(salvar.FileName, Excel_.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Excel_.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
MessageBox.Show("Exportado com sucesso!");
}
private void btnExportar_Click(object sender, EventArgs e)
{
int i = 0;
DataTableReader dr;
SaveFileDialog salvar = new SaveFileDialog();
Microsoft.Office.Interop.Excel.Application App;
Microsoft.Office.Interop.Excel.Workbook WorkBook;
Microsoft.Office.Interop.Excel.Worksheet WorkSheet;
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
WorkSheet.Cells[1, 1] = "Relatório de Pesagem";
WorkSheet.Cells[2, 1] = "Emissão:";
WorkSheet.Cells[2, 2] = Clock.dateTime.ToString(Clock.getDate()) + " " + Clock.dateTime.ToString(Clock.getTime());
WorkSheet.Cells[4, 1] = "Unidade:";
WorkSheet.Cells[4, 2] = "Usuário:";
WorkSheet.Cells[4, 3] = "Emissor:";
WorkSheet.Cells[4, 4] = "Placa:";
WorkSheet.Cells[4, 5] = "Resíduo:";
WorkSheet.Cells[4, 6] = "Peso Bruto (Kg):";
WorkSheet.Cells[4, 7] = "Peso Tara (Kg):";
WorkSheet.Cells[4, 8] = "Peso Líquido (Kg):";
WorkSheet.Cells[4, 9] = "Entrada:";
WorkSheet.Cells[4, 10] = "Saída:";
dr = getPesagens();
i = 6;
while (dr.Read())
{
WorkSheet.Cells[i, 1] = dr["UNIDADE"].ToString();
WorkSheet.Cells[i, 2] = dr["USUARIO"].ToString();
WorkSheet.Cells[i, 3] = dr["EMISSOR"].ToString();
WorkSheet.Cells[i, 4] = dr["PLACA"].ToString();
WorkSheet.Cells[i, 5] = dr["RESIDUO"].ToString();
WorkSheet.Cells[i, 6] = dr["PES_PESO_BRUTO"].ToString();
WorkSheet.Cells[i, 7] = dr["PES_PESO_TARA"].ToString();
WorkSheet.Cells[i, 8] = dr["PES_PESO_LIQUIDO"].ToString();
WorkSheet.Cells[i, 9] = dr["DATA_ENTRADA"].ToString();
WorkSheet.Cells[i, 10] = dr["DATA_SAIDA"].ToString();
i++;
}
WorkSheet.Cells.EntireColumn.AutoFit();
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog();
if (salvar.FileName == "")
{
MessageBox.Show("Especifique o nome do arquivo para exportar.", "Exportar");
return;
}
WorkBook.SaveAs(salvar.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit();
MessageBox.Show("Relatório exportado com sucesso.", "Exportar");
}
private void btnGerarExcel_Click(object sender, EventArgs e)
{
if (txtArquivoExcel.Text == null)
{
MessageBox.Show("Insira o nome que deseja para o arquivo Excel!", "Preencha os campos", MessageBoxButtons.OK);
return;
}
if (dgv[0, 0].Value == null)
{
MessageBox.Show("Nenhum dado foi importado ainda.\n Importe via Serial ou via Arquivo Texto.", "Importe os dados", MessageBoxButtons.OK);
return;
}
if (dgv.Rows.Count <= 0)
{
MessageBox.Show("Nenhum dado foi importado ainda.\n Importe via Serial ou via Arquivo Texto.", "Importe os dados", MessageBoxButtons.OK);
return;
}
try
{
// CRIA O ARQUIVO
SaveFileDialog salvar = new SaveFileDialog(); // novo SaveFileDialog
Excel.Application App;
Excel.Workbook WorkBook;
Excel.Worksheet WorkSheet;
object misValue = System.Reflection.Missing.Value;
App = new Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
// COLOCA AS COISA NO EXCEL
for (int i = 0; i < dgv.Columns.Count - 1; i++)
{
WorkSheet.Cells[0, i] = dgv.Columns[i].HeaderText;
Console.Write(WorkSheet.Cells[i + 1, 0].Value);
}
for (int i = 0; i <= dgv.RowCount - 1; i++)
{
for (int j = 0; j <= dgv.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgv[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
//try
//{
// XcelApp.Application.Workbooks.Add(Type.Missing);
// for (int i = 1; i < dgv.Columns.Count + 1; i++)
// {
// XcelApp.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
// }
// for (int i = 0; i < dgv.Rows.Count - 1; i++)
// {
// for (int j = 0; j < dgv.Columns.Count; j++)
// {
// XcelApp.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
// }
// }
// XcelApp.Columns.AutoFit();
// XcelApp.Visible = true;
//}
//catch (Exception ex)
//{
// MessageBox.Show("Erro : " + ex.Message);
// XcelApp.Quit();
//}
// E SALVA
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog(); // mostra
WorkBook.SaveAs(salvar.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
MessageBox.Show("Exportado com sucesso!");
liberarObjetos(WorkSheet);
liberarObjetos(WorkBook);
liberarObjetos(App);
//MessageBox.Show("O arquivo Excel foi criado com sucesso. Você pode encontrá-lo na sua Área de Trabalho, com o nome: " + txtArquivoExcel.Text);
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex.Message);
}
// cria o nosso arquivo vazio e cria a pasta3 com os dados
// o excel ta indo pra ca C:\Users\giova\Documents
// tentar trocar o design da tabela
}
private void exportarTabela()
{
try
{
progress.Value = progress.Minimum;
btSorteio.Enabled = false;
grSorteio.Visible = false;
grProgress.Visible = true;
grProcesso.Text = "Atualizar Banco de Dados...";
wizardPage1.Refresh();
List <Dados> dadosModelo = new List <Dados>();
dadosModelo.Add(new Dados {
NOME = "NOME", EVENTO = "EVENTO", SORTEADO = "SORT"
});
int cont = 0;
while (cont < dgv.RowCount)
{
dadosModelo.Add(new Dados {
NOME = dgv.Rows[cont].Cells[0].Value.ToString(), EVENTO = dgv.Rows[cont].Cells[1].Value.ToString(), SORTEADO = dgv.Rows[cont].Cells[2].Value.ToString()
});
cont++;
}
BindingSource bs = new BindingSource();
bs.DataSource = dadosModelo;
dgv.DataSource = dadosModelo;
SaveFileDialog salvar = new SaveFileDialog();
Microsoft.Office.Interop.Excel.Application App;
Microsoft.Office.Interop.Excel.Workbook WorkBook;
Microsoft.Office.Interop.Excel.Worksheet WorkSheet;
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
progress.Maximum = dgv.RowCount + 1;
for (i = 0; i <= dgv.RowCount - 1; i++)
{
for (j = 0; j <= dgv.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgv[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
progress.Value = i;
}
WorkBook.SaveAs(txFile.Text, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit();
progress.Value = progress.Maximum;
MessageBox.Show(null, "O arquivo atualizado foi salvo em:" + '\n' + txFile.Text, "Arquivo Gerado", MessageBoxButtons.OK, MessageBoxIcon.Information);
grProgress.Visible = false;
grSorteio.Visible = true;
btSorteio.Enabled = true;
}
catch (Exception exception)
{
MessageBox.Show(null, exception.ToString(), "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void Close()
{
WorkBook.Close();
}
private void btnExpXls_Click(object sender, EventArgs e)
{
//Read more: http://www.linhadecodigo.com.br/artigo/3462/exportar-datagridview-para-arquivo-do-excel-com-csharp.aspx#ixzz6W4QV0PVL
if (gridPessoa.Rows.Count > 0)
{
Excel.Application App; // Aplicação Excel
Excel.Workbook WorkBook; // Pasta
Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
try
{
if (salvarExporta().Equals(true))
{
apoio.Aguarde("Aguarde! Criando arquivo...");
SaveFileDialog salvar = new SaveFileDialog();
int i = 0;
int j = 0;
//Exporta o Título
for (i = 1; i < gridPessoa.Columns.Count + 1; i++)
{
//DataGridViewCell cell = gridPessoa[j, i];
//WorkSheet.Cells[i + 1, j + 1] = cell.Value;
WorkSheet.Cells[1, i] = gridPessoa.Columns[i - 1].HeaderText;
lblExportado.Text = "Exportando o cabeçalho das colunas...";
lblExportado.Update();
}
// passa as celulas do DataGridView para a Pasta do Excel
for (i = 0; i <= gridPessoa.RowCount - 1; i++)
{
lblExportado.Text = "Exportando os dados do irmão(ã) " + gridPessoa["Nome", i].Value + "...";
lblExportado.Update();
for (j = 0; j <= gridPessoa.ColumnCount - 1; j++)
{
DataGridViewCell cell = gridPessoa[j, i];
WorkSheet.Cells[i + 2, j + 1] = cell.Value;
}
}
// define algumas propriedades da caixa salvar
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog(); // mostra
// salva o arquivo
WorkBook.SaveAs(salvar.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
App.Columns.AutoFit();
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
MessageBox.Show("Exportação executada com êxito!", "Atenção", MessageBoxButtons.OK, MessageBoxIcon.Information);
System.Diagnostics.Process.Start(salvar.FileName);
}
}
catch (SqlException exl)
{
excp = new clsException(exl);
}
catch (Exception ex)
{
excp = new clsException(ex);
}
finally
{
apoio.FecharAguarde();
}
}
}
private string TesteExcel(List <Planilha> planilha)
{
Microsoft.Office.Interop.Excel.Application App; // Aplicação Excel
Microsoft.Office.Interop.Excel.Workbook WorkBook; // Pasta
Microsoft.Office.Interop.Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
List <string> colunas = new List <string>()
{
"Endereço", "Latitude", "Longitude"
};
int linha = 1;
int coluna = 1;
//Response.ClearContent();
//Response.Buffer = true;
//Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "confidential feedback.xls"));
//Response.ContentType = "application/ms-excel";
//Response.Write("");
//Response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
//Response.Write("<body>");
//Response.Write("<table>");
//planilha.ForEach(a =>
//{
// Response.Write("<tr>");
// Response.Write($"<td>{a.Endereco}</td><td>{string.Concat("'",a.Latitude, "'")}</td><td>{a.Longitude.ToString()}</td>");
// Response.Write("<tr>");
//});
//Response.Write("</table>");
//Response.Write("</body>");
//Response.Write("</html>");
colunas.ForEach(a =>
{
WorkSheet.Cells[linha, coluna] = a;
coluna++;
});
coluna = 1;
linha = 2;
planilha.ForEach(item =>
{
WorkSheet.Cells[linha, coluna] = item.Endereco;
coluna++;
WorkSheet.Cells[linha, coluna] = item.Latitude;
coluna++;
WorkSheet.Cells[linha, coluna] = item.Longitude;
linha++;
coluna = 1;
});
// salva o arquivo
var nomeArquivo = "Enderecos-" + Guid.NewGuid().ToString();
WorkBook.SaveAs(@"c:\enderecos\" + nomeArquivo, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
return(nomeArquivo);
}
private void gerarModelo()
{
try
{
//GERAR MODELO
List <Dados> dadosModelo = new List <Dados>();
dadosModelo.Add(new Dados {
NOME = "NOME", EVENTO = "EVENTO", SORTEADO = "SORT"
});
dadosModelo.Add(new Dados {
NOME = "MODELO", EVENTO = "EVENTO", SORTEADO = "N"
});
BindingSource bs = new BindingSource();
bs.DataSource = dadosModelo;
dgv.DataSource = dadosModelo;
//SALVAR ARQUIVO
SaveFileDialog salvar = new SaveFileDialog();
Microsoft.Office.Interop.Excel.Application App; //EXCEL
Microsoft.Office.Interop.Excel.Workbook WorkBook; //PASTA
Microsoft.Office.Interop.Excel.Worksheet WorkSheet; //PLANILHA
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
//EXPORTAR EXCEL
for (i = 0; i <= dgv.RowCount - 1; i++)
{
for (j = 0; j <= dgv.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgv[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog();
string caminho = salvar.FileName;
WorkBook.SaveAs(caminho, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit();
MessageBox.Show(null, "O arquivo gerado foi salvo em:" + '\n' + caminho, "Arquivo Gerado", MessageBoxButtons.OK, MessageBoxIcon.Information);
dgv.DataSource = null;
}
catch (Exception exception)
{
MessageBox.Show(null, exception.ToString(), "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnExportarExcel_Click(object sender, EventArgs e)
{
if (MessageBox.Show("EXPORTAR PARA EXCEL ?", "MENSAGEM", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == (DialogResult.Yes))
{
if (dgvconsulta.Rows.Count == 0)
{
MessageBox.Show("NÃO A DADOS PARA EXPORTAR", "ATENÇÃO");
}
else
{
SaveFileDialog salvar = new SaveFileDialog(); // novo
Microsoft.Office.Interop.Excel.Application App; // Aplicação Excel
Microsoft.Office.Interop.Excel.Workbook WorkBook; // Pasta
Microsoft.Office.Interop.Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
/* passa as celulas do DataGridView para a Pasta do Excel
* for (i = 0; i <= dgvconsulta.RowCount - 1; i++)
* {
*
*
* for (j = 0; j <= dgvconsulta.ColumnCount - 1; j++)
* {
* DataGridViewCell cell = dgvconsulta[j, i];
* WorkSheet.Cells[i + 1, j + 1] = cell.Value;
* }
* }*/
// passa as celulas do DataGridView para a Pasta do Excel
for (j = 0; j < dgvconsulta.ColumnCount; j++)
{
WorkSheet.Cells[1, j + 1] = dgvconsulta.Columns[j].HeaderText;
}
for (i = 1; i <= dgvconsulta.RowCount; i++)
{
for (j = 0; j < dgvconsulta.ColumnCount; j++)
{
DataGridViewCell cell = dgvconsulta[j, i - 1];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
// define algumas propriedades da caixa salvar
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
//salvar.ShowDialog(); // mostra
// salva o arquivo
if (salvar.ShowDialog(this) == DialogResult.OK)
{
WorkBook.SaveAs(salvar.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
MessageBox.Show("EXPORTADO COM SUCESSO PARA EXCEL", "MENSAGEM", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
WorkBook.Close(false, misValue, misValue);
App.Quit(); // encerra o excel
}
}
}
}
private void btnExportar_Click(object sender, EventArgs e)
{
SaveFileDialog salvar = new SaveFileDialog(); // novo
Excel.Application App; // Aplicação Excel
Excel.Workbook WorkBook; // Pasta
Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Excel.Application();
App.DisplayAlerts = false;
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
pbRelatorios.Visible = true;
pbRelatorios.Value = 0;
pbRelatorios.Maximum = dgvRelatorio.Rows.Count;
// passa as colunas do DataGridView para a Pasta do Excel
WorkSheet.Application.Workbooks.Add(Type.Missing);
int pos = 1;
for (int i = 1; i < dgvRelatorio.Columns.Count + 1; i++)
{
if (dgvRelatorio.Columns[i - 1].Name != "ID" && dgvRelatorio.Columns[i - 1].Name != "ID_ARQ" && dgvRelatorio.Columns[i - 1].Name != "FLAG_DIV")
{
WorkSheet.Cells[1, pos] = dgvRelatorio.Columns[i - 1].HeaderText;
WorkSheet.Cells[1, pos].Font.Bold = true;
WorkSheet.Cells[1, pos].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
WorkSheet.Cells[1, pos].Interior.Color = ColorTranslator.ToWin32(Color.LightGray);
pos++;
}
}
CultureInfo cultura = new CultureInfo("pt-br");
double x;
DateTime y;
// passa as celulas do DataGridView para a Pasta do Excel
for (int i = 0; i < dgvRelatorio.Rows.Count; i++)
{
pos = 0;
for (int j = 0; j < dgvRelatorio.Columns.Count; j++)
{
if (dgvRelatorio.Columns[j].Name != "ID" && dgvRelatorio.Columns[j].Name != "ID_ARQ" && dgvRelatorio.Columns[j].Name != "FLAG_DIV")
{
bool EhNumerico = Double.TryParse(dgvRelatorio.Rows[i].Cells[j].Value.ToString(), NumberStyles.Any, cultura, out x);
bool EhData = DateTime.TryParse(dgvRelatorio.Rows[i].Cells[j].Value.ToString(), out y);
if (EhNumerico)
{
WorkSheet.Cells[i + 2, pos + 1] = x;
}
else if (EhData)
{
WorkSheet.Cells[i + 2, pos + 1] = y.ToShortDateString();
}
else
{
WorkSheet.Cells[i + 2, pos + 1] = dgvRelatorio.Rows[i].Cells[j].Value.ToString();
}
WorkSheet.Cells[i + 2, pos + 1].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
pos++;
}
}
pbRelatorios.Value++;
}
//Ajusta as colunas
WorkSheet.Columns.AutoFit();
// define algumas propriedades da caixa salvar
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog(); // mostra
// salva o arquivo
WorkBook.SaveAs(salvar.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
pbRelatorios.Value = 0;
MessageBox.Show("Exportado com sucesso!", "Exportação Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
/// Closes the excel spreadsheet
/// </summary>
public void ExcelCloseSpreadSheet()
{
WorkBook.Close();
XlApp.Quit();
}