private ExcelFile Consolidate(Stream[] fileDatas, string[] fileNames, bool OnlyData)
{
ExcelFile XlsIn = new XlsFile();
ExcelFile XlsOut = new XlsFile(true);
XlsOut.NewFile(1);
if (fileNames.Length > 1 && cbOnlyData.Checked)
{
XlsOut.InsertAndCopySheets(1, 2, fileNames.Length - 1);
}
for (int i = 0; i < fileNames.Length; i++)
{
if (fileDatas != null)
{
XlsIn.Open(fileDatas[i]);
}
else
{
XlsIn.Open(fileNames[i]);
}
XlsIn.ConvertFormulasToValues(true); //If there is any formula referring to other sheet, convert it to value.
//We could also call an overloaded version of InsertAndCopySheets() that
//copies many sheets at the same time, so references are kept.
XlsOut.ActiveSheet = i + 1;
if (OnlyData)
{
XlsOut.InsertAndCopyRange(TXlsCellRange.FullRange(), 1, 1, 1, TFlxInsertMode.ShiftRangeDown, TRangeCopyMode.All, XlsIn, 1);
}
else
{
XlsOut.InsertAndCopySheets(1, XlsOut.ActiveSheet, 1, XlsIn);
}
//Change sheet name.
string s = Path.GetFileName(fileNames[i]);
if (s.Length > 32)
{
XlsOut.SheetName = s.Substring(0, 29) + "...";
}
else
{
XlsOut.SheetName = s;
}
}
if (!cbOnlyData.Checked)
{
XlsOut.ActiveSheet = XlsOut.SheetCount;
XlsOut.DeleteSheet(1); //Remove the empty sheet that came with the workbook.
}
XlsOut.ActiveSheet = 1;
return(XlsOut);
}
private void btnGo_Click(object sender, System.EventArgs e)
{
// On this demo we will fill data on an existing file with the api, starting with an encrypted file holding the starting formats.
// Declare some data for the chart.
string[] Names = { "Dog", "Cat", "Cow", "Horse", "Fish" };
int[] Quantities = { 123, 200, 150, 0, 180 };
// Use two folders up to where the exe is to store the data. (Exe is stored at bin\debug)
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
XlsFile xls = new XlsFile(true);
// We will use the OnPassword event here to show how to
// open a file if you don't know a priory if it is encrypted or not.
// If you already knew the file was encrypted, (as in this case)you could use:
// xls.Protection.OpenPassword = "******";
xls.Protection.OnPassword += new OnPasswordEventHandler(GetPassword);
xls.Open(Path.Combine(DataPath, "EmptyForm.xls"));
// Insert rows so the chart range grows. On this case we assume the data is at least 2 rows long. If not, we should handle
// the case and do a xls.DeleteRange.
xls.InsertAndCopyRange(new TXlsCellRange(1, 1, 1, 2), 5, 1, Names.Length - 2, TFlxInsertMode.ShiftRangeDown, TRangeCopyMode.None);
// Fill the data.
for (int i = 0; i < Names.Length; i++)
{
xls.SetCellValue(4 + i, 1, Names[i]);
xls.SetCellValue(4 + i, 2, Quantities[i]);
}
// Set a new password for opening.
xls.Protection.OpenPassword = "******";
xls.Protection.SetModifyPassword("43", false, "Ford Prefect");
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
xls.Save(saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file? (Remember password is 43)", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
}
public void GerarExcel(string Nome_Arquivo_Origem,string Nome_Arquivo_Destino, int CodLayout, Dados d, ProgressBar statusGeracaoArquivo)
{
// Criando Aplicação
XlsFile excelDestino = new XlsFile();
XlsFile excelOrigem = new XlsFile();
excelOrigem.Open(Nome_Arquivo_Origem);
int QtdeLinhas = excelOrigem.RowCount;
//Gerando arquivo de saída
for (int i = 1; i <= excelOrigem.SheetCount; i++)
{
excelOrigem.ActiveSheet = i;
excelDestino.NewFile(1);
//LEITURA DOS DADOS ABAS
int qtdeColunas = excelOrigem.ColCountOnlyData;
if (qtdeColunas > 0)
{
for (int cont = 1; cont <= qtdeColunas; cont++)
{
Object titulo = excelOrigem.GetCellValue(1, cont);
if (titulo != null)
{
string LetraColuna = d.PosicaoVinculada(CodLayout, titulo.ToString());
int numeroColuna;
try
{
numeroColuna = Convert.ToInt16(LetraColuna);
}
catch (Exception)
{
numeroColuna = LetrasParaNumero(LetraColuna);
}
if (LetraColuna != null)
{
excelDestino.InsertAndCopyRange(
new TXlsCellRange(1, cont, QtdeLinhas, cont),
1,
numeroColuna,
1,
TFlxInsertMode.NoneRight,
TRangeCopyMode.All,
excelOrigem,
i
);
}
}
}
int c = 0;
string novoNomeArquivo = Path.GetDirectoryName(Nome_Arquivo_Destino) + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(Nome_Arquivo_Destino) + "_" + excelOrigem.SheetName + Path.GetExtension(Nome_Arquivo_Destino);
while (File.Exists(novoNomeArquivo))
{
c++;
novoNomeArquivo = Path.GetDirectoryName(Nome_Arquivo_Destino) + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(Nome_Arquivo_Destino) + "_" + excelOrigem.SheetName + i + Path.GetExtension(Nome_Arquivo_Destino);
}
excelDestino.Save(novoNomeArquivo);
}
statusGeracaoArquivo.Value = statusGeracaoArquivo.Value + 1;
}
}
