421 lines
12 KiB
C#
421 lines
12 KiB
C#
// UNCLASSIFIED
|
|
/*-------------------------------------------------------------------------
|
|
RAYTHEON PROPRIETARY: THIS DOCUMENT CONTAINS DATA OR INFORMATION
|
|
PROPRIETARY TO RAYTHEON COMPANY AND IS RESTRICTED TO USE ONLY BY PERSONS
|
|
AUTHORIZED BY RAYTHEON COMPANY IN WRITING TO USE IT. DISCLOSURE TO
|
|
UNAUTHORIZED PERSONS WOULD LIKELY CAUSE SUBSTANTIAL COMPETITIVE HARM TO
|
|
RAYTHEON COMPANY'S BUSINESS POSITION. NEITHER SAID DOCUMENT NOR ITS
|
|
CONTENTS SHALL BE FURNISHED OR DISCLOSED TO OR COPIED OR USED BY PERSONS
|
|
OUTSIDE RAYTHEON COMPANY WITHOUT THE EXPRESS WRITTEN APPROVAL OF RAYTHEON
|
|
COMPANY.
|
|
|
|
THIS PROPRIETARY NOTICE IS NOT APPLICABLE IF DELIVERED TO THE U.S.
|
|
GOVERNMENT.
|
|
|
|
UNPUBLISHED WORK - COPYRIGHT RAYTHEON COMPANY.
|
|
-------------------------------------------------------------------------*/
|
|
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Runtime.InteropServices;
|
|
using Microsoft.Office.Interop.Excel;
|
|
|
|
namespace Raytheon.Common
|
|
{
|
|
/// <summary>
|
|
/// An interface to an excel workbook
|
|
/// </summary>
|
|
public class ExcelReader : IDisposable
|
|
{
|
|
#region PublicClassMembers
|
|
public struct CellRowInfo
|
|
{
|
|
public int _colNumber;
|
|
public string _cellValue;
|
|
|
|
internal CellRowInfo(int colNumber, string cellValue)
|
|
{
|
|
_colNumber = colNumber;
|
|
_cellValue = cellValue;
|
|
}
|
|
};
|
|
#endregion
|
|
|
|
#region PrivateClassMembers
|
|
private readonly string _fileName;
|
|
private Application _excelApp;
|
|
private _Workbook _excelWorkBook;
|
|
#endregion
|
|
|
|
#region PublicFuctions
|
|
|
|
/// <summary>
|
|
/// The constructor.
|
|
/// </summary>
|
|
/// <param name="fileName">The excel file name (full path)</param>
|
|
public ExcelReader(string fileName)
|
|
{
|
|
_fileName = System.IO.Path.GetFullPath(fileName);
|
|
|
|
bool doesFileExist = File.Exists(_fileName);
|
|
|
|
if (doesFileExist == false)
|
|
{
|
|
throw new Exception("ExcelReader::ExcelReader() - File: " + fileName + " Does not exist");
|
|
}
|
|
|
|
//Start Excel and get Application object.
|
|
_excelApp = new Application();
|
|
_excelApp.Visible = false;
|
|
|
|
_excelWorkBook = _excelApp.Workbooks.Open(_fileName);
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
~ExcelReader()
|
|
{
|
|
Dispose(false);
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
public void Dispose()
|
|
{
|
|
Dispose(true);
|
|
|
|
GC.SuppressFinalize(this);
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="sheetName"></param>
|
|
/// <param name="rows"></param>
|
|
public void AddRows(string sheetName, List<List<ExcelReader.CellRowInfo>> rows)
|
|
{
|
|
_Worksheet sheet = null;
|
|
Range excelRange = null;
|
|
|
|
try
|
|
{
|
|
sheet = _excelWorkBook.Sheets[sheetName];
|
|
sheet.Select(Type.Missing);
|
|
excelRange = sheet.UsedRange;
|
|
|
|
int rowCount = excelRange.Rows.Count;
|
|
int colCount = excelRange.Columns.Count;
|
|
int newRowNumber = rowCount + 1;
|
|
|
|
// insert the new rows
|
|
string insertCommand = newRowNumber.ToString() + ":" + (newRowNumber + rows.Count).ToString();
|
|
|
|
sheet.Range[insertCommand].Insert();
|
|
|
|
// fill the cell values
|
|
foreach (List<ExcelReader.CellRowInfo> cellInfoList in rows)
|
|
{
|
|
foreach (CellRowInfo cellInfo in cellInfoList)
|
|
{
|
|
Range cell = sheet.Cells[newRowNumber, cellInfo._colNumber];
|
|
cell.Value = cellInfo._cellValue;
|
|
}
|
|
|
|
newRowNumber++;
|
|
}
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
Marshal.ReleaseComObject(excelRange);
|
|
Marshal.ReleaseComObject(sheet);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="sheetName"></param>
|
|
/// <param name="rowValues"></param>
|
|
/*public void AddRow(string sheetName, List<CellRowInfo> rowValues)
|
|
{
|
|
_Worksheet sheet = null;
|
|
Range excelRange = null;
|
|
|
|
try
|
|
{
|
|
sheet = _excelWorkBook.Sheets[sheetName];
|
|
sheet.Select(Type.Missing);
|
|
excelRange = sheet.UsedRange;
|
|
|
|
int rowCount = excelRange.Rows.Count;
|
|
int colCount = excelRange.Columns.Count;
|
|
int newRowNumber = rowCount + 1;
|
|
|
|
// insert the new row
|
|
sheet.Rows[rowCount].Insert(newRowNumber);
|
|
|
|
// set each cell to empty string
|
|
int col = 1;
|
|
while (col < colCount)
|
|
{
|
|
Range cell = sheet.Cells[newRowNumber, col];
|
|
cell.Value = "";
|
|
col++;
|
|
}
|
|
|
|
// fill the cell values
|
|
foreach (CellRowInfo cellInfo in rowValues)
|
|
{
|
|
Range cell = sheet.Cells[newRowNumber, cellInfo.colNumber];
|
|
cell.Value = cellInfo.cellValue;
|
|
col++;
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
Marshal.ReleaseComObject(excelRange);
|
|
Marshal.ReleaseComObject(sheet);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="sheetName"></param>
|
|
/// <param name="rowValues"></param>
|
|
public void AddRow(string sheetName, List<string> rowValues)
|
|
{
|
|
_Worksheet sheet = null;
|
|
Range excelRange = null;
|
|
|
|
try
|
|
{
|
|
sheet = _excelWorkBook.Sheets[sheetName];
|
|
sheet.Select(Type.Missing);
|
|
excelRange = sheet.UsedRange;
|
|
|
|
int rowCount = excelRange.Rows.Count;
|
|
int colCount = excelRange.Columns.Count;
|
|
int newRowNumber = rowCount + 1;
|
|
|
|
// insert the new row
|
|
sheet.Rows[rowCount].Insert(newRowNumber);
|
|
|
|
// set each cell to empty string
|
|
int col = 1;
|
|
while (col < colCount)
|
|
{
|
|
Range cell = sheet.Cells[newRowNumber, col];
|
|
cell.Value = "";
|
|
col++;
|
|
}
|
|
|
|
// fill the cell values
|
|
foreach (string cellValue in rowValues)
|
|
{
|
|
Range cell = sheet.Cells[newRowNumber, col];
|
|
cell.Value = cellValue;
|
|
col++;
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
Marshal.ReleaseComObject(excelRange);
|
|
Marshal.ReleaseComObject(sheet);
|
|
}
|
|
}*/
|
|
|
|
public void ColorCell(string sheetName, int row, int col, System.Drawing.Color color)
|
|
{
|
|
_Worksheet sheet = null;
|
|
Range excelRange = null;
|
|
|
|
try
|
|
{
|
|
if (row < 1 || col < 1)
|
|
{
|
|
throw new Exception("ExcelReader::ColorCell() - row and col inputs must be greater than 0");
|
|
}
|
|
|
|
sheet = _excelWorkBook.Sheets[sheetName];
|
|
sheet.Select(Type.Missing);
|
|
excelRange = sheet.UsedRange;
|
|
|
|
Range cell = sheet.Cells[row, col];
|
|
|
|
cell.Interior.Color = color;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
Marshal.ReleaseComObject(excelRange);
|
|
Marshal.ReleaseComObject(sheet);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public List<string> ReadAllSheetNames()
|
|
{
|
|
List<string> sheetList = new List<string>();
|
|
|
|
foreach (_Worksheet temp in _excelWorkBook.Sheets)
|
|
{
|
|
sheetList.Add(temp.Name.ToUpper());
|
|
}
|
|
|
|
return sheetList;
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="sheetName"></param>
|
|
/// <param name="startingRow"></param>
|
|
/// <param name="startingCol"></param>
|
|
/// <returns></returns>
|
|
public string ReadAllRows(string sheetName, int startingRow, int startingCol)
|
|
{
|
|
_Worksheet sheet = null;
|
|
Range excelRange = null;
|
|
|
|
try
|
|
{
|
|
if (startingRow < 1 || startingCol < 1)
|
|
{
|
|
throw new Exception("ExcelReader::ReadAllRows() - startingRow and startingCol inputs must be greater than 0");
|
|
}
|
|
|
|
// check to see if the requested sheet exists.
|
|
bool doesSheetExist = false;
|
|
foreach (_Worksheet temp in _excelWorkBook.Sheets)
|
|
{
|
|
if (temp.Name.ToUpper() == sheetName.ToUpper())
|
|
{
|
|
doesSheetExist = true;
|
|
break;
|
|
}
|
|
}
|
|
|
|
// give a nice error if it doesnt exist
|
|
if (doesSheetExist == false)
|
|
{
|
|
throw new Exception("ExcelReader::ReadAllRows() - sheet: " + sheetName + " does not exist in file: " + _fileName);
|
|
}
|
|
|
|
sheet = _excelWorkBook.Sheets[sheetName];
|
|
sheet.Select(Type.Missing);
|
|
excelRange = sheet.UsedRange;
|
|
|
|
string allRows = "";
|
|
|
|
int rowCount = excelRange.Rows.Count;
|
|
int colCount = excelRange.Columns.Count;
|
|
|
|
for (int currentRowIndex = startingRow; currentRowIndex <= rowCount; currentRowIndex++)
|
|
{
|
|
string currentRowData = "";
|
|
for (int currentColIndex = startingCol; currentColIndex <= colCount; currentColIndex++)
|
|
{
|
|
if (excelRange.Cells[currentRowIndex, currentColIndex] != null)
|
|
{
|
|
if (excelRange.Cells[currentRowIndex, currentColIndex].Value2 == null)
|
|
{
|
|
currentRowData += ",";
|
|
}
|
|
else
|
|
{
|
|
string cellValue = excelRange.Cells[currentRowIndex, currentColIndex].Value2.ToString();
|
|
cellValue = cellValue.Replace(',', ' ');
|
|
currentRowData += cellValue + ",";
|
|
}
|
|
}
|
|
}
|
|
|
|
// remove any newlines
|
|
currentRowData = currentRowData.Replace('\n', ' ');
|
|
|
|
// strip off the last comma and add the newline
|
|
currentRowData = currentRowData.TrimEnd(',');
|
|
allRows += currentRowData + "\n";
|
|
}
|
|
|
|
// remove the \n at the end of the string
|
|
allRows = allRows.TrimEnd('\n');
|
|
|
|
return allRows;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
if (excelRange != null)
|
|
{
|
|
Marshal.ReleaseComObject(excelRange);
|
|
}
|
|
|
|
if (sheet != null)
|
|
{
|
|
Marshal.ReleaseComObject(sheet);
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="fileName"></param>
|
|
public void SaveAs(string fileName)
|
|
{
|
|
_excelWorkBook.SaveAs(fileName);
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="disposing"></param>
|
|
protected virtual void Dispose(bool disposing)
|
|
{
|
|
if (disposing)
|
|
{
|
|
//close and release
|
|
if (_excelWorkBook != null)
|
|
{
|
|
_excelWorkBook.Close();
|
|
Marshal.ReleaseComObject(_excelWorkBook);
|
|
}
|
|
|
|
//quit and release
|
|
if (_excelApp != null)
|
|
{
|
|
_excelApp.Quit();
|
|
Marshal.ReleaseComObject(_excelApp);
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
}
|
|
}
|