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