private void WorkSheet_SelectionChange(Excel.Range Target)
{
//myRange = Target.Copy();
foreach (Excel.Range cell in Target.Cells)
{
Console.WriteLine("Cell " + cell.Address);
string fontname = ((Excel.Range)cell).Font.Name.ToString();
Console.WriteLine("\t Font Name = " + fontname);
Excel.XlHAlign horizontalAlignment = (Excel.XlHAlign)cell.HorizontalAlignment;
Console.WriteLine("\t Horizontal Alignment = " + horizontalAlignment);
Excel.XlVAlign verticalalignment = (Excel.XlVAlign)cell.VerticalAlignment;
Console.WriteLine("\t Vertical Alignment = " + verticalalignment);
Console.WriteLine("\t Borders Line Style >");
Console.WriteLine("\t\t Left = " + (Excel.XlLineStyle)cell.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle);
Console.WriteLine("\t\t Right = " + (Excel.XlLineStyle)cell.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle);
Console.WriteLine("\t\t Top = " + (Excel.XlLineStyle)cell.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle);
Console.WriteLine("\t\t Bottom = " + (Excel.XlLineStyle)cell.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle);
//xlRgbColor에 기록된 컬러만 이용해야 한다.
Excel.XlRgbColor cellColor = (Excel.XlRgbColor)cell.Interior.Color;
Console.WriteLine("\t Cell Color = " + cellColor);
Excel.XlRgbColor fontColor = (Excel.XlRgbColor)cell.Font.Color;
Console.WriteLine("\t Font Color = " + fontColor);
bool merged = (bool)cell.MergeCells;
Console.WriteLine("\tMerged?\t" + merged);
if (merged)
{
Excel.Range mergeArea = (Excel.Range)cell.MergeArea;
Console.WriteLine("\tMerged Area = " + mergeArea.Address);
}
Excel.XlOrientation orientation = (Excel.XlOrientation)cell.Orientation;
Console.WriteLine("\tOrientation(Text Angle) = " + orientation);
}
SetText(this.textBox1, Target.Address);
}
public void criaCelulas(int pLinha, int pColuna, string pTexto, string pCelula1, string pCelula2, int pColunasMerge,
System.Drawing.Color pCorFundo, bool pNegrito, int pTamanhoCelula, System.Drawing.Color pCorLetra, Excel.XlHAlign pAlinhamentoH,
Excel.XlVAlign pAlinhamentoV, string pNomeFonte)
{
excelWorkSheetRange = excelWorksheet.get_Range(pCelula1, pCelula2);
excelWorkSheetRange.Merge(pColunasMerge);
excelWorkSheetRange.NumberFormat = "@";
excelWorksheet.Cells[pLinha, pColuna] = pTexto;
excelWorkSheetRange.VerticalAlignment = pAlinhamentoV;
excelWorkSheetRange.HorizontalAlignment = pAlinhamentoH;
excelWorkSheetRange.Interior.Color = pCorFundo.ToArgb();
excelWorkSheetRange.Borders.Color = System.Drawing.Color.Black.ToArgb();
excelWorkSheetRange.Font.Bold = pNegrito;
excelWorkSheetRange.Font.Name = pNomeFonte;
excelWorkSheetRange.ColumnWidth = pTamanhoCelula;
excelWorkSheetRange.Font.Color = pCorLetra.ToArgb();
}
public void SetAliment(int startRow, int startCol, int endRow, int endCol, Excel.XlHAlign horizontalAlignment, Excel.XlVAlign verticalAlignment)
{
this.SetRange(startRow, startCol, endRow, endCol);
this.SetAliment(horizontalAlignment, verticalAlignment);
}
private void writeToSingleCell(Location cellLocation, string value, int mode, int cellType, double cellWidth, double cellHeight, bool boldFlag, bool italicsFlag, bool wrapTextFlag, System.Drawing.Color cellColour,
System.Drawing.Color textColour, Excel.XlHAlign horizAlignment, Excel.XlVAlign vertAlignment, String fontName, int fontSize)
{
int row = cellLocation.getRow();
int col = cellLocation.getColumn();
if (mode == 1 || mode == 2)
{
Console.WriteLine("Writing to " + col + " (" + cellLocation.getExcelAddress() + "): " + value);
}
if (mode == 0 || mode == 2)
{
currentSheet.Cells[row, col] = value;
Excel.Range r = currentSheet.Cells[row, col] as Excel.Range;
if (cellWidth > 0)
{
currentSheet.Columns[startCol].ColumnWidth = cellWidth;
}
if (cellHeight > 0)
{
currentSheet.Rows[startRow].RowHeight = cellHeight;
}
r.Interior.Color = System.Drawing.ColorTranslator.ToOle(cellColour);
r.Font.Color = System.Drawing.ColorTranslator.ToOle(textColour);
r.Font.Bold = boldFlag;
r.Font.Italic = italicsFlag;
r.WrapText = wrapTextFlag;
r.HorizontalAlignment = horizAlignment;
r.VerticalAlignment = vertAlignment;
r.Font.Name = fontName;
r.Font.Size = fontSize;
}
}
private void GetAlignment(FarPoint.Win.Spread.Cell cell, out XL.XlHAlign hAlign, out XL.XlVAlign vAlign)
{
switch (cell.HorizontalAlignment)
{
case CellHorizontalAlignment.Center:
hAlign = XL.XlHAlign.xlHAlignCenter;
break;
case CellHorizontalAlignment.General:
hAlign = XL.XlHAlign.xlHAlignGeneral;
break;
case CellHorizontalAlignment.Left:
hAlign = XL.XlHAlign.xlHAlignLeft;
break;
case CellHorizontalAlignment.Right:
hAlign = XL.XlHAlign.xlHAlignRight;
break;
default:
hAlign = XL.XlHAlign.xlHAlignGeneral;
break;
}
switch (cell.VerticalAlignment)
{
case CellVerticalAlignment.Bottom:
vAlign = XL.XlVAlign.xlVAlignBottom;
break;
case CellVerticalAlignment.Center:
vAlign = XL.XlVAlign.xlVAlignCenter;
break;
case CellVerticalAlignment.General:
vAlign = XL.XlVAlign.xlVAlignTop;
break;
case CellVerticalAlignment.Top:
vAlign = XL.XlVAlign.xlVAlignTop;
break;
default:
vAlign = XL.XlVAlign.xlVAlignTop;
break;
}
}
public void SetAliment(int row, int col, Excel.XlHAlign horizontalAlignment, Excel.XlVAlign verticalAlignment)
{
this.SetRange(row, col);
this.SetAliment(horizontalAlignment, verticalAlignment);
}
} // xlFontName, xlFontSize
private void SetValueCell(XLExcel.Range xlRange, Object xlValue, Boolean xlMergeCells = false, XLExcel.XlVAlign xlVAlign = XLExcel.XlVAlign.xlVAlignTop, XLExcel.XlHAlign xlHAlign = XLExcel.XlHAlign.xlHAlignLeft, Boolean xlBold = false, Boolean xlUnderline = false) //String xlFontName, Int32 xlFontSize;
{
SetValue(xlRange, xlValue, xlMergeCells, xlVAlign, xlHAlign, xlBold, xlUnderline);
} //, xlFontName, xlFontSize
private void SetValueHeader(XLExcel.Range xlRange, Object xlValue, Boolean xlMergeCells = true, XLExcel.XlVAlign xlVAlign = XLExcel.XlVAlign.xlVAlignCenter, XLExcel.XlHAlign xlHAlign = XLExcel.XlHAlign.xlHAlignCenter, Boolean xlBold = true, Boolean xlUnderline = false)
{
SetValue(xlRange, xlValue, xlMergeCells, xlVAlign, xlHAlign, xlBold, xlUnderline);
}
// EXPORT GARDE CORPS
public static void exportgardecorps(Excel.Application xlapp, Excel.Workbook workbook, Dictionary <string, double> gardecorps, Dictionary <int, string> erreursgc)
{
object misvalue = System.Reflection.Missing.Value;
Excel.Worksheet worksheet3 = workbook.Sheets.Add(misvalue, workbook.Sheets.Item[2]);
worksheet3.Columns.ColumnWidth = 25;
worksheet3.Name = "Garde-Corps";
Excel.XlBordersIndex lgauche = Excel.XlBordersIndex.xlEdgeLeft;
Excel.XlBordersIndex ldroite = Excel.XlBordersIndex.xlEdgeRight;
Excel.XlBordersIndex lhaut = Excel.XlBordersIndex.xlEdgeTop;
Excel.XlBordersIndex lbas = Excel.XlBordersIndex.xlEdgeBottom;
Excel.XlLineStyle lcontinue = Excel.XlLineStyle.xlContinuous;
Excel.XlBorderWeight llarge = Excel.XlBorderWeight.xlMedium;
Excel.XlBorderWeight lfin = Excel.XlBorderWeight.xlThin;
Excel.XlVAlign centre = Excel.XlVAlign.xlVAlignCenter;
Excel.XlRgbColor rouge = Excel.XlRgbColor.rgbRed;
Excel.XlRgbColor gold = Excel.XlRgbColor.rgbGold;
Excel.XlRgbColor vert = Excel.XlRgbColor.rgbLightGreen;
Excel.XlRgbColor gris = Excel.XlRgbColor.rgbLightGrey;
if (gardecorps.Count != 0)
{
Excel.Range rangetitre1 = worksheet3.Range["A1", "C1"];
rangetitre1.Merge();
rangetitre1.Value = "GARDE-CORPS";
rangetitre1.Font.Bold = true;
rangetitre1.Font.Color = rouge;
rangetitre1.Font.Size = 14;
rangetitre1.VerticalAlignment = centre;
worksheet3.Range["A3"].Value = "Nombre de type de garde corps";
worksheet3.Range["A3", "A4"].BorderAround2(lcontinue, llarge);
worksheet3.Range["A3", "A4"].Interior.Color = vert;
worksheet3.Range["A6"].Value = "Liste des type de garde corps";
worksheet3.Range["A6", "B6"].BorderAround2(lcontinue, llarge);
worksheet3.Range["A6", "B6"].Interior.Color = gold;
worksheet3.Range["B6"].Value = "métré (en m)";
worksheet3.Range["B3"].Value = "métré total (en m)";
worksheet3.Range["B3", "B4"].BorderAround2(lcontinue, llarge);
worksheet3.Range["B3", "B4"].Interior.Color = vert;
worksheet3.Range["D5"].Value = "ERREURS DE MODELISATION";
worksheet3.Range["D5"].BorderAround2(lcontinue, llarge);
worksheet3.Range["D5"].Interior.Color = gold;
worksheet3.Range["D6"].Value = "ID du type de garde-corps";
worksheet3.Range["D6"].Interior.Color = gold;
worksheet3.Range["E6"].Value = "Type du garde corps";
worksheet3.Range["E6"].Interior.Color = gold;
worksheet3.Range["D6", "E6"].BorderAround2(lcontinue, llarge);
int i = 6;
double sommemetre = 0;
worksheet3.Cells[4, 1] = gardecorps.Count;
foreach (string garde in gardecorps.Keys)
{
i += 1;
Excel.Range nomgarde = worksheet3.Cells[i, 1];
nomgarde.Value = garde;
nomgarde.BorderAround2(lcontinue, lfin);
Excel.Range metregarde = worksheet3.Cells[i, 2];
metregarde.Value = gardecorps[garde];
metregarde.BorderAround2(lcontinue, lfin);
sommemetre += gardecorps[garde];
if (i % 2 == 0)
{
nomgarde.Interior.Color = gris;
metregarde.Interior.Color = gris;
}
}
worksheet3.Range["B4"].Value = sommemetre;
string gardeBD = "B" + i.ToString();
string gardeBG = "A" + i.ToString();
Excel.Range rangegardeD = worksheet3.Range["B7", gardeBD];
Excel.Range rangegardeB = worksheet3.Range[gardeBG, gardeBD];
rangegardeB.Borders[lbas].LineStyle = lcontinue;
rangegardeB.Borders[lbas].Weight = llarge;
rangegardeD.Borders[ldroite].LineStyle = lcontinue;
rangegardeD.Borders[ldroite].Weight = llarge;
}
else if (gardecorps.Count == 0)
{
revitui.TaskDialog.Show("Erreur", "Aucun garde-corps correctement modélisé");
}
if (erreursgc.Count != 0)
{
int j = 6;
foreach (int gardeid in erreursgc.Keys)
{
j += 1;
Excel.Range cellid = worksheet3.Cells[j, 4];
cellid.Value = gardeid;
cellid.BorderAround2(lcontinue, lfin);
Excel.Range cellerr = worksheet3.Cells[j, 5];
cellerr.Value = erreursgc[gardeid];
cellerr.BorderAround2(lcontinue, lfin);
if (j % 2 == 0)
{
cellid.Interior.Color = gris;
cellerr.Interior.Color = gris;
}
}
string erreurB = "D" + j.ToString();
string erreurBD = "E" + j.ToString();
Excel.Range lgarderr = worksheet3.Range["D7", erreurB];
Excel.Range rangeD = worksheet3.Range["E7", erreurBD];
rangeD.Borders[ldroite].LineStyle = lcontinue;
rangeD.Borders[ldroite].Weight = llarge;
lgarderr.Borders[lgauche].LineStyle = lcontinue;
lgarderr.Borders[lgauche].Weight = llarge;
worksheet3.Range[erreurB, erreurBD].Borders[lbas].Weight = llarge;
}
else if (gardecorps.Count == 0 & erreursgc.Count == 0)
{
revitui.TaskDialog.Show("Aucun garde-corps modélisé", "L'export des garde-corps vers Excel ne sera pas réalisé.");
}
}
private void SetValue(XLExcel.Range xlRange, Object xlValue, Boolean xlMergeCells = false, XLExcel.XlVAlign xlVAlign = XLExcel.XlVAlign.xlVAlignCenter, XLExcel.XlHAlign xlHAlign = XLExcel.XlHAlign.xlHAlignCenter, Boolean xlBold = false, Boolean xlUnderline = false)//String xlFontName, Int32 xlFontSize,
{
xlRange.MergeCells = xlMergeCells;
xlRange.Value = xlValue;
//xlRange.Font.Name = xlFontName;
//xlRange.Font.Size = xlFontSize;
xlRange.Font.Bold = xlBold;
xlRange.Font.Underline = xlUnderline;
xlRange.VerticalAlignment = xlVAlign;
xlRange.HorizontalAlignment = xlHAlign;
}
//EXPORT APPARTEMENT
public static void Exporttypoappart(Excel.Application xlapp, Excel.Workbook workbook, Dictionary <string, string> listappart, Dictionary <string, double> areaappart, Dictionary <string, List <double> > listtypo, Dictionary <string, double> autrepieces)
{
object misvalue = System.Reflection.Missing.Value;
Excel.Worksheet worksheet1 = workbook.Sheets[1];
worksheet1.Columns.ColumnWidth = 25;
worksheet1.Name = "Logements";
if (listappart.Count != 0)
{
Excel.XlBordersIndex lgauche = Excel.XlBordersIndex.xlEdgeLeft;
Excel.XlBordersIndex ldroite = Excel.XlBordersIndex.xlEdgeRight;
Excel.XlBordersIndex lhaut = Excel.XlBordersIndex.xlEdgeTop;
Excel.XlBordersIndex lbas = Excel.XlBordersIndex.xlEdgeBottom;
Excel.XlLineStyle lcontinue = Excel.XlLineStyle.xlContinuous;
Excel.XlBorderWeight llarge = Excel.XlBorderWeight.xlMedium;
Excel.XlBorderWeight lfin = Excel.XlBorderWeight.xlThin;
Excel.XlVAlign centre = Excel.XlVAlign.xlVAlignCenter;
Excel.XlVAlign justi = Excel.XlVAlign.xlVAlignJustify;
Excel.XlRgbColor rouge = Excel.XlRgbColor.rgbRed;
Excel.XlRgbColor gold = Excel.XlRgbColor.rgbGold;
Excel.XlRgbColor vert = Excel.XlRgbColor.rgbLightGreen;
Excel.XlRgbColor gris = Excel.XlRgbColor.rgbLightGrey;
Excel.Range rangetitre1 = worksheet1.Range["A1"];
rangetitre1.Value = "INDICATEUR LOGEMENT";
rangetitre1.Font.Bold = true;
rangetitre1.Font.Color = rouge;
rangetitre1.Font.Size = 14;
rangetitre1.VerticalAlignment = justi;
rangetitre1.VerticalAlignment = centre;
Excel.Range rangetitre2 = worksheet1.Range["A3"];
rangetitre2.Value = "Nombre de logement total :";
worksheet1.Range["A3", "B3"].BorderAround2(lcontinue, llarge);
worksheet1.Range["A3", "B3"].Interior.Color = vert;
Excel.Range nombrelogement = worksheet1.Range["B3"];
nombrelogement.Value = listappart.Count;
Excel.Range rangetypo = worksheet1.Range["A4"];
rangetypo.Value = "Typologie";
rangetypo.BorderAround2(lcontinue, llarge);
rangetypo.Interior.Color = gold;
rangetypo.Font.Bold = true;
Excel.Range rangenbrappart = worksheet1.Range["A5"];
rangenbrappart.Value = "nombre de type de logement";
rangenbrappart.BorderAround2(lcontinue, llarge);
rangenbrappart.Interior.Color = gold;
rangenbrappart.Font.Bold = true;
Excel.Range rangemoy = worksheet1.Range["A6"];
rangemoy.Value = "Surface moyenne du type";
rangemoy.BorderAround2(lcontinue, llarge);
rangemoy.Interior.Color = gold;
rangemoy.Font.Bold = true;
Excel.Range rangetitre3 = worksheet1.Range["H8", "I8"];
rangetitre3.BorderAround2(lcontinue, llarge);
rangetitre3.Value = "Nombre de pièces communes";
worksheet1.Cells[8, 9] = autrepieces.Count;
rangetitre3.Interior.Color = vert;
Excel.Range rangetitre4 = worksheet1.Range["H9"];
rangetitre4.BorderAround2(lcontinue, llarge);
rangetitre4.Value = "Pièces communes";
rangetitre4.Font.Bold = true;
rangetitre4.VerticalAlignment = centre;
rangetitre4.Interior.Color = gold;
Excel.Range rangetitreC = worksheet1.Range["I9"];
rangetitreC.BorderAround2(lcontinue, llarge);
rangetitreC.Value = "Surface (m²)";
rangetitreC.Font.Bold = true;
rangetitreC.VerticalAlignment = centre;
rangetitreC.Interior.Color = gold;
Excel.Range rangetitre5 = worksheet1.Range["A9"];
rangetitre5.BorderAround2(lcontinue, llarge);
rangetitre5.Value = "Appartements";
rangetitre5.Font.Bold = true;
rangetitre5.VerticalAlignment = centre;
rangetitre5.Interior.Color = gold;
Excel.Range rangetitre6 = worksheet1.Range["B9"];
rangetitre6.BorderAround2(lcontinue, llarge);
rangetitre6.Value = "Typologies";
rangetitre6.Font.Bold = true;
rangetitre6.VerticalAlignment = centre;
rangetitre6.Interior.Color = gold;
Excel.Range rangetitre7 = worksheet1.Range["C9"];
rangetitre7.BorderAround2(lcontinue, llarge);
rangetitre7.Value = "Surfaces (m²)";
rangetitre7.Font.Bold = true;
rangetitre7.VerticalAlignment = centre;
rangetitre7.Interior.Color = gold;
int i = 9;
foreach (string appart in listappart.Keys)
{
i += 1;
Excel.Range nomappartcell = worksheet1.Cells[i, 1];
nomappartcell.Value = appart;
Excel.Range typapartcell = worksheet1.Cells[i, 2];
typapartcell.Value = listappart[appart];
double surfaceappart = areaappart[appart];
surfaceappart = Math.Round(surfaceappart, 2);
Excel.Range surfappartcell = worksheet1.Cells[i, 3];
surfappartcell.Value = surfaceappart;
if (i % 2 == 0)
{
nomappartcell.Interior.Color = gris;
typapartcell.Interior.Color = gris;
surfappartcell.Interior.Color = gris;
}
}
string pBG = "A" + i.ToString();
string pBD = "C" + i.ToString();
Excel.Range rangeA = worksheet1.Range["A10", pBG];
Excel.Range rangeB = worksheet1.Range["C10", pBD];
Excel.Range rangefin = worksheet1.Range[pBG, pBD];
rangeA.Borders[lgauche].LineStyle = lcontinue;
rangeA.Borders[lgauche].Weight = llarge;
rangeB.Borders[ldroite].LineStyle = lcontinue;
rangeB.Borders[ldroite].Weight = llarge;
rangefin.Borders[ldroite].LineStyle = lcontinue;
rangefin.Borders[lbas].Weight = llarge;
int j = 1;
foreach (string typo in listtypo.Keys)
{
j += 1;
Excel.Range typocell = worksheet1.Cells[4, j];
typocell.Value = typo;
typocell.Interior.Color = gris;
typocell.BorderAround2(lcontinue, lfin);
typocell.Borders[ldroite].Weight = llarge;
typocell.Borders[lgauche].Weight = llarge;
Excel.Range nbrtypocell = worksheet1.Cells[5, j];
nbrtypocell.Value = listtypo[typo][0];
nbrtypocell.BorderAround2(lcontinue, lfin);
nbrtypocell.Borders[ldroite].Weight = llarge;
nbrtypocell.Borders[lgauche].Weight = llarge;
double moyennetypo = listtypo[typo][1];
moyennetypo = Math.Round(moyennetypo, 2);
Excel.Range moytypocell = worksheet1.Cells[6, j];
moytypocell.Value = moyennetypo;
moytypocell.Interior.Color = gris;
moytypocell.BorderAround2(lcontinue, lfin);
moytypocell.Borders[ldroite].Weight = llarge;
moytypocell.Borders[lgauche].Weight = llarge;
}
Excel.Range cell = worksheet1.Cells[4, j];
Excel.Range cell2 = worksheet1.Cells[6, j];
Excel.Range rangetypohaut = worksheet1.Range["B4", cell];
Excel.Range rangetypobas = worksheet1.Range["B6", cell2];
Excel.Range rangetypD = worksheet1.Range[cell, cell2];
rangetypohaut.Borders[lhaut].LineStyle = lcontinue;
rangetypohaut.Borders[lhaut].Weight = llarge;
rangetypobas.Borders[lbas].LineStyle = lcontinue;
rangetypobas.Borders[lbas].Weight = llarge;
rangetypD.Borders[ldroite].LineStyle = lcontinue;
rangetypD.Borders[ldroite].Weight = llarge;
int k = 9;
foreach (string piece in autrepieces.Keys)
{
k += 1;
Excel.Range piececell = worksheet1.Cells[k, 8];
piececell.Value = piece;
Excel.Range autrecell = worksheet1.Cells[k, 9];
autrecell.Value = autrepieces[piece];
if (k % 2 == 0)
{
piececell.Interior.Color = gris;
autrecell.Interior.Color = gris;
}
}
string communBG = "H" + k.ToString();
string communBD = "I" + k.ToString();
Excel.Range rangecomg = worksheet1.Range["H10", communBG];
Excel.Range rangecomd = worksheet1.Range["I10", communBD];
Excel.Range rangecomb = worksheet1.Range[communBG, communBD];
rangecomg.Borders[lgauche].LineStyle = lcontinue;
rangecomg.Borders[lgauche].Weight = llarge;
rangecomd.Borders[ldroite].LineStyle = lcontinue;
rangecomd.Borders[ldroite].Weight = llarge;
rangecomb.Borders[lbas].LineStyle = lcontinue;
rangecomb.Borders[lbas].Weight = llarge;
}
else
{
revitui.TaskDialog.Show("Aucun appartement", "Aucun appartement trouvé. L'export des appartements vers Excel ne sera pas réalisé");
}
}
// EXPORT PARKINGS
public static void exportparking(Excel.Application xlapp, Excel.Workbook workbook, Dictionary <string, int> parkings, int sommeplace)
{
object misvalue = System.Reflection.Missing.Value;
Excel.Worksheet worksheet2 = workbook.Sheets.Add(misvalue, workbook.Sheets.Item[1]);
worksheet2.Columns.ColumnWidth = 25;
worksheet2.Name = "Parking";
if (parkings.Count != 0)
{
Excel.XlBordersIndex lgauche = Excel.XlBordersIndex.xlEdgeLeft;
Excel.XlBordersIndex ldroite = Excel.XlBordersIndex.xlEdgeRight;
Excel.XlBordersIndex lhaut = Excel.XlBordersIndex.xlEdgeTop;
Excel.XlBordersIndex lbas = Excel.XlBordersIndex.xlEdgeBottom;
Excel.XlLineStyle lcontinue = Excel.XlLineStyle.xlContinuous;
Excel.XlBorderWeight llarge = Excel.XlBorderWeight.xlMedium;
Excel.XlBorderWeight lfin = Excel.XlBorderWeight.xlThin;
Excel.XlVAlign centre = Excel.XlVAlign.xlVAlignCenter;
Excel.XlRgbColor rouge = Excel.XlRgbColor.rgbRed;
Excel.XlRgbColor sofyacolor = Excel.XlRgbColor.rgbGold;
Excel.XlRgbColor vert = Excel.XlRgbColor.rgbLightGreen;
Excel.XlRgbColor gris = Excel.XlRgbColor.rgbLightGrey;
Excel.Range rangetitre1 = worksheet2.Range["A1", "C1"];
rangetitre1.Merge();
rangetitre1.Font.Bold = true;
rangetitre1.Font.Size = 14;
rangetitre1.Font.Color = rouge;
rangetitre1.Value = "PARKINGS";
rangetitre1.VerticalAlignment = centre;
worksheet2.Range["A4"].Value = "Type de place de parking";
worksheet2.Range["A4"].BorderAround2(lcontinue, llarge);
worksheet2.Range["A4"].Interior.Color = sofyacolor;
worksheet2.Range["B4"].Value = "Nombre de place du type";
worksheet2.Range["B4"].BorderAround2(lcontinue, llarge);
worksheet2.Range["B4"].Interior.Color = sofyacolor;
worksheet2.Range["C4"].Value = "Pourcentage (%)";
worksheet2.Range["C4"].BorderAround2(lcontinue, llarge);
worksheet2.Range["C4"].Interior.Color = sofyacolor;
try
{
int i = 4;
foreach (string park in parkings.Keys)
{
i += 1;
Excel.Range nompark = worksheet2.Cells[i, 1];
nompark.Value = park;
nompark.BorderAround2(lcontinue, lfin);
Excel.Range nbrparking = worksheet2.Cells[i, 2];
nbrparking.Value = parkings[park];
nbrparking.BorderAround2(lcontinue, lfin);
double parc = parkings[park];
double somme = sommeplace;
double pourcent = (parc / somme) * 100;
pourcent = Math.Round(pourcent, 2);
Excel.Range ratiocell = worksheet2.Cells[i, 3];
ratiocell.Value = pourcent;
ratiocell.BorderAround2(lcontinue, lfin);
if (i % 2 == (0))
{
nompark.Interior.Color = gris;
nbrparking.Interior.Color = gris;
ratiocell.Interior.Color = gris;
}
}
string parkBG = "A" + i.ToString();
string parkBD = "C" + i.ToString();
Excel.Range rangeparkD = worksheet2.Range["C4", parkBD];
Excel.Range rangeparkB = worksheet2.Range[parkBG, parkBD];
rangeparkD.Borders[ldroite].LineStyle = lcontinue;
rangeparkD.Borders[ldroite].Weight = llarge;
rangeparkB.Borders[lbas].LineStyle = lcontinue;
rangeparkB.Borders[lbas].Weight = llarge;
//Total place
worksheet2.Range["A3"].Value = "Total :";
worksheet2.Range["B3"].Value = sommeplace;
worksheet2.Range["A3", "B3"].BorderAround2(lcontinue, llarge);
worksheet2.Range["A3", "B3"].Interior.Color = vert;
}
catch
{
revitui.TaskDialog.Show("Erreur", "Erreur lors de l'export Excel des parkings");
}
}
else
{
revitui.TaskDialog.Show("Aucune place de parking modélisée", "L'export des places de parkings vers Excel ne sera pas réalisé.");
}
}
public static void RangeVerticalAlignment(dynamic Range, Excel.XlVAlign Alignment)
{
Range.VerticalAlignment = Alignment;
}
public void SetVerticalAlignment(Excel.XlVAlign verticalAlignment)
{
_VerticalAlignment = verticalAlignment;
}
private void SetValueCell(XLExcel.Range xlRange, Object xlValue, Boolean xlMergeCells = false, XLExcel.XlVAlign xlVAlign = XLExcel.XlVAlign.xlVAlignTop, XLExcel.XlHAlign xlHAlign = XLExcel.XlHAlign.xlHAlignLeft, Boolean xlBold = false, Boolean xlUnderline = false)
{
SetValue(xlRange, xlValue, xlMergeCells, xlVAlign, xlHAlign, xlBold, xlUnderline);
}
private void writeToMultipleCells(Location startRangeLocation, Location endRangeLocation, string value, int mode, int cellType, double cellWidth, double cellHeight, bool boldFlag, bool italicsFlag, bool wrapTextFlag, System.Drawing.Color cellColour,
System.Drawing.Color textColour, Excel.XlHAlign horizAlignment, Excel.XlVAlign vertAlignment, String fontName, int fontSize)
{
int startRow = startRangeLocation.getRow();
int startCol = startRangeLocation.getColumn();
int endRow = endRangeLocation.getRow();
int endCol = endRangeLocation.getColumn();
string startRange = getExcelColumnName(startCol) + startRow.ToString();
string endRange = getExcelColumnName(endCol) + endRow.ToString();
if (mode == 1 || mode == 2)
{
Console.WriteLine("Writing to " + startCol + " (" + startRange + ":" + endRange + ") " + value);
}
if (mode == 0 || mode == 2)
{
if (cellWidth > 0)
{
currentSheet.Columns[startCol].ColumnWidth = cellWidth;
}
if (cellHeight > 0)
{
currentSheet.Rows[startRow].RowHeight = cellHeight;
}
Excel.Range r = currentSheet.get_Range(startRange, endRange); //Excel.Range r = newTableSheet.Cells[startRow, startCol] as Excel.Range;
r.Interior.Color = System.Drawing.ColorTranslator.ToOle(cellColour);
r.Font.Color = System.Drawing.ColorTranslator.ToOle(textColour);
r.Font.Bold = boldFlag;
r.Font.Italic = italicsFlag;
r.WrapText = wrapTextFlag;
r.HorizontalAlignment = horizAlignment;
r.VerticalAlignment = vertAlignment;
r.Font.Name = fontName;
r.Font.Size = fontSize;
if (value != "" && value != null)
{
r.Cells.Value = value;
}
}
}
//R1 and R2 are ranges of cells and rows, FontSize, FontName, VAling and HAling changes the font size, font name, vertical and horizontal align of the selected range
public void FormatHelper(Excel.Worksheet xlWorkSheet, string R1, string R2, int FontSize, string FontName, Excel.XlVAlign VAlign, Excel.XlHAlign HAlign)
{
xlWorkSheet.Range[R1, R2].Font.Size = FontSize;
xlWorkSheet.Range[R1, R2].Font.Name = FontName;
xlWorkSheet.Range[R1, R2].Cells.VerticalAlignment = VAlign;
xlWorkSheet.Range[R1, R2].Cells.HorizontalAlignment = HAlign;
}
public void SetVerticalAlignment(int iStartRow, int iStartCol, int iEndRow, int iEndCol, Microsoft.Office.Interop.Excel.XlVAlign vAlign)
{
Range objRange = m_objExcel.Range[m_objExcel.Cells[iStartRow, iStartCol], m_objExcel.Cells[iEndRow, iEndCol]];
objRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignGeneral;
objRange.VerticalAlignment = vAlign;
}
/// <summary>
/// Sets the vertical alignment for the specified cells on the default sheet of the default workbook.
/// </summary>
/// <param name="cellSelection">Indicates the cells to be changed. Works just like Excel. Example: "A:A,1:1,A2:B3,F4,F5"</param>
/// <param name="alignment">The vertical justication enumeration.</param>
public void ChangeVerticalAlignment(string cellSelection, Microsoft.Office.Interop.Excel.XlVAlign alignment)
{
ChangeVerticalAlignment(ReportGlobals.defaultWorkbookName, ReportGlobals.defaultSheetIndex, cellSelection, alignment);
}
private void SetValueHeader(XLExcel.Range xlRange, Object xlValue, Boolean xlMergeCells = true, XLExcel.XlVAlign xlVAlign = XLExcel.XlVAlign.xlVAlignCenter, XLExcel.XlHAlign xlHAlign = XLExcel.XlHAlign.xlHAlignCenter, Boolean xlBold = true, Boolean xlUnderline = false)//String xlFontName, Int32 xlFontSize,
{
SetValue(xlRange, xlValue, xlMergeCells, xlVAlign, xlHAlign, xlBold, xlUnderline);
} // xlFontName, xlFontSize
public xlsf SetVrticlAlgmet(Excel.XlVAlign AlignType)
{
CurrCell.VerticalAlignment = AlignType;
return(this);
}
public void AddHeaderDataWithOrientation(int row, int col, string data, string cell1, string cell2, bool isMergeCells, bool isFontBold, string fontName, int fontSize, Excel.XlVAlign alignment, string format, int Orientation, System.Drawing.Color color)
{
xlWorkSheet.Cells[row, col] = data;
xlWorkSheet.Cells[row, col].VerticalAlignment = alignment;
Excel.Range range = xlWorkSheet.get_Range(cell1, cell2);
range.Orientation = Orientation;
range.NumberFormat = format;
range.MergeCells = isMergeCells;
range.Font.Name = fontName;
range.Font.Bold = isFontBold;
range.Font.Color = color;
range.Font.Size = fontSize;
range.WrapText = true;
//range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true);
}
private void ExportExcel(string filename)
{
if (FileIsLocked(filename))
{
throw new ApplicationException("File is used by another program.");
}
ExcelControl xlApp = new ExcelControl();
xlApp.Hide();
bool bHeader = chkExcel_Header.Checked;
//int iCurrentRowIndex = 0;
FarPoint.Win.Spread.SheetView sht = m_spread.ActiveSheet;
//คำนวณค่าของ MaxProgress ทั้งหมด
prgExport.Maximum = sht.RowCount + 1;
prgExport.Value = 1;
//Current excel column
int iCurrentExcelColumn = 0;
int iTotalExcelColumn = 0;
List <int> lastExportcolumn = new List <int>();
//#################
//ColumnHeader :: Adjust column-width
//#################
for (int i = 0; i < sht.Columns.Count; i++)
{
if (sht.Columns[i].StyleName == "NO_EXPORT")
{
continue;
}
if (sht.Columns[i].StyleName == "EXPORT_LAST")
{
lastExportcolumn.Add(i);
continue;
}
//Adjust column-width
//xlApp.SetColumnWidth(i + 1, i + 1, Convert.ToInt32(sht.Columns[i].Width));
xlApp.SetColumnWidth(iCurrentExcelColumn + 1, iCurrentExcelColumn + 1, Convert.ToInt32(sht.Columns[i].Width));
iCurrentExcelColumn = iCurrentExcelColumn + 1;
iTotalExcelColumn = iTotalExcelColumn + 1;
}
//Add export_last column
foreach (int i in lastExportcolumn)
{
xlApp.SetColumnWidth(iCurrentExcelColumn + 1, iCurrentExcelColumn + 1, Convert.ToInt32(sht.Columns[i].Width));
iCurrentExcelColumn = iCurrentExcelColumn + 1;
iTotalExcelColumn = iTotalExcelColumn + 1;
}
//#################
// ColumnHeader :: ColSpan && RowSpan
//#################
if (bHeader)
{
for (int i = 0; i < sht.ColumnHeader.RowCount; i++)
{
iCurrentExcelColumn = 0;
for (int j = 0; j < sht.ColumnHeader.Columns.Count; j++)
{
if (sht.Columns[j].StyleName == "NO_EXPORT" || sht.Columns[j].StyleName == "EXPORT_LAST")
{
continue;
}
//WriteText
if (sht.ColumnHeader.Cells[i, j].Text == "")
{
//xlApp.WriteCellText(i + 1, j + 1, sht.ColumnHeader.Cells[i, j].Column.Label);
xlApp.WriteCellText(i + 1, iCurrentExcelColumn + 1, "'" + sht.ColumnHeader.Cells[i, j].Column.Label);
}
else
{
//xlApp.WriteCellText(i + 1, j + 1, sht.ColumnHeader.Cells[i, j].Text);
xlApp.WriteCellText(i + 1, iCurrentExcelColumn + 1, "'" + sht.ColumnHeader.Cells[i, j].Text);
}
//Set alignment ColumnHeader
XL.XlHAlign hAlign = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
XL.XlVAlign vAlign = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
GetColumnHeaderAlignment(sht.ColumnHeader.Cells[i, j], out hAlign, out vAlign);
//xlApp.SetAlignment(i + 1, j + 1, i + 1, j + 1, hAlign, vAlign);
xlApp.SetAlignment(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, hAlign, vAlign);
//xlApp.SetBackgroundColor(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, Color.FromArgb(250, 206, 135));
//xlApp.SetFontBold(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, true);
//Set ColumnSpan and RowSpan
if (sht.ColumnHeader.Cells[i, j].ColumnSpan > 1)
{
//xlApp.MergeCell(i + 1, j + 1, i + 1, j + sht.ColumnHeader.Cells[i, j].ColumnSpan);
xlApp.MergeCell(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + sht.ColumnHeader.Cells[i, j].ColumnSpan);
j += sht.ColumnHeader.Cells[i, j].ColumnSpan - 1;
}
if (sht.ColumnHeader.Cells[i, j].RowSpan > 1)
{
//xlApp.MergeCell(i + 1, j + 1, i + sht.ColumnHeader.Cells[i, j].RowSpan, j + 1);
xlApp.MergeCell(i + 1, iCurrentExcelColumn + 1, i + sht.ColumnHeader.Cells[i, j].RowSpan, iCurrentExcelColumn + 1);
}
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
//Set label to export_last column
foreach (int j in lastExportcolumn)
{
//WriteText
if (sht.ColumnHeader.Cells[i, j].Text == "")
{
xlApp.WriteCellText(i + 1, iCurrentExcelColumn + 1, "'" + sht.ColumnHeader.Cells[i, j].Column.Label);
}
else
{
xlApp.WriteCellText(i + 1, iCurrentExcelColumn + 1, "'" + sht.ColumnHeader.Cells[i, j].Text);
}
//Set alignment ColumnHeader
XL.XlHAlign hAlign = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
XL.XlVAlign vAlign = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
GetColumnHeaderAlignment(sht.ColumnHeader.Cells[i, j], out hAlign, out vAlign);
xlApp.SetAlignment(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, hAlign, vAlign);
//xlApp.SetBackgroundColor(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, Color.FromArgb(250, 206, 135));
//xlApp.SetFontBold(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, true);
//Set ColumnSpan and RowSpan
if (sht.ColumnHeader.Cells[i, j].ColumnSpan > 1)
{
xlApp.MergeCell(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + sht.ColumnHeader.Cells[i, j].ColumnSpan);
//j += sht.ColumnHeader.Cells[i, j].ColumnSpan - 1;
}
if (sht.ColumnHeader.Cells[i, j].RowSpan > 1)
{
//xlApp.MergeCell(i + 1, j + 1, i + sht.ColumnHeader.Cells[i, j].RowSpan, j + 1);
xlApp.MergeCell(i + 1, iCurrentExcelColumn + 1, i + sht.ColumnHeader.Cells[i, j].RowSpan, iCurrentExcelColumn + 1);
}
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
xlApp.SetRowHeight(i + 1, i + 1, 200);
xlApp.SetBackgroundColor(i + 1, 1, i + 1, iCurrentExcelColumn, Color.FromArgb(250, 206, 135));
xlApp.SetFontBold(i + 1, 1, i + 1, iCurrentExcelColumn, true);
xlApp.SetWrapText(i + 1, 1, i + 1, iCurrentExcelColumn);
}
//Microsoft.Office.Interop.Excel.Range range = xlApp.ExcelApplication.get_Range(
// "A1:F1"
// //+ (sht.RowCount + sht.ColumnHeader.RowCount).ToString()
// //, xlApp.GetColumnChar(sht.ColumnHeader.Columns.Count)
// // + (sht.RowCount + sht.ColumnHeader.RowCount).ToString()
//);
//range.Interior.Color = System.Drawing.Color.Moccasin.ToArgb();
//range.Font.Bold = true;
}
//if (bHeader)
//{
// //Update FormatText for ColumnHeader = "@"
// xlApp.FormatRow(1, sht.ColumnHeader.RowCount, "@");
// //Update start row index.
// iCurrentRowIndex = sht.ColumnHeader.RowCount;
//}
//#################
// Draw DataContent
//#################
//for (int i = 0; i < sht.Rows.Count; i++)
//{
// for (int j = 0; j < sht.Columns.Count; j++)
// {
// ICellType cellType = sht.GetCellType(i, j);
// string strFormat = GetExcelCellFormatString(cellType);
// if (cellType is DateTimeCellType) // Check CellType of current cell for DateTime type.
// {
// if (String.IsNullOrEmpty(strFormat))
// strFormat = CommonLib.Common.CurrentUserInfomation.DateFormatString;//.ToString();
// if (sht.Cells[i, j].Value != null)
// {
// DateTime dateTime = Convert.ToDateTime(sht.Cells[i, j].Value);
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, dateTime.ToString(strFormat),strFormat);
// }
// }
// else // Another CellType.
// {
// string strOutput = String.Empty;
// if (sht.Cells[i, j].Value != null)
// strOutput = sht.Cells[i, j].Value.ToString();
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, strOutput, strFormat);
// }
// }
// prgExport.Value += 1;
// prgExport.Refresh();
// iCurrentRowIndex++;
//}
//xlApp.DeclareDataContent(sht.ColumnHeader.Rows.Count, sht.Rows.Count, sht.Columns.Count);
xlApp.DeclareDataContent(sht.ColumnHeader.Rows.Count, sht.Rows.Count, iTotalExcelColumn);
//check row count แล้วค่อย set format เพราะว่าข้างในมันต้องใช้ cell type ไป checkว่าเป็น typeอะไร
if (sht.Rows.Count > 0)
{
iCurrentExcelColumn = 0;
for (int iSetColFormat = 0; iSetColFormat < sht.Columns.Count; iSetColFormat++)
{
if (sht.Columns[iSetColFormat].StyleName == "NO_EXPORT" || sht.Columns[iSetColFormat].StyleName == "EXPORT_LAST")
{
continue;
}
ICellType cellType = sht.GetCellType(0, iSetColFormat);
string strFormat = GetExcelCellFormatString(cellType);
if (cellType is DateTimeCellType) // Check CellType of current cell for DateTime type.
{
if (String.IsNullOrEmpty(strFormat))
{
strFormat = CommonLib.Common.CurrentUserInfomation.DateFormatString;//.ToString();
}
//if (sht.Cells[i, j].Value != null)
//{
// DateTime dateTime = Convert.ToDateTime(sht.Cells[i, j].Value);
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, dateTime.ToString(strFormat), strFormat);
//}
xlApp.SetColumnFormat(iCurrentExcelColumn + 1, strFormat);
//xlApp.SetColumnFormat(iSetColFormat + 1, strFormat);
}
else // Another CellType.
{
//string strOutput = String.Empty;
//if (sht.Cells[i, j].Value != null)
// strOutput = sht.Cells[i, j].Value.ToString();
//xlApp.WriteText(iCurrentRowIndex + 1, j + 1, strOutput, strFormat);
//xlApp.SetColumnFormat(iSetColFormat + 1, strFormat);
xlApp.SetColumnFormat(iCurrentExcelColumn + 1, strFormat);
}
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
//Set format to export_last column
foreach (int iSetColFormat in lastExportcolumn)
{
ICellType cellType = sht.GetCellType(0, iSetColFormat);
string strFormat = GetExcelCellFormatString(cellType);
if (cellType is DateTimeCellType) // Check CellType of current cell for DateTime type.
{
if (String.IsNullOrEmpty(strFormat))
{
strFormat = CommonLib.Common.CurrentUserInfomation.DateFormatString;
}
xlApp.SetColumnFormat(iCurrentExcelColumn + 1, strFormat);
}
else // Another CellType.
{
xlApp.SetColumnFormat(iCurrentExcelColumn + 1, strFormat);
}
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
}
int iVisibledRowCount = 0;
for (int i = 0; i < sht.Rows.Count; i++)
{
//modify by Bunyapat L. on 29 Jun 2011
//export เฉพาะ row ที่แสดงผลเท่านั้น
if (sht.GetRowVisible(i))
{
iCurrentExcelColumn = 0;
for (int j = 0; j < sht.Columns.Count; j++)
{
//ICellType cellType = sht.GetCellType(i, j);
////set format ของ column
//string strFormat = GetExcelCellFormatString(cellType);
//if (cellType is DateTimeCellType) // Check CellType of current cell for DateTime type.
//{
// if (String.IsNullOrEmpty(strFormat))
// strFormat = CommonLib.Common.CurrentUserInfomation.DateFormatString;//.ToString();
// if (sht.Cells[i, j].Value != null)
// {
// DateTime dateTime = Convert.ToDateTime(sht.Cells[i, j].Value);
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, dateTime.ToString(strFormat), strFormat);
// }
//}
//else // Another CellType.
//{
// string strOutput = String.Empty;
// if (sht.Cells[i, j].Value != null)
// strOutput = sht.Cells[i, j].Value.ToString();
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, strOutput, strFormat);
//}
if (sht.Columns[j].StyleName == "NO_EXPORT" || sht.Columns[j].StyleName == "EXPORT_LAST")
{
continue;
}
//xlApp.WriteRawData(iVisibledRowCount, j, sht.Cells[i, j].Value);
xlApp.WriteRawData(iVisibledRowCount, iCurrentExcelColumn, sht.Cells[i, j].Value);
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
//Set data to export_last column
foreach (int j in lastExportcolumn)
{
xlApp.WriteRawData(iVisibledRowCount, iCurrentExcelColumn, sht.Cells[i, j].Value);
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
iVisibledRowCount++;
}
prgExport.Value += 1;
prgExport.Refresh();
//iCurrentRowIndex++;
}
//set alignment of detail
xlApp.SetVerticalAlignment(sht.ColumnHeader.Rows.Count + 1, 1, iVisibledRowCount + 1, iTotalExcelColumn, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop);
//xlApp.ResizeDataContent(sht.ColumnHeader.Rows.Count, iVisibledRowCount, sht.Columns.Count);
xlApp.ResizeDataContent(sht.ColumnHeader.Rows.Count, iVisibledRowCount, iTotalExcelColumn);
//#################
// Save to file.
//#################
xlApp.SaveAs(filename);
// Close Excel Application.
xlApp.Hide();
xlApp.Dispose();
}
/// <summary>
/// Sets the vertical alignment for the specified cells on the specified sheet of the specified workbook.
/// </summary>
/// <param name="workbookName">The stored index of the workbook.</param>
/// <param name="sheetIndex">The sheet number you wish to alter (note: excel sheets begin with number 1, not 0).</param>
/// <param name="cellSelection">Indicates the cells to be changed. Works just like Excel. Example: "A:A,1:1,A2:B3,F4,F5"</param>
/// <param name="alignment">The vertical justication enumeration.</param>
public void ChangeVerticalAlignment(string workbookName, int sheetIndex, string cellSelection, Microsoft.Office.Interop.Excel.XlVAlign alignment)
{
List <Range> rangesToAlter = GetRanges(workbookName, sheetIndex, cellSelection);
foreach (Range rangeToAlter in rangesToAlter)
{
rangeToAlter.VerticalAlignment = alignment;
}
}
public void SetAliment(Excel.XlHAlign horizontalAlignment, Excel.XlVAlign verticalAlignment)
{
this._range.HorizontalAlignment = horizontalAlignment;
this._range.VerticalAlignment = verticalAlignment;
}
/// <summary>
/// 单元格文字对齐方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="hAlign">水平对齐</param>
/// <param name="vAlign">垂直对齐</param>
public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, Microsoft.Office.Interop.Excel.XlHAlign hAlign, Microsoft.Office.Interop.Excel.XlVAlign vAlign)
{
Range range = m_pExcelApp.get_Range(m_pExcelApp.Cells[startRow, startColumn], m_pExcelApp.Cells[endRow, endColumn]);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = vAlign;
//作者:董兰芳 日期:2009年2月4日17:17:06 描述:
Marshal.ReleaseComObject(range);
}
/// <summary>
/// CellValue
/// </summary>
/// <param name="cell"></param>
/// <param name="value"></param>
/// <param name="fontsize"></param>
/// <param name="halign"></param>
/// <param name="valign"></param>
/// <param name="font_color"></param>
/// <param name="font"></param>
/// <param name="bold"></param>
/// <param name="underline"></param>
public void CellValue(string cell, string value, int fontsize = 10, Excel.XlHAlign halign = Excel.XlHAlign.xlHAlignLeft, Excel.XlVAlign valign = Excel.XlVAlign.xlVAlignBottom, Color?font_color = null, string font = "Arial", bool bold = false, bool underline = false)
{
int row;
Int32.TryParse(new String(cell.Where(Char.IsDigit).ToArray()), out row);
string col = new String(cell.Where(Char.IsLetter).ToArray());
Color font_rgb = font_color ?? Color.FromArgb(0, 0, 0);
sheet1.Cells[row, col].WrapText = true;
sheet1.Cells[row, col].Font.Name = font;
sheet1.Cells[row, col].Font.Size = fontsize;
sheet1.Cells[row, col].Font.Bold = bold;
sheet1.Cells[row, col].Font.Underline = underline;
sheet1.Cells[row, col].Font.Color = ColorTranslator.ToOle(font_rgb);
sheet1.Cells[row, col] = value;
sheet1.Cells[row, col].HorizontalAlignment = halign;
sheet1.Cells[row, col].VerticalAlignment = valign;
}