private void m_B_Export_Click(object sender, EventArgs e)
{
if (System.IO.File.Exists(this.m_TaskCsvName))
{
if (MessageBox.Show("文件已经存在，是否覆盖?", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.Cancel)
{
return;
}
}
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Mrg");
try
{
DataGridViewConverter.ImportFromDataGridView(ws, this.m_dgv_ItemDetail, new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
});
ef.Save(this.m_TaskCsvName, CsvSaveOptions.CsvDefault);
Process.Start("Excel.exe", this.m_TaskCsvName);
}
catch (System.Exception ex)
{
MessageBox.Show("文件保存失败 : " + ex.Message, "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
}
}
private void ExportToFile(DataGridView dg, string worksheetName)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "XLS files (*.xls)|*.xls|XLT files (*.xlt)|*.xlt|XLSX files (*.xlsx)|*.xlsx|XLSM files (*.xlsm)|*.xlsm|XLTX (*.xltx)|*.xltx|XLTM (*.xltm)|*.xltm|ODS (*.ods)|*.ods|OTS (*.ots)|*.ots|CSV (*.csv)|*.csv|TSV (*.tsv)|*.tsv|HTML (*.html)|*.html|MHTML (.mhtml)|*.mhtml|PDF (*.pdf)|*.pdf|XPS (*.xps)|*.xps|BMP (*.bmp)|*.bmp|GIF (*.gif)|*.gif|JPEG (*.jpg)|*.jpg|PNG (*.png)|*.png|TIFF (*.tif)|*.tif|WMP (*.wdp)|*.wdp";
saveFileDialog.FilterIndex = 3;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add(worksheetName);
DataGridViewConverter.ImportFromDataGridView(
ef.Worksheets[0],
dg,
new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
}
);
ws.PrintOptions.FitWorksheetWidthToPages = 1;
ef.Save(saveFileDialog.FileName);
}
}
public static void exportToExcel(DataGridView dgv, string name, bool footer)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
// Load Excel (XLSX) from a file.
ExcelFile workbook = new ExcelFile();
ExcelWorksheet worksheet = workbook.Worksheets.Add($"{name}");
worksheet.Cells["A1"].Value = "";
workbook.Save($"{name}.xlsx");
var workbook1 = ExcelFile.Load($"{name}.xlsx");
// Import DataGridView back to active worksheet.
DataGridViewConverter.ImportFromDataGridView(
workbook1.Worksheets.ActiveWorksheet,
dgv,
new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
});
// Save Excel (XLSX) to a file.
workbook1.Save($"{name}.xls");
File.Delete($"{name}.xlsx");
if (footer == true)
{
SheetHeaderFooter hfooter = workbook1.Worksheets.ActiveWorksheet.HeadersFooters;
hfooter.DefaultPage.Footer.CenterSection.Content = $"Number of Non-Active Members: {numberNonActive},\nNumber of Active Members: {numberActive},\nNumber of Members Owing: {numberOwing},\nTotal Amount Owed: {amountOwedTotal}";
workbook1.Save($"{name}.xls");
}
}
private void Import()
{
try
{
m_dgv_ItemDetail.Rows.Clear();
m_dgv_ItemDetail.Columns.Clear();
ExcelFile ef = ExcelFile.Load(this.m_TaskCsvName);
DataGridView dgv = new DataGridView();
DataGridViewConverter.ExportToDataGridView(ef.Worksheets.ActiveWorksheet, dgv, new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
//PrintColumnText(new PB.Task());
for (int row_index = 0; row_index < dgv.Rows.Count - 1; ++row_index) //Default datagridview has 1 record
{
int new_index = this.m_dgv_ItemDetail.Rows.Add();
for (int column_index = 0; column_index < dgv.Rows[row_index].Cells.Count; ++column_index)
{
this.m_dgv_ItemDetail.Rows[new_index].Cells[column_index].Value = dgv.Rows[row_index].Cells[column_index].Value;
}
}
}
catch (System.Exception ex)
{
MessageBox.Show("文件导入失败:" + ex.Message, "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
}
}
private void btnUpdateExcel_Click(object sender, EventArgs e)
{
// the saving dialog
var saveFileDialog = new SaveFileDialog();
// the types of files that can be saved
saveFileDialog.Filter = "XLS files (*.xls)|*.xls|XLT files (*.xlt)|*.xlt|XLSX files (*.xlsx)|*.xlsx|XLSM files (*.xlsm)|*.xlsm|XLTX (*.xltx)|*.xltx|XLTM (*.xltm)|*.xltm|ODS (*.ods)|*.ods|OTS (*.ots)|*.ots|CSV (*.csv)|*.csv|TSV (*.tsv)|*.tsv|HTML (*.html)|*.html|MHTML (.mhtml)|*.mhtml|PDF (*.pdf)|*.pdf|XPS (*.xps)|*.xps|BMP (*.bmp)|*.bmp|GIF (*.gif)|*.gif|JPEG (*.jpg)|*.jpg|PNG (*.png)|*.png|TIFF (*.tif)|*.tif|WMP (*.wdp)|*.wdp";
saveFileDialog.FilterIndex = 3;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
// create a new excel file
var workbook = new ExcelFile();
// add a new excel sheet
var worksheet = workbook.Worksheets.Add("Sheet1");
// From metrogrid1 to ExcelFile.
DataGridViewConverter.ImportFromDataGridView(worksheet, this.metroGrid1, new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
});
// saves the file
workbook.Save(saveFileDialog.FileName);
}
}
private void fillDataGrid()
{
var workbook = ExcelFile.Load((String)Directory.GetCurrentDirectory().ToString() + "\\PowerSpreadSheet\\PowerPrice.xlsx");
DataGridViewConverter.ExportToDataGridView(workbook.Worksheets.ActiveWorksheet, this.dataGridView1, new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
}
private void Upload_quote_btn_Click(object sender, EventArgs e)
{
var openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "XLS files (*.xls, *.xlt)|*.xls;*.xlt|XLSX files (*.xlsx, *.xlsm, *.xltx, *.xltm)|*.xlsx;*.xlsm;*.xltx;*.xltm|ODS files (*.ods, *.ots)|*.ods;*.ots|CSV files (*.csv, *.tsv)|*.csv;*.tsv|HTML files (*.html, *.htm)|*.html;*.htm";
openFileDialog.FilterIndex = 2;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
var workbook = ExcelFile.Load(openFileDialog.FileName);
// From ExcelFile to DataGridView.
DataGridViewConverter.ExportToDataGridView(workbook.Worksheets.ActiveWorksheet, this.dataGridView1, new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
}
/*
* for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
* {
*
*
* Convert.ToInt32(dataGridView1.Rows[i].Cells[3].Value);
* Convert.ToDecimal(dataGridView1.Rows[i].Cells[4].Value);
* Convert.ToDecimal(dataGridView1.Rows[i].Cells[5].Value);
* Convert.ToDecimal(dataGridView1.Rows[i].Cells[6].Value);
* }
*/
foreach (DataGridViewRow row in dataGridView1.Rows)
{
//multiply qty by cog and add the result to cogItem
cogItem += Convert.ToDouble(dataGridView1.Rows[i].Cells[4].Value) * Convert.ToDouble(dataGridView1.Rows[i].Cells[5].Value);
total_sale_label.Text = "Total CoG: $" + cogItem.ToString();
//adds the row total to sumprofit
sumProfit += Convert.ToDouble(dataGridView1.Rows[i].Cells[7].Value);
total_label.Text = "Total: $" + sumProfit.ToString();
//subtract to obtain the total earnings
totalProf = sumProfit - cogItem;
total_profit_label.Text = "Total Profit: $" + totalProf.ToString();
//calculate average markup for quote
percentAve = ((totalProf / cogItem) * 100);
percent_label.Text = "MarkUp Average: " + Math.Round(percentAve, 2).ToString() + "%";
i++;
}
}
private void LoadExcelToDataGridView(string excelFile)
{
ExcelFile ef = ExcelFile.Load(excelFile);
ExcelWorksheet ws = ef.Worksheets[0];
// From ExcelFile to DataGridView.
DataGridViewConverter.ExportToDataGridView(
ws,
this.dataGridView1,
new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
}
public void SaveDataToExcel(DataGridView dataGridView1, string file)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Sheet1");
// From DataGridView to ExcelFile.
DataGridViewConverter.ImportFromDataGridView(worksheet, dataGridView1, new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
});
workbook.Save(file);
}
private void SaveExcelFromDataGridView(string excelFile)
{
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("DGW Sheet");
// From DataGridView to ExcelFile.
DataGridViewConverter.ImportFromDataGridView(
ws,
this.dataGridView1,
new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
});
ef.Save(excelFile);
}
private void button3_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "XLS files(*.xls)|*.xls";
ofd.FilterIndex = 3;
if (ofd.ShowDialog() == DialogResult.OK)
{
dataGridView1.Columns.Clear();
ExcelFile ef = ExcelFile.Load(ofd.FileName);
DataGridViewConverter.ExportToDataGridView(ef.Worksheets.ActiveWorksheet, this.dataGridView1, new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
}
}
private void saveButton_Click(object sender, EventArgs e)
{
DataGridViewConverter.ImportFromDataGridView(
File.Tables.Worksheets[tabControl1.SelectedIndex],
this.tableGridView1,
new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
});
try
{
FileManager.SaveFile(File);
}
catch (Exception)
{
}
}
private void btn_agregarArchivo_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "XLS files (*.xls, *.xlt)|*.xls;*.xlt|XLSX files (*.xlsx, *.xlsm, *.xltx, *.xltm)|*.xlsx;*.xlsm;*.xltx;*.xltm|ODS files (*.ods, *.ots)|*.ods;*.ots|CSV files (*.csv, *.tsv)|*.csv;*.tsv|HTML files (*.html, *.htm)|*.html;*.htm";
//openFileDialog.FilterIndex = 2;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
ExcelFile ef = ExcelFile.Load(openFileDialog.FileName);
// Export Excel worksheet to DataGridView control.
DataGridViewConverter.ExportToDataGridView(ef.Worksheets.ActiveWorksheet, this.dataGridView1, new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
}
}
private void btnLoadFile_Click(object sender, EventArgs e)
{
var openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "XLS files (*.xls, *.xlt)|*.xls;*.xlt|XLSX files (*.xlsx, *.xlsm, *.xltx, *.xltm)|*.xlsx;*.xlsm;*.xltx;*.xltm|ODS files (*.ods, *.ots)|*.ods;*.ots|CSV files (*.csv, *.tsv)|*.csv;*.tsv|HTML files (*.html, *.htm)|*.html;*.htm";
openFileDialog.FilterIndex = 2;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
var workbook = ExcelFile.Load(openFileDialog.FileName);
// From ExcelFile to DataGridView.
DataGridViewConverter.ExportToDataGridView(workbook.Worksheets.ActiveWorksheet, this.dataGridView1, new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
}
}
private void btnExaminar_Click(object sender, EventArgs e)
{
OpenFileDialog OpenFlDialog = new OpenFileDialog();
OpenFlDialog.Filter = "XLS files (*.xls, *.xlt)|*.xls;*.xlt|XLSX files (*.xlsx, *.xlsm, *.xltx, *.xltm)|*.xlsx;*.xlsm;*.xltx;*.xltm|ODS files (*.ods, *.ots)|*.ods;*.ots|CSV files (*.csv, *.tsv)|*.csv;*.tsv|HTML files (*.html, *.htm)|*.html;*.htm";
OpenFlDialog.FilterIndex = 2;
if (OpenFlDialog.ShowDialog() == DialogResult.OK)
{
StartLoading();
txtRutaArchivo.Text = OpenFlDialog.FileName.Trim();
var workBook = ExcelFile.Load(OpenFlDialog.FileName);
DataGridViewConverter.ExportToDataGridView(workBook.Worksheets.ActiveWorksheet, listCotizaciones, new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
btnGuardar.Enabled = true;
CloseLoading();
}
}
private void save_service_Click(object sender, EventArgs e)
{
var saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "XLS files (*.xls)|*.xls|XLSX files (*.xlsx)|*.xlsx|CSV (*.csv)|*.csv|All files (*.*)|*.*";
saveFileDialog.FilterIndex = 3;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Services");
// From DataGridView to ExcelFile.
DataGridViewConverter.ImportFromDataGridView(worksheet, this.dgvService, new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
});
workbook.Save(saveFileDialog.FileName);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "XLS files (*.xls)|*.xls|XLT files (*.xlt)|*.xlt|XLSX files (*.xlsx)|*.xlsx|XLSM files (*.xlsm)|*.xlsm|XLTX (*.xltx)|*.xltx|XLTM (*.xltm)|*.xltm|ODS (*.ods)|*.ods|OTS (*.ots)|*.ots|CSV (*.csv)|*.csv|TSV (*.tsv)|*.tsv|HTML (*.html)|*.html|MHTML (.mhtml)|*.mhtml|PDF (*.pdf)|*.pdf|XPS (*.xps)|*.xps|BMP (*.bmp)|*.bmp|GIF (*.gif)|*.gif|JPEG (*.jpg)|*.jpg|PNG (*.png)|*.png|TIFF (*.tif)|*.tif|WMP (*.wdp)|*.wdp";
saveFileDialog.FilterIndex = 3;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Sheet1");
// From DataGridView to ExcelFile.
DataGridViewConverter.ImportFromDataGridView(ws, this.dataGridView1, new ImportFromDataGridViewOptions()
{
ColumnHeaders = true
});
ef.Save(saveFileDialog.FileName);
}
}
private void btn_excel_Click(object sender, EventArgs e)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
string path = AppDomain.CurrentDomain.BaseDirectory + @"\lib\Ficheros\deslinde.xls";
ExcelFile workbook = ExcelFile.Load(path);
//var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.ActiveWorksheet;
DataGridViewConverter.ImportFromDataGridView(worksheet, dataGridView1, new ImportFromDataGridViewOptions()
{
ColumnHeaders = false, StartRow = 7, StartColumn = 1
});
//workbook.Save("deslinde.xls");
workbook.Save(path);
MessageBox.Show("Done");
Process.Start(path);
}
private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
if (tabControl1.SelectedIndex != -1)
{
DataGridViewConverter.ExportToDataGridView(
File.Tables.Worksheets[tabControl1.SelectedIndex],
this.tableGridView1,
new ExportToDataGridViewOptions()
{
ColumnHeaders = true
});
SelectColumComboBox.Items.Clear();
for (int i = 0; i < tableGridView1.Columns.Count; i++)
{
SelectColumComboBox.Items.Add(tableGridView1.Columns[i].HeaderText);
}
tabControl1.SelectedTab.Controls.Add(tableGridView1);
tableGridView1.Location = new Point(0, 0);
tableGridView1.Size = PreferredSize;
tableGridView1.AutoResizeColumns();
tableGridView1.AutoResizeRows();
}
}
