public void Delete_Unused_Columns(Excel.Worksheet RawData_Sheet)
{
Excel.Range Unused_Range = RawData_Sheet.get_Range("E1", "N" + RawData_Sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row);
Unused_Range.Delete();
Excel.Range Unused_Range_2 = RawData_Sheet.get_Range("K1", "AI" + RawData_Sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row);
Unused_Range_2.Delete();
}
//Method to get value; cellname is A1,A2, or B1,B2 etc...in excel.
private string ExcelGetValue(string cellname, Excel._Worksheet sheet)
{
string value = string.Empty;
try {
value = sheet.get_Range(cellname).get_Value().ToString();
} catch {
value = "";
}
return value;
}
private void ColorCodeColumn(Excel.Worksheet ws, string range)
{
// Create a two-color ColorScale object for the created sample data
// range.
Excel.Top10 cfColorScale = (Excel.Top10)(ws.get_Range(range,
Type.Missing).FormatConditions.AddTop10());
cfColorScale.Interior.Color = 0x0000FF00;
// Set the minimum threshold to red (0x000000FF) and maximum threshold
// to green (0x0000FF00).
//cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF;
//cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x0000FF00;
}
public void Add_Metric_Calculations(Excel.Worksheet RawData_Sheet)
{
// Creates the range for average report write times
Excel.Range Metric1 = RawData_Sheet.get_Range("K1", "K" + RawData_Sheet.UsedRange.Rows.Count);
// Creates the range for the average check times
Excel.Range Metric2 = RawData_Sheet.get_Range("L1", "L" + RawData_Sheet.UsedRange.Rows.Count);
// Creates the range for the average invoice times
Excel.Range Metric3 = RawData_Sheet.get_Range("M1", "M" + RawData_Sheet.UsedRange.Rows.Count);
// Sets the formula for the average report write times (excludes those written by May or Shah)
Metric1.Formula = "=IF(AND(B1=\"A\" , (G1-E1)>=0, F1 <> \"May\", F1 <> \"Shah\"), G1-E1, \" Invalid\" )";
// Sets the formula for the average report check times (no exclusions needed as rows marked invalid from previous formula are deleted)
Metric2.Formula = "=IF((H1-G1)>=0, H1-G1, \" Invalid\" )";
Metric3.Formula = "=IF((J1-I1)>=0, J1-I1, \" Invalid\" )";
Metric1.Cells[1] = "Time for Report";
Metric2.Cells[1] = "Time for Check";
Metric3.Cells[1] = "Time for Invoice";
}
private void CreateGraph(IScriptWorker CurrentScriptWorker, Excel.Worksheet xlWorkSheet, int RowCounter, int ColumnCounterForNamesOfTests)
{
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(100, 200, 800, 350);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range((Excel.Range)xlWorkSheet.Cells[1, 1], (Excel.Range)xlWorkSheet.Cells[RowCounter - 1, ColumnCounterForNamesOfTests - 1]);
chartPage.ChartWizard(Source: chartRange,
Gallery: Excel.XlChartType.xlColumnClustered,
Title: (string)CurrentScriptWorker.GetLabName(),
CategoryTitle: (string)CurrentScriptWorker.GetValueSeparator(),
ValueTitle: (string)CurrentScriptWorker.GetValueType());
}
public void autofitColumn(String columnName, Excel.Worksheet sheet)
{
// hackish - there has to be a better way to select the whole column....
String colName = columnName + "1";
String colEnd = columnName + "1000";
try
{
// Get the column and tell it to autofit
Excel.Range columns = sheet.get_Range(colName, colEnd);
columns.Columns.AutoFit();
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message, "Column AutoFit Failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
private void findNow(Excel.Worksheet worksheet, string textToFind)
{
Excel.Range range;
string address;
string first; // it will store the address of the cell where we find the word for the very first time<br/>
// This is to ensure that we will not trap in a loop
// As after finding the whole document, search returns to first cell
// So if we find that the currently finded cell is same to the firstly founded address, then stop the processing
range = worksheet.Cells.Find(textToFind, worksheet.get_Range("A1", "A1"),
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
false, false);
// This will find the 1st occurance of the word in our Excel file
if (range != null) // Check whether it has found something in our file or not
{
first = range.get_Address(
true, true, Excel.XlReferenceStyle.xlA1, null, null); // Save the address of the first found item
MessageBox.Show("1st match found at cell " + first);
range = worksheet.Cells.FindNext(range);
// Continue searching items using FindNext
address = range.get_Address(
true, true, Excel.XlReferenceStyle.xlA1, null, null);
// get the address of newly searched cell
while (!address.Equals(first))
{
// this loop will find all the occurances of your word
MessageBox.Show("match found at cell " + address);
range = worksheet.Cells.FindNext(range);
address = range.get_Address(
true, true, Excel.XlReferenceStyle.xlA1, null, null);
}
}
}
protected void WriteTitle(ExcelCOM.Worksheet worksheet)
{
BUSHoatDong bushoatdong = new BUSHoatDong();
HOATDONG hoatdong = bushoatdong.TimKiem(int.Parse(Request.QueryString["id"]));
ExcelCOM.Range sheetTitle = (ExcelCOM.Range)worksheet.get_Range("A1", "D1");
sheetTitle.MergeCells = true;
sheetTitle.Value2 = "Chi Tiết Đăng Ký Hoạt Động " + hoatdong.TenHoatDong;
sheetTitle.Font.Bold = true;
sheetTitle.Font.Size = 20;
sheetTitle.HorizontalAlignment = ExcelCOM.XlHAlign.xlHAlignCenter;
sheetTitle.Rows.AutoFit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheetTitle);
for (int i = 0; i < GridViewDangKyHoatDong.Columns.Count; i++)
{
WriteIndexColumn(worksheet, 3, i + 1, GridViewDangKyHoatDong.Columns[i].ToString());
}
}
//检查文字考点，参数为考点列表、继续检索的位置、学生工作表、标答工作表
private int check_Words(List<OfficeElement> ls, int startPosition, Excel.Worksheet stuWs, Excel.Worksheet ansWs)
{
int thisPoint = 0;
int i;
if (stuWs == null)
return 0;
for (i = startPosition; i < ls.Count; i++)
{
OfficeElement oe = ls[i];
if (oe.AttribName == "Name")
{
if (stuWs.Name == ansWs.Name)
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "Range")
{
stuRange = stuWs.get_Range(oe.AttribValue, nullobj);
ansRange = ansWs.get_Range(oe.AttribValue, nullobj);
continue;
}
if (oe.AttribName == "Font")
{
continue;
}
#region Fonts
if (oe.AttribName == "FontName")
{
if (stuRange.Font.Name.ToString() == ansRange.Font.Name.ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "Size")
{
if (stuRange.Font.Size.ToString() == ansRange.Font.Size.ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "Bold")
{
if (stuRange.Font.Bold.ToString() == ansRange.Font.Bold.ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "Italic")
{
if (stuRange.Font.Italic.ToString() == ansRange.Font.Italic.ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "Underline")
{
if (stuRange.Font.Underline.ToString() == ansRange.Font.Underline.ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "Color")
{
if (stuRange.Font.Color.ToString() == ansRange.Font.Color.ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
#endregion
#region Manipulation
if (oe.AttribName == "SubTotal" || oe.AttribName == "Sort")
{
int row = ansRange.Rows.Count, col = ansRange.Columns.Count;
int check = 1;
Excel.Range stuCmp, ansCmp;
int p, q;
if (check == 0) { continue; }
for (p = 1; p <= row; p++)
{
for (q = 1; q <= col; q++)
{
stuCmp = (Excel.Range)stuRange.Cells[p, q];
ansCmp = (Excel.Range)ansRange.Cells[p, q];
if (stuCmp.Text.ToString() != ansCmp.Text.ToString())
{
check = 0;
break;
}
}
if (check == 0)
break;
}
if (check == 0)
continue;
if (oe.AttribName == "SubTotal") //分类汇总还需要判断大纲层次是否一致
{
for (p = 1; p <= ansRange.Rows.Count; p++)
{
if (((Excel.Range)ansRange.Rows[p, nullobj]).ShowDetail.ToString() !=
((Excel.Range)stuRange.Rows[p, nullobj]).ShowDetail.ToString()) //判断大纲层次是否一致
{
check = 0;
break;
}
}
}
if (check == 1)
thisPoint += int.Parse(oe.AttribValue);
continue;
}
#endregion
#region Contents
if (oe.AttribName == "NumberFormat")
{
try
{
if (stuRange.NumberFormat.ToString() == ansRange.NumberFormat.ToString())
thisPoint += int.Parse(oe.AttribValue);
}
catch { }
continue;
}
if (oe.AttribName == "Text")
{
try
{
if (stuRange.Text.ToString() == ansRange.Text.ToString())
thisPoint += int.Parse(oe.AttribValue);
}
catch { }
continue;
}
if (oe.AttribName == "Formula")
{
string stuFormula = stuRange.Formula.ToString(), ansFormula = ansRange.Formula.ToString();
if (stuFormula == ansFormula)
thisPoint += int.Parse(oe.AttribValue);
else
{
stuFormula = stuFormula.Replace("$", "");
stuFormula = stuFormula.Replace(" ", "");
ansFormula = ansFormula.Replace("$", "");
ansFormula = ansFormula.Replace(" ", "");
if (stuFormula == ansFormula)
thisPoint += int.Parse(oe.AttribValue);
}
continue;
}
#endregion
#region Styles
if (oe.AttribName == "Border")
{
#region 边框线型
if (((Excel.XlLineStyle)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle).ToString() !=
((Excel.XlLineStyle)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle).ToString())
continue;
if (((Excel.XlLineStyle)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle).ToString() !=
((Excel.XlLineStyle)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle).ToString())
continue;
if (((Excel.XlLineStyle)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle).ToString() !=
((Excel.XlLineStyle)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle).ToString())
continue;
if (((Excel.XlLineStyle)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle).ToString() !=
((Excel.XlLineStyle)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle).ToString())
continue;
if (((Excel.XlLineStyle)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle).ToString() !=
((Excel.XlLineStyle)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle).ToString())
continue;
if (((Excel.XlLineStyle)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle).ToString() !=
((Excel.XlLineStyle)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle).ToString())
continue;
#endregion
#region 边框粗细
if (((Excel.XlBorderWeight)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight).ToString() !=
((Excel.XlBorderWeight)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight).ToString())
continue;
if (((Excel.XlBorderWeight)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight).ToString() !=
((Excel.XlBorderWeight)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight).ToString())
continue;
if (((Excel.XlBorderWeight)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight).ToString() !=
((Excel.XlBorderWeight)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight).ToString())
continue;
if (((Excel.XlBorderWeight)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight).ToString() !=
((Excel.XlBorderWeight)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight).ToString())
continue;
if (((Excel.XlBorderWeight)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).Weight).ToString() !=
((Excel.XlBorderWeight)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).Weight).ToString())
continue;
if (((Excel.XlBorderWeight)stuRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).Weight).ToString() !=
((Excel.XlBorderWeight)ansRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).Weight).ToString())
continue;
#endregion
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "InteriorPattern")
{
if (((Excel.XlPattern)stuRange.Interior.Pattern).ToString() ==
((Excel.XlPattern)ansRange.Interior.Pattern).ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "RowHeight")
{
if (stuRange.RowHeight.ToString() == ansRange.RowHeight.ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "ColWidth")
{
if (stuRange.ColumnWidth.ToString() == ansRange.ColumnWidth.ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "Align")
{
if (((Excel.XlHAlign)stuRange.HorizontalAlignment).ToString() ==
((Excel.XlHAlign)ansRange.HorizontalAlignment).ToString())
thisPoint += int.Parse(oe.AttribValue);
continue;
}
if (oe.AttribName == "Merge")
{
if (ansRange.MergeArea.Count != stuRange.MergeArea.Count) //先判断包含的单元格数量是否一致
continue;
Excel.Range stuFirstRange, ansFirstRange;
ansFirstRange = ansRange.MergeArea;
stuFirstRange = stuRange.MergeArea;
if (checkRangeSame(stuFirstRange, ansFirstRange) == false) //判断左上角单元格是否一致
continue;
#region unused
//for (p = 1; ; p++)
//{
// tmpRange = (Excel.Range)ansFirstRange.Cells[p, 1];
// if (checkRangeSame(tmpRange.MergeArea, ansRange.MergeArea) == false)
// { ansRow = p - 1; break; }
//}
//for (p = 1; ; p++)
//{
// tmpRange = (Excel.Range)ansFirstRange.Cells[1, p];
// if (checkRangeSame(tmpRange.MergeArea, ansRange.MergeArea) == false)
// { ansCol = p - 1; break; }
//}
//for (p = 1; ; p++)
//{
// tmpRange = (Excel.Range)stuFirstRange.Cells[p, 1];
// if (checkRangeSame(tmpRange.MergeArea, stuRange.MergeArea) == false)
// { stuRow = p - 1; break; }
//}
//for (p = 1; ; p++)
//{
// tmpRange = (Excel.Range)stuFirstRange.Cells[1, p];
// if (checkRangeSame(tmpRange.MergeArea, stuRange.MergeArea) == false)
// { stuCol = p - 1; break; }
//} //计算合并单元格的行数和列数
#endregion
if (ansRange.MergeArea.Rows.Count == stuRange.MergeArea.Rows.Count
&& ansRange.MergeArea.Columns.Count == stuRange.MergeArea.Columns.Count)
thisPoint += int.Parse(oe.AttribValue);
continue;
}
#endregion
}
return thisPoint;
}
private List<int> CompareLists(Excel.Worksheet sheet1, Excel.Worksheet sheet2, string[] sheet1Columns, string[] sheet2Columns, bool sheet1HeaderRow, bool sheet2HeaderRow, bool ignoreCaps, bool ignoreSpecialChars)
{
List<int> sheetIndices = new List<int>();
//Gets the range and columns in the worksheet that are used. Range will be used to loop, and col to keep data intact
var sheet1NumOfCols = sheet1.UsedRange.Columns.Count;
var sheet1NumOfRows = sheet1.UsedRange.Rows.Count;
var sheet2NumOfCols = sheet2.UsedRange.Columns.Count;
var sheet2NumOfRows = sheet2.UsedRange.Rows.Count;
// Compare each row in sheet1 to every row in sheet2. If a match is found, save the current row's index
for (int i = (!sheet1HeaderRow) ? 1 : 2; i <= sheet1NumOfRows; i++)
{
StringBuilder sheet1RowString = new StringBuilder();
bool matchFound = false;
// Create the comparison string for the current row in sheet 1
foreach (string column in sheet1Columns)
{
// Add each cells' contents to the string
Range cell = sheet1.get_Range(column + i.ToString()); //sheet1.Cells[i, j] as Range;
if (cell.Value != null)
{
string value = Convert.ToString(cell.Value);
if (ignoreSpecialChars) // @"\s+"
value = Regex.Replace(value, "[^0-9a-zA-Z]", "");
sheet1RowString.Append(value + ",");
}
}
sheet1RowString.Replace(" ", "");
// Compare the row in the base sheet with every row in the compare sheet
for (int k = (!sheet2HeaderRow) ? 1 : 2; k <= sheet2NumOfRows; k++)
{
StringBuilder sheet2RowString = new StringBuilder();
// Create the comparison string for the row in sheet 2
foreach (string column in sheet2Columns)
{
// Add each cells' contents to the string
Range cell = sheet2.get_Range(column + k.ToString());
if (cell.Value != null)
{
string value = Convert.ToString(cell.Value);
if (ignoreSpecialChars)
value = Regex.Replace(value, "[^0-9a-zA-Z]", "");
sheet2RowString.Append(value + ",");
}
}
sheet2RowString.Replace(" ", "");
string sheet1Row = sheet1RowString.ToString();
string sheet2Row = sheet2RowString.ToString();
bool romanize = true;
if (ignoreCaps)
{
sheet1Row = sheet1Row.ToLower();
sheet2Row = sheet2Row.ToLower();
}
if (romanize)
{
sheet1Row = Unidecoder.Unidecode(sheet1Row);
sheet2Row = Unidecoder.Unidecode(sheet2Row);
}
// Compare the two rows and see if they are the same
if (sheet1Row == sheet2Row)
{
matchFound = true;
break;
}
}
// If a similar entry was not found in the other list, add it to the list
if (!matchFound)
{
sheetIndices.Add(i);
}
}
return sheetIndices;
}
private Excel.Range findRow(Excel.Worksheet portfolioWorkSheet, Excel.Range row, int rowNumber)
{
Excel.Range currentFind = null;
Excel.Range firstFind = null;
Excel.Range previousColumn1 = portfolioWorkSheet.get_Range("A1:A" + (rowNumber - 1));
currentFind = previousColumn1.Find(row.Cells[1, 1]);
while (currentFind != null)
{
Excel.Range currentRow = currentFind.EntireRow;
if (equalRows(currentRow, row))
{
return currentRow;
}
if (firstFind == null)
{
firstFind = currentFind;
}
else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1)
== firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
currentFind = previousColumn1.FindNext(currentFind);
}
return null;
//for (int i = portfolioWorkSheet.UsedRange.Rows.Count - 1; i > 1; i--)
//{
// Excel.Range currentRow = portfolioWorkSheet.Rows[i];
// //Excel.Range currentRow = (portfolioWorkSheet.get_Range(portfolioWorkSheet.Cells[i, 1], portfolioWorkSheet.Cells[i, baseEffectiveColumnNumber]) as Excel.Range);
// if (equalRows(currentRow, row))
// {
// return currentRow;
// }
//}
//return null;
}
/// <summary>
/// Schreibt einen Wert in die Zelle des gegebenen Excel-Sheets
/// </summary>
/// <param name="sheet">Das Excel Sheet.</param>
/// <param name="zelle">Die Zelle, z. B. A2.</param>
/// <param name="value">Den Wert der Zelle als String.</param>
public void WriteValueProtectedCell(Excel.Worksheet sheet, string zelle, string value)
{
sheet.Unprotect("1111");
Excel.Range r = sheet.get_Range(zelle, missing);
r.Value2 = value;
sheet.Protect("1111", false, true);
}
/// <summary>
/// Liest einen Wert aus einer Zelle des gegebenen ExcelSheets.
/// </summary>
/// <param name="sheet">Das Excel Sheet.</param>
/// <param name="zelle">Die Zelle, z. B. A2.</param>
/// <returns>Den Wert der Zelle als String.</returns>
public string ReadValue(Excel.Worksheet sheet, string zelle)
{
Excel.Range r = sheet.get_Range(zelle, missing);
return r.Value2 == null ? null : Convert.ToString(r.Value2).Trim();
}
void InitInstrumentFromRange(string a_rangeInput, Excel.Worksheet a_sheet)
{
if (a_rangeInput.Length <= 0)
{
return;
}
Excel.Range l_instrumentRange = a_sheet.get_Range(a_rangeInput);
if (l_instrumentRange.Rows.Count == 1 && l_instrumentRange.Columns.Count == 1)
{
string l_valueArrTmp = l_instrumentRange.get_Value();
m_instrumentList.Add(l_valueArrTmp);
}
else
{
System.Array l_valueArrTmp = (System.Array)l_instrumentRange.get_Value();
foreach (string l_value in l_valueArrTmp)
{
m_instrumentList.Add(l_value);
}
}
}
//, object cell2, int fontSize, int InteriorColor, bool merge
//單元格樣式
private void CellStyle(Excel.Application excel, object cell1, object cell2, string rangeName, bool merge)
{
Excel.Range TempRange = excel.get_Range(cell1, cell2);
TempRange.Merge(merge); //合并单元格
TempRange.MergeCells = merge;
//TempRange.WrapText = true;
//TempRange.Font.Size = 10;//字体大小
TempRange.WrapText = true; //文本自动换行
// TempRange.ShrinkToFit = true;//縮小字體填充
//TempRange.Font.Name = "楷体";//設置字體 //標楷體
TempRange.Value2 = rangeName;
//TempRange.EntireColumn.AutoFit(); //自动列宽
//TempRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop ].LineStyle = Excel.XlLineStyle.xlDot;
//TempRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;
//TempRange.Cells.Interior.Color = -4142;
//TempRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平居中
}
/// <summary>
/// Adds all headers in the <c>Excel.Worksheet</c>.
/// </summary>
/// <param name="sheet">The <c>Excel.Worksheet</c> to create the headers in.</param>
private void addHeaders(Excel.Worksheet sheet)
{
Microsoft.Office.Interop.Excel.Range headerRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A1", "E1");
headerRange.Cells[1, COLUMN_DATE].Value = "Date";
headerRange.Cells[1, COLUMN_STUDENT].Value = "Student";
headerRange.Cells[1, COLUMN_TYPE].Value = "Type";
headerRange.Cells[1, COLUMN_GRADING].Value = "Grading";
headerRange.Cells[1, COLUMN_COMMENT].Value = "Comment";
}
/// <summary>
/// This method is called by <see cref="updateLogtable(string, string, string)"/> and <see cref="createLogtable(string, string, string)"/> and is responsible for filling and formatting the cells of the <c>Excel.Worksheet</c>.
/// </summary>
/// <param name="classToken">The class the <see cref="ExcelAllGrade.model.Gradelog"/>s come from.</param>
/// <param name="subjectToken">The subject the <see cref="ExcelAllGrade.model.Gradelog"/>s come from.</param>
/// <param name="teacherToken">The teacher that teaches this <paramref name="classToken"/>.</param>
/// <param name="sheet">The <c>Excel.Worksheet</c> to write the <see cref="ExcelAllGrade.model.Gradelog"/>s in.</param>
/// <param name="gradelogs">All <see cref="ExcelAllGrade.model.Gradelog"/>s from a specific <see cref="ExcelAllGrade.model.Lesson"/>.</param>
private void createOrUpdateLogtable(string classToken, string subjectToken, string teacherToken, Excel.Worksheet sheet, List<Gradelog> gradelogs)
{
this.addHeaders(sheet);
gradelogs.Sort(
delegate(Gradelog a, Gradelog b)
{
int diff = a.GradelogTimestamp.CompareTo(b.GradelogTimestamp);
return diff;
}
);
for (int i = 0; i < gradelogs.Count; i++)
{
addGradelogToLogtable(sheet, gradelogs.ElementAt(i), i + 1);
}
Excel.Range headers = sheet.get_Range("A1", "E1");
headers.Font.Bold = true;
headers.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
headers.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
Excel.Range allCells = sheet.get_Range("A1", "E" + (gradelogs.Count + 1));
allCells.Columns.AutoFit();
allCells.Rows.AutoFit();
allCells.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
/// <summary>
/// Adds a single <see cref="ExcelAllGrade.model.Gradelog"/> to the <c>Excel.Worksheet</c>.
/// </summary>
/// <param name="sheet">The <c>Excel.Worksheet</c> to write the <see cref="ExcelAllGrade.model.Gradelog"/>s in.</param>
/// <param name="gradelog">The <see cref="ExcelAllGrade.model.Gradelog"/> which should be added to the <c>Excel.Worksheeet</c>.</param>
/// <param name="index">The index of the <see cref="ExcelAllGrade.model.Gradelog"/>.</param>
private void addGradelogToLogtable(Excel.Worksheet sheet, Gradelog gradelog, int index)
{
Microsoft.Office.Interop.Excel.Range gradelogRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A" + (index + 1),
"E" + (index + 1));
gradelogRange.Cells[1, COLUMN_DATE].Value = gradelog.GradelogTimestamp.ToString();
gradelogRange.Cells[1, COLUMN_STUDENT].Value = gradelog.GradelogStudent;
gradelogRange.Cells[1, COLUMN_TYPE].Value = gradelog.GradelogType;
gradelogRange.Cells[1, COLUMN_GRADING].Value = gradelog.GradelogGrading;
gradelogRange.Cells[1, COLUMN_COMMENT].Value = gradelog.GradelogComment;
if (gradelog.GradelogGrading == 5)
{
gradelogRange.Cells[1, COLUMN_GRADING].Font.Bold = true;
}
if (gradelog.GradelogType.Equals(Gradelog.GradelogTypeEnum.Test.GetStringValue()))
{
gradelogRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(COLOR_TEST);
}
else if (gradelog.GradelogType.Equals(Gradelog.GradelogTypeEnum.SA.GetStringValue()))
{
gradelogRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(COLOR_SA);
}
else
{
gradelogRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.White);
}
}
private static void validator(Excel.Worksheet sheet, int lastCellRowNum, Color color, string subject)
{
FormatCondition cond = sheet.get_Range("A1:I" + lastCellRowNum, Type.Missing).FormatConditions.Add(XlFormatConditionType.xlTextString, Type.Missing, Type.Missing, Type.Missing, subject, XlContainsOperator.xlContains, Type.Missing, Type.Missing);
cond.Interior.Color = color;
}
//Done vad som va målet inte det som står th.: returnera cellen eller cellrange o gör det möjligt att i efterhand göra autoFitColumnWidth. //Excel.Range addRow
public static int addRow(Excel.Worksheet sheet, string saveAsSheetName, ref object[] oa, Hashtable cellLayOutSettings, bool autofit, System.Drawing.Color color, int insertInRow, int insertInColumn, params object[] args)
{
try
{
if (args.Length == 0)
{
return -1;
}
else if (args.Length.Equals(1) && (args[0] as object[]) != null )
{
args = args[0] as object[];
}
int nextRow = (int)oa[1];
//spara cellerna som det skrivs till i en sträng-array, skr sedan alla på en gång
//string[,] cellsToWrite = new string[1, args.Length + insertInColumn];
object[,] cellsToWrite = null;
if (args.Length == 1)//onödig?, hmm nej, inte om det är special för 1 grejj, ska man inte kunna skriva enradsgrejjer till andra kolumnerm, hm det har med DbInfos new och +/- kolumn att göra troligt
{
cellsToWrite = new object[1, args.Length];// + insertInColumn
}
else
{
cellsToWrite = new object[1, args.Length + insertInColumn];
}
//string toWriteIncells = args;
#region Write each cell at a time to temp variable
int rowWrittenTo = 0;
for (int i = insertInColumn; i < args.Length + insertInColumn; i++)
{
//string toWriteIncell = args[i - insertInColumn].ToString();
//Om det inte finns något att skriva, gå till nästa
if (args[i - insertInColumn] == null)
continue;
//Strängar längre än ca912 kan inte skrivas till en cell, uten ger ett exception med lite info i. Så längder över 900 tecken klipps bort.
const int maxCellLength = 900;
//foreach (var arg in args[0] as object[])
//{
// if (arg.ToString().Length > maxCellLength)
// {
// arg = arg.ToString().Substring(0, 900);
// }
//}
object toWriteIncell = args[i - insertInColumn];
//(args[i - insertInColumn].ToString()).Length > 900 ?
// (args[i - insertInColumn].ToString()).Substring(0, 900)
// : args[i - insertInColumn].ToString();
//Det blir problem med celler som börjar med "=", och sedan inte ger en riktig formel, så detta sätts till
//TODO: Fixa något allmänt test för formler som kan gå fel, eller formatera rangen som text, men det vill man iofs inte alltid...
//if (toWriteIncell.ToString().StartsWith("=") && toWriteIncell.ToString().Contains("x")) toWriteIncell = " " + toWriteIncell;
rowWrittenTo = nextRow;//Vilken rad som verkligen skrivits till, används för layout av cellen
if (insertInRow > 0)
{
//sheet.Cells[insertInRow, i + 1] = toWriteIncell;
cellsToWrite[0, i - insertInColumn] = toWriteIncell;
rowWrittenTo = insertInRow;
}
else// if (args[i] != null)
{
//sheet.Cells[nextRow, i + 1] = toWriteIncell;
//cellsToWrite[0, i] = toWriteIncell;
cellsToWrite[0, i] = toWriteIncell;// (toWriteIncell as string);//.Length > 900 ?
//(toWriteIncell as string).Substring(0, 900)
//: toWriteIncell;
}
#region old Exceltester
//cellRange.Interior.ColorIndex = 36;//36 = Gul//Fungerar
//cellRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);//Fungerar
//rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//((Excel.Worksheet)sheet.Activate())
//objRange.Font.Background = 4.0;
//string strData = objRange.get_Value(Type.Missing).ToString();
//objRange.Select();
//objRange.Style =
//object tempObject = objRange.Borders.Color;
//objRange.Borders.Color = 5.0;//Ändrar faktiskt ramen för cellen
//((Range)sheet.Cells[nextRow, i + 1]).AutoFit();
//sheet.Cells[nextRow, i + 1].AutoFit();
#endregion
}
#endregion
//Write cells several at a tiem, Fill A2:B6 with an array of values (First and Last Names).
string fromColumn = Utilities.ExcelLogRowComparer.GetStandardExcelColumnName(insertInColumn + 1);//nextRow.ToString();
string toColumn = Utilities.ExcelLogRowComparer.GetStandardExcelColumnName(args.Length + insertInColumn);//nextRow.ToString();
Excel.Range
cellRange = sheet.get_Range(fromColumn + rowWrittenTo.ToString(), toColumn + rowWrittenTo.ToString());
//Write to excel sheet
cellRange.Value2 = cellsToWrite;//"A"
#region buggtest Skriv till ExcelSheet
//Excel.Range //cellRange = null;
//object[,] tempp = new object[2, 5] { "a", "f", "g", "h" };
//cellRange.Value2 = cellsToWrite;//"A"
//object to = new object[] { cellsToWrite };
//fromColumn = Utilities.ExcelLogRowComparer.GetStandardExcelColumnName(insertInColumn + 1);//nextRow.ToString();
//toColumn = Utilities.ExcelLogRowComparer.GetStandardExcelColumnName(cellsToWrite.Length + insertInColumn);//nextRow.ToString();
//cellRange = sheet.get_Range(fromColumn + rowWrittenTo.ToString(), toColumn + rowWrittenTo.ToString());
//buggtest
//object[,] tempp = new object[2, 5] { "a", "f", "g", "h" };
//cellRange.Value2 = cellsToWrite;//"A"
//object to = new object[] { cellsToWrite };
#endregion
#region Layout (färg, autofit column etc)
if (
(cellLayOutSettings != null && cellLayOutSettings.Count > 0)
|| (autofit)
|| (color != System.Drawing.Color.Empty)
)
{
//Excel.Range //cellRange = null;
// cellRange =
// sheet.get_Range(fromColumn + rowWrittenTo.ToString(), toColumn + rowWrittenTo.ToString());//"A"
//(Excel.Range)sheet.Cells[rowWrittenTo, i + 1];
if (cellLayOutSettings != null && cellLayOutSettings.Count > 0)
{
EditCellLayOut(cellLayOutSettings, cellRange);
}
//Det hade med insertrow att göra, så det va fel range hela tiden...Inte ens detta ger bold på columnnamnen
//Excel.Range cellRanges = (Excel.Range)sheet.Cells[nextRow, i + 1];
//cellRanges.Font.Bold = true;
//cellRange.Font.Color = System.Drawing.ColorTranslator.ToOle(color);
//color = System.Drawing.Color.Empty;
if (autofit)
{
cellRange.EntireColumn.AutoFit();//autofittar hela columnen för all som loggas
//cellRange.Font.Bold = true;
}
if (color != System.Drawing.Color.Empty)
{
cellRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);//Fungerar
}
}
#endregion
System.Runtime.InteropServices.Marshal.ReleaseComObject(cellRange);
cellRange = null;
if (insertInRow > 0)
nextRow--;
oa[1] = nextRow + 1;//efter detta ska det kollas om maxrader är uppnått
return nextRow;
}
catch (Exception e)
{
string allArgs = "";
foreach (var item in args)
{
allArgs += ";" + item;
}
if (allArgs == "") allArgs = "<empty>";
string errMess = "Error in Logger. In sheet; " + saveAsSheetName + ", may be Excel error: " + e.Message + "\r\n" + "Tried to Log" + allArgs;
Console.WriteLine(errMess);
try
{
if (!_uniqueLoggerErrorMessages.ContainsKey(errMess))
{
_uniqueLoggerErrorMessages.Add(errMess, 1);
//Kolla så inte samma skrivs ut hela tiden
TextWriter tW = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + @"Logs\LoggerExceptions.txt");
string toLogerrMess = "";
foreach (var item in _uniqueLoggerErrorMessages.Keys)
{
toLogerrMess += item;
}
tW.Write(toLogerrMess);
tW.Close();
}
}
catch (Exception excExcp)
{
Console.WriteLine("Error in Logger in sheet; " + saveAsSheetName + ", error with error reporting: " + excExcp.Message);
}
//throw e;
//return null;
return -1;
}
}
//获取单个单元格
private Excel.Range getCell(Excel.Worksheet ws, int row, int col)
{
return (Excel.Range)ws.get_Range(ws.Cells[row, col], ws.Cells[row, col]);
}
//currently the excel sheets(ds and info) data structure are hard-coded,
//will improve later
private List<Product> ReadIdInfoIntoDataTable(Excel._Worksheet sheet)
{
List<Product> listProducts = new List<Product>();
//sheet header:
//Device Family | Product | SW_WHOAMI | Production SWRev | ES SWRev | Continuity
string[] header = { "Device Family", "Product", "SW_WHOAMI", "Production SWRev", "ES SWRev", "Continuity" };
int posDeviceFamily = 1;
int posProduct = 2;
int posSWWhoAmI = 3;
int posContinuity = 6;
List<string> list = new List<string>();
string[] val = new string[4];
string sep = ", ";
string[] sep2 = { "," };
List<string> idinfo = new List<string>();
if (sheet != null) {
//create a list without any duplicate
Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = sheet.get_Range("A1", last);
int rows = last.Row;
string cellName = string.Empty;
string cellVal = string.Empty;
for (int i = 2; i < rows; i++) {
val[0] = ExcelGetValue(GetExcelColumnName(posDeviceFamily) + i.ToString(), sheet);
val[1] = ExcelGetValue(GetExcelColumnName(posProduct) + i.ToString(), sheet);
val[2] = ExcelGetValue(GetExcelColumnName(posSWWhoAmI) + i.ToString(), sheet);
val[3] = ExcelGetValue(GetExcelColumnName(posContinuity) + i.ToString(), sheet);
list.Add(String.Join(sep, val));
}
//now remove duplicates
idinfo = list.Distinct().ToList();
}
if (idinfo != null) {
foreach (string prod in idinfo) {
val = prod.Split(',');
FamilyName familyname = GetFamilyName(val[0].Trim());
ProductName productname = GetProductName(val[1].Replace("-", "").Trim());
byte whoami = Convert.ToByte(val[2].Trim(), 16);
int continuity = Convert.ToInt16(val[3].Trim());
Product product = new Product(familyname, productname, whoami, continuity);
listProducts.Add(product);
}
}
return listProducts;
}
//获取单元格区域
private Excel.Range getRegion(Excel.Worksheet ws, int row0, int col0, int row1, int col1)
{
return (Excel.Range)ws.get_Range(ws.Cells[row0, col0], ws.Cells[row1, col1]);
}
private void SelectColumnsInRange(Excel.Worksheet sheet, string columns)
{
if (ValidateColumnInput(sheet, columns, false))
{
// Make sure the provided sheet is selected
sheet.Select();
string[] columnArray = columns.ToUpper().Split(',');
StringBuilder columnRangeString = new StringBuilder();
foreach (string column in columnArray)
{
// A blank column will only occur if extra commas were placed after the columns
if (column == "")
continue;
columnRangeString.Append(column + ":" + column + ",");
}
columnRangeString.Length = columnRangeString.Length - 1;
Range range = sheet.get_Range(columnRangeString.ToString(), Type.Missing);
range.EntireColumn.Select();
}
}
//合併單元格
private void MergeColumn(Excel.Application excel, object cell1, object cell2, string rangeName, int fontSize, int InteriorColor, double columnWidth)
{
Excel.Range TempRange = excel.get_Range(cell1, cell2);
TempRange.Merge(true); //合并单元格
TempRange.MergeCells = true;
TempRange.WrapText = true;
TempRange.Value2 = rangeName;//标题
TempRange.Font.Bold = true;//标题加粗
TempRange.Font.Size = fontSize;//字体大小
//TempRange.WrapText = true; //文本自动换行
TempRange.Font.Name = "楷体";//設置字體 //繁體時 ： 標楷體 ;簡體是：楷体
TempRange.EntireColumn.AutoFit(); //自动列宽
TempRange.Borders.LineStyle = 1;
if (columnWidth > 0)
{
TempRange.ColumnWidth = columnWidth;
}
TempRange.Cells.Interior.Color = InteriorColor;
TempRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平居中
}
/// <summary>
/// Schreibt einen Wert in die Zelle des gegebenen Excel-Sheets
/// </summary>
/// <param name="sheet">Das Excel Sheet.</param>
/// <param name="zelle">Die Zelle, z. B. A2.</param>
/// <param name="value">Den Wert der Zelle als String.</param>
public void WriteValue(Excel.Worksheet sheet, string zelle, string value)
{
Excel.Range r = sheet.get_Range(zelle, missing);
r.Value2 = value;
}
public void rangeToGroup(Excel.Worksheet sheet,int from,int to)
{
Excel.Range range = sheet.get_Range(sheet.Cells[from + 2, 1], sheet.Cells[to + 1, 1]);
this.AppendLine("group from " + (from + 2) + " to " + (to + 1));
range.Rows.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
protected void AddValueToRange(excel.Worksheet xlsWorksheet, excel.Range rng, string cell, object value)
{
object missing = System.Reflection.Missing.Value;
rng = xlsWorksheet.get_Range(cell, missing);
rng.Value = value;
}
protected void RemoveDataValidation(excel.Worksheet xlsWorksheet, excel.Range rng, string cell)
{
object missing = System.Reflection.Missing.Value;
//Select the specified cell
rng = (Microsoft.Office.Interop.Excel.Range)xlsWorksheet.get_Range(cell, missing);
//Delete any previous validation
rng.Validation.Delete();
}
private ReportDataTable GetDataTable(Excel.Worksheet sheet, int headerRowNumber)
{
int columnCount = 0;
int rowCount = 0;
//calculate the column number
while (true)
{
string columnName = ExcelColumnIndexToName(columnCount + 1);
var range = sheet.get_Range(columnName + headerRowNumber);
if ((range != null && !string.IsNullOrEmpty(((string)range.Value2))) || columnCount < 3)
{
columnCount++;
}
else
{
break;
}
}
//calculate the row number
while (true)
{
var range = sheet.get_Range("A" + (headerRowNumber + rowCount));
if (range != null && !string.IsNullOrEmpty(((string)range.Value2)))
{
rowCount++;
}
else
{
break;
}
}
string[,] data = new string[rowCount, columnCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
string columnName = ExcelColumnIndexToName(j + 1);
var range = sheet.get_Range(columnName + (headerRowNumber + i));
if (i == 0 && columnName == "B" && (range == null || range.Value2 == null))
{
data[i, j] = "FormulaColumn";
}
else
{
data[i, j] = (range == null || range.Value2 == null) ? string.Empty : range.Value2.ToString();
}
}
}
ReportDataTable dataset = new ReportDataTable(data,sheet.Name);
return dataset;
}
