public GenerarExcel(M_Eno m, string titulo)
{
this.m = m;
this.titulo = titulo;
excel = new Excel.Application();
libro = (Excel._Workbook)excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
hoja = (Excel._Worksheet)libro.Worksheets.Add();
hoja.Name = "Resultado Del ENO";
((Excel.Worksheet)excel.ActiveWorkbook.Sheets["Hoja1"]).Delete();
generarDocumento(ref hoja);
if (System.IO.File.Exists(Environment.CurrentDirectory + @"Result.xlsx"))
{
libro.Save();
}
else
{
libro.SaveAs(Environment.CurrentDirectory + @"\Result.xlsx");
}
libro.Close();
releaseObject(libro);
excel.UserControl = false;
excel.Quit();
releaseObject(excel);
}
//-> Export to Excel
private void StatisticExportToExcel()
{
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[0];
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < dgv_Statistic.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgv_Statistic.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dgv_Statistic.Rows.Count - 1; i++)
{
for (int j = 0; j < dgv_Statistic.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgv_Statistic.Rows[i].Cells[j].Value.ToString();
}
}
string path = Directory.GetParent(System.IO.Directory.GetCurrentDirectory()).Parent.Parent.Parent.FullName + "\\Statistic & Analyze\\Excel\\output.xls";
// save the application
workbook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
app.Quit();
}
private void PrintFileImage_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exported from gridview";
for (int i = 1; i < dataGridView_Course.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView_Course.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView_Course.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView_Course.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView_Course.Rows[i].Cells[j].Value.ToString();
}
}
workbook.SaveAs("D:\\list4.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
MessageBox.Show("Save file successful!", "Save File", MessageBoxButtons.OK, MessageBoxIcon.Information);
app.Quit();
}
private void bwExportExcel_DoWork(object sender, DoWorkEventArgs e)
{
EXCEL._Application excel = new Microsoft.Office.Interop.Excel.Application();
EXCEL._Workbook workbook = excel.Workbooks.Add(Type.Missing);
EXCEL._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Cells.HorizontalAlignment = EXCEL.XlHAlign.xlHAlignCenter;
worksheet.Cells.Font.Name = "Arial";
worksheet.Cells.Font.Size = 10;
// Title, Header.
int rowTitle = 1;
int rowHeader = 2;
int columnNumber = 0;
Dispatcher.Invoke(new Action(() =>
{
columnNumber = dgWHDeliveryDetail.Columns.Count();
// Title
worksheet.Range[worksheet.Cells[rowTitle, 1], worksheet.Cells[rowTitle, columnNumber]].Merge();
worksheet.Cells.Rows[rowTitle].Font.Size = 15;
worksheet.Cells.Rows[rowTitle].Font.FontStyle = "Bold";
// Header
worksheet.Cells[rowTitle, 1] = txtTitle.Text;
for (int i = 0; i < columnNumber; i++)
{
var column = dgWHDeliveryDetail.Columns[i] as DataGridTemplateColumn;
worksheet.Cells[rowHeader, i + 1] = column.Header;
}
worksheet.Cells.Rows[rowHeader].Font.Size = 10;
worksheet.Cells.Rows[rowHeader].Font.FontStyle = "Bold";
progressBar.Maximum = (double)excelExportList.Count();
}));
int rowContent = 3;
for (int i = 0; i < excelExportList.Count(); i++)
{
Dispatcher.Invoke(new Action(() =>
{
progressBar.Value = rowContent - 2;
lblStatus.Text = string.Format("Writing {0} rows / {1}", rowContent - 2, excelExportList.Count());
}));
var export = excelExportList[i];
worksheet.Cells[rowContent, 1] = export.ProductNo;
worksheet.Cells[rowContent, 2] = export.ArticleNo;
worksheet.Cells[rowContent, 3] = export.OrderEFD;
worksheet.Cells[rowContent, 4] = export.DeliveryEFD;
worksheet.Cells[rowContent, 5] = export.SewingStartDate;
worksheet.Cells[rowContent, 6] = export.QuantityOrder;
worksheet.Cells[rowContent, 7] = export.Release;
worksheet.Cells[rowContent, 8] = export.Delivery;
worksheet.Cells[rowContent, 9] = export.Reject;
for (int j = 0; j < export.ContentStatusList.Count(); j++)
{
worksheet.Cells[rowContent, j + 10].ColumnWidth = export.ContentStatusList[j].WidthDefaultCell;
worksheet.Cells[rowContent, j + 10].Font.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].ForegroundCell);
worksheet.Cells[rowContent, j + 10].Interior.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].BackgroundCell);
worksheet.Cells[rowContent, j + 10].Borders.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].BorderColorCell);
if (export.ContentStatusList[j].Quantity > 0)
{
worksheet.Cells[rowContent, j + 10] = export.ContentStatusList[j].Quantity;
}
if (export.ContentStatusList[j].ForegroundCell != null)
{
worksheet.Cells[rowContent, j + 10].Font.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].ForegroundCell);
}
if (export.ContentStatusList[j].BackgroundCell != null)
{
worksheet.Cells[rowContent, j + 10].Interior.Color = System.Drawing.ColorTranslator.ToOle(export.ContentStatusList[j].BackgroundCell);
}
}
worksheet.Cells[rowContent, columnNumber - 1] = export.TotalBalance;
worksheet.Cells[rowContent, columnNumber] = export.TotalReleasePainting;
rowContent++;
}
Dispatcher.Invoke(new Action(() =>
{
if (workbook != null)
{
var sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Title = "Master Schedule - Export Excel File";
sfd.Filter = "Excel Documents (*.xls)|*.xls|Excel Documents (*.xlsx)|*.xlsx";
sfd.FilterIndex = 2;
sfd.FileName = String.Format("Outsole WH Delivery Report");
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(sfd.FileName);
MessageBox.Show("Export Successful !", "Master Schedule - Export Excel File", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
}));
}
catch (System.Exception ex)
{
Dispatcher.Invoke(new Action(() =>
{
MessageBox.Show(ex.Message, "Master Schedule - Export Excel File", MessageBoxButton.OK, MessageBoxImage.Error);
}));
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void buttonExport_Click(object sender, EventArgs e)
{
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
excel.SheetsInNewWorkbook = 6;
workbook = excel.Workbooks.Add();
worksheet = workbook.Worksheets[1];
worksheet.Name = "Gironi";
int cellRowIndex = 1;
int cellColumnIndex = 1;
#region Gironi
//Loop through gironi
for (int j = 0; j < dataGridViewGironi.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewGironi.Columns[j].HeaderText;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
for (int i = 0; i < dataGridViewGironi.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewGironi.Columns.Count; j++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewGironi.Rows[i].Cells[j].Value.ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
#endregion
#region sedicesimi
worksheet = workbook.Sheets[2];
worksheet.Name = "Sedicesimi";
cellRowIndex = 1;
cellColumnIndex = 1;
//Loop through Sedicesimi
for (int j = 0; j < dataGridViewSedicesimi.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewSedicesimi.Columns[j].HeaderText;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
for (int i = 0; i < dataGridViewSedicesimi.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewSedicesimi.Columns.Count; j++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewSedicesimi.Rows[i].Cells[j].Value.ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
#endregion
#region ottavi
worksheet = workbook.Sheets[3];
worksheet.Name = "Ottavi";
cellRowIndex = 1;
cellColumnIndex = 1;
//Loop through Ottavi
for (int j = 0; j < dataGridViewOttavi.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewOttavi.Columns[j].HeaderText;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
for (int i = 0; i < dataGridViewOttavi.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewOttavi.Columns.Count; j++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewOttavi.Rows[i].Cells[j].Value.ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
#endregion
#region Quarti
worksheet = workbook.Sheets[4];
worksheet.Name = "Quarti";
cellRowIndex = 1;
cellColumnIndex = 1;
//Loop through Ottavi
for (int j = 0; j < dataGridViewQuarti.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewQuarti.Columns[j].HeaderText;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
for (int i = 0; i < dataGridViewQuarti.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewQuarti.Columns.Count; j++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewQuarti.Rows[i].Cells[j].Value.ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
#endregion
#region Semifinali
worksheet = workbook.Sheets[5];
worksheet.Name = "Semifinali";
cellRowIndex = 1;
cellColumnIndex = 1;
//Loop through Semifinali
for (int j = 0; j < dataGridViewSemifinali.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewSemifinali.Columns[j].HeaderText;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
for (int i = 0; i < dataGridViewSemifinali.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewSemifinali.Columns.Count; j++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewSemifinali.Rows[i].Cells[j].Value.ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
#endregion
#region Finali
worksheet = workbook.Sheets[6];
worksheet.Name = "Finali";
cellRowIndex = 1;
cellColumnIndex = 1;
//Loop through Finali
for (int j = 0; j < dataGridViewFinali.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewFinali.Columns[j].HeaderText;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
for (int i = 0; i < dataGridViewFinali.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewFinali.Columns.Count; j++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridViewFinali.Rows[i].Cells[j].Value.ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
#endregion
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; //|All files (*.*)|*.*";
saveDialog.FilterIndex = 1;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export completato con successo", "Salvataggio", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public void Exportieren(string turniername, DataGrid Starterliste_DataGrid)
{
string filename = "";
// Configure save file dialog box
SaveFileDialog dlg = new SaveFileDialog();
dlg.FileName = turniername; // Default file name
dlg.DefaultExt = ".xlsx"; // Default file extension
dlg.Filter = "Excel documents (.xlsx)|*.xlsx"; // Filter files by extension
// Show save file dialog box
Nullable <bool> result = dlg.ShowDialog();
// Process save file dialog box results
if (result == true)
{
// Save document
filename = dlg.FileName;
}
//instantiate excel objects (application, workbook, worksheets)
excel.Application XlObj = new excel.Application();
XlObj.Visible = false;
excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;
DataTable dt = DataGridtoDataTable(Starterliste_DataGrid);
//run through datatable and assign cells to values of datatable
try
{
int row = 1; int col = 1;
foreach (DataColumn column in dt.Columns)
{
//adding columns
WsObj.Cells[row, col] = column.ColumnName;
col++;
}
//reset column and row variables
col = 1;
row++;
for (int i = 0; i < dt.Rows.Count; i++)
{
//adding data
foreach (var cell in dt.Rows[i].ItemArray)
{
WsObj.Cells[row, col] = cell;
col++;
}
col = 1;
row++;
}
WbObj.SaveAs(filename);
}
catch (COMException x)
{
Console.WriteLine(x);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
WbObj.Close();
}
}
private void Export_Click(object sender, System.EventArgs e)
{
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
/////////////////////////////////////incercarea 2/////////////
//Microsoft.Office.Interop.Excel._Application app =
// new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
//Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
//worksheet = workbook.Sheets["Sheet1"];
//worksheet = workbook.ActiveSheet;
//worksheet.Name = "Toti Angajatii";
//for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
//{
// worksheet.Cells[1,i]=dataGridView1.Columns[i-1].HeaderText;
//}
//for (int i = 0; i < dataGridView1.Rows.Count + 1; i++)
//{
// for (int j = 0; j < dataGridView1.Columns.Count ; j++)
// {
// worksheet.Cells[i + 2, j + 1] =
// dataGridView1.Rows[i].Cells[j].Value.ToString();
// }
//}
//var saveFileDialoge = new SaveFileDialog();
//saveFileDialoge.FileName = "Excel_exportContacte";
//saveFileDialoge.DefaultExt = ".xlsx";
//if(saveFileDialoge.ShowDialog()==DialogResult.OK)
//{
// workbook.SaveAs(saveFileDialoge.FileName,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
// Type.Missing, Type.Missing, Type.Missing
// , Type.Missing, Type.Missing);
//}
//app.Quit();
////////////////////////////////////prima incercare///////
//saveFileDialog1.InitialDirectory = "C:";
//saveFileDialog1.Title = "Salveaza ca fila Excel";
//saveFileDialog1.FileName = "Excel_exportContacte";
//saveFileDialog1.Filter = "ExcelFiles(2016)|*.xlsx";
//if (saveFileDialog1.ShowDialog() != DialogResult.Cancel) {
// Microsoft.Office.Interop.Excel.Application ExcelApp =
// new Microsoft.Office.Interop.Excel.Application();
// ExcelApp.Application.Workbooks.Add( );
// //schimb propietati Workbook
// ExcelApp.Columns.ColumnWidth = 20;
// //Storing
// for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
// {
// ExcelApp.Cells[1, i] =
// dataGridView1.Columns[i - 1].HeaderText;
// }
// for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
// {
// for (int j = 0; j < dataGridView1.Columns.Count; j++)
// {
// ExcelApp.Cells[i + 2, j + 1] =
// dataGridView1.Rows[i].Cells[j].Value.ToString();
// }
// }
// ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString());
// ExcelApp.ActiveWorkbook.Saved = true;
// ExcelApp.Quit();
}
static void MainTask(string[] args)
{
// display usage if no arguments
if (args.Length == 0)
{
Console.WriteLine(Usage());
return;
}
var xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
Microsoft.Office.Interop.Excel._Workbook wbk = null;
Microsoft.Office.Interop.Excel.Range rng = null;
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
xlApp.Interactive = true;
// no-well established parsearg method
for (int i = 0; i < args.Length; i++)
{
var cmd = args[i];
while (cmd[0] == '-' || cmd[0] == '/')
{
cmd = cmd.Substring(1);
}
switch (cmd)
{
case "xlFileOpen":
case "xlFilePath":
var openPath = MakeFullPath(args[++i]);
Console.WriteLine($"> Open Workbook {openPath}");
if (wbk != null)
{
wbk.Close(SaveChanges: false);
}
wbk = xlApp.Workbooks.Open(openPath);
break;
case "xlFileNew":
Console.WriteLine($"> New Workbook");
if (wbk != null)
{
wbk.Close(SaveChanges: false);
}
wbk = xlApp.Workbooks.Add();
break;
case "xlFileSave":
Console.WriteLine($"> Save Workbook");
wbk.Save();
break;
case "xlFileSaveAs":
var savePath = MakeFullPath(args[++i]);
Console.WriteLine($"> Save Workbook as {savePath}");
wbk.SaveAs(savePath);
break;
case "xlEvalMacro":
var macro = args[++i];
Console.WriteLine($"> Evaluate macro {macro}");
xlApp.Evaluate(macro);
break;
case "xlRefreshLeftToRight":
Console.WriteLine($"> Refresh sheets left to right");
foreach (Microsoft.Office.Interop.Excel._Worksheet wsh in wbk.Worksheets)
{
if (wsh.Visible == XlSheetVisibility.xlSheetVisible)
{
wsh.Calculate();
}
}
break;
case "xlRngGet":
var rngGetAddr = args[++i];
Console.WriteLine($"> Get Range {rngGetAddr}");
rng = xlApp.Range[rngGetAddr];
Console.WriteLine(rng.Value);
break;
case "xlRngSet":
var rngSetAddr = args[++i];
var rngSetValue = args[++i];
Console.WriteLine($"> Set Range {rngSetAddr} {rngSetValue}");
rng = xlApp.Range[rngSetAddr];
rng.Value = rngSetValue;
break;
case "timeout":
i++;
break;
default:
throw new Exception($"> Unexpected command {cmd}");
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Environment.Exit(1);
}
finally
{
foreach (Microsoft.Office.Interop.Excel._Workbook wbk in xlApp.Workbooks)
{
wbk.Close(SaveChanges: false);
}
xlApp.Quit();
Console.WriteLine("Done.");
}
}
public Result Execute(
ExternalCommandData commandData,
ref string message,
ElementSet elements)
{
UIApplication uiapp = commandData.Application;
UIDocument uidoc = uiapp.ActiveUIDocument;
Application app = uiapp.Application;
Document doc = uidoc.Document;
// creating bfs document instance
BfsDocument bfsDocument = new BfsDocument(doc);
// collecting schedules data from Revit
List <List <List <string> > > schedulesData = getSchedulesData(doc);
List <List <List <string> > > revisionData = getRevisionData(doc);
if (schedulesData == null || revisionData == null)
{
return(Result.Cancelled);
}
// asking file name to save
System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog();
saveFileDialog.InitialDirectory = doc.PathName;
saveFileDialog.Title = "Salvar";
saveFileDialog.DefaultExt = ".xlsx";
saveFileDialog.Filter = "Excel file|*.xlsx";
saveFileDialog.FileName += string.Format("{0}-QTD-Rev.{1}", bfsDocument.Codigo, bfsDocument.Revisao);
saveFileDialog.OverwritePrompt = false;
if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
// creating export to excel instance
ExportToExcel exportToExcel = new ExportToExcel(
saveFileDialog.FileName,
"Planilha de quantidades",
schedulesData,
revisionData);
// solving export
exportToExcel.Export(ExportToExcel.TemplateType.QUANTIDADES, bfsDocument);
// running export() and getting the application created
Excel._Application oXL = exportToExcel.Application;
Excel._Workbook oWB = exportToExcel.Workbook;
oXL.UserControl = false;
try
{
// saving and closing
oWB.SaveAs(saveFileDialog.FileName, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("O arquivo a ser substituído está aberto.", "Erro");
return(Result.Failed);
}
oWB.Close();
oXL.Quit();
// launching the file
System.Diagnostics.Process.Start(saveFileDialog.FileName);
return(Result.Succeeded);
}
return(Result.Cancelled);
}
private void reportQuiz()
{
Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
StreamWriter streamWritertxt;
string MeanTime = MeanResponseTime().ToString();
string MeanTrueTime = MeanTrueAnswersResponseTime().ToString();
string MeanFalseTime = MeanFalseAnswersResponseTime().ToString();
if (dataTableQuizResults.Rows.Count != 0)
{
dataTableQuizResults.Rows[0]["Time"] = DBNull.Value;
dataTableQuizResults.Rows[0]["Accepted"] = DBNull.Value;
dataGridView1[3, 0].Style.BackColor = Color.White;
}
dataGridView1.EndEdit();
btnStartStop.Select(); // I did this to change control to s.th other than datagridview to have my changes accepted
tbxCorrectAnswers.Text = dataTableQuizResults.Compute("Count(Accepted)", "Accepted = TRUE").ToString();
// Set saveFileDialog1 Properties
saveFileDialog1.Filter = "Both Excel and Text Files (*.xls;*.txt)|*.*|Excel Files (*.xls)|*.*|Text Files (*.txt)|*.*";
saveFileDialog1.FilterIndex = 1;
try
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
if (saveFileDialog1.FilterIndex != 3)
{
// Instantiate Excel and start a new workbook.
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Quiz Properties
objSheet.Cells[1, 6] = "Quiz Type";
if (radioBtnBoth.Checked)
{
objSheet.Cells[1, 7] = "Both";
}
else if (radioBtnPASAT.Checked)
{
objSheet.Cells[1, 7] = "PASAT";
}
else
{
objSheet.Cells[1, 7] = "PVSAT";
}
objSheet.Cells[2, 6] = "Response Time Limit";
objSheet.Cells[2, 7] = numUpDnResponseTime.Value.ToString();
objSheet.Cells[3, 6] = "Automatic Query";
objSheet.Cells[3, 7] = ChkBxAutomatic.Checked.ToString();
// Patient's identification
objSheet.Cells[5, 6] = "Name";
objSheet.Cells[5, 7] = tbxName.Text;
objSheet.Cells[6, 6] = "Age";
objSheet.Cells[6, 7] = tbxAge.Text;
//Quiz Summary
objSheet.Cells[8, 6] = "Number of Correct Answers";
objSheet.Cells[8, 7] = tbxCorrectAnswers.Text;
objSheet.Cells[9, 6] = "Total Mean Time";
objSheet.Cells[9, 7] = MeanTime;
objSheet.Cells[10, 6] = "Correct Answers Mean Time";
objSheet.Cells[10, 7] = MeanTrueTime;
objSheet.Cells[11, 6] = "Incorrect Answers Mean Time";
objSheet.Cells[11, 7] = MeanFalseTime;
// Fill Column Headers
objSheet.Cells[1, 1] = dataTableQuizResults.Columns[0].ColumnName;
objSheet.Cells[1, 2] = dataTableQuizResults.Columns[1].ColumnName;
objSheet.Cells[1, 3] = dataTableQuizResults.Columns[2].ColumnName;
objSheet.Cells[1, 4] = dataTableQuizResults.Columns[3].ColumnName;
// Fill other Fields
for (int iRow = 0; iRow < dataTableQuizResults.Rows.Count; iRow++)
{
for (int iCol = 0; iCol < dataTableQuizResults.Columns.Count; iCol++)
{
objSheet.Cells[iRow + 2, iCol + 1] = dataTableQuizResults.Rows[iRow][iCol].ToString();
}
}
objSheet.Columns.AutoFit();
// Save Excel WorkBook
objBook.SaveAs(@saveFileDialog1.FileName + ".xls", Excel.XlFileFormat.xlWorkbookNormal, Missing.Value,
Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
objApp.Quit();
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
if (saveFileDialog1.FilterIndex != 2)
{
// Instantiate File Stream
streamWritertxt = File.CreateText(@saveFileDialog1.FileName + ".txt");
// Write Quiz Properties
streamWritertxt.WriteLine("Quiz Properties:");
if (radioBtnBoth.Checked)
{
streamWritertxt.WriteLine("Quiz Type\t" + "Both");
}
else if (radioBtnPASAT.Checked)
{
streamWritertxt.WriteLine("Quiz Type\t" + "PASAT");
}
else
{
streamWritertxt.WriteLine("Quiz Type\t" + "PVSAT");
}
streamWritertxt.WriteLine("Response Time Limit\t" + numUpDnResponseTime.Value.ToString());
streamWritertxt.WriteLine("Automatic Query\t" + ChkBxAutomatic.Checked.ToString());
streamWritertxt.WriteLine("---------------------------------------------------------");
streamWritertxt.WriteLine();
// Patient's identification
streamWritertxt.WriteLine("Name\t" + tbxName.Text);
streamWritertxt.WriteLine("Age\t" + tbxAge.Text);
streamWritertxt.WriteLine();
// //Quiz Summary
streamWritertxt.WriteLine("Number of Correct Answers\t" + tbxCorrectAnswers.Text);
streamWritertxt.WriteLine("Total Mean Time\t" + MeanTime);
streamWritertxt.WriteLine("Correct Answers Mean Time\t" + MeanTrueTime);
streamWritertxt.WriteLine("Incorrect Answers Mean Time\t" + MeanFalseTime);
streamWritertxt.WriteLine();
// Write Column Headers
streamWritertxt.WriteLine(dataTableQuizResults.Columns[0].ColumnName + "\t" +
dataTableQuizResults.Columns[1].ColumnName + "\t" + dataTableQuizResults.Columns[2].ColumnName +
"\t" + dataTableQuizResults.Columns[3].ColumnName);
// Fill other Fields
for (int iRow = 0; iRow < dataTableQuizResults.Rows.Count; iRow++)
{
for (int iCol = 0; iCol < dataTableQuizResults.Columns.Count; iCol++)
{
streamWritertxt.Write(dataTableQuizResults.Rows[iRow][iCol].ToString() + "\t");
}
streamWritertxt.WriteLine();
}
// Save Text File
streamWritertxt.Close();
}
}
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
public int CreateDataBase(string fileName, ProductParameter prdParm, ProductBatInfo prdBatInfo)
{
try
{
fName = fileName;
// Check if the file is existing
xls_exp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (System.IO.File.Exists(fileName))
{
xls_book = xls_exp.Workbooks.Open(fileName, Missing.Value,
false, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, true,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
if (xls_book != null)
{
// find the last index
xls_sheet = (Microsoft.Office.Interop.Excel._Worksheet)xls_book.Worksheets.get_Item(1);
int i = xls_sheet.UsedRange.Rows.Count;
if (i >= 9)
{
startIndex = 9;
}
else
{
startIndex = i + 1;
}
index = i + 1;
Close();
return(0);
}
}
// Create a new file
//xls_exp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xls_book = xls_exp.Workbooks.Add(true);
xls_sheet = (Microsoft.Office.Interop.Excel._Worksheet)xls_book.Worksheets.get_Item(1);
object misValue = System.Reflection.Missing.Value;
//xls_sheet.Cells[1, 1] = "客户编号";
//xls_sheet.Cells[1, 2] = prdBatInfo.customCode;
//xls_sheet.Cells[2, 1] = "随工单号";
//xls_sheet.Cells[2, 2] = prdBatInfo.suigongCode;
//xls_sheet.Cells[3, 1] = "操作员";
//xls_sheet.Cells[3, 2] = prdBatInfo.operatorCode;
//xls_sheet.Cells[4, 1] = "入纤功率";
//xls_sheet.Cells[4, 2] = prdBatInfo.ruQianPower.ToString() + "dBm";
//xls_sheet.Cells[5, 1] = "机器号";
//xls_sheet.Cells[5, 2] = prdParm.MachineCode;
//xls_sheet.Cells[6, 1] = "测试序号";
//xls_sheet.Cells[6, 2] = "VPP(MV)";
//xls_sheet.Cells[6, 3] = "耦合时间";
xls_sheet.Cells[6, 1] = "目标值(mV/uA)";
xls_sheet.Cells[6, 2] = prdParm.MinimumVpp.ToString();
xls_sheet.Cells[7, 1] = "生产信息";
xls_sheet.Cells[8, 1] = "Index";
xls_sheet.Cells[8, 2] = "产品编号";
xls_sheet.Cells[8, 3] = "Vpp(mV)/Iop(uA)";
xls_sheet.Cells[8, 4] = "Vbr(V)";
xls_sheet.Cells[8, 5] = "LX1(um)";
xls_sheet.Cells[8, 6] = "LY1(um)";
xls_sheet.Cells[8, 7] = "LZ(um)";
xls_sheet.Cells[8, 8] = "LX2(um)";
xls_sheet.Cells[8, 9] = "LY2(um)";
xls_sheet.Cells[8, 10] = "Coupling Time(S)";
xls_sheet.Cells[8, 11] = "Total Time(S)";
xls_sheet.Cells[8, 12] = "Date in producted";
xls_sheet.Cells[8, 13] = "Vpp/Iop_10V(mV/uA)";
xls_sheet.Cells[8, 14] = "Vpp/Iop_Check(mV/uA)";
xls_book.Saved = true;
xls_book.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xls_book.Save();
startIndex = 9;
index = 9;
Close();
}
catch (Exception ee)
{
Close();
error = ee.Message;
return(-1);
}
return(0);
}
/// <summary>
/// 导出数据到excel文件
/// </summary>
/// <param name="dt">要导出的数据集</param>
/// <returns>生成的文件名</returns>
static public string ExportToExcel(DataTable dt)
{
Excel.Application excelApp = null;
Excel.Workbooks wbks = null;
Excel._Workbook wbk = null;
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;//是打开不可见
wbks = excelApp.Workbooks;
wbk = wbks.Add(true);
System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog();
saveFileDialog.Title = "保存为";
saveFileDialog.Filter = "xls工作薄|*.xls|xlsx工作薄|*.xlsx";
String version = excelApp.Version; //获取你使用的excel 的版本号
int FormatNum; //保存excel文件的格式
if (Convert.ToDouble(version) < 12) //You use Excel 97-2003
{
{
FormatNum = -4143;
}
}
else//you use excel 2007 or later
{
{
FormatNum = 56;
}
}
object Nothing = Missing.Value;
Excel._Worksheet whs;
whs = (Excel._Worksheet)wbk.Sheets[1];//获取第一张工作表
whs.Activate();
//写入标题行
int rowIndex = 1;
for (int col = 0; col < dt.Columns.Count; col++)
{
whs.Cells[rowIndex, col + 1] = dt.Columns[col].Caption.ToString();
}
rowIndex++;
//写入数据内容
foreach (DataRow row in dt.Rows)
{
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
whs.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
rowIndex++;
}
excelApp.DisplayAlerts = false;
//保存excel文件
//wbk.SaveCopyAs(@"D:\test.xls");
string newFileName = string.Empty;
if (saveFileDialog.ShowDialog() == DialogResult.OK && saveFileDialog.FileName.Trim() != "")
{
newFileName = saveFileDialog.FileName;
wbk.SaveAs(newFileName, FormatNum);
System.Windows.Forms.MessageBox.Show("数据已经成功导入EXCEL文件" + newFileName, "数据导出", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
//关闭文件
wbk.Close(false, Nothing, Nothing);
return(newFileName);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.ToString());
return("EXPORT ERROR");
}
finally
{
//wbks.Close();//关闭工作簿
excelApp.Quit(); //关闭excel应用程序
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); //释放excel进程
excelApp = null;
}
}
public ActionResult DownloadMonthlyActiveBeneficiariesSummaryReport([Bind(Include = "SequenceID")] TrimesterIntervalViewModel trimesterIntervalDTO)
{
var fileName = @"~\Templates\SummaryReports\monthly-active-beneficiaries-summary.xlsx";
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook workbook = excelApp.Workbooks.Open(Server.MapPath(fileName));
//Excel._Worksheet worksheet = excelApp.ActiveSheet;
DateTime CurrentTrimesterInitialDate = trimesterQueryService.getTrimesterStartOrEndDateByID(trimesterIntervalDTO.SequenceID, "start");
DateTime CurrentTrimesterLastDate = trimesterQueryService.getTrimesterStartOrEndDateByID(trimesterIntervalDTO.SequenceID, "end");
string evaluationType = "ReportData";
// Worksheet 2, grouped by partner
Excel._Worksheet worksheet = excelApp.Worksheets[2];
worksheet.Range["AG2:AN2"].Value = "Período de " + CurrentTrimesterInitialDate.ToShortDateString() + " à " + CurrentTrimesterLastDate.ToShortDateString();
var row = 6;
var column = 0;
List <EFDataAccess.DTO.MonthlyActiveBeneficiariesSummaryReportDTO> dataPartner = summaryReports.getMonthlyActiveBeneficiariesSummaryPartner(CurrentTrimesterInitialDate, CurrentTrimesterLastDate);
foreach (var refType in dataPartner)
{
row++; column = 1;
foreach (var item in refType.PopulatedValues())
{
column++;
worksheet.Cells[row, column] = item;
}
worksheet.Rows[row + 1].Insert();
}
// Worksheet 1, grouped by head partner
worksheet = excelApp.Worksheets[1];
worksheet.Range["AG2:AN2"].Value = "Período de " + CurrentTrimesterInitialDate.ToShortDateString() + " à " + CurrentTrimesterLastDate.ToShortDateString();
row = 6;
column = 0;
List <EFDataAccess.DTO.MonthlyActiveBeneficiariesSummaryReportDTO> dataChiefPartner = summaryReports.getMonthlyActiveBeneficiariesSummaryChiefPartner(CurrentTrimesterInitialDate, CurrentTrimesterLastDate);
foreach (var refType in dataChiefPartner)
{
row++; column = 1;
foreach (var item in refType.PopulatedValues())
{
column++;
worksheet.Cells[row, column] = item;
}
worksheet.Rows[row + 1].Insert();
}
var temporaryFilepath = Path.ChangeExtension(Path.GetTempFileName(), ".xlsx");
workbook.SaveAs(temporaryFilepath);
workbook.Close(0);
excelApp.Quit();
DownloadXlsxFile(temporaryFilepath);
return(RedirectToAction("MonthlyActiveBeneficiariesSummaryReport"));
}
private void button1_Click(object sender, EventArgs e)
{
//int columnWidth = 0;
// Daten speichern
try
{
// creating Excel Application
Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Worksheets.get_Item(1);
worksheet = workbook.ActiveSheet;
// Set Orientation to Landscape
worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
// changing the name of active sheet
worksheet.Name = "Jahresrangliste";
// storing title in Excel
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, dataGridView1.Columns.Count]].Merge(false);
worksheet.Cells[1, 1] = label4.Text;
worksheet.Cells[1, 1].Font.Bold = true;
worksheet.Cells[1, 1].Font.Size = 20;
worksheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[1, 1].Interior.Color = ColorTranslator.ToOle(Color.Gray);
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, dataGridView1.Columns.Count]].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
if (i > 3 && i < dataGridView1.Columns.Count)
{
worksheet.Cells[3, i] = "Lauf " + (i - 3).ToString();
}
worksheet.Cells[2, i].Font.Bold = true;
worksheet.Cells[2, i].Font.Size = 12;
worksheet.Cells[2, i].Interior.Color = ColorTranslator.ToOle(Color.LightGray);
worksheet.Cells[2, i].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
if (i > 3 && i < dataGridView1.Columns.Count)
{
worksheet.Cells[2, i] = dataGridView1.Columns[i - 1].HeaderText;
}
else
{
worksheet.Cells[3, i] = dataGridView1.Columns[i - 1].HeaderText;
}
worksheet.Cells[3, i].Font.Bold = true;
worksheet.Cells[3, i].Interior.Color = ColorTranslator.ToOle(Color.LightGray);
worksheet.Cells[3, i].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
try
{
worksheet.Cells[i + 4, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
catch //Fängt Ausnahmen ab wenn Zellen leer sind
{
}
worksheet.Cells[i + 4, j + 1].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
}
}
worksheet.Range[worksheet.Cells[dataGridView1.Rows.Count + 4, 1], worksheet.Cells[dataGridView1.Rows.Count + 4, dataGridView1.Columns.Count]].Merge(false);
worksheet.Cells[dataGridView1.Rows.Count + 4, 1] = DateTime.Today.ToString("d");
worksheet.Cells[dataGridView1.Rows.Count + 4, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
// Set column width
worksheet.UsedRange.Columns.AutoFit();
// for(int j = 1; j < dataGridView1.Columns.Count; j++)
// {
// if(columnWidth < worksheet.Cells[3,j].width) columnWidth = (int)worksheet.Cells[3, j].width;
// }
//worksheet.UsedRange.ColumnWidth = 15;
// save the application
workbook.SaveAs(path + "\\" + jahr + "_" + klasse + "_0_Jahresrangliste" + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// export PDF
worksheet.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, path + "\\" + jahr + "_" + klasse + "_0_Jahresrangliste" + ".pdf");
// Exit from the application
app.Quit();
}
catch
{
}
}
protected byte[] getPhoneCalls()
{
string date = "";
if (RequestArr != null && RequestArr.Length > 0)
{
foreach (string req_str in RequestArr)
{
string[] p = req_str.Split("=".ToCharArray());
if (p[0] == "date")
{
date = p[1].Replace("%20", " ");
}
}
}
if (date == "")
{
date = DateTime.Today.ToString();
}
//DateTime dt = Convert.ToDateTime(date);
DB db = new DB();
List <GetPhoneCallsData> phoneCalls = db.getPhoneCalls(date);
if (data_type == "xml")
{
string answer = "";
Log.add("Формируем xml файл GetPhoneCalls");
ContentType = "xml";
// Create XML
answer += "<phone_calls>";
foreach (GetPhoneCallsData phoneCall in phoneCalls)
{
answer += "<phone_call>";
if (!String.IsNullOrEmpty(phoneCall.CompanyId.ToString()))
{
answer += "<CompanyId>" + phoneCall.CompanyId.ToString() + "</CompanyId>";
}
if (!String.IsNullOrEmpty(phoneCall.CompanyName))
{
answer += "<CompanyName>" + phoneCall.CompanyName + "</CompanyName>";
}
if (!String.IsNullOrEmpty(phoneCall.PhoneCallId.ToString()))
{
answer += "<PhoneCallId>" + phoneCall.PhoneCallId.ToString() + "</PhoneCallId>";
}
if (!String.IsNullOrEmpty(phoneCall.PhoneCallNr))
{
answer += "<PhoneCallNr>" + phoneCall.PhoneCallNr + "</PhoneCallNr>";
}
if (!String.IsNullOrEmpty(phoneCall.Pin))
{
answer += "<Pin>" + phoneCall.Pin + "</Pin>";
}
if (!String.IsNullOrEmpty(phoneCall.CustomerName))
{
answer += "<CustomerName>" + phoneCall.CustomerName + "</CustomerName>";
}
if (!String.IsNullOrEmpty(phoneCall.Inn))
{
answer += "<Inn>" + phoneCall.Inn + "</Inn>";
}
if (!String.IsNullOrEmpty(phoneCall.Kpp))
{
answer += "<Kpp>" + phoneCall.Kpp + "</Kpp>";
}
if (!String.IsNullOrEmpty(phoneCall.Email))
{
answer += "<Email>" + phoneCall.Email + "</Email>";
}
if (!String.IsNullOrEmpty(phoneCall.BitrixId))
{
answer += "<BitrixId>" + phoneCall.BitrixId + "</BitrixId>";
}
if (!String.IsNullOrEmpty(phoneCall.ScheduledStart.ToString()))
{
answer += "<ScheduledStart>" + phoneCall.ScheduledStart.ToString() + "</ScheduledStart>";
}
if (!String.IsNullOrEmpty(phoneCall.Overview))
{
answer += "<Overview>" + phoneCall.Overview + "</Overview>";
}
if (!String.IsNullOrEmpty(phoneCall.Priority.ToString()))
{
answer += "<Priority>" + phoneCall.Priority.ToString() + "</Priority>";
}
if (!String.IsNullOrEmpty(phoneCall.EventNr.ToString()))
{
answer += "<EventNr>" + phoneCall.EventNr.ToString() + "</EventNr>";
}
if (!String.IsNullOrEmpty(phoneCall.DateEvent.ToString()))
{
answer += "<DateEvent>" + phoneCall.DateEvent.ToString() + "</DateEvent>";
}
if (!String.IsNullOrEmpty(phoneCall.ProductEvent))
{
answer += "<ProductEvent>" + phoneCall.ProductEvent + "</ProductEvent>";
}
if (!String.IsNullOrEmpty(phoneCall.Phone))
{
answer += "<Phone>" + phoneCall.Phone + "</Phone>";
}
if (!String.IsNullOrEmpty(phoneCall.EventName))
{
answer += "<EventName>" + phoneCall.EventName + "</EventName>";
}
if (!String.IsNullOrEmpty(phoneCall.TimeDifference.ToString()))
{
answer += "<TimeDifference>" + phoneCall.TimeDifference.ToString() + "</TimeDifference>";
}
answer += "</phone_call>";
}
answer += "</phone_calls>";
return(Encoding.UTF8.GetBytes(answer));
}
if (data_type == "xls")
{
var excelApp = new Excel.Application();
excelApp.Visible = false;
Excel._Workbook workBook = excelApp.Workbooks.Add();
Excel._Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
byte[] answer;
Log.add("Формируем xls файл GetPhoneCalls");
ContentType = "xls";
//delete old xls file
if (File.Exists("phone_calls.xls"))
{
File.Delete("phone_calls.xls");
}
workSheet.Cells[1, "A"] = "ID Компании";
workSheet.Cells[1, "B"] = "Наименование кампании";
workSheet.Cells[1, "C"] = "ИД звонка";
workSheet.Cells[1, "D"] = "№ звонка";
workSheet.Cells[1, "E"] = "Пин клиента";
workSheet.Cells[1, "F"] = "Наименование клиента";
workSheet.Cells[1, "G"] = "ИНН";
workSheet.Cells[1, "H"] = "КПП";
workSheet.Cells[1, "I"] = "E-mail";
workSheet.Cells[1, "J"] = "BitrixId";
workSheet.Cells[1, "K"] = "Назначенная дата звонка";
workSheet.Cells[1, "L"] = "Презентация";
workSheet.Cells[1, "M"] = "Приоритет";
workSheet.Cells[1, "N"] = "№ события";
workSheet.Cells[1, "O"] = "Дата события";
workSheet.Cells[1, "P"] = "Продукт по событию";
workSheet.Cells[1, "Q"] = "Телефон";
workSheet.Cells[1, "R"] = "Событие";
workSheet.Cells[1, "S"] = "Часовая разница";
// Start from 1 col
int i = 2;
// Write data to xls
foreach (GetPhoneCallsData phoneCall in phoneCalls)
{
if (!String.IsNullOrEmpty(phoneCall.CompanyId.ToString()))
{
workSheet.Cells[i, "A"] = phoneCall.CompanyId.ToString();
}
if (!String.IsNullOrEmpty(phoneCall.CompanyName))
{
workSheet.Cells[i, "B"] = phoneCall.CompanyName;
}
if (!String.IsNullOrEmpty(phoneCall.PhoneCallId.ToString()))
{
workSheet.Cells[i, "C"] = phoneCall.PhoneCallId.ToString();
}
if (!String.IsNullOrEmpty(phoneCall.PhoneCallNr))
{
workSheet.Cells[i, "D"] = phoneCall.PhoneCallNr;
}
if (!String.IsNullOrEmpty(phoneCall.Pin))
{
workSheet.Cells[i, "E"] = phoneCall.Pin;
}
if (!String.IsNullOrEmpty(phoneCall.CustomerName))
{
workSheet.Cells[i, "F"] = phoneCall.CustomerName;
}
if (!String.IsNullOrEmpty(phoneCall.Inn))
{
workSheet.Cells[i, "G"] = phoneCall.Inn;
}
if (!String.IsNullOrEmpty(phoneCall.Kpp))
{
workSheet.Cells[i, "H"] = phoneCall.Kpp;
}
if (!String.IsNullOrEmpty(phoneCall.Email))
{
workSheet.Cells[i, "I"] = phoneCall.Email;
}
if (!String.IsNullOrEmpty(phoneCall.BitrixId))
{
workSheet.Cells[i, "J"] = phoneCall.BitrixId;
}
if (!String.IsNullOrEmpty(phoneCall.ScheduledStart.ToString()))
{
workSheet.Cells[i, "K"] = phoneCall.ScheduledStart.ToString();
}
if (!String.IsNullOrEmpty(phoneCall.Overview))
{
workSheet.Cells[i, "L"] = phoneCall.Overview;
}
if (!String.IsNullOrEmpty(phoneCall.Priority.ToString()))
{
workSheet.Cells[i, "M"] = phoneCall.Priority.ToString();
}
if (!String.IsNullOrEmpty(phoneCall.EventNr.ToString()))
{
workSheet.Cells[i, "N"] = phoneCall.EventNr.ToString();
}
if (!String.IsNullOrEmpty(phoneCall.DateEvent.ToString()))
{
workSheet.Cells[i, "O"] = phoneCall.DateEvent.ToString();
}
if (!String.IsNullOrEmpty(phoneCall.ProductEvent))
{
workSheet.Cells[i, "P"] = phoneCall.ProductEvent;
}
if (!String.IsNullOrEmpty(phoneCall.Phone))
{
workSheet.Cells[i, "Q"] = phoneCall.Phone;
}
if (!String.IsNullOrEmpty(phoneCall.EventName))
{
workSheet.Cells[i, "R"] = phoneCall.EventName;
}
if (!String.IsNullOrEmpty(phoneCall.TimeDifference.ToString()))
{
workSheet.Cells[i, "S"] = phoneCall.TimeDifference.ToString();
}
i++;
}
// Write xls
Random rnd = new Random();
string fname = Application.StartupPath + @"\phone_calls_" + rnd.Next(1000, 9000).ToString() + ".xlsx";
workBook.SaveAs(fname, Excel.XlFileFormat.xlOpenXMLWorkbook);
workBook.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
Marshal.FinalReleaseComObject(workSheet);
Marshal.FinalReleaseComObject(workBook);
Marshal.FinalReleaseComObject(excelApp);
//workbook.SaveToFile("phone_calls.xls");
// Read xls
answer = File.ReadAllBytes(fname);
// Delete xls
if (File.Exists(fname))
{
File.Delete(fname);
}
//send xls to client
return(answer);
}
return(Encoding.UTF8.GetBytes(""));
}
public static void Open_xls_Files()
{
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\SPA特价.xlsx");
//app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = workbook.Sheets[1];
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["SPA台湾"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Copy(Type.Missing, worksheet);
Microsoft.Office.Interop.Excel.Worksheet wcSheet = workbook.Sheets[2];
wcSheet.Name = "ExSPA台湾";
wcSheet.UsedRange.UnMerge();
Console.WriteLine(wcSheet.UsedRange.Rows.Count);
Range Ro1 = null;
Range Rd1 = null;
Range Ro2 = null;
Range Rd2 = null;
Range tempR = null;
for (int i = 1; i <= wcSheet.UsedRange.Rows.Count; i++)
{
try
{
string s = wcSheet.UsedRange.Cells[i, 1].Value2.ToString();
Console.WriteLine(s);
if (wcSheet.Cells[i, 1].Value2 != null)//DBNull)
{
if (s == "SPA: ")
{
Ro1 = wcSheet.Range[wcSheet.Cells[i, 1], wcSheet.Cells[i, 9]];
// Console.WriteLine("=============="+wcSheet.Cells[i, 1].Value2.ToString());
Rd1 = wcSheet.Range[wcSheet.Cells[i + 2, 10], wcSheet.Cells[i + 2, 19]];
Ro1.Cut(Rd1);
/////////////////////////////////////
Ro2 = wcSheet.Range[wcSheet.Cells[i + 1, 1], wcSheet.Cells[i + 1, 9]];
// Console.WriteLine(Ro.Cells.Count);
Rd2 = wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]];
Ro2.Cut(Rd2);
// Console.WriteLine(wcSheet.UsedRange.Cells[i,1]);
i = i + 4;
}
//Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]);
tempR = wcSheet.Range[wcSheet.Cells[i, 10], wcSheet.Cells[i, 19]];
//= //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 3, 10], wcSheet.Cells[i + 3, 19]]);
Ro2.Copy(tempR);
// tempR.Clear();
// Ro2.Clear();
}
else if (wcSheet.Cells[i, 1].Value == "")
{
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
//for (int i = 1; i <= wcSheet.UsedRange.Rows.Count; i++)
//{
// try
// {
// string s = wcSheet.UsedRange.Cells[i, 1].Value2.ToString();
// Console.WriteLine(s);
// if (wcSheet.Cells[i, 1].Value2 != null)//DBNull)
// {
// if (s == "SPA: ")
// {
// /////////////////////////////////////
// // Ro2 = wcSheet.Range[wcSheet.Cells[i + 1, 1], wcSheet.Cells[i + 1, 9]];
// // Console.WriteLine(Ro.Cells.Count);
// // Rd2 = wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]];
// // Ro2.Cut(Rd2);
// // Console.WriteLine(wcSheet.UsedRange.Cells[i,1]);
// Ro1 = wcSheet.Range[wcSheet.Cells[i, 1], wcSheet.Cells[i, 9]];
// // Console.WriteLine(Ro.Cells.Count);
// Rd1 = wcSheet.Range[wcSheet.Cells[i + 2, 10], wcSheet.Cells[i + 2, 19]];
// Ro1.Cut (Rd1);
// }
// //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]);
// // tempR = wcSheet.Range[wcSheet.Cells[i, 10], wcSheet.Cells[i, 19]];
// //= //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 3, 10], wcSheet.Cells[i + 3, 19]]);
// // Ro2.Copy(tempR);
// }
// }
// catch (Exception ex)
// {
// Console.WriteLine(ex.Message);
// }
//}
//// storing header part in Excel
////for (int i = 1; i < this.DGV1.Columns.Count + 1; i++)
////{
//// worksheet.Cells[1, i] = DGV1.Columns[i - 1].Header;//.HeaderText;
// // worksheet.Columns.ColumnWidth
//}
//// storing Each row and column value to excel sheet
//for (int i = 0; i < DGV1.Items.Count - 1; i++)
//{
// for (int j = 0; j < DGV1.Columns.Count; j++)
// {
// // var drview= DGV1.Items[i];
// // worksheet.Cells[i + 2, j + 1] = .Cells[j].Value.ToString();
// // System.Data.DataRowView dataRow = drview;
// DataRowView dataRow = (DataRowView)DGV1.Items[i];//.Row.//.Row;
// worksheet.Cells[i + 2, j + 1] = dataRow[j].ToString();//dataRow.Row..ToString();
// // worksheet.Cells[i + 2, j + 1]
// //MessageBox.Show(DGV1.Items[i].ToString());
// }
//}
// save the application
// Microsoft.Office.Interop.Excel.Worksheet ws = app.ActiveWorkbook.Worksheets[1];
// Microsoft.Office.Interop.Excel.Range range = ws.UsedRange;
// ws.Columns.AutoFit();
// ws.Rows.AutoFit();
try
{
Fname = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\temp" + DateTime.Now.ToString().Trim().Replace(':', '_').Replace('/', '_').Trim() + ".xlsx";
workbook.SaveAs(Fname, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
// workbook.SaveCopyAs(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\列宽output.xls");
}
catch (Exception es)
{
// MessageBox.Show(es.Message);
Console.WriteLine(es.Message);
}
// workbook.Saved = true;
app.Quit();
Marshal.ReleaseComObject(app);
Console.WriteLine("excel文件导出到当前app运行目录下，命名为output.xls,Excel将关闭退出~");
}
public void SaveResult(string path = @"D:\GoogleDrive\Roslesinforg\Дела\2020.07.14 - Ц\ОСВ 205.31 - result2.xlsx")
{
workbook.SaveAs(path);
workbook.Close();
CloseApp();
}
private void exportDGV()
{
// Creating a Excel object.
Excel._Application excel = new Excel.Application();
Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Excel._Worksheet worksheet = null;
Excel.Range chartRange;
int wrkRow = 0;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
//Add or subtract from the count to determine which portion of the datagrid view is exported
for (int i = 0; i < dgvDailyReport.Columns.Count - 1; i++)
{
worksheet.Cells[1, i + 1] = dgvDailyReport.Columns[i].HeaderText;
}
chartRange = worksheet.get_Range("a1", "l1");
chartRange.Font.Bold = true;
chartRange.Font.Size = 15;
chartRange.Columns.AutoFit();
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
//Loop through each row and read value from each column.
for (int worksheetrow = 2, ddgrow = 0; ddgrow < dgvDailyReport.Rows.Count; worksheetrow++, ddgrow++)
{
//Add or subtract from the count to determine which portion of the datagrid view is exported, this should most likely match to the above one
for (int dgvcolumn = 0; dgvcolumn < dgvDailyReport.Columns.Count - 1; dgvcolumn++)
{
worksheet.Cells[worksheetrow, dgvcolumn + 1] = dgvDailyReport.Rows[ddgrow].Cells[dgvcolumn].Value.ToString();
if (ddgrow == dgvDailyReport.Rows.Count - 1)
{
if (dgvcolumn < 6)
{
worksheet.Cells[worksheetrow, dgvcolumn + 1] = dgvDailyReport.Rows[ddgrow].Cells[dgvcolumn].Value.ToString();
}
else
{
break;
}
}
}
wrkRow = worksheetrow;
}
//gets range of cells and autosizes the data columns
string cell2 = "l" + wrkRow.ToString();
chartRange = worksheet.get_Range("a2", cell2);
chartRange.Font.Size = 11;
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PeachPuff);
//Puts a border around the actual data
chartRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
//Totals Row Changes
cell2 = "l" + wrkRow.ToString();
string cell1 = "a" + wrkRow.ToString();
chartRange = worksheet.get_Range(cell1, cell2);
//Borders to separate and organize the totals row
chartRange = worksheet.get_Range("b" + wrkRow.ToString(), "c" + wrkRow.ToString());
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Peru);
chartRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = worksheet.get_Range("d" + wrkRow.ToString(), "e" + wrkRow.ToString());
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Salmon);
chartRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = worksheet.get_Range("f" + wrkRow.ToString(), "g" + wrkRow.ToString());
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSalmon);
chartRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
saveDialog.FileName = DateTime.Now.ToString("yyyy-MM-dd hh_mm") + " " + "Daily Report.xlsx";
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void button_Click(object sender, RoutedEventArgs e)
{
for (int j = 0; j < ExcelData.ExcelDataValuesCumulative.Count(); j++)
{
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//set the middle column width as it acts a delimiter column only
oSheet.Columns["C:C"].ColumnWidth = 2;
oSheet.Columns["A:A"].ColumnWidth = 23;
oSheet.Columns["B:B"].ColumnWidth = 23;
oSheet.Columns["D:D"].ColumnWidth = 23;
oSheet.Columns["E:E"].ColumnWidth = 23;
oSheet.Rows.RowHeight = 22.5;
oSheet.Rows.WrapText = true;
#region RowWidth For Special ROws
//Included rows - DOB, Email, Immigration History Table Headers
oSheet.Rows[5].RowHeight = 30;
oSheet.Rows[7].RowHeight = 30;
oSheet.Rows[38].RowHeight = 30;
oSheet.Rows[44].RowHeight = 30;
#endregion
#region Labels Only Include text bold code
//Header Code
oSheet.Cells[1, 1] = "General Information Sheet";
oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[1, 5]].Merge();
oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[1, 5]].HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//Last Name and First Name Code - Row 2
oSheet.Cells[2, 1] = "Last Name";
oSheet.Cells[2, 4] = "First Name";
//Middle Name and CWID Code - Row 3
oSheet.Cells[3, 1] = "Middle Name";
oSheet.Cells[3, 4] = "Banner ID (Student ID)";
//Visa Type and SEVIS ID Code - Row 4
oSheet.Cells[4, 1] = "Visa Type";
oSheet.Cells[4, 4] = "SEVIS ID";
//DOB and COB Code - Row 5
oSheet.Cells[5, 1] = "Date Of Birth (DD-MMM-YYYY)";
oSheet.Cells[5, 4] = "Country Of Birth";
//Gender and COC Code - Row 6
oSheet.Cells[6, 1] = "Gender";
oSheet.Cells[6, 4] = "Country Of Citizenship";
//OSU Email Address and Alternate Email Address Code - Row 7
oSheet.Cells[7, 1] = "OSU Email Address";
oSheet.Cells[7, 4] = "Alternate Email Address";
//Row 8 will be empty
//Home Country Address Heading Only - Row 9
oSheet.Cells[9, 1] = "Home Country Address";
oSheet.Range[oSheet.Cells[9, 1], oSheet.Cells[9, 5]].Merge();
//Street Address 1 - Row 10
oSheet.Cells[10, 1] = "Street Address 1";
oSheet.Range[oSheet.Cells[10, 2], oSheet.Cells[10, 5]].Merge();
//Street Address 2 - Row 11
oSheet.Cells[11, 1] = "Street Address 2";
oSheet.Range[oSheet.Cells[11, 2], oSheet.Cells[11, 5]].Merge();
//City and State - Row 12
oSheet.Cells[12, 1] = "City";
oSheet.Cells[12, 4] = "State";
//Country and Zip Code - Row 13
oSheet.Cells[13, 1] = "Country";
oSheet.Cells[13, 4] = "Zip Code";
//Row 14 will be empty
//Local/Temporary Address Heading Only Row 15
oSheet.Cells[15, 1] = "Local/Temporary Address";
oSheet.Range[oSheet.Cells[15, 1], oSheet.Cells[15, 5]].Merge();
//Street Address 1 - Row 16
oSheet.Cells[16, 1] = "Street Address 1";
oSheet.Range[oSheet.Cells[16, 2], oSheet.Cells[16, 5]].Merge();
//Street Address 2 - Row 17
oSheet.Cells[17, 1] = "Street Address 2";
oSheet.Range[oSheet.Cells[17, 2], oSheet.Cells[17, 5]].Merge();
//City and State - Row 18
oSheet.Cells[18, 1] = "City";
oSheet.Cells[18, 4] = "State";
//Country and Zip Code - Row 19
oSheet.Cells[19, 1] = "Country";
oSheet.Cells[19, 4] = "Zip Code";
//Local Phone - Row 20
oSheet.Cells[20, 1] = "Local Phone\n(Leave blank if you don't have a local phone now but update ISS once you do)";
oSheet.Range[oSheet.Cells[20, 1], oSheet.Cells[20, 4]].Merge();
oSheet.Range[oSheet.Cells[20, 1], oSheet.Cells[20, 4]].RowHeight = 27.75;
//Row 21 will be empty
//Emergency Contact Information Heading Only Row 22
oSheet.Cells[22, 1] = "Emergency Contact Information";
oSheet.Range[oSheet.Cells[22, 1], oSheet.Cells[22, 5]].Merge();
oSheet.Range[oSheet.Cells[22, 1], oSheet.Cells[22, 5]].HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//Explanation Text Row 23
oSheet.Cells[23, 1] = "Family Member, Friend or relative in the U.S. the ISS office should contact on your behalf in case of medical emergency (such as illness or accident). Please fill as much information as you can and update the ISS office with any changes";
oSheet.Range[oSheet.Cells[23, 1], oSheet.Cells[23, 5]].Merge();
oSheet.Range[oSheet.Cells[23, 1], oSheet.Cells[23, 5]].RowHeight = 45;
//Row 24 will be empty
//Name and Relationship Row 25
oSheet.Cells[25, 1] = "Name";
oSheet.Cells[25, 4] = "Relationship";
//Street Address 1 - Row 26
oSheet.Cells[26, 1] = "Street Address 1";
//Street Address 2 - Row 27
oSheet.Cells[27, 1] = "Street Address 2";
//City and State - Row 28
oSheet.Cells[28, 1] = "City";
oSheet.Cells[28, 4] = "State";
//Country and Zip Code - Row 29
oSheet.Cells[29, 1] = "Zip Code";
//Phone and Email Address - Row 30
oSheet.Cells[30, 1] = "Phone";
oSheet.Cells[30, 4] = "Email Address";
//Row 31 is empty
//Row 32 is empty
//Row 33 is empty
//Previous semester field Row 34
oSheet.Cells[34, 1] = "If you previously attended OSU enter the last semester you attended(Eg. Fall 2016)";
oSheet.Range[oSheet.Cells[34, 1], oSheet.Cells[34, 4]].Merge();
//Row 35 is empty
//Previously entered US heading Row 36
oSheet.Cells[36, 1] = "If you previously entered the U.S. provide information below:";
oSheet.Range[oSheet.Cells[36, 1], oSheet.Cells[36, 5]].Merge();
//Row 37 is empty
//Approximate Date of Entry, Approximate Date of Exit, Immigration Status E.g. F1, F2 etc, Primary Activity E.g. High School, Exchange etc - Row 38
oSheet.Cells[38, 1] = "Approximate Date of Entry";
oSheet.Cells[38, 2] = "Approximate Date of Exit";
oSheet.Cells[38, 4] = "Immigration Status E.g. F1, F2 etc";
oSheet.Cells[38, 5] = "Primary Activity E.g. High School, Exchange etc";
//Row 39, 40, 41, 42, 43 is empty
//Attestation Statement Row 44
oSheet.Cells[44, 1] = "I attest the information provided here is accurate";
oSheet.Range[oSheet.Cells[44, 1], oSheet.Cells[44, 5]].Merge();
//Signature and Date Row 45
oSheet.Cells[45, 1] = "Signature";
oSheet.Cells[45, 4] = "Date";
//Row 46, 47, 48, 49, 50 will be empty
//For ISS use only heading Row 51
oSheet.Cells[51, 1] = "For ISS use only";
//Row 52 will be empty
//Reviewed By and Date - Row 53
oSheet.Cells[53, 1] = "Reviewed By";
oSheet.Cells[53, 4] = "Date";
#endregion
#region Bold Text for Labels
//Make the labels bold
for (int i = 1; i <= 53; i++)
{
oSheet.Cells[i, 1].Font.Bold = true;
oSheet.Cells[i, 4].Font.Bold = true;
}
//left out cells that are not part of for loop
oSheet.Cells[38, 2].Font.Bold = true;
oSheet.Cells[38, 5].Font.Bold = true;
//underline the cells
oSheet.Cells[1, 1].Font.Underline = true;
oSheet.Cells[22, 1].Font.Underline = true;
#endregion
#region Underline and Borders For Values
for (int i = 2; i <= 45; i++)
{
//draw underlines for Street Address Fields
if (i == 10 || i == 11 || i == 16 || i == 17 || i == 26 || i == 27)
{
oSheet.Cells[i, 2].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oSheet.Cells[i, 3].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oSheet.Cells[i, 4].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oSheet.Cells[i, 5].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
else if (i == 34 || i == 20)
{
oSheet.Cells[i, 5].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
continue;
}
//Blank Fields - Don't draw underlines
else if (i == 8 || i == 9 || i == 10 || i == 14 || i == 15 || i == 20 || i == 21 || i == 22 || i == 23 || i == 24 || i == 31 || i == 32 || i == 33 || i == 34 || i == 35 || i == 36 || i == 37 || i == 38 || i == 39 || i == 40 || i == 41 || i == 42 || i == 43 || i == 44)
{
continue;
}
//Label Value Fields need single cell underlines
oSheet.Cells[i, 2].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oSheet.Cells[i, 5].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
//Border for Immigration History
oRng = oSheet.get_Range("A36", "E43");
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oRng = oSheet.get_Range("A36", "E43");
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//Outside only Borders for ISS Signature
oRng = oSheet.get_Range("A51", "E53");
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble;
oRng.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble;
#endregion
#region Values From Excel Data
ArrayList al = ExcelData.ExcelDataValuesCumulative.ElementAt(j);
oSheet.Cells[2, 2] = al[0];
oSheet.Cells[2, 5] = al[1];
oSheet.Cells[3, 2] = al[2];
oSheet.Cells[3, 5] = al[3];
oSheet.Cells[4, 2] = al[4];
oSheet.Cells[4, 5] = al[5];
oRng = oSheet.get_Range("B5");
oRng.NumberFormat = "DD-MMM-YYYY";
oSheet.Cells[5, 2] = al[6];
oSheet.Cells[5, 5] = al[7];
oSheet.Cells[6, 2] = al[8];
oSheet.Cells[6, 5] = al[9];
oSheet.Cells[7, 2] = al[10];
oSheet.Cells[7, 5] = al[11];
oSheet.Cells[10, 2] = al[12];
oSheet.Cells[11, 2] = al[13];
oSheet.Cells[12, 2] = al[14];
oSheet.Cells[12, 5] = al[15];
oSheet.Cells[13, 2] = al[16];
oSheet.Cells[13, 5] = al[17];
var admitTerm = al[18];
//Extract year and semester from it
var year = admitTerm.ToString().Substring(0, 4);
var term = admitTerm.ToString().Substring(4);
string termText = "";
if (term == "20")
{
termText = "Spring";
}
else if (term == "40")
{
termText = "Summer";
}
else if (term == "60")
{
termText = "Fall";
}
oSheet.Cells[1, 1] = "General Information Sheet (" + termText + " " + year + ")";
//Adding Barcode Image to sheet
Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[55, 2];
b.IncludeLabel = true;
System.Drawing.Image img = b.Encode(BarcodeLib.TYPE.CODE39, al[3].ToString());
img.Save("C:\\images\\" + al[3] + ".jpg");
float Left = (float)((double)oRange.Left);
float Top = (float)((double)oRange.Top);
const float imageWidth = 327;
const float imageHeight = 139;
oSheet.Shapes.AddPicture("C:\\images\\" + al[3] + ".jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, imageWidth, imageHeight);
#endregion
string fileName = al[0] + ", " + al[1];
// oSheet.Cells[56, 4] = al[3];
// oRange = oSheet.Cells[56, 4];
//oRange.Cells.Font.Size = 7;
oSheet.PageSetup.Zoom = 92;
oSheet.PageSetup.FitToPagesWide = 1;
oWB.SaveAs(textBox1.Text + "\\" + fileName + ".xlsx");
oWB.Close();
oXL.Quit();
}
catch (Exception ex)
{
}
}
MessageBox.Show("Process Completed Successfully");
}
public void ExcelCreate(String path, int day, Dictionary <int, String> em)
{
if (File.Exists(path))
{
excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.DisplayAlerts = false;
wBook = excelapp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
DateTime date = DateTime.Now;
Calendar cal = dfi.Calendar;
bool found = false;
foreach (Excel.Worksheet sheet in wBook.Sheets)
{
if (sheet.Name == ("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek)))
{
found = true;
}
}
if (found == true)
{
wSheet = wBook.Sheets[("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek))];
wSheet.Activate();
}
else
{
Excel.Worksheet newWorksheet;
newWorksheet = (Excel.Worksheet)wBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorksheet.Name = ("week" + cal.GetWeekOfYear(date, dfi.CalendarWeekRule, dfi.FirstDayOfWeek));
wSheet = newWorksheet;
wSheet.Activate();
NameTagInit(excelapp);
}
Excel.Range rng = excelapp.Range[excelapp.Cells[1, day], excelapp.Cells[em.Count + 2, day]];
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Empty);
excelapp.get_Range("A1", "J99").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelapp.Cells[1, day] = DateTime.Now.ToString("ddd");
//1,2 = 週一，1,3 = 週二 ...
for (int i = 2; i < em.Count + 2; i++)
{
if (em.ElementAt(i - 2).Value.Equals("出席"))
{
excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = em.ElementAt(i - 2).Value;
}
else if (em.ElementAt(i - 2).Value.Equals("遲到"))
{
Excel.Range rang = excelapp.Range[excelapp.Cells[em.ElementAt(i - 2).Key + 1, day], excelapp.Cells[em.ElementAt(i - 2).Key + 1, day]];
rang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = "出席";
}
else
{
Excel.Range rang = excelapp.Range[excelapp.Cells[em.ElementAt(i - 2).Key + 1, day], excelapp.Cells[em.ElementAt(i - 2).Key + 1, day]];
rang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
excelapp.Cells[em.ElementAt(i - 2).Key + 1, day] = em.ElementAt(i - 2).Value;
}
}
try
{
wBook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Console.WriteLine("儲存文件於 " + Environment.NewLine + path);
}
catch
{
MessageBox.Show("儲存檔案出錯，檔案可能正在使用", "錯誤:檔案無法儲存", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
wBook.Close(false, Type.Missing, Type.Missing);
excelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp);
wBook = null;
wSheet = null;
excelapp = null;
GC.Collect();
Console.Read();
}
else
{
MessageBox.Show("檔案不存在，請確認路徑", "錯誤:路徑", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ExportBtn_Click(object sender, EventArgs e)
{
if (Table.Rows.Count > 0)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = @"Excel (.xlsx)| *.xlsx";
sfd.FileName = "Output.xlsx";
bool fileError = false;
if (sfd.ShowDialog() == DialogResult.OK)
{
if (File.Exists(sfd.FileName))
{
try
{
File.Delete(sfd.FileName);
}
catch (IOException ex)
{
fileError = true;
MessageBox.Show(Messages.ImpossibleToWriteData + ex.Message);
}
}
if (!fileError)
{
try
{
Excel.Application xcelApp = new Excel.Application();
Excel._Workbook workbook = xcelApp.Workbooks.Add(Type.Missing);
//worksheet = (Excel._Worksheet) workbook.Sheets["Sheet1"];
var worksheet = (Excel._Worksheet)workbook.ActiveSheet;
worksheet.Name = "Output";
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
for (int i = 0; i < Table.Rows.Count; i++)
{
for (int j = 0; j < Table.Columns.Count; j++)
{
if (Table.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 1, j + 1] = Table.Rows[i].Cells[j].Value.ToString();
}
}
}
worksheet.Columns.AutoFit();
workbook.SaveAs(sfd.FileName);
xcelApp.Quit();
ReleaseObject(worksheet);
ReleaseObject(workbook);
ReleaseObject(xcelApp);
MessageBox.Show(Messages.SuccesfulExport, @"Info");
}
catch (Exception ex)
{
MessageBox.Show(@"Error :" + ex.Message);
}
}
}
}
else
{
MessageBox.Show(Messages.NoRecord, @"Info");
}
}
public static void CreateWorkbook(string suf, int MaxColumnLength, string dir, int MinG, int MaxG, int MinR, int MaxR)
{
var dirs = FileReader.DigForFiles(dir, suf);
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
Sheets xlSheets = null;
xlSheets = workbook.Sheets as Sheets;
// see the excel sheet behind the program
app.Visible = false;
//Select the sheet
worksheet = workbook.Worksheets[1];
//Rename the sheet
worksheet.Name = "Summary";
string[] names = GetNames(dirs);
Console.WriteLine("Processing Files:");
for (int i = 0; i < names.Length; i++)
{
Console.WriteLine(names[i]);
Microsoft.Office.Interop.Excel._Workbook csvWorkbook = app.Workbooks.Open(dirs[i]);
Microsoft.Office.Interop.Excel._Worksheet worksheetCSV = ((Microsoft.Office.Interop.Excel._Worksheet)csvWorkbook.Worksheets[1]);
worksheetCSV.Copy(xlSheets[1]);
xlSheets[1].Name = names[i];
((_Worksheet)xlSheets[1]).Cells[1, 24] = "Mean_Cell - Mean_Noise";
for (int row = 2; row < MaxColumnLength; row++)
{
((_Worksheet)xlSheets[1]).Cells[row, 24] = "=E" + row + "-U" + row;
}
((_Worksheet)xlSheets[1]).Cells[1, 25] = "Max_Cell";
((_Worksheet)xlSheets[1]).Cells[2, 25] = "=MAX(X2:X" + MaxColumnLength + ")";
// Exit from the application
csvWorkbook.Close();
}
worksheet.Move(Before: workbook.Sheets[1]);
Console.WriteLine("Preparing the summary...");
//worksheet.Cells[row, column] = "=cell57_Q2_Ch0_Green_Results!E2";
int currentColumn = 1;
int currentRow = 1;
int increment = names.Length;
for (currentRow = 1; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, currentColumn] = "=" + names[0] + "!C" + currentRow;
}
currentRow = 1;
for (int i = 0; i < names.Length; i++)
{
//Area cell
currentColumn = i + 2;
worksheet.Cells[currentRow, currentColumn] = "Area_Cell_" + names[i];
//Mean cell
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Mean_Cell_" + names[i];
//Area noise
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Area_Noise_" + names[i];
//Mean noise
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Mean_Noise_" + names[i];
//Area spots
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Area_Spot_" + names[i];
//Mean spots
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Mean_Spot_" + names[i];
//Mean-Noise cell
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Mean-Noise_Cell_" + names[i];
//Max cell
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Max_Cell_" + names[i];
worksheet.Cells[currentRow + 1, currentColumn] = "=" + names[i] + "!Y2";
//Max-Results
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Max-Results_" + names[i];
//Results
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Results_" + names[i];
//ResultsTo0
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "To0_Results_" + names[i];
//ResultsTo1
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "nTo0_Results_" + names[i];
}
//Mean result
worksheet.Cells[currentRow, currentColumn + 1] = "nAvgMob";
worksheet.Cells[currentRow, currentColumn + 2] = "nnAvgMob";
worksheet.Cells[currentRow, currentColumn + 3] = "nStDevMob";
worksheet.Cells[currentRow, currentColumn + 4] = "AvgMob";
worksheet.Cells[currentRow, currentColumn + 5] = "StDevMob";
int spotSignal, MaxCell, CellMinusNoise;
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
for (int i = 0; i < names.Length; i++)
{
//Area cell
currentColumn = i + 2;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!D" + currentRow;
//Mean cell
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!E" + currentRow;
//Area noise
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!T" + currentRow;
//Mean noise
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!U" + currentRow;
//Area spots
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] =
"=AVERAGE(" + names[i] + "!H" + currentRow + ","
+ names[i] + "!L" + currentRow + ","
+ names[i] + "!P" + currentRow + ")";
//Mean spots
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] =
"=AVERAGE(" + names[i] + "!I" + currentRow + ","
+ names[i] + "!M" + currentRow + ","
+ names[i] + "!Q" + currentRow + ")";
spotSignal = currentColumn;
//Cell-Noise mean
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!X" + currentRow;
CellMinusNoise = currentColumn;
//Max Cell
currentColumn += increment;
MaxCell = currentColumn;
//Min-Results
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + ColumnLabel(MaxCell) + "2-(" + ColumnLabel(MaxCell) + "2/" +
ColumnLabel(CellMinusNoise) + currentRow + ")*(" + ColumnLabel(spotSignal) + currentRow + "-" +
names[i] + "!U" + currentRow +
")";
//Results
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxCell) + "2/" +
ColumnLabel(CellMinusNoise) + currentRow + ")*(" + ColumnLabel(spotSignal) + currentRow + "-" +
names[i] + "!U" + currentRow +
")";
}
}
//calculations
currentColumn++;
int MaxMinResults = 2 + 8 * increment;
int ResultsTo0 = currentColumn;
for (int i = 0; i < names.Length; i++)
{
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
if (suf == "Green")
{
worksheet.Cells[currentRow, currentColumn] = "=(" +
ColumnLabel(MaxMinResults) + currentRow +
"-AVERAGE(" + ColumnLabel(MaxMinResults) + MinG + ":" + ColumnLabel(MaxMinResults) + MaxG + ")" +
")";
}
else
{
worksheet.Cells[currentRow, currentColumn] = "=(" +
ColumnLabel(MaxMinResults) + currentRow +
"-AVERAGE(" + ColumnLabel(MaxMinResults) + MinR + ":" + ColumnLabel(MaxMinResults) + MaxR + ")" +
")";
}
}
currentColumn++;
MaxMinResults++;
}
//NormTo1
MaxMinResults = currentColumn - increment;
for (int i = 0; i < names.Length; i++)
{
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, currentColumn] = "=(" +
ColumnLabel(MaxMinResults) + currentRow +
"/MAX(" + ColumnLabel(MaxMinResults) + "2" + ":" + ColumnLabel(MaxMinResults) + MaxColumnLength + ")" +
")";
}
currentColumn++;
MaxMinResults++;
}
{
int MinInd = currentColumn - increment;
int MaxInd = currentColumn - 1;
int Mean = currentColumn;
int nMean = currentColumn + 1;
int StDev = currentColumn + 2;
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, Mean] = "=" +
"AVERAGE(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")";
worksheet.Cells[currentRow, StDev] = "=" +
"STDEV.S(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")";
}
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, nMean] = "=(" +
ColumnLabel(Mean) + currentRow +
"/MAX(" + ColumnLabel(Mean) + "2:" + ColumnLabel(Mean) + MaxColumnLength + ")" +
")";
}
MinInd -= increment;
MaxInd -= increment;
Mean = currentColumn + 3;
StDev = currentColumn + 4;
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, Mean] = "=" +
"AVERAGE(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")";
worksheet.Cells[currentRow, StDev] = "=" +
"STDEV.S(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")";
}
}
string name = dir.Substring(dir.LastIndexOf("\\") + 1, dir.Length - dir.LastIndexOf("\\") - 1);
name = dir + "\\" + "Res_" + suf + ".xlsx";
app.Visible = true;
workbook.SaveAs(name, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Console.WriteLine("Results saved to :\n" + name);
app.Quit();//Check is working
Console.WriteLine("Done!");
}
public void generatExcelNoSubmit(string f)
{
string file_Name = f;
m_oExcelApp = new Excel.Application();
m_oExcelApp.Visible = false;
m_oExcelApp.UserControl = false;
m_oSheet = null;
excelRange = null;
try
{
m_oBook = m_oExcelApp.Workbooks.Add(missing);
Excel.Sheets sheets = m_oBook.Worksheets;
//Add new 4 Sheet
//sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet);
//Product Sheet [Sheet1]
m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1];
m_oSheet.Activate();
m_oSheet.Name = "รายการเสนอซื้อ";
SetData_to_SheetNoSubmit();
string template = Application.StartupPath;
string strRunReport = file_Name;
//string strPass = "******"; password
m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);
m_oExcelApp.Visible = true;
}
catch (interop.COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
if (m_oExcelApp == null)
{
m_oExcelApp.Quit();
m_oExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
private void button4_Click(object sender, EventArgs e)
{
if (dataGridView1.DataSource != null)
{
// Krijimi i Excel Application
_excel._Application app = new _excel.Application();
// krijimi i new WorkBook Ne Excel application
_excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// krijimi i new Excelsheet in workbook
_excel._Worksheet worksheet = null;
// Shfaqja e excel sheet Sapo te ekzekutohet programi
//app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// Ndryshimi i emrit te active sheet
worksheet.Name = "Exported from gridview";
// Ruajtja e header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// ruajtja e cdo rreshti dhe kolone (value) ne excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
if (File.Exists(@"C:\Users\albana\Desktop\output.xlsx"))
{
DialogResult result = MessageBox.Show("File exist! Do you want to owerite it?", "Please Confirm", MessageBoxButtons.YesNoCancel);
if (result == DialogResult.Yes)
{// if (app.ActiveWorkbook = true) { }
// xlApp.IgnoreRemoteRequests = true;
bool isopen = IsOpened("output.xlsx");
if (isopen)
{
MessageBox.Show("Please close Excel file in order to replace it with the file generated!");
app.DisplayAlerts = false;
}
else
{
File.Delete(@"C:\Users\albana\Desktop\output.xlsx");
workbook.SaveAs(@"C:\Users\albana\Desktop\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.DisplayAlerts = false;
dataGridView1.DataSource = null;
dataGridView1.Refresh();
}
}
else if (result == DialogResult.No)
{
excelcopy += 1;
while ((File.Exists(@"C:\Users\albana\Desktop\output" + excelcopy + ".xlsx") == true))
{
excelcopy += 1;
}
//nese dumam te dime emrin e filit qe ka te njejtin emer dhe qe duam ti bejme kopje
//perjashtojme rastin kur eshte output1 dhe ne rastet tjera bejme 'show ["output"+"excelcopy-1+]'
workbook.SaveAs(@"C:\Users\albana\Desktop\output" + excelcopy + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
MessageBox.Show("File is saved in the same directori with existing file name and the name of file created is output" + excelcopy + ".xlsx");
app.DisplayAlerts = false;
dataGridView1.DataSource = null;
dataGridView1.Refresh();
}
else if (result == DialogResult.Cancel)
{
app.DisplayAlerts = false;
dataGridView1.Refresh();
}
}
else
{
workbook.SaveAs(@"C:\Users\albana\Desktop\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.DisplayAlerts = false;
dataGridView1.DataSource = null;
dataGridView1.Refresh();
}
// Exit from the application
app.Quit();
}
else
{
MessageBox.Show("Datagridview is empty! Nothing to save");
}
}
public static void export2Excel(string heading1, string heading2, string heading3, DataGridView dgv, ListView lvcolumns)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
//excel.Windows.Application.ActiveWindow.DisplayGridlines = false;
//excel.ScreenUpdating = false;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Columns.ColumnWidth = 20;
////worksheet.Cells.HorizontalAlignment = ContentAlignment.MiddleCenter;
worksheet.Columns.WrapText = true;
worksheet.Cells[1, 1] = heading1;
worksheet.Cells[2, 1] = heading2;
worksheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
////worksheet.get_Range("A1", "B2").HorizontalAlignment = ContentAlignment.MiddleLeft;
////worksheet.get_Range("A1", "B2").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
worksheet.get_Range("A1", "B2").WrapText = false;
worksheet.get_Range("A1", "B2").Font.Bold = true;
worksheet.get_Range("A1", "B2").Font.Size = 15;
worksheet.get_Range("A1", "B2").Font.Color = Color.SeaGreen;
string[] detail = { };
if (heading3.Length != 0)
{
detail = heading3.Split(Main.delimiter2);
}
int k = 3;
for (int i = 0; i < detail.Length; i++)
{
try
{
DateTime temp;
string str1 = detail[i].Substring(0, detail[i].IndexOf(Main.delimiter1));
string str2 = detail[i].Substring(detail[i].IndexOf(Main.delimiter1) + 1);
worksheet.Cells[k, 1] = str1;
if (DateTime.TryParse(str2, out temp))
{
worksheet.Cells[k, 2] = (Convert.ToDateTime(str2)).Date;
}
else
{
worksheet.Cells[k, 2] = str2;
}
}
catch (Exception ex)
{
MessageBox.Show("export2Excel() : ERROR in header String format");
}
k++;
}
worksheet.Name = heading1;
int col = 0;
DateTime dt;
//---------16/10/2017
int[] tArray = new Int32[dgv.ColumnCount];
int colCount = 0;
int colNumber = 0;
foreach (ListViewItem itemRow in lvcolumns.Items)
{
if (itemRow.Checked)
{
int cNumber = dgv.Columns.IndexOf(dgv.Columns[itemRow.SubItems[3].Text]);
tArray[colNumber] = cNumber;
colNumber++;
}
colCount++;
}
for (int i = colNumber; i < tArray.Length; i++)
{
tArray[i] = -1;
}
//int row = 3;
int row = k;
//print heading
//Boolean flip = true;
for (int i = 0; i < colNumber; i++)
{
int cNumber = tArray[i];
worksheet.Cells[row, i + 1] = dgv.Columns[cNumber].HeaderText;
worksheet.Cells[row, i + 1].Interior.Color = Color.Orange;
}
row++;
//Using Range
int a = 0;
object[,] data = new object[dgv.Rows.Count, dgv.Columns.Count];
foreach (DataGridViewRow rw in dgv.Rows)
{
int b = 0;
foreach (DataGridViewCell c in rw.Cells)
{
if (tArray.Contains(c.ColumnIndex))
{
data[a, b] = c.Value;
b++;
}
}
a++;
}
worksheet.Range[worksheet.Cells[row, 1], worksheet.Cells[dgv.Rows.Count + k, dgv.Columns.Count]].value = data;
int lastrow = worksheet.UsedRange.Rows.Count;
int lastcolumn = worksheet.UsedRange.Columns.Count;
Microsoft.Office.Interop.Excel.Range rng = worksheet.Range[worksheet.Cells[row, 1], worksheet.Cells[lastrow, lastcolumn]];
Microsoft.Office.Interop.Excel.FormatCondition format = rng.Rows.FormatConditions.
Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression,
Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlEqual, "=MOD(ROW(),2)=0");
format.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGreen;
Microsoft.Office.Interop.Excel.Range rangeData = excel.Range[worksheet.Cells[row, 1], worksheet.Cells[lastrow, lastcolumn]];
rangeData.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range rangeHead = excel.Range[worksheet.Cells[row - 1, 1], worksheet.Cells[row - 1, lastcolumn]];
rangeHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//-----------
//excel.ScreenUpdating = false;
SaveFileDialog saveDialog = new SaveFileDialog();
////saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
saveDialog.FilterIndex = 2;
saveDialog.OverwritePrompt = false;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
workbook.Close(false, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show("Export Failed");
}
}
private void ExportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
//Set region = US
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook wb = app.Workbooks.Add(Type.Missing);
Excel._Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];;
try
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Title = "Export to Excel";
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx;*.xls";
//saveDialog.FilterIndex = 2;
saveDialog.FileName = "Book" + number.ToString();
if (saveDialog.ShowDialog() == DialogResult.OK)
{
ws.Name = "Ket Qua Noi Suy";
Excel.Range r = ws.Range["A1:L1"];
r.Merge();
r.Value = "Bảng tính nội suy";
r.BorderAround2();
r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//r.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
// r.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
// r.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
// r.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
int i = 0, j = 0;
while (i < dgvKQ.ColumnCount)
{
//tieu de cot
ws.Cells[2, i + 1] = dgvKQ.Columns[i].HeaderText;
for (j = 0; j < dgvKQ.RowCount; j++)
{
ws.Cells[j + 3, i + 1] = dgvKQ[i, j].Value;
ws.Range[ws.Cells[j + 2, i + 1], ws.Cells[j + 2, i + 1]].BorderAround2();
}
i++;
}
wb.SaveAs(saveDialog.FileName);
app.Quit();
MessageBox.Show("Export Successful");
}
//Open Folder
Process folderProcess = new Process();
folderProcess.StartInfo.UseShellExecute = true;
folderProcess.StartInfo.FileName = Path.GetDirectoryName(saveDialog.FileName);
// folderProcess.StartInfo.Arguments = "/select";
folderProcess.Start();
// Process.Start("explorer", "/select," + saveDialog.FileName);
saveDialog = null;
folderProcess = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\n" + "Không thể mở được Folder do không thấy đường dẫn");
}
app = null;
wb = null;
ws = null;
}
public static void export2Excel20102017(string heading1, string heading2, string heading3, DataGridView dgv, ListView lvcolumns)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
excel.Windows.Application.ActiveWindow.DisplayGridlines = false;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Columns.ColumnWidth = 20;
////worksheet.Cells.HorizontalAlignment = ContentAlignment.MiddleCenter;
worksheet.Columns.WrapText = true;
worksheet.Cells[1, 1] = heading1;
worksheet.Cells[2, 1] = heading2;
worksheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
////worksheet.get_Range("A1", "B2").HorizontalAlignment = ContentAlignment.MiddleLeft;
////worksheet.get_Range("A1", "B2").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
worksheet.get_Range("A1", "B2").WrapText = false;
worksheet.get_Range("A1", "B2").Font.Bold = true;
worksheet.get_Range("A1", "B2").Font.Size = 15;
worksheet.get_Range("A1", "B2").Font.Color = Color.SeaGreen;
string[] detail = { };
if (heading3.Length != 0)
{
detail = heading3.Split(Main.delimiter2);
}
int k = 3;
for (int i = 0; i < detail.Length; i++)
{
try
{
DateTime temp;
string str1 = detail[i].Substring(0, detail[i].IndexOf(Main.delimiter1));
string str2 = detail[i].Substring(detail[i].IndexOf(Main.delimiter1) + 1);
worksheet.Cells[k, 1] = str1;
if (DateTime.TryParse(str2, out temp))
{
worksheet.Cells[k, 2] = (Convert.ToDateTime(str2)).Date;
}
else
{
worksheet.Cells[k, 2] = str2;
}
}
catch (Exception ex)
{
MessageBox.Show("export2Excel() : ERROR in header String format");
}
k++;
}
worksheet.Name = heading1;
int col = 0;
DateTime dt;
//---------16/10/2017
int[] tArray = new Int32[dgv.ColumnCount];
int colCount = 0;
int colNumber = 0;
foreach (ListViewItem itemRow in lvcolumns.Items)
{
if (itemRow.Checked)
{
int cNumber = dgv.Columns.IndexOf(dgv.Columns[itemRow.SubItems[3].Text]);
tArray[colNumber] = cNumber;
colNumber++;
}
colCount++;
}
for (int i = colNumber; i < tArray.Length; i++)
{
tArray[i] = -1;
}
int row = 3;
//print heading
Boolean flip = true;
for (int i = 0; i < colNumber; i++)
{
int cNumber = tArray[i];
worksheet.Cells[row, i + 1] = dgv.Columns[cNumber].HeaderText;
worksheet.Cells[row, i + 1].Interior.Color = Color.Orange;
}
row++;
//print details
DateTime stime = DateTime.Now;
for (int j = 0; j < dgv.Rows.Count; j++)
{
for (int i = 0; i < colNumber; i++)
{
int cNumber = tArray[i];
if (dgv.Rows[j].Cells[cNumber].Value == null || dgv.Rows[j].Cells[cNumber].Value.ToString() == "")
{
worksheet.Cells[row, i + 1] = "";
}
else
{
string str1 = dgv.Rows[j].Cells[cNumber].Value.ToString();
worksheet.Cells[row, i + 1] = str1;
}
if (flip)
{
worksheet.Cells[row, i + 1].Interior.Color = Color.LightGreen;
}
else
{
worksheet.Cells[row, i + 1].Interior.Color = Color.LightSkyBlue;
}
}
if (flip)
{
flip = false;
}
else
{
flip = true;
}
row++;
}
DateTime etime = DateTime.Now;
//-----------
worksheet.get_Range("A1", "A30").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
SaveFileDialog saveDialog = new SaveFileDialog();
////saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
saveDialog.FilterIndex = 2;
saveDialog.OverwritePrompt = false;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
workbook.Close(false, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show("Export Failed");
}
}
private void ExportData()
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
//Get a new workbook.
//oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oWB = oXL.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
oXL.Visible = true;
//oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//oSheet.Activate();
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.Add();
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Activate();
oSheet.Name = DateTime.Now.ToString("yyyyMMdd-HHmm");
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary";
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
oSheet.get_Range("A2", "B6").Value2 = saNames;
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
//AutoFit columns A:D.
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
//Save file
//oWB.Save();
//Save as
oWB.SaveAs("C:\\file.xls", XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
oWB.Close();
}
public static void export2ExcelOld(string heading1, string heading2, string heading3, DataGridView dgv, ListView lvcolumns)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
excel.Windows.Application.ActiveWindow.DisplayGridlines = false;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Columns.ColumnWidth = 20;
////worksheet.Cells.HorizontalAlignment = ContentAlignment.MiddleCenter;
worksheet.Columns.WrapText = true;
worksheet.Cells[1, 1] = heading1;
worksheet.Cells[2, 1] = heading2;
worksheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
////worksheet.get_Range("A1", "B2").HorizontalAlignment = ContentAlignment.MiddleLeft;
////worksheet.get_Range("A1", "B2").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
worksheet.get_Range("A1", "B2").WrapText = false;
worksheet.get_Range("A1", "B2").Font.Bold = true;
worksheet.get_Range("A1", "B2").Font.Size = 15;
worksheet.get_Range("A1", "B2").Font.Color = Color.SeaGreen;
string[] detail = { };
if (heading3.Length != 0)
{
detail = heading3.Split(Main.delimiter2);
}
int k = 3;
for (int i = 0; i < detail.Length; i++)
{
try
{
DateTime temp;
string str1 = detail[i].Substring(0, detail[i].IndexOf(Main.delimiter1));
string str2 = detail[i].Substring(detail[i].IndexOf(Main.delimiter1) + 1);
worksheet.Cells[k, 1] = str1;
if (DateTime.TryParse(str2, out temp))
{
worksheet.Cells[k, 2] = (Convert.ToDateTime(str2)).Date;
}
else
{
worksheet.Cells[k, 2] = str2;
}
}
catch (Exception ex)
{
MessageBox.Show("ERROR in HeaderDetail String format");
}
k++;
}
worksheet.Name = heading1;
int col = 0;
DateTime dt;
foreach (ListViewItem itemRow in lvcolumns.Items)
{
if (itemRow.Checked)
{
int row = k + 1;
col = col + 1;
string str = itemRow.SubItems[1].Text;
string[] format = { "dd-MM-yyyy", "dd/MM/yyyy", "dd/MM/yyyy hh:mm:ss", "dd-MM-yyyy hh:mm:ss", "dd/MM/yyyy hh:mm:ss tt", "dd-MM-yyyy hh:mm:ss tt",
"dd-MM-yy", "dd/MM/yy", "dd/MM/yy hh:mm:ss", "dd-MM-yy hh:mm:ss", "dd/MM/yy hh:mm:ss tt", "dd-MM-yy hh:mm:ss tt" };
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].HeaderText.Equals(itemRow.SubItems[1].Text))
// if column is selected for export
{
////worksheet.Columns[col].ColumnWidth = itemRow.SubItems[2].Text;
worksheet.Cells[row, col] = dgv.Columns[i].HeaderText;
worksheet.Cells[row, col].Interior.Color = Color.Orange;
Boolean flip = true;
for (int j = 0; j <= dgv.Rows.Count - 1; j++)
{
row = row + 1;
if (dgv.Rows[j].Cells[i].Value == null || dgv.Rows[j].Cells[i].Value.ToString() == "")
{
worksheet.Cells[row, col] = "";
}
else
{
string str1 = dgv.Rows[j].Cells[i].Value.ToString();
worksheet.Cells[row, col] = str1;
//string format = "";
////if (DateTime.TryParseExact(dgv.Rows[j].Cells[i].Value.ToString(), format, CultureInfo.InvariantCulture, DateTimeStyles.None, out dt))
//// worksheet.Cells[row + 1, col] = Convert.ToDateTime(dgv.Rows[j].Cells[i].Value.ToString());
////else
//// worksheet.Cells[row + 1, col] = dgv.Rows[j].Cells[i].Value.ToString();
}
if (flip)
{
worksheet.Cells[row, col].Interior.Color = Color.LightGreen;
flip = false;
}
else
{
worksheet.Cells[row, col].Interior.Color = Color.LightSkyBlue;
flip = true;
}
worksheet.Cells[row, col].Borders.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
worksheet.get_Range("A1", "A30").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
SaveFileDialog saveDialog = new SaveFileDialog();
////saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
saveDialog.FilterIndex = 2;
saveDialog.OverwritePrompt = false;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
workbook.Close(false, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show("Export Failed");
}
}
//Execute Method takes the file selected and formats it
//to the rockland template specification. The new excel
//workbook is then saved in the same directory as the file
//that was selected.
public void Execute(String path)
{
filename = path;
//templatePath will be where template.xls will be stored
//source path get from browse button
//appPath is directory the .exe is located, which the template should be in same directory
string appPath = Application.StartupPath;
string templatePath, sourcePath;
//root of exe plus template name
appPath += "\\template.xls";
templatePath = @appPath;
sourcePath = @filename;
//If the template file is found, run program
if (File.Exists(templatePath) && File.Exists(sourcePath))
{
//Template setup
tmpApp = new Excel.Application();
tmpApp.Visible = false;
tmpBook = (Excel._Workbook)(tmpApp.Workbooks.Add(templatePath));
tmpSheet = (Excel._Worksheet)tmpBook.Sheets[1];
//source setup
srcApp = new Excel.Application();
srcApp.Visible = false;
srcBook = (Excel._Workbook)(srcApp.Workbooks.Add(sourcePath));
srcSheet = (Excel._Worksheet)srcBook.Sheets[1];
//Merge Both files here
for (int i = 1; i < 33; i++)
{
string str = srcSheet.Cells[2, i].Value2;
tmpSheet.Cells[3, i] = str;
}
//Results page
tmpSheet = (Excel._Worksheet)tmpBook.Sheets[2];
srcSheet = (Excel._Worksheet)srcBook.Sheets[2];
int mod = 1;
for (int i = 2; i <= srcSheet.UsedRange.Rows.Count; i++)
{
//skip FLDTMP Row
if (srcSheet.Cells[i, 1].Value2 != "FLDTEMP")
{
for (int j = 1; j < 16; j++)
{
string str = srcSheet.Cells[i, j].Value2;
tmpSheet.Cells[i + mod, j] = str;
}
//re size row height
tmpSheet.Cells[i + 1, 1].RowHeight = 12.75;
}
else
{
mod = 0;
}
}
object m_objOpt = System.Reflection.Missing.Value;
//remove extension of filename
string[] remove = { ".xls", ".xlsx" };
foreach (string item in remove)
{
if (filename.EndsWith(item))
{
filename = filename.Substring(0, filename.LastIndexOf(item));
break; //only allow one match at most
}
}
//save workbook
tmpBook.SaveAs(@filename + "_Formatted.xls", m_objOpt,
m_objOpt, m_objOpt, true, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
tmpBook.Close(true);
tmpApp.Workbooks.Close();
tmpApp.Quit();
srcBook.Close(true);
srcApp.Workbooks.Close();
srcApp.Quit();
//Open file location after saved
if (File.Exists(@filename + "_Formatted.xls"))
{
Process.Start("explorer.exe", "/select, " + @filename + "_Formatted.xls");
}
}
else
{
this.label1.Text = "Error: File not found. Please Try again.";
}
}
public void XLS(DirectoryInfo RutaExcel, DataTable Datos)
{
//BackgroundWorker bw = sender as BackgroundWorker;
Excel.Application excel = new Excel.Application();
Excel._Workbook libro = null;
Excel._Worksheet hoja = null;
Excel.Range rango = null;
try
{
//creamos un libro nuevo y la hoja con la que vamos a trabajar
libro = (Excel._Workbook)excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
hoja = (Excel._Worksheet)libro.Worksheets.Add();
hoja.Name = "EJEMPLO";
((Excel.Worksheet)excel.ActiveWorkbook.Sheets["Hoja1"]).Delete(); //Borramos la hoja que crea en el libro por defecto
//Montamos las cabeceras
montaCabeceras(1, ref hoja, Datos);
//Rellenamos las celdas
int inColumn = 0, inRow = 0;
for (int m = 0; m < Datos.Rows.Count; m++)
{
for (int n = 0; n < Datos.Columns.Count; n++)
{
inColumn = n + 1;
inRow = 2 + m;//1 + 2 + m;
hoja.Cells[inRow, inColumn] = Datos.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
hoja.get_Range("A" + inRow.ToString(), "W" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#DAA520");
}
}
}
libro.Saved = true;
libro.SaveAs(Environment.CurrentDirectory + @"\Ejemplo.xlsx"); // Si es un libro nuevo
libro.Close();
releaseObject(libro);
excel.UserControl = false;
excel.Quit();
releaseObject(excel);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message, "Error en creación/actualización del Ejemplo");
libro.Saved = true;
libro.SaveAs(Environment.CurrentDirectory + @"\Ejemplo.xlsx");
// libro.Save();
libro.Close();
releaseObject(libro);
excel.UserControl = false;
excel.Quit();
releaseObject(excel);
System.Threading.Thread.Sleep(2000);
}
}
