/// <summary> /// /// </summary> /// <param name="rows"></param> /// <param name="excelTitle"></param> /// <param name="path"></param> /// <param name="isPrint"></param> private void GenerateExportData(List <PlanBatchInfo> rows, string excelTitle, string path, bool isPrint) { ExcelWork excelWork = new ExcelWork(); excelWork.ExcelSheet = new List <ExcelSheet>(); ExcelSheet excelSheet = new ExcelSheet() { SheetName = excelTitle, SheetDataGrid = new SheetDataGrid() { ContentItems = ListConvertToDataTable.ToDataTable(rows), DataGridColumn = GetExcelColumn(), ContentRowHeight = 20, StartRowIndex = 1, ContentStyle = ExcelStyle.SecordTitleStyle }, }; excelWork.FilePath = path; excelWork.ExcelSheet.Add(excelSheet); ExcelAsposeOper.ExportExcel(excelWork, isPrint); }
public void Compare(ExcelWork firstObj, int firstRowIndex, ExcelWork secondObj, int secondRowIndex) { try { resultBook = excelApp.Workbooks.Open(SavePath); resultSheet = resultBook.Worksheets[1]; WorkBookManager(firstObj, firstRowIndex); WorkBookManager(secondObj, secondRowIndex); startColumnIndex++; resultBook.Save(); } catch (Exception ex) { /// Возврат Error Message во View /// throw new Exception(ex.Message); } finally { resultBook?.Close(); } }
private void WorkBookManager(ExcelWork arg, int rowIndex) { Excel.Workbook comparedBook = null; try { comparedBook = excelApp.Workbooks.Open(arg.FileName); /// Получение нужной страницы var workSheet = (Excel.Worksheet)comparedBook.Worksheets[arg.WorkSheet]; dynamic value = ReadSheet(workSheet, arg.Column); WriteToNewSheet(value, rowIndex); } catch (Exception ex) { /// Возврат Error Message во View /// throw new Exception(ex.Message); } finally { comparedBook?.Close(); } }
public void Dispose() { DisposePics(); if (m_ew != null) { m_ew.Dispose(); } m_ew = null; }
private void bCreateAct_Click(object sender, EventArgs e) { if (comboBox1.Text == string.Empty) { MessageBox.Show("Выберите отдел фондодержателя!", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (comboBox2.Text == string.Empty) { MessageBox.Show("Выберите год!", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (comboBox3.Text == string.Empty) { MessageBox.Show("Выберите акт!", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } KeyValuePair <string, string> item = (KeyValuePair <string, string>)comboBox3.SelectedItem; string ActNumberSort = item.Key.ToString(); string ActNumber = item.Value.ToString(); using (ExcelWork excel = new ExcelWork(ActNumber)) { try { excel.Init(); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } List <BJBookInfo> books = wi.GetBooksByAct(ActNumberSort); int RowIndex = 0; int Cost = 0; foreach (BJBookInfo b in books) { foreach (BJExemplarInfo exemplar in b.Exemplars) { if (exemplar.Fields["929$b"].ToString() != string.Empty) { if (exemplar.Fields["929$b"].ToString() == ActNumber) { RowIndex++; excel.InsertExemplar(exemplar, b, RowIndex); } } } } excel.InsertDocumentHeader(RowIndex, comboBox1.Text, Cost); MessageBox.Show("Формирование акта успешно завершено!"); } }
private void GenerateAct(List <BJExemplarInfo> Exemplars, string ActNumber) { if (comboBox1.Text == string.Empty) { MessageBox.Show("Выберите отдел фондодержателя!", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (comboBox2.Text == string.Empty) { MessageBox.Show("Выберите год!", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } KeyValuePair <string, string> item = (KeyValuePair <string, string>)comboBox3.SelectedItem; string ActNumberSort = item.Key.ToString(); using (ExcelWork excel = new ExcelWork(ActNumber)) { try { excel.Init(); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } int RowIndex = 0; int Cost = 0; //foreach (BJBookInfo b in books) { foreach (BJExemplarInfo exemplar in Exemplars) { if (exemplar.Fields["929$b"] != null) { //if (exemplar.Fields["929$b"].ToString() == ActNumber) { BJBookInfo b = BJBookInfo.GetBookInfoByPIN(exemplar.IDMAIN, exemplar.Fund); RowIndex++; excel.InsertExemplar(exemplar, b, RowIndex); } } } } excel.InsertDocumentHeader(RowIndex, comboBox1.Text, Cost); MessageBox.Show("Формирование акта успешно завершено!"); } }
public bool Load(string filename) { //System.Diagnostics.Debugger.Break(); if (m_ew != null) { latest_error = "Unexpected! {3FB775C6-5A13-4AE7-B2CD-54CE5358C78E}"; throw new SystemException(latest_error); } if (!File.Exists(filename)) { latest_error = "File not found : " + filename; return(false); } try { m_ew = new ExcelWork(); m_ew.Load(filename); m_cell_list = new List <Cell>(); return(true); } catch (SystemException e) { latest_error = e.Message; return(false); } }
private void BtnBuscar_Click(object sender, EventArgs e) { ArrayList ComandoCompleto = new ArrayList(); for (int i = 0; i < ListGerar.Items.Count; i++) { if (ListGerar.Items[i].Text.Equals("Preço de custo")) { ComandoCompleto.Add("1"); } else if (ListGerar.Items[i].Text.Equals("Nome")) { ComandoCompleto.Add("SELECT nome as DESCRIÇÃO FROM produto WHERE excluido = 'N'"); } else if (ListGerar.Items[i].Text.Equals("Codigo")) { ComandoCompleto.Add("SELECT id as ID FROM produto WHERE excluido = 'N'"); } else if (ListGerar.Items[i].Text.Equals("Markup")) { ComandoCompleto.Add(" SELECT markup as MARKUP FROM produto WHERE excluido = 'N'"); } else if (ListGerar.Items[i].Text.Equals("Preço de Venda - Vendedor")) { ComandoCompleto.Add("2"); } else if (ListGerar.Items[i].Text.Equals("Preço de Venda - A prazo")) { ComandoCompleto.Add("3"); } else if (ListGerar.Items[i].Text.Equals("Preço de Venda - A vista")) { ComandoCompleto.Add("4"); } else if (ListGerar.Items[i].Text.Equals("Preço de Venda - Atacado")) { ComandoCompleto.Add("5"); } else if (ListGerar.Items[i].Text.Equals("Listar materia prima cadastrada")) { ComandoCompleto.Add("SELECT id as ID, descricao as DESCRICAO , valor as VALOR FROM materiaprima WHERE excluido = 'N'"); } else if (ListGerar.Items[i].Text.Equals("Listar serviço cadastrado")) { ComandoCompleto.Add("SELECT id as ID,descricao as DESCRICAO , valor as VALOR FROM servico WHERE excluido = 'N'"); } else if (ListGerar.Items[i].Text.Equals("Listar aviamento cadastrado")) { ComandoCompleto.Add("SELECT id as ID,descricao as DESCRICAO , valor as VALOR FROM aviamento WHERE excluido = 'N'"); } else if (ListGerar.Items[i].Text.Equals("Listar custo cadastrado")) { ComandoCompleto.Add("SELECT id as ID,descricao as DESCRICAO , valor as VALOR FROM custooperacional WHERE excluido = 'N'"); } } if (salvar == 1) { Relatorio.SalvarRelatorio(ComandoCompleto, NomeRelatorio); salvar = 0; } if (ListGerar.Items.Count > 5) { DialogResult Resposta = MessageBox.Show( "Muitos campos foram selecionados, portanto será gerado o relatório apenas em EXCEL para melhor visualização. Deseja continuar ?", "Pergunta ?", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (Resposta.Equals(DialogResult.Yes)) { Thread.CurrentThread.CurrentCulture = new CultureInfo(0x0416); DataTable Resultado = Relatorio.RelatorioPersonalizado(ComandoCompleto); Application ExcelApp; Workbook ExcelWork; Worksheet ExcelSheet; object misValue = Missing.Value; ExcelApp = new Application(); ExcelWork = ExcelApp.Workbooks.Add(misValue); ExcelSheet = (Worksheet)ExcelWork.Worksheets.get_Item(1); for (int j = 1; j <= Resultado.Columns.Count; j++) { ExcelSheet.Cells[1, j] = ListGerar.Items[j - 1].Text; } int linha = 2; for (int i = 1; i <= Resultado.Rows.Count; i++) { for (int j = 1; j <= Resultado.Columns.Count; j++) { ExcelSheet.Cells[linha, j] = Resultado.Rows[i - 1][j - 1].ToString(); } linha++; } string folderPath = "C:\\Relatórios\\"; if (!Directory.Exists(folderPath)) { Directory.CreateDirectory(folderPath); } ExcelWork.SaveAs(@"C:\Relatórios\Relatório Mac Giant.xls", XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); ExcelWork.Close(true, misValue, misValue); ExcelApp.Quit(); liberarObjetos(ExcelSheet); liberarObjetos(ExcelWork); liberarObjetos(ExcelApp); MessageBox.Show("Relatorio gerado com sucesso !", "Sucesso !", MessageBoxButtons.OK, MessageBoxIcon.Information); Process.Start("Explorer", "C:\\Relatórios"); } else { MessageBox.Show("Relatório cancelado, favor selecionar novos itens !", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else if (RadioPdf.Checked) { DataTable Resultado = Relatorio.RelatorioPersonalizado(ComandoCompleto); dataGridView1.DataSource = Resultado; Gerador.GerarPdf(dataGridView1, 2); } else { Thread.CurrentThread.CurrentCulture = new CultureInfo(0x0416); DataTable Resultado = Relatorio.RelatorioPersonalizado(ComandoCompleto); Application ExcelApp; Workbook ExcelWork; Worksheet ExcelSheet; object misValue = Missing.Value; ExcelApp = new Application(); ExcelWork = ExcelApp.Workbooks.Add(misValue); ExcelSheet = (Worksheet)ExcelWork.Worksheets.get_Item(1); for (int j = 1; j <= Resultado.Columns.Count; j++) { ExcelSheet.Cells.Font.Bold = true; ExcelSheet.Cells[1, j] = ListGerar.Items[j - 1].Text; } int linha = 2; for (int i = 1; i <= Resultado.Rows.Count; i++) { for (int j = 1; j <= Resultado.Columns.Count; j++) { ExcelSheet.Cells.Font.Bold = false; ExcelSheet.Cells[linha, j] = Resultado.Rows[i - 1][j - 1].ToString(); } linha++; } string folderPath = "C:\\Relatórios\\"; if (!Directory.Exists(folderPath)) { Directory.CreateDirectory(folderPath); } ExcelWork.SaveAs(@"C:\Relatórios\Relatório Mac Giant.xls", XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); ExcelWork.Close(true, misValue, misValue); ExcelApp.Quit(); liberarObjetos(ExcelSheet); liberarObjetos(ExcelWork); liberarObjetos(ExcelApp); MessageBox.Show("Relatorio gerado com sucesso !", "Sucesso !", MessageBoxButtons.OK, MessageBoxIcon.Information); Process.Start("Explorer", "C:\\Relatórios"); } }
private void padrãoToolStripMenuItem_DropDownItemClicked(object sender, ToolStripItemClickedEventArgs e) { ArrayList ComandoCompleto = Relatorio.RetornoQuerryRelatorioSalvo(e.ClickedItem.Text); if (RadioPdf.Checked) { DataTable Resultado = Relatorio.RelatorioPersonalizado(ComandoCompleto); dataGridView1.DataSource = Resultado; Gerador.GerarPdf(dataGridView1, 2); } else { Thread.CurrentThread.CurrentCulture = new CultureInfo(0x0416); DataTable Resultado = Relatorio.RelatorioPersonalizado(ComandoCompleto); Application ExcelApp; Workbook ExcelWork; Worksheet ExcelSheet; object misValue = Missing.Value; ExcelApp = new Application(); ExcelWork = ExcelApp.Workbooks.Add(misValue); ExcelSheet = (Worksheet)ExcelWork.Worksheets.get_Item(1); for (int j = 1; j <= Resultado.Columns.Count; j++) { ExcelSheet.Cells.Font.Bold = true; ExcelSheet.Cells[1, j] = ListGerar.Items[j - 1].Text; } int linha = 2; for (int i = 1; i <= Resultado.Rows.Count; i++) { for (int j = 1; j <= Resultado.Columns.Count; j++) { ExcelSheet.Cells.Font.Bold = false; ExcelSheet.Cells[linha, j] = Resultado.Rows[i - 1][j - 1].ToString(); } linha++; } string folderPath = "C:\\Relatórios\\"; if (!Directory.Exists(folderPath)) { Directory.CreateDirectory(folderPath); } ExcelWork.SaveAs(@"C:\Relatórios\Relatório Mac Giant.xls", XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); ExcelWork.Close(true, misValue, misValue); ExcelApp.Quit(); liberarObjetos(ExcelSheet); liberarObjetos(ExcelWork); liberarObjetos(ExcelApp); MessageBox.Show("Relatorio gerado com sucesso !", "Sucesso !", MessageBoxButtons.OK, MessageBoxIcon.Information); Process.Start("Explorer", "C:\\Relatórios"); } }