/// <summary>
/// 设置条件格式.
/// </summary>
/// <param name="fromSheetName"> 需要设置条件格式的 Sheet 名字.</param>
/// <param name="formula1"> 条件公式. </param>
/// <param name="bold"> 满足条件后的行 是否粗体. </param>
/// <param name="italic"> 满足条件后的行 是否斜体. </param>
/// <param name="color"> 满足条件后的行 字体的颜色. </param>
public void SetFormatConditionsExpression(
string fromSheetName,
string formula1,
bool bold,
bool italic,
int color)
{
// 选择源工作表.
Excel.Worksheet mySheet = (Excel.Worksheet)xlBook.Sheets.get_Item(fromSheetName);
// 选择
mySheet.Select();
// 范围选择.
Excel.Range myRange = mySheet.Range["A2", GetEndAddress(mySheet)];
// 设置公式.
Excel.FormatCondition myCond = myRange.FormatConditions.Add(
Excel.XlFormatConditionType.xlExpression,
Type.Missing,
formula1);
// 设置条件字体.
myCond.Font.Bold = bold;
myCond.Font.Italic = italic;
myCond.Font.Color = color;
}
/// <summary>
/// 设置条件格式.
/// </summary>
/// <param name="fromSheetName"> 需要设置条件格式的 Sheet 名字.</param>
/// <param name="baseAddress"> 基准地址. </param>
/// <param name="startAddress"> 起始地址. </param>
/// <param name="subRow"> 结束行递减数量. </param>
/// <param name="subCol"> 结束列递减数量. </param>
/// <param name="oper"> 公式的判断方式. </param>
/// <param name="formula1"> 条件公式. </param>
/// <param name="bold"> 满足条件后的行 是否粗体. </param>
/// <param name="italic"> 满足条件后的行 是否斜体. </param>
/// <param name="color"> 满足条件后的行 字体的颜色. </param>
public void SetFormatConditionsCellValueLessEqual(
string fromSheetName,
string baseAddress,
string startAddress,
int subRow,
int subCol,
ExcelFormatConditionOperator oper,
string formula1,
bool bold,
bool italic,
int color)
{
// 选择源工作表.
Excel.Worksheet mySheet = (Excel.Worksheet)xlBook.Sheets.get_Item(fromSheetName);
// 选择
mySheet.Select();
// 范围选择.
string finishAddress = GetEndAddress(mySheet, baseAddress, subRow, subCol);
Excel.Range myRange = mySheet.Range[startAddress, finishAddress];
// 设置公式.
Excel.FormatCondition myCond = myRange.FormatConditions.Add(
Excel.XlFormatConditionType.xlCellValue,
oper,
formula1);
// 设置条件字体.
myCond.Font.Bold = bold;
myCond.Font.Italic = italic;
myCond.Font.Color = color;
}
public void setPriorityColor(Excel.Range priorityCell)
{
// get background color and translate to Color object
int count = priorityCell.FormatConditions.Count;
for (int i = 1; i <= count; i++)
{
Excel.FormatCondition formatObject = priorityCell.FormatConditions.Item(i) as Excel.FormatCondition;
if (formatObject.Operator == (int)Excel.XlFormatConditionOperator.xlEqual)
{
string formula = formatObject.Formula1;
int value = int.Parse(formula.Substring(1, formula.Length - 1));
Color filledColor = System.Drawing.ColorTranslator.FromOle(Convert.ToInt32(formatObject.Interior.Color as object));
Color fontColor = new Color();
object dynamicObj = formatObject.Font.Color;
// check DBNull type object
if (!(dynamicObj is DBNull))
{
fontColor = System.Drawing.ColorTranslator.FromOle(Convert.ToInt32(formatObject.Font.Color as object));
}
this.project.putPriorityColor(value, filledColor);
this.project.putPriorityFontColor(value, fontColor);
}
}
}
private static void RemoveCondition()
{
if (ConditionRange.FormatConditions != null && ConditionRange.FormatConditions.Count > 0)
{
try
{
for (Int32 i = 0; i < ConditionRange.FormatConditions.Count; i++)
{
if (i < 0 || i >= ConditionRange.FormatConditions.Count)
{
break;
}
MSExcel.FormatCondition c = ConditionRange.FormatConditions[i + 1] as MSExcel.FormatCondition;
if (c != null && c.Formula1.Contains(ConditionRangeName))
{
c.Delete();
i -= 1;
}
}
}
catch (Exception ex)
{
Msg.ShowError("聚光灯清除条件格式发生异常：" + ex.Message, ex);
}
}
}
internal void AddConditionalFormatting(Excel.ListObject LO)
{
// for each independent variable
foreach (Excel.ListColumn LC in LO.ListColumns)
{
if (DS.IndependentVariables.Contains(LC.Name))
{
double low = 0;
double high = 0;
double[] values = Utilities.DataHelper.objectTOdblArray(LC.Range.Value2 as object[, ]);
string fltr = "VariableName='" + LC.Name.Replace("'", "''") + "'";
DataRow row = DS.PredictorRange().Select(fltr).First();
// get the low value
low = Double.TryParse(row[1].ToString(), out low) ? Double.Parse(row[1].ToString()) : 0;
// get the high value
high = Double.TryParse(row[2].ToString(), out high) ? Double.Parse(row[2].ToString()) : 0;
// apply conditional formatting
if (low != 0 && high != 0)
{
Excel.Range rng = Utilities.ExcelHelpers.GetListColumn(LO, LC.Name).DataBodyRange;
Excel.FormatCondition fc = (Excel.FormatCondition)rng.FormatConditions.Add(
Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlNotBetween, low, high
, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
fc.Font.ColorIndex = 3;
}
}
}
}
public static void SetFormatSmaller(ExcelInterop.Range range, double limit)
{
Utility.AddNativieResource(range);
ExcelInterop.FormatConditions formcond = range.FormatConditions;
Utility.AddNativieResource(formcond);
ExcelInterop.FormatCondition newcond = formcond.Add(ExcelInterop.XlFormatConditionType.xlCellValue, ExcelInterop.XlFormatConditionOperator.xlLess, limit);
Utility.AddNativieResource(newcond);
ExcelInterop.Font condfont = newcond.Font;
Utility.AddNativieResource(condfont);
condfont.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); //Red letters
}
public void CreateMainUltimateLoadsSheet()
{
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets["MainUltimateLoads"];
if (bladedDatas.Count < 1)
{
Console.WriteLine("CreateMainUltimateLoadsSheet-bladedDatas.Count < 1 error!");
}
else
{
int col = 0;
int colCount = 5;
foreach (BladedData b in bladedDatas)
{
int row = 0;
int rowCount = 10;
//机组名称
Excel.Range rb = ws.get_Range(ws.Cells[row + 1, col + 1], ws.Cells[row + 1, col + colCount]);
rb.Merge();
rb.Value = b.turbineMainCompenontResult.turbineID;
//主要部件名称和数据
var comBase = bladedDatas[0].turbineMainCompenontResult.results.ultmateData.component;
foreach (var com in b.turbineMainCompenontResult.results.ultmateData.component)
{
Excel.Range rHeader = ws.get_Range(ws.Cells[row + 2, col + 1], ws.Cells[row + 2, col + colCount]); //主要部件名称
rHeader.Merge();
rHeader.Value = com.name;
Excel.Range rHeade1 = ws.get_Range(ws.Cells[row + 3, col + 2], ws.Cells[row + 3, col + colCount]); //主要部件名称
rHeade1.Value = new string[4] {
"DLC", "Value", "Path", "Div"
};
//数据放入excel表中
Excel.Range rData = ws.get_Range(ws.Cells[row + 4, col + 1], ws.Cells[(row + 4 + rowCount - 2), col + colCount]);
rData.Value = com.resultMatrix;
for (int i = 0; i < 8; i++)
{
string basediv = String.Format("R{0}C{1}", (row + 4 + i), 3);
string div = String.Format("R{0}C{1}", (row + 4 + i), col + 3);
Excel.Range comP = ws.get_Range(ws.Cells[row + 4 + i, col + colCount], ws.Cells[row + 4 + i, col + colCount]);
comP.FormulaR1C1 = "=" + div + "/" + basediv;
}
Excel.Range formatCell = ws.get_Range(ws.Cells[row + 1, col + 1], ws.Cells[row + rowCount + 1, col + colCount]);
formatCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//转化为数字，将>1.05的数值标红
Excel.Range numData = ws.get_Range(ws.Cells[row + 4, col + colCount], ws.Cells[row + 4 + rowCount - 2, col + colCount]);
Excel.FormatCondition condition1 = (Excel.FormatCondition)numData.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, "1.05", Type.Missing);
condition1.Interior.Color = 13551615;
row = row + rowCount + 1;
}
col = col + colCount + 1;
}
}
}
public void SetFormula()
{
try
{
Excel.Range rg;
rg = this.Range["T" + (TITLE_ROW_COUNT + 1).ToString(), "T" + (TITLE_ROW_COUNT + DataCount).ToString()];
rg.Formula = string.Format("=R{0}+S{0}", TITLE_ROW_COUNT + 1);
rg = this.Range["V" + (TITLE_ROW_COUNT + 1).ToString(), "V" + (TITLE_ROW_COUNT + DataCount).ToString()];
rg.Formula = string.Format("=R{0}+U{0}", TITLE_ROW_COUNT + 1);
rg = this.Range["X" + (TITLE_ROW_COUNT + 1).ToString(), "X" + (TITLE_ROW_COUNT + DataCount).ToString()];
rg.Formula = string.Format("=S{0}+W{0}", TITLE_ROW_COUNT + 1);
rg = this.Range["Y" + (TITLE_ROW_COUNT + 1).ToString(), "Y" + (TITLE_ROW_COUNT + DataCount).ToString()];
if (Globals.ThisWorkbook.TNTPlanCheck.Q1 == 0 && Globals.ThisWorkbook.TNTPlanCheck.Q3 == 0)
{
rg.Formula = string.Format("=X{0}", TITLE_ROW_COUNT + 1);
}
else if (Globals.ThisWorkbook.TNTPlanCheck.Q2 == 0 && Globals.ThisWorkbook.TNTPlanCheck.Q4 == 0)
{
rg.Formula = string.Format("=V{0}", TITLE_ROW_COUNT + 1);
}
else
{
rg.Formula = string.Format("=V{0}+X{0}", TITLE_ROW_COUNT + 1);
}
rg = this.Range["Z" + (TITLE_ROW_COUNT + 1).ToString(), "Z" + (TITLE_ROW_COUNT + DataCount).ToString()];
rg.Formula = string.Format("=IF(OR(AND(DashBoard!$J$2<>\"\",V{0}=0),AND(DashBoard!$J$3<>\"\",X{0}=0)),\"医院对应的岗位指标为0，这家医院的销量将无法计入该岗位\",\"\")", TITLE_ROW_COUNT + 1);
// 设置条件公式.
Excel.FormatCondition cond = rg.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlNotEqual, "\"\"");
cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
rg = this.Range["AA" + (TITLE_ROW_COUNT + 1).ToString(), "AA" + (TITLE_ROW_COUNT + DataCount).ToString()];
cond = rg.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "FALSE");
cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
rg = this.Range["AB" + (TITLE_ROW_COUNT + 1).ToString(), "AB" + (TITLE_ROW_COUNT + DataCount).ToString()];
cond = rg.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "TRUE");
cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
catch (Exception ex)
{
LogHelper.WriteError("", ex);
}
}
private void ColorColumnForPNCSpec(Excel.Worksheet worksheet, int column)
{
Excel.Range Start = worksheet.Cells[1, 2];
Excel.Range Finish = worksheet.Cells[1000, 2 + (2 * column)];
worksheet.Range[Start, Finish].Font.Color = Color.Red;
Start = worksheet.Cells[1, 3 + (2 * column)];
Finish = worksheet.Cells[1000, 3 + (4 * column)];
worksheet.Range[Start, Finish].Font.Color = Color.Green;
Start = worksheet.Cells[1, 4 + (4 * column)];
Finish = worksheet.Cells[1000, 4 + (5 * column)];
worksheet.Range[Start, Finish].Font.Color = Color.Red;
Start = worksheet.Cells[1, 5 + (5 * column)];
Finish = worksheet.Cells[1000, 5 + (6 * column)];
worksheet.Range[Start, Finish].Font.Color = Color.Green;
Start = worksheet.Cells[1, 7 + (6 * column)];
Finish = worksheet.Cells[1000, 7 + (6 * column)];
worksheet.Range[Start, Finish].Font.Color = Color.Red;
Start = worksheet.Cells[1, 8 + (6 * column)];
Finish = worksheet.Cells[1000, 8 + (6 * column)];
worksheet.Range[Start, Finish].Font.Color = Color.Green;
Start = worksheet.Cells[2, 9 + (6 * column)];
Finish = worksheet.Cells[1000, 9 + (6 * column)];
Excel.FormatCondition format = (Excel.FormatCondition)worksheet.Range[Start, Finish].FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, 0);
format.Font.Color = Color.Green;
format = (Excel.FormatCondition)worksheet.Range[Start, Finish].FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, 0);
format.Font.Color = Color.Red;
Start = worksheet.Cells[2, 1];
Finish = worksheet.Cells[1000, 9 + (6 * column)];
worksheet.Range[Start, Finish].NumberFormat = "0";
}
public void ExportDataTable(DataTable table, ExcelExportParm parms)
{
int totalCount = parms.UseView ? table.DefaultView.Count : table.Rows.Count;
ExportationStarted(this, new EventArgs <int>(table.Rows.Count));
new Thread(new ThreadStart(delegate
{
try
{
var excelApp = new ExcelApp.Application();
var excelWorkbook = excelApp.Workbooks.Add();
ExcelApp.Worksheet sheet = excelWorkbook.Worksheets.Add();
foreach (var sh in excelWorkbook.Worksheets.Cast <ExcelApp.Worksheet>().Where(sh => sh != sheet))
{
sh.Delete();
}
sheet.Name = string.IsNullOrWhiteSpace(table.TableName) ? "Table" : table.TableName;
var cols = table.Columns.OfType <DataColumn>().Select(dc => dc.ColumnName).ToList();
var excelHeaders = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, cols.Count]];
excelHeaders.Value2 = cols.ToArray();
ApplyStyle(excelHeaders, parms.DefaultCellFormat);
if (parms.HeaderFormat != null)
{
ApplyStyle(excelHeaders, parms.HeaderFormat);
}
var i = 2;
foreach (DataRow dr in parms.UseView ? table.DefaultView.OfType <DataRowView>().Select(x => x.Row) : table.Rows.OfType <DataRow>())
{
ProgressUpdated(this, new EventArgs <int>(i - 1));
var data = new string[cols.Count];
for (var j = 0; j < data.Length; ++j)
{
data[j] = dr[j].ToString();
}
var excelRow = sheet.Range[sheet.Cells[i, 1], sheet.Cells[i, cols.Count]];
ApplyStyle(excelRow, parms.DefaultCellFormat);
excelRow.Value2 = data;
excelRow.Rows.AutoFit();
i++;
}
foreach (var colName in parms.ColumnParms.Keys)
{
var col = parms.ColumnParms[colName];
var colId = cols.IndexOf(colName) + 1;
var xlCol = sheet.Range[sheet.Cells[2, colId], sheet.Cells[totalCount + 1, colId]];
if (col.EnumValues != null)
{
xlCol.Validation.Add(ExcelApp.XlDVType.xlValidateList
, ExcelApp.XlDVAlertStyle.xlValidAlertInformation
, ExcelApp.XlFormatConditionOperator.xlBetween
, string.Join(";", col.EnumValues)
, Type.Missing);
xlCol.Validation.InCellDropdown = true;
xlCol.Validation.ShowError = false;
}
var style = excelWorkbook.Styles.Add("col#" + colId);
style.HorizontalAlignment = col.HorizontalAlignment;
style.VerticalAlignment = col.VerticalAlignment;
xlCol.Style = style;
foreach (var fcond in col.FormatConditions)
{
ExcelApp.FormatCondition cond = xlCol.FormatConditions.Add(fcond.Type, fcond.Operator, fcond.Formula);
ApplyBorderStyle(parms.DefaultCellFormat.Border, cond.Borders, true);
ApplyFontStyle(parms.DefaultCellFormat, cond.Font);
ApplyInteriorStyle(parms.DefaultCellFormat, cond.Interior);
ApplyBorderStyle(fcond.Format.Border, cond.Borders, true);
ApplyFontStyle(fcond.Format, cond.Font);
ApplyInteriorStyle(fcond.Format, cond.Interior);
}
ExcelApp.FormatCondition dummyCond = xlCol.FormatConditions.Add(ExcelApp.XlFormatConditionType.xlCellValue, ExcelApp.XlFormatConditionOperator.xlNotEqual, "I AM DUMB FOR DEFINING DEFAULT FORMAT");
ApplyBorderStyle(parms.DefaultCellFormat.Border, dummyCond.Borders, true);
ApplyFontStyle(parms.DefaultCellFormat, dummyCond.Font);
ApplyInteriorStyle(parms.DefaultCellFormat, dummyCond.Interior);
}
sheet.Application.ActiveWindow.SplitRow = 1;
sheet.Application.ActiveWindow.FreezePanes = true;
var firstRow = (ExcelApp.Range)sheet.Rows[1];
firstRow.AutoFilter(1, Type.Missing, ExcelApp.XlAutoFilterOperator.xlAnd, Type.Missing, true);
firstRow.EntireColumn.AutoFit();
var firstcol = (ExcelApp.Range)sheet.Columns[1];
firstcol.EntireRow.AutoFit();
excelApp.Visible = true;
ExportationEnded(this, new EventArgs <bool>(true));
}
catch
{
ExportationEnded(this, new EventArgs <bool>(false));
}
})).Start();
}
public void CreateMainEequivalentFatigueLoadsSheet()
{
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets["MainEequivalentFatigueLoads"];
if (bladedDatas.Count < 1)
{
Console.WriteLine("CreateMainEequivalentFatigueLoadsSheet-bladedDatas.Count < 1 error!");
}
else
{
int colStart = 1;
int colCount = 4;
foreach (BladedData b in bladedDatas)
{
int rowStart = 1;
//机组名称
Excel.Range rb = ws.get_Range(ws.Cells[rowStart, colStart], ws.Cells[rowStart, colStart + colCount - 1]);
rb.Merge();
rb.Value = b.turbineMainCompenontResult.turbineID;
//主要部件名称和数据
var comBase = bladedDatas[0].turbineMainCompenontResult.results.equivalentFatigueData.component;
foreach (var com in b.turbineMainCompenontResult.results.equivalentFatigueData.component)
{
Excel.Range rHeader = ws.get_Range(ws.Cells[rowStart + 1, colStart], ws.Cells[rowStart + 1, colStart + colCount - 1]);
rHeader.Merge();
rHeader.Value = com.name;
//只输出m=4&&m=10
string[,] tempMatrix = new string[7, 5];
for (int i = 0; i < 7; i++)
{
tempMatrix[i, 0] = com.resultMatrix[0, i]; //表头
tempMatrix[i, 1] = com.resultMatrix[2, i]; //
tempMatrix[i, 2] = com.resultMatrix[8, i];
if (i == 0)
{
tempMatrix[i, 3] = com.resultMatrix[2, 0];
tempMatrix[i, 4] = com.resultMatrix[8, 0];
}
}
//变换输出格式
Excel.Range header1;
for (int i = 0; i < 6; i++)
{
header1 = ws.get_Range(ws.Cells[rowStart + 3 + i * 2, colStart], ws.Cells[rowStart + 3 + i * 2 + 1, colStart]);
header1.Merge();
header1.Value = tempMatrix[i + 1, 0];;
}
//列表头
string[] headerCol = { "m", "Value", "Div" };
Excel.Range header2 = ws.get_Range(ws.Cells[rowStart + 2, colStart + 1], ws.Cells[rowStart + 2, colStart + colCount - 1]);
header2.Value = headerCol;
//数据矩阵
float[,] dataMatrixTemp = new float[12, 3];
for (int i = 0; i < 6; i++)
{
dataMatrixTemp[2 * i, 0] = 4;
dataMatrixTemp[2 * i + 1, 0] = 6;
dataMatrixTemp[2 * i, 1] = Convert.ToSingle(tempMatrix[i + 1, 1]);
dataMatrixTemp[2 * i + 1, 1] = Convert.ToSingle(tempMatrix[i + 1, 2]);
}
Excel.Range rData = ws.get_Range(ws.Cells[rowStart + 3, colStart + 1], ws.Cells[rowStart + 14, colStart + 2]);
rData.Value = dataMatrixTemp;
for (int i = 0; i < 12; i++)
{
string basediv = String.Format("R{0}C{1}", (rowStart + 3 + i), 3);
string div = String.Format("R{0}C{1}", (rowStart + 3 + i), colStart + 2);
Excel.Range comP = ws.get_Range(ws.Cells[rowStart + 3 + i, colStart + 3], ws.Cells[rowStart + 3 + i, colStart + 3]);
comP.FormulaR1C1 = "=" + div + "/" + basediv;
}
Excel.Range formatCell = ws.get_Range(ws.Cells[rowStart, colStart], ws.Cells[rowStart + 15, colStart + colCount]);
formatCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//转化为数字，将>1.05的数值标红
Excel.Range numData = ws.get_Range(ws.Cells[rowStart + 3, colStart + colCount - 1], ws.Cells[rowStart + 14, colStart + colCount - 1]);
Excel.FormatCondition condition1 = (Excel.FormatCondition)numData.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, "1.05", Type.Missing);
condition1.Interior.Color = 13551615;
rowStart = rowStart + 15;
}
colStart = colStart + 5;
}
}
}
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");
}
}
/// <summary>
/// Formatta il blocco entità.
/// </summary>
/// <param name="siglaEntita">Sigla entità.</param>
/// <param name="desEntita">Descrizione.</param>
/// <param name="codiceRUP">Codice RUP.</param>
protected void FormattaBloccoEntita(object siglaEntita, object desEntita, object codiceRUP)
{
Range rngMercatoPrec = new Range();
//Titolo
Range rng = new Range(_definedNames.GetRowByName(siglaEntita, "T"), _struttura.colBlock, 1, 10);
Style.RangeStyle(_ws.Range[rng.ToString()], fontSize: 12, merge: true, bold: true, align: Excel.XlHAlign.xlHAlignCenter, borders: "[top:medium,right:medium,bottom:medium,left:medium]");
_ws.Range[rng.ToString()].Value = "PROGRAMMA A 15 MINUTI " + desEntita;
_ws.Range[rng.ToString()].RowHeight = 25;
//Data
rng = new Range(_definedNames.GetRowByName(siglaEntita, "DATA"), _struttura.colBlock, 1, 5);
Style.RangeStyle(_ws.Range[rng.ToString()], fontSize: 10, bold: true, align: Excel.XlHAlign.xlHAlignCenter, borders: "[top:medium,right:medium,bottom:medium,left:medium,insidev:medium]", numberFormat: "dd/MM/yyyy");
_ws.Range[rng.ToString()].RowHeight = 18;
_ws.Range[rng.Columns[0].ToString()].Value = "Data";
_ws.Range[rng.Columns[1, 3].ToString()].Merge();
_ws.Range[rng.Columns[1].ToString()].Value = Workbook.DataAttiva;
_ws.Range[rng.Columns[4].ToString()].Value = _mercato;
//Tabella
DataTable categoriaEntita = Workbook.Repository[DataBase.TAB.CATEGORIA_ENTITA];
DataView informazioni = Workbook.Repository[DataBase.TAB.ENTITA_INFORMAZIONE].DefaultView;
List <DataRow> entitaRif =
(from r in categoriaEntita.AsEnumerable()
where r["IdApplicazione"].Equals(Workbook.IdApplicazione) && r["Gerarchia"].Equals(siglaEntita)
select r).ToList();
bool hasEntitaRif = entitaRif.Count > 0;
int numEntita = Math.Max(entitaRif.Count, 1);
rng = new Range(_definedNames.GetRowByName(siglaEntita, "UM", "T"), _struttura.colBlock, 1, 5 * numEntita);
for (int i = 0; i < numEntita; i++)
{
informazioni.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND Visibile = '1' " + (hasEntitaRif ? "AND SiglaEntitaRif = '" + entitaRif[i]["SiglaEntita"] + "'" : "") + " AND IdApplicazione = " + _appID;
//range grande come tutta la tabella
rng = new Range(_definedNames.GetRowByName(siglaEntita, "UM", "T"), _definedNames.GetColFromName("RIF" + (i + 1), "PROGRAMMAQ1") - 1, Date.GetOreGiorno(Workbook.DataAttiva) + 2, 5);
Style.RangeStyle(_ws.Range[rng.ToString()], borders: "[top:medium,right:medium,bottom:medium,left:medium,insideH:thin,insideV:thin]", align: Excel.XlHAlign.xlHAlignCenter, numberFormat: "general");
Style.RangeStyle(_ws.Range[rng.Rows[1, rng.Rows.Count - 1].Columns[0].ToString()], backColor: 15, bold: true, align: Excel.XlHAlign.xlHAlignLeft);
Style.RangeStyle(_ws.Range[rng.Rows[0].ToString()], backColor: 15, bold: true, fontSize: 11);
Style.RangeStyle(_ws.Range[rng.Rows[1].ToString()], backColor: 15, bold: true);
_ws.Range[rng.Rows[0].Columns[1, rng.Columns.Count - 1].ToString()].Merge();
if (hasEntitaRif)
{
_ws.Range[rng.Rows[0].ToString()].Value = new object[] { "UM", entitaRif[i]["CodiceRUP"] is DBNull ? entitaRif[i]["DesEntita"] : entitaRif[i]["CodiceRUP"] }
}
;
else
{
_ws.Range[rng.Rows[0].ToString()].Value = new object[] { "UM", codiceRUP is DBNull ? desEntita : codiceRUP }
};
for (int h = 1; h <= Date.GetOreGiorno(Workbook.DataAttiva); h++)
{
_ws.Range[rng.Columns[0].Rows[h + 1].ToString()].Value = "Ora " + h;
}
var isOrario = informazioni
.OfType <DataRowView>()
.Any(r => r["SiglaInformazione"].ToString().StartsWith("PROGRAMMA_"));
if (!isOrario)
{
for (int j = 0; j < 4; j++)
{
_ws.Range[rng.Rows[1].Columns[j + 1].ToString()].Value = 15 * j + "-" + 15 * (j + 1);
}
}
else
{
_ws.Range[rng.Cells[1, 1].ToString()].Value = "0-60";
}
//TODO controllare che non ci siano problemi
if (_mercato != "MSD1")
{
string mercatoPrec = Simboli.GetMercatoPrec(_mercato);
//calcolo il range nel foglio del mercato precedente (non è detto che siano nella stessa posizione (anche se non ha senso che non lo siano...))
rngMercatoPrec = new Range(_definedNamesMercatoPrec.GetRowByName(siglaEntita, "UM", "T"), _definedNamesMercatoPrec.GetColFromName("RIF" + (i + 1), "PROGRAMMAQ1") - 1, Date.GetOreGiorno(Workbook.DataAttiva) + 2, 5);
Excel.FormatCondition condGreater = _ws.Range[rng.Rows[2, rng.Rows.Count - 1].Columns[1, 4].ToString()].FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Formula1: "=" + rng.Cells[2, 1] + " > '" + mercatoPrec + "'!" + rngMercatoPrec.Cells[2, 1]);
condGreater.Interior.ColorIndex = Struct.COLORE_VARIAZIONE_POSITIVA;
Excel.FormatCondition condLess = _ws.Range[rng.Rows[2, rng.Rows.Count - 1].Columns[1, 4].ToString()].FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Formula1: "=" + rng.Cells[2, 1] + " < '" + mercatoPrec + "'!" + rngMercatoPrec.Cells[2, 1]);
condLess.Interior.ColorIndex = Struct.COLORE_VARIAZIONE_NEGATIVA;
}
}
}
