public void OnSheetCalculate()
{
if ((Range.HasFormula && Range.Formula != currentFormula) || !object.Equals(Range.Value2, currentValue))
{
Microsoft.Office.Interop.Excel.WorksheetFunction worksheetFunction = ETKExcel.ExcelApplication.Application.WorksheetFunction;
if (Range.HasFormula && worksheetFunction.IsError(Range))
{
if (excelBindingDefinitionWithFormula.TargetBindingDefinition != null)
{
Type type = excelBindingDefinitionWithFormula.TargetBindingDefinition.BindingType;
if (type != null)
{
object nullValue = type.IsValueType ? Activator.CreateInstance(type) : null;
excelBindingDefinitionWithFormula.TargetBindingDefinition.UpdateDataSource(DataSource, nullValue);
}
}
}
else
{
if (excelBindingDefinitionWithFormula.TargetBindingDefinition != null)
{
excelBindingDefinitionWithFormula.TargetBindingDefinition.UpdateDataSource(DataSource, Range.Value2);
}
}
currentValue = Range.Value2;
currentFormula = Range.HasFormula ? Range.Formula : null;
if (worksheetFunction != null)
{
ExcelApplication.ReleaseComObject(worksheetFunction);
worksheetFunction = null;
}
}
}
public void OnSheetCalculate()
{
if (!Range.HasFormula)
{
return;
}
object resolvedBinding = excelBindingDefinitionFormulaResult.NestedBindingDefinition.ResolveBinding(DataSource);
if (Range.FormulaLocal != resolvedBinding || !object.Equals(Range.Value2, currentValue))
{
ExcelInterop.WorksheetFunction worksheetFunction = ETKExcel.ExcelApplication.Application.WorksheetFunction;
if (worksheetFunction.IsError(Range))
{
Type type = excelBindingDefinitionFormulaResult.NestedBindingDefinition.BindingType;
if (type != null)
{
object nullValue = type.IsValueType ? Activator.CreateInstance(type) : null;
excelBindingDefinitionFormulaResult.NestedBindingDefinition.UpdateDataSource(DataSource, nullValue);
}
}
else
{
excelBindingDefinitionFormulaResult.NestedBindingDefinition.UpdateDataSource(DataSource, Range.Value2);
}
currentValue = Range.Value2;
if (worksheetFunction != null)
{
ExcelApplication.ReleaseComObject(worksheetFunction);
worksheetFunction = null;
}
}
}
static void Main(string[] args)
{
Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(@"C:\test.xlsx");
Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Sheets[1];
xl.Visible = true;
//use this if you want to use native Excel functions (such as index)
Microsoft.Office.Interop.Excel.WorksheetFunction wsFunc = xl.WorksheetFunction;
int maxNum = 100; // set maximum number of rows/columns to search
string from = "blah";
string to = "blum";
//this is pretty slow, since it has to interact with 10,000 cells in Excel
// just one example of how to access and set cell values
for (int col = 1; col <= maxNum; col++)
{
for (int row = 1; row <= maxNum; row++)
{
Range cell = (Range)sheet.Cells[row, col];
if ((string)cell.Value == from) //cast to string to avoid null reference exceptions
{
cell.Value = to;
}
}
}
}
private void btnConvert_Click(object sender, EventArgs e)
{
string inputPath = this.textBox2.Text;
string outputPath = this.textBox3.Text;
Object misValue = System.Reflection.Missing.Value;
Excel.Application xls = new Excel.Application();
String excelExt = "xlsx";
Directory.CreateDirectory(outputPath);
this.textBox4.Clear();
int numCheckedStations = checkedListBox2.CheckedItems.Count;
int numStationsProcessed = 0;
foreach (string stationCode in checkedListBox2.CheckedItems)
{
numStationsProcessed++;
Excel.Workbook xlsWorkBook = xls.Workbooks.Add(misValue);
Excel.Worksheet xlsWorkSheet = (Excel.Worksheet)xlsWorkBook.Sheets[1];
System.IO.StreamReader fileReader;
String info1, info2;
int nt = 0;
double dt;
String filePathPrefix = inputPath + "\\" + stationCode;
String outputFile = outputPath + "\\" + stationCode + "." + excelExt;
//this.textBox4.Text +="Processing "+stationCode + "("+numStationsProcessed+"/"+numCheckedStations+") ";
this.textBox4.AppendText("Processing " + stationCode + "(" + numStationsProcessed + "/" + numCheckedStations + ") ");
xlsWorkSheet.Cells[1, 1] = "Component";
xlsWorkSheet.Cells[2, 1] = "No. of timesteps";
xlsWorkSheet.Cells[3, 1] = "Size of timesteps (seconds)";
xlsWorkSheet.Cells[4, 1] = "Time (seconds)";
xlsWorkSheet.Cells[5, 1] = "Acceleration";
String[] componentsCode = { "000", "090", "ver" };
String[] componentsStr = { "X-axis (000)", "Y-axis (090)", "Z-axis (ver)" };
for (int k = 0; k < 3; k++) //Iterates each component file
{
this.textBox4.AppendText(Convert.ToString(k + 1) + "...");
String ext = componentsCode[k];
fileReader = new StreamReader(new FileStream(filePathPrefix + "." + ext, FileMode.Open));
info1 = fileReader.ReadLine();
info2 = fileReader.ReadLine();
int LastNonEmpty = -1;
String[] info2Array = info2.Split();
for (int i = 0; i < info2Array.Length; i++)
{
if (info2Array[i] != "")
{
LastNonEmpty += 1;
info2Array[LastNonEmpty] = info2Array[i];
}
}
nt = Convert.ToInt32(info2Array[0]); //number of time steps
dt = Convert.ToDouble(info2Array[1]); //time step size
Double[,] timeRange = new double[nt, 1];
xlsWorkSheet.Cells[1, 2 + k] = componentsStr[k];
xlsWorkSheet.Cells[2, 2 + k] = nt;
xlsWorkSheet.Cells[3, 2 + k] = dt;
LastNonEmpty = -1;
String lines = fileReader.ReadToEnd();
String[] strArray = lines.Split();
Double[,] dblArray = new Double[nt, 1]; //needs to be 2-d to be able to bulk write
for (int i = 0; i < strArray.Length; i++)
{
if (strArray[i] != "")
{
LastNonEmpty += 1;
timeRange[LastNonEmpty, 0] = LastNonEmpty * dt;
dblArray[LastNonEmpty, 0] = Convert.ToDouble(strArray[i].Replace("\n", ""));
}
}
int nt2 = LastNonEmpty + 1;
if (k == 0)
{
xlsWorkSheet.Range["A5"].Resize[nt2, 1].Value = timeRange; // first column filled with time steps
}
Excel.Range cell;
cell = xlsWorkSheet.Cells[5, 2 + k];
xlsWorkSheet.Range[cell, cell].Resize[nt2, 1].Value = dblArray; //bulk write at column "B, C, D"
}
Excel.WorksheetFunction wsf = xls.WorksheetFunction;
Double maxAcc = wsf.Max(xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 4], xlsWorkSheet.Cells[nt + 4, 4]]); //find the maximum and minimum
Double minAcc = wsf.Min(xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 4], xlsWorkSheet.Cells[nt + 4, 4]]);
Double maxAmpAcc = Math.Max(maxAcc, -1.0 * minAcc); //Select the max of absolute value of two
for (int k = 0; k < 3; k++) // Plotting
{
String ext = componentsCode[k];
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlsWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 80 + 300 * k, 600, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 2 + k], xlsWorkSheet.Cells[nt + 4, 2 + k]];
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlLine;
chartPage.HasTitle = true;
Excel.Series series = (Excel.Series)chartPage.SeriesCollection(1);
Microsoft.Office.Interop.Excel.Axis xAxis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
Microsoft.Office.Interop.Excel.Axis yAxis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = "Time (sec)";
xAxis.CategoryNames = (Excel.Range)xlsWorkSheet.Range["A5"].Resize[nt, 1];
xAxis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = "Acceleration (cm/s^2)";
yAxis.MinimumScale = (-1 * maxAmpAcc * 3.0);
yAxis.MaximumScale = (maxAmpAcc * 3.0);
series.Name = ext;
switch (k)
{
case 0:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along X-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbRed;
break;
case 1:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along Y-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbBlue;
break;
case 2:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along Z-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbGreen;
break;
}
}
this.textBox4.AppendText("Done..!" + Environment.NewLine);
if (File.Exists(outputFile)) // delete file if it already exists
{
File.Delete(outputFile);
}
xlsWorkBook.SaveAs(outputFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
misValue, misValue, misValue, misValue, misValue);
xlsWorkBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkBook);
}
xls.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xls);
//MessageBox.Show("Done!");
this.textBox4.AppendText("Finished!!" + Environment.NewLine);
}
private void btnExcel_Click(object sender, EventArgs e)
{
if ( !timer1.Enabled )
btnStart_Click(sender, e);
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.Visible = false;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlFunction = xlApp.WorksheetFunction;
xlWorkSheetData = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheetData.Name = "Pendulum";
xlWorkSheetData.Activate();
xlWorkSheetData.Cells[1, 1] = "Constants used, mass: " + tbm.Text + ", gravity: " + tbG.Text + ", Spring Constant: " + tbk.Text + ", Length: " + tbH.Text;
xlRng = xlWorkSheetData.get_Range("A1", "N1");
xlRng.Select();
xlRng.Merge();
xlWorkSheetData.Cells[2, 1] = "Initial Values used, Intial X: " + tbXi.Text + ", Initial Y: " + tbYi.Text + ", Initial X Velocity: " + vx0.Text + ", Initial Y Velocity: " + vy0.Text;
xlRng = xlWorkSheetData.get_Range("A2", "N2");
xlRng.Select();
xlRng.Merge();
xlWorkSheetData.Cells[lastRowExcel, 1] = "t"; // changes these to whatever you want
xlWorkSheetData.Cells[lastRowExcel, 2] = "X";
xlWorkSheetData.Cells[lastRowExcel, 3] = "Y";
xlWorkSheetData.Cells[lastRowExcel, 4] = "Vx";
xlWorkSheetData.Cells[lastRowExcel, 5] = "Vy";
lblTransfer.Visible = true;
for (int i = 0; i < excelData.Count; i++)
{
xlWorkSheetData.Cells[i + 4, 1] = (excelData[i].time / 1000.00).ToString();
xlWorkSheetData.Cells[i + 4, 2] = excelData[i].x.ToString();
xlWorkSheetData.Cells[i + 4, 3] = excelData[i].y.ToString();
xlWorkSheetData.Cells[i + 4, 4] = excelData[i].vx.ToString();
xlWorkSheetData.Cells[i + 4, 5] = excelData[i].vy.ToString();
}
lblTransfer.Visible = false;
try //essaye le sauvegarde
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
//sauvegarde le classeur courant
xlWorkBook.SaveAs(saveFileDialog1.FileName,
Excel.XlFileFormat.xlWorkbookDefault, misValue,
misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue,
misValue, misValue, misValue);
xlWorkBook.Close();
}
}
catch //en cas d'erreur affiche le message
{
MessageBox.Show("Impossible de sauvegarder le fichier.", "Erreur de sauvegarde de fichier Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
