public static void outputDataToSheet(Worksheet sht, dataStructures.row row)
{
var rowInsertpoint = row.insertPoint[0];
var colInsertPoint = row.insertPoint[1];
// loop through input data and output to excel
for (int k = 0; k < row.twoDimArray.GetLength(0); k++)
{
for (int l = 0; l < row.twoDimArray.GetLength(1); l++)
{
var val = row.twoDimArray[k, l];
sht.Cells[k + rowInsertpoint, l + colInsertPoint] = val;
}
}
}
public static void updateTable(Document doc, dataStructures.row row)
{
var count = doc.Tables.Count;
object oMissing = System.Reflection.Missing.Value;
for (var i = 1; i <= count; i++)
{
// table found in word
if (doc.Tables[i].Title == row.param)
{
var tbl = doc.Tables[i];
var rowInsertpoint = row.insertPoint[0];
var colInsertPoint = row.insertPoint[1];
// loop through input table and output to word document
for (int k = 0; k < row.twoDimArray.GetLength(0); k++)
{
// check if row exists
if (tbl.Rows.Count - (rowInsertpoint - 1) < k + 1)
{
tbl.Rows.Add(ref oMissing);
}
for (int l = 0; l < row.twoDimArray.GetLength(1); l++)
{
var val = row.twoDimArray[k, l];
string strVal;
// handle nulls in dataset
if (val != null)
{
strVal = val.ToString();
}
else
{
strVal = val;
}
tbl.Cell(k + rowInsertpoint, l + colInsertPoint).Range.Text = strVal;
}
}
}
}
}
// Find a chart in a document and update its data
public static void updateChartData(Document doc, dataStructures.row row)
{
var count = doc.InlineShapes.Count;
// Loop through all inline shapes
for (var i = 1; i <= count; i++)
{
String shapeType = doc.InlineShapes[i].Type.ToString();
// chart found
if (shapeType == "wdInlineShapeChart")
{
Chart chart = doc.InlineShapes[i].Chart;
String title = chart.ChartTitle.Text.ToString();
// Specific chart found
if (title == row.param)
{
excelFunctions.outputDataToWordChart(chart, row);
}
}
}
}
public static void outputDataToWordChart(Microsoft.Office.Interop.Word.Chart chart, dataStructures.row row)
{
// Get charts connected workbook
Workbook wb = chart.ChartData.Workbook;
Worksheet sht = wb.Worksheets[1];
outputDataToSheet(sht, row);
// refresh chart
chart.ChartData.Activate();
chart.ChartData.Workbook.Application.WindowState = -4140;
// close workbook when finished processing data
wb.Close();
}
public static void insertFileAtTable(Document doc, dataStructures.Parameters parameters, dataStructures.row row, string fileExt)
{
var count = doc.Tables.Count;
object oMissing = System.Reflection.Missing.Value;
// loop through all tables in word document
for (var i = 1; i <= count; i++)
{
// Find table specific to current variable to be inserted into document
if (doc.Tables[i].Title == row.insertTableName)
{
var tbl = doc.Tables[i];
var rowInsertpoint = row.insertPoint[0];
var colInsertPoint = row.insertPoint[1];
// loop through file list and output to word document
for (int k = 0; k < row.oneDimArray.GetLength(0); k++)
{
// add new row to table if applicable
if (tbl.Rows.Count - (rowInsertpoint - 1) < k + 1)
{
tbl.Rows.Add(ref oMissing);
}
// get filename
string filename = row.oneDimArray[k].ToString();
// define insert range
Range rng = doc.Range(0, 0);
rng = tbl.Cell(k + rowInsertpoint, colInsertPoint).Range;
insertFile(doc, rng, filename, fileExt, row, parameters);
}
}
}
}
public static void insertFile(Document doc, Range rng, string filename, string fileExt, dataStructures.row row, dataStructures.Parameters parameters)
{
string path = null;
// define file path
if (fileExt == ".jpg")
{
path = row.photoAssetsPath + @"\" + filename + ".jpg";
}
else if (fileExt == ".pdf")
{
path = row.pdfAssetsPath + @"\" + filename + ".pdf";
}
// confirm file exists before attempting to do any operations
if (File.Exists(path) == true)
{
if (fileExt == ".jpg")
{
insertImage(doc, rng, path);
}
else if (fileExt == ".pdf")
{
insertPdf(doc, rng, path, parameters.temporaryFilesPath);
}
}
else
{
// insert text indicating no file was found
rng.Text = filename + fileExt + " was not found";
}
}
// lookup variable in word document and insert value
public static void updateParameter(Document doc, dataStructures.row row)
{
doc.Variables[row.param].Value = row.value.ToString();
doc.Fields.Update();
}
public static void insertFileAtTag(Document doc, dataStructures.Parameters parameters, dataStructures.row row, string fileExt)
{
// select empty range in document
Range rng = doc.Range(0, 0);
Range tmp = doc.Range(0, 0);
// Find location to insert images
if (rng.Find.Execute("<" + row.param + ">"))
{
// range is now set to bounds of the word "<" + parameter name + ">"
// loop through file list and output to word document
for (int k = 0; k < row.oneDimArray.GetLength(0); k++)
{
string filename = row.oneDimArray[k].ToString();
// insert a new text value for the image to be pasted over
rng.InsertBefore("<" + filename + ">");
// define temporary range as inserted tag
tmp = doc.Range(0, 0);
tmp.Find.Execute("<" + filename + ">");
// insert File into document
insertFile(doc, tmp, filename, fileExt, row, parameters);
}
// clean up document tags
rng.Find.Execute("<" + row.param + ">");
rng.Delete();
}
}
