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