public void FormatRange(string from, string to, Color?background, Align vertical, Align horizontal, BorderType borderType, BorderLineStyle borderStyle)
{
range = worksheet.get_Range(from, to);
if (background != null)
{
range.Interior.Color = ColorTranslator.ToOle(background.Value);
}
if (!vertical.Equals(Align.None))
{
range.VerticalAlignment = vertical;
}
if (!horizontal.Equals(Align.None))
{
range.HorizontalAlignment = horizontal;
}
if (borderType == BorderType.None)
{
return;
}
if (borderType == BorderType.BorderOutside)
{
range.BorderAround(borderStyle, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexNone, missing);
}
else
{
range.Borders.LineStyle = borderStyle;
}
}
static void AllBorders(Excel.Range formatRange)
{
formatRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
formatRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
formatRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
formatRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
}
static void Main(string[] args)
{
Console.WriteLine("Interop Assemblies Performance Test - 10000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat and do a BorderArround.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook book = books.Add(Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel.Worksheet sheet = sheets.Add() as Excel.Worksheet;
// do test 10 times
List <MarshalByRefObject> comReferencesList = new List <MarshalByRefObject>();
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 10000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.Range[rangeAdress];
cellRange.Value = "value";
Excel.Font font = cellRange.Font;
font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.BorderAround(Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 0);
comReferencesList.Add(font as MarshalByRefObject);
comReferencesList.Add(cellRange as MarshalByRefObject);
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
foreach (var item in comReferencesList)
{
Marshal.ReleaseComObject(item);
}
comReferencesList.Clear();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test2-Interop.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
}
void FormatCell(ref Excel.Range range)
{
// merge cells
range.Merge();
// add border
range.BorderAround(XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
// center allign
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// set data format as text
range.NumberFormatLocal = "@";
}
public void ApplyStyleGrandTotalValue(Excel.Range RngColGrandTotal)
{
RngColGrandTotal.EntireRow.Font.Size = 12;
RngColGrandTotal.EntireRow.Font.Bold = true;
RngColGrandTotal.HorizontalAlignment = 3;
RngColGrandTotal.VerticalAlignment = 3;
RngColGrandTotal.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
RngColGrandTotal.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
RngColGrandTotal.Borders.Weight = 2d;
RngColGrandTotal.EntireColumn.AutoFit();
}
public void SetReportName(Excel.Range RngReportName, string Header = "")
{
RngReportName.Merge(false);
RngReportName.Value2 = Header;
RngReportName.EntireRow.Font.Size = 12;
RngReportName.EntireRow.Font.Bold = true;
//chartRange.FormulaR1C1 = "Your Heading Here";
RngReportName.HorizontalAlignment = 3;
RngReportName.VerticalAlignment = 3;
RngReportName.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
}
public void ApplyStyleRowValue(Excel.Range RngColumnValue)
{
//RngColumnValue.EntireRow.Font.Size = 10;
//RngColumnValue.EntireRow.Font.Bold = false;
RngColumnValue.HorizontalAlignment = 3;
RngColumnValue.VerticalAlignment = 3;
RngColumnValue.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
RngColumnValue.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
RngColumnValue.Borders.Weight = 2d;
RngColumnValue.EntireColumn.AutoFit();
}
private void PutCellBorder(string cell, string val)
{
PutCell(cell, val);
_range.Interior.Color = ColorTranslator.ToOle(_color);
_range.Columns.AutoFit();
_range.BorderAround(
Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlThin,
Excel.XlColorIndex.xlColorIndexAutomatic,
Type.Missing
);
}
public void setNormalBorderOutline(int rowStart, int colStart, int rowEnd, int colEnd, int sheetIdx)
{
string rangeStart = GetExcelColumnName(colStart).ToString() + rowStart;
string rangeStop = GetExcelColumnName(colEnd).ToString() + rowEnd;
//Get active workbook sheet
Excel.Worksheet oSheet = (Excel.Worksheet)oWB.Sheets[sheetIdx];
Excel.Range oRng = oSheet.get_Range(rangeStart, rangeStop);
oRng.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin);
}
//объединение ячеек
public Boolean Merge(Int32 inRowLeft, Int32 inColLeft, Int32 inRowRight, Int32 inColRight)
{
try
{
Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)_workSheet.get_Range((Microsoft.Office.Interop.Excel.Range)_workSheet.Cells[inRowLeft, inColLeft], (Microsoft.Office.Interop.Excel.Range)_workSheet.Cells[inRowRight, inColRight]);
rng.Merge(this.MergeRowsOnly);
// Установить границу объединенных ячеек
rng.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
return(true);
}
catch
{
return(false);
}
}
/// <summary>
/// 获得保存路径
/// </summary>
/// <returns></returns>
//public static string SaveFileDialog()
//{
// SaveFileDialog sfd = new SaveFileDialog();
// sfd.DefaultExt = "xls";
// sfd.Filter = "Excel文件(*.xls)|*.xls";
// if (sfd.ShowDialog() == DialogResult.OK)
// {
// return sfd.FileName;
// }
// return string.Empty;
//}
/// <summary>
/// 获得打开文件的路径
/// </summary>
/// <returns></returns>
//public static string OpenFileDialog()
//{
// OpenFileDialog ofd = new OpenFileDialog();
// ofd.DefaultExt = "xls";
// ofd.Filter = "Excel文件(*.xls)|*.xls";
// if (ofd.ShowDialog() == DialogResult.OK)
// {
// return ofd.FileName;
// }
// return string.Empty;
//}
/// <summary>
/// 设置单元格边框
/// </summary>
protected void SetCellsBorderAround()
{
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
//if (dt.Rows.Count > 0)
//{
// range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
// range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
// range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
//}
//if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
}
public void createSubHeaders(string data, int Srow, int Scol, int Erow, int Ecol, System.Drawing.Color backcolor, bool fontbold, int size, System.Drawing.Color fcolor, int fontsize, ExAlign HAlignment)
{
worksheet.Cells[Srow, Scol] = data;
workSheet_range = worksheet.get_Range(worksheet.Cells[Srow, Scol], worksheet.Cells[Erow, Ecol]);// (Excel.Range)worksheet.Cells[row, col]; // worksheet.get_Range(cell1, cell2);
workSheet_range.MergeCells = true;
workSheet_range.Merge(false);
workSheet_range.HorizontalAlignment = HorizontalAlignment(HAlignment);
workSheet_range.Interior.Color = backcolor.ToArgb();
workSheet_range.Font.Bold = fontbold;
workSheet_range.Font.Size = fontsize;
if (size != 0)
{
workSheet_range.ColumnWidth = size;
}
workSheet_range.Font.Color = fcolor.ToArgb();
workSheet_range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexNone, 1);
//workSheet_range.WrapText = false;
}
public void SetReportHeader(Excel.Range RngColumnHeader, DataTable dtTemp)
{
/////Column Header
for (int Intcol = 0; Intcol <= dtTemp.Columns.Count - 1; Intcol++)
{
RngColumnHeader.set_Item(1, Intcol + 1, dtTemp.Columns[Intcol].ColumnName);
string DataType = dtTemp.Columns[Intcol].DataType.Name.ToString();
string Formatedcolname = ColumnIndexToColumnLetter(Intcol + 1);
Excel.Range RngColFormat;
RngColFormat = Worksheet.get_Range(Formatedcolname + "3", Formatedcolname + dtTemp.Rows.Count + 3);
switch (DataType.ToUpper())
{
case "DECIMAL":
case "Double":
RngColFormat.NumberFormat = "#,###,###.00";
break;
case "DATETIME":
RngColFormat.NumberFormat = "dd/mm/yyyy";
break;
case "INT16":
case "INT32":
case "INT64":
RngColFormat.NumberFormat = "###";
break;
}
}
RngColumnHeader.EntireRow.Font.Size = 10;
RngColumnHeader.EntireRow.Font.Bold = true;
RngColumnHeader.HorizontalAlignment = 3;
RngColumnHeader.VerticalAlignment = 3;
RngColumnHeader.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
RngColumnHeader.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
RngColumnHeader.Borders.Weight = 2d;
RngColumnHeader.EntireColumn.AutoFit();
}
static void makeimgCell_common(int startrow, int startcolum, Excel.Worksheet imgws, Excel.Worksheet copyws, String imgpath, readVO vo, int addcolum)
{
int afterrow = startrow;
Excel.Range selectCell = null;
//Insert Image
Excel.Range imgRange = (Excel.Range)imgws.Cells[startrow + 1, startcolum + 1 + addcolum];
float Left = (float)((double)imgRange.Left);
float Top = (float)((double)imgRange.Top);
imgws.Shapes.AddPicture(imgpath + "\\" + vo.pictureFileNameInExcel + "." + settingText9, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, (float)432, (float)246.8976);
selectCell = imgws.Range[imgws.Cells[startrow, startcolum + addcolum], imgws.Cells[startrow + 17 - 1, startcolum + 9 + addcolum]];
selectCell.Merge();
selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
selectCell = (Excel.Range)copyws.Cells[vo.orignalImgCell, settingText8];
copyws.Hyperlinks.Add(selectCell, "#'" + imgws.Name + "'!" + ExcelColumnIndexToName(startcolum + addcolum) + (startrow), Type.Missing, Type.Missing, Type.Missing);
//1열
selectCell = imgws.Range[imgws.Cells[startrow + 17, startcolum + addcolum], imgws.Cells[startrow + 17, startcolum + 1 + addcolum]];
selectCell.RowHeight = 24;
selectCell.Merge();
selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
selectCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
selectCell.Value = "위 치";
selectCell = imgws.Range[imgws.Cells[startrow + 17, startcolum + 2 + addcolum], imgws.Cells[startrow + 17, startcolum + 9 + addcolum]];
selectCell.Merge();
selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
selectCell.IndentLevel = 1;
selectCell.Value = vo.sub_position + "(" + vo.position + ")";
//2열
selectCell = imgws.Range[imgws.Cells[startrow + 18, startcolum + addcolum], imgws.Cells[startrow + 18, startcolum + 1 + addcolum]];
selectCell.RowHeight = 24;
selectCell.Merge();
selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
selectCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
selectCell.Value = "내 용";
selectCell = imgws.Range[imgws.Cells[startrow + 18, startcolum + 2 + addcolum], imgws.Cells[startrow + 18, startcolum + 5 + addcolum]];
selectCell.Merge();
selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
selectCell.IndentLevel = 1;
selectCell.Value = vo.content;
selectCell = imgws.Range[imgws.Cells[startrow + 18, startcolum + 6 + addcolum], imgws.Cells[startrow + 18, startcolum + 9 + addcolum]];
selectCell.Merge();
selectCell.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
selectCell.IndentLevel = 1;
selectCell.Value = vo.supply + " / " + vo.ea + " / " + vo.unit + "EA";
}
private Excel.Range SetRangeParams(ref Excel.Worksheet newWorkSheet, string startRange, string endRange,
bool merge = true, string rangeValue = "", bool borderAround = false,
bool lineStyle = false, bool release = true, bool bold = false, bool center = false)
{
Excel.Range range = newWorkSheet.get_Range(startRange, endRange);
range.WrapText = true;
if (merge)
{
range.Merge();
}
if (borderAround)
{
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
}
if (lineStyle)
{
range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
if (!string.IsNullOrEmpty(rangeValue))
{
range.Value2 = rangeValue;
}
range.Font.Bold = bold;
if (center)
{
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
if (release)
{
Marshal.FinalReleaseComObject(range);
}
return(range);
}
//FUNCION DE EXPORTACION DE EXCEL
public bool exportarExcel(DataGridView dgView)
{
try
{
string sValorGrid;
string sFont = "Arial";
int iSize = 11;
//CREACIÓN DE LOS OBJETOS DE EXCEL
Excel.Application xlsApp = new Excel.Application();
Excel.Worksheet xlsSheet;
Excel.Workbook xlsBook;
//AGREGAMOS EL LIBRO Y HOJA DE EXCEL
xlsBook = xlsApp.Workbooks.Add(true);
xlsSheet = (Excel.Worksheet)xlsBook.ActiveSheet;
//ESPECIFICAMOS EL TIPO DE LETRA Y TAMAÑO DE LA LETRA DEL LIBRO
xlsSheet.Rows.Cells.Font.Size = iSize;
xlsSheet.Rows.Cells.Font.Name = sFont;
//AGREGAMOS LOS ENCABEZADOS
int iFil = 0, iCol = 0;
foreach (DataGridViewColumn column in dgView.Columns)
{
if (column.Visible)
{
xlsSheet.Cells[1, ++iCol] = column.HeaderText;
}
}
//MARCAMOS LAS CELDAS DEL ENCABEZADO EN NEGRITA Y EN COLOR DE RELLENO GRIS
xlsSheet.get_Range((object)xlsSheet.Cells[1, 1], (object)xlsSheet.Cells[1, dgView.ColumnCount]).Font.Bold = true;
xlsSheet.get_Range((object)xlsSheet.Cells[1, 1], (object)xlsSheet.Cells[1, dgView.ColumnCount]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
//RECORRIDO DE LAS FILAS Y COLUMNAS (PINTADO DE CELDAS)
Excel.Range r;
Color c;
for (iFil = 0; iFil < dgView.RowCount; iFil++)
{
for (iCol = 0; iCol < dgView.ColumnCount; iCol++)
{
sValorGrid = dgView.Rows[iFil].Cells[iCol].Value.ToString();
if ((esNumero(sValorGrid) == true) && (sValorGrid.Length >= 9))
{
xlsSheet.Cells[iFil + 2, iCol + 1] = "'" + sValorGrid;
}
else
{
xlsSheet.Cells[iFil + 2, iCol + 1] = sValorGrid;
}
c = dgView.Rows[iFil].Cells[iCol].Style.BackColor;
if (!c.IsEmpty)
{// COMPARAMOS SI ESTÁ PINTADA LA CELDA (SI ES VERDADERO PINTAMOS LA CELDA)
r = (Excel.Range)(object) xlsSheet.Cells[iFil + 2, iCol + 1];
xlsSheet.get_Range(r, r).Interior.Color = System.Drawing.ColorTranslator.ToOle(dgView.Rows[iFil].Cells[iCol].Style.BackColor);
}
}
}
xlsSheet.Columns.AutoFit();
xlsSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
xlsSheet.PageSetup.Zoom = 80;
Excel.Range rango = xlsSheet.get_Range((object)xlsSheet.Cells[1, 1], (object)xlsSheet.Cells[dgView.RowCount + 1, dgView.ColumnCount]);
rango.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
rango.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
xlsApp.Visible = true;
return(true);
}
catch (Exception ex)
{
//ok = new VentanasMensajes.frmMensajeNuevoOk();
//ok.lblMensaje.Text = ex.Message;
//ok.ShowDialog();
return(false);
}
}
private void bttnExportar_Click(object sender, EventArgs e)
{
Excel.Application oApp;
Excel.Worksheet oSheet;
Excel.Workbook oBook;
oApp = new Excel.Application();
oBook = oApp.Workbooks.Add();
oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);
//rango de celdas
Excel.Range rango = oSheet.Range["A1", "F9"];
//estilo del borde
rango.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
rango.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
//estilo de fuente
oSheet.Rows.Cells.Font.Size = 11;
oSheet.Rows.Cells.Font.Name = "Calibri";
//ancho de columnas
rango.ColumnWidth = 20;
//text align
//rango.HorizontalAlignment = HorizontalAlignment.Center;
rango.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
rango.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//rango.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.AliceBlue);
Excel.Range rangoTitulo = oSheet.Range["B1", "D1"];
Excel.Range rangoDias = oSheet.Range["A3", "F3"];
Excel.Range rangoGrupo = oSheet.Range["A1", "F2"];
Excel.Range rangoCampos = oSheet.Range["A4", "F9"];
//color de Horario y grupo
rangoTitulo.Font.Size = 14;
rangoTitulo.Font.Bold = true;
rangoDias.Font.Bold = true;
rangoDias.Font.Size = 14;
rangoDias.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
rangoGrupo.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
rangoDias.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue);
rangoCampos.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.AliceBlue);
oSheet.Cells[1, 2] = "Horario";
oSheet.Cells[1, 3] = "del grupo:";
if (editar)
{
oSheet.Cells[1, 4] = Conector.leerGrupoPorID(gs.getID());
h = Conector.LeerTodosHorarios(gs.getID());
for (int i = 0; i < h.Length; i++)
{
oSheet.Cells[h[i].Hora + 3, h[i].Dia + 1] = Conector.leerClase(h[i].Clase).NombreMateria + Environment.NewLine + Conector.leerNombreProfesor(Conector.leerClase(h[i].Clase).Profesor)
+ Environment.NewLine + Conector.leerAulaPorId(h[i].Aula);
}
}
else
{
oSheet.Cells[1, 4] = Conector.TodosGrupo()[Conector.TodosGrupo().Length - 1].Nombre;
h = Conector.LeerTodosHorarios(Conector.TodosGrupo()[Conector.TodosGrupo().Length - 1].Id);
for (int i = 0; i < h.Length; i++)
{
oSheet.Cells[h[i].Hora + 3, h[i].Dia + 1] = Conector.leerClase(h[i].Clase).NombreMateria + Environment.NewLine +
Conector.leerNombreProfesor(Conector.leerClase(h[i].Clase).Profesor)
+ Environment.NewLine + Conector.leerAulaPorId(h[i].Aula);
}
}
oSheet.Cells[3, 1] = "Hora";
oSheet.Cells[3, 2] = "Lunes";
oSheet.Cells[3, 3] = "Martes";
oSheet.Cells[3, 4] = "Miércoles";
oSheet.Cells[3, 5] = "Jueves";
oSheet.Cells[3, 6] = "Viernes";
oSheet.Cells[4, 1] = "2:10-3:00 PM";
oSheet.Cells[5, 1] = "3:00-3:50 PM";
oSheet.Cells[6, 1] = "3:50-4:40 PM";
oSheet.Cells[7, 1] = "5:10-6:00 PM";
oSheet.Cells[8, 1] = "6:00-7:40 PM";
oSheet.Cells[9, 1] = "7:40-8:30 PM";
oBook.Close();
oApp.Quit();
}
public void CreateTable()
{
string[] headers = new string[] {
"Település",
"Hotel név",
"Sorszám",
"Éjszakák száma",
"Fő/éjszaka (forint)",
};
for (int i = 0; i < headers.Length; i++)
{
xlSheet.Cells[1, 1 + i] = headers[i];
}
object[,] values = new object[Lista.Count, headers.Length];
int counter = 0;
foreach (Adat a in Lista)
{
values[counter, 0] = a.Telepules;
values[counter, 1] = a.Hotelnev;
values[counter, 2] = a.Sorszam;
values[counter, 3] = a.Ejszaka;
values[counter, 4] = a.Forint;
counter++;
}
string GetCell(int x, int y)
{
string ExcelCoordinate = "";
int dividend = y;
int modulo;
while (dividend > 0)
{
modulo = (dividend - 1) % 26;
ExcelCoordinate = Convert.ToChar(65 + modulo).ToString() + ExcelCoordinate;
dividend = (int)((dividend - modulo) / 26);
}
ExcelCoordinate += x.ToString();
return(ExcelCoordinate);
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 30;
headerRange.Interior.Color = Color.Beige;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range adatokRange = xlSheet.get_Range(GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1)));
adatokRange.EntireColumn.AutoFit();
adatokRange.Interior.Color = Color.SeaShell;
adatokRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
adatokRange.BorderAround(Excel.XlLineStyle.xlContinuous);
adatokRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
protected override void ApplyStyle(Excel.Range rng)
{
rng.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
}
public static void DataTable2XLSX(System.Data.DataTable dt, string fileName)
{
if (string.IsNullOrEmpty(fileName))
{
return;
}
int rowCount = dt.Rows.Count;
int columnCount = dt.Columns.Count;
int index = 0;
//diyProcessBar1.Maximum = rowCount;
//diyProcessBar1.Visible = true;
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
xlsApp.Application.Workbooks.Add(true);
xlsApp.Visible = false;
//Microsoft.Office.Interop.Excel.Workbooks workBooks = xlsApp.ActiveWorkbook;
//Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Workbook workBook = xlsApp.ActiveWorkbook;
Microsoft.Office.Interop.Excel.Worksheet workSheet1 = null;
try
{
#region range create
workSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 0; i < rowCount; i++)
{
if (i == 0)
{
for (int j = 2; j < columnCount + 2; j++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet1.Cells[i + 1, j - 1];
range.Value2 = dt.Columns[j - 2].ColumnName;
range.Font.Bold = true;
range.Interior.Color = Color.Azure.ToArgb();
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();
}
}
for (int j = 2; j < columnCount + 2; j++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet1.Cells[i + 2, j - 1];
range.Value = dt.Rows[i][j - 2];
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();
if (range.Value2 != null && range.Value2.ToString().StartsWith("http://", StringComparison.OrdinalIgnoreCase))
{
workSheet1.Hyperlinks.Add(range, range.Value2.ToString(), Missing.Value, Missing.Value, Missing.Value);
}
}
index++;
//diyProcessBar1.Value = index;
//Application.DoEvents();
//workSheet.get_Range((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[i, 1], (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[i, 8]).Select();
}
xlsApp.DisplayAlerts = false;
xlsApp.AlertBeforeOverwriting = false;
//diyProcessBar1.Visible = false;
workBook.SaveCopyAs(fileName);
#endregion
}
catch (Exception ex)
{
log.WriteLine("WriteExcelField error" + ex.Message);
}
finally
{
#region finally
if (workBook != null)
{
workBook.Close(false, Type.Missing, Type.Missing);
}
IntPtr t = new IntPtr(xlsApp.Hwnd);
xlsApp.Quit();
if (workSheet1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet1);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
GC.Collect();
#endregion
}
}
public static string ExportToDataGridView <T>(List <T> dataList, string exceptColumns)
{
Excel.Application excel = null;
Excel.Workbook workBook = null;
Excel.Worksheet workSheet = null;
Excel.Range range = null;
string strHeaderStart = "A1";
string strDataStart = "A2";
object optionalValue = Missing.Value;
try
{
excel = new Excel.Application();
workBook = excel.Workbooks.Add(); // 워크북 추가
workSheet = workBook.Worksheets.get_Item(1); // as Excel.Worksheet; // 엑셀 첫번째 워크시트 가져오기
//excel.Application.Workbooks.Add(true);
Dictionary <string, string> objHeaders = new Dictionary <string, string>();
int columnIndex = 0;
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (PropertyInfo property in headerInfo)
{
if (!exceptColumns.Contains(property.Name))
{
var attribute = property.GetCustomAttributes(typeof(T), false)
.Cast <T>().FirstOrDefault();
objHeaders.Add(property.Name, attribute == null ?
property.Name : attribute.ToString());
}
}
range = workSheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(1, objHeaders.Count);
range.set_Value(optionalValue, objHeaders.Values.ToArray());
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range.Interior.Color = Color.FromArgb(51, 52, 79);
range.Font.Color = Color.White;
int rowIndex = 0;
object[,] objData = new object[rowIndex, objHeaders.Count];
foreach (T data in dataList)
{
rowIndex++;
columnIndex = 0;
foreach (PropertyInfo prop in typeof(T).GetProperties())
{
if (!exceptColumns.Contains(prop.Name))
{
columnIndex++;
if (prop.GetValue(data, null) != null)
{
excel.Cells[rowIndex + 1, columnIndex] = prop.GetValue(data, null).ToString();
}
}
}
}
range = workSheet.get_Range(strDataStart, optionalValue);
range = range.get_Resize(rowIndex, objHeaders.Count);
range.set_Value(optionalValue, objData);
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range = workSheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(rowIndex + 1, objHeaders.Count);
range.WrapText = false;
range.Columns.AutoFit();
excel.Visible = true;
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
worksheet.Activate();
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public static void Form(ReportData reportData)
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlApp.StandardFont = "Times New Roman";
SetColumnWidth();
xlWorkSheet.Range["a2", "j2"].Merge(false);
chartRange = xlWorkSheet.Range["a2"];
chartRange.Value = $"Акт №{reportData.Number}";
//chartRange.Font.Name = "Times New Roman";
chartRange.Font.Size = 11;
chartRange.Font.Bold = true;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range["a3", "j3"].Merge(false);
chartRange = xlWorkSheet.Range["a3"];
chartRange.Value = $"прийому-передачі наданих послуг до договору № {reportData.Contract.Number} від {reportData.Contract.From.ToShortDateString()} року";
//chartRange.Font.Name = "Times New Roman";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range["a4", "c4"].Merge(false);
chartRange = xlWorkSheet.Range["a4"];
chartRange.Value = $"м. {GetCity()}";
//chartRange.Font.Name = "Times New Roman";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range["h4", "j4"].Merge(false);
chartRange = xlWorkSheet.Range["h4"];
chartRange.Value = $"{reportData.Date.ToShortDateString()} року";
//chartRange.Font.Name = "Times New Roman";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange = xlWorkSheet.Range["a5"];
chartRange.RowHeight = 7;
xlWorkSheet.Range["a6", "j6"].Merge(false);
chartRange = xlWorkSheet.Range["a6"];
chartRange.RowHeight = 66;
chartRange.Value = $" Ми, що нижче підписалися, Виконавець - {reportData.Executor.FullName} " +
$"та представник Замовника – Голова правління БО \"БТ \"100% ЖИТТЯ ДНІПРО\" " +
$"{ConfigurationManager.AppSettings["chairman"]}, який діє на підставі Статуту, цим актом " +
$"засвідчуємо що Виконавець надав, а Замовник отримав, в рамках виконання " +
$"умов Грантової Угоди № {reportData.GrantAgreement.Number} від " +
$"{reportData.GrantAgreement.From}р." +
$" та проекту «{reportData.GrantAgreement.Name}», наступні послуги: ";
chartRange.Characters[42, reportData.Executor.FullName.Length].Font.Bold = true;
chartRange.WrapText = true;
chartRange.Font.Size = 9;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
int row = 7;
for (int d = 0; d < reportData.Directions.Count; ++d)
{
chartRange = xlWorkSheet.Range[$"a{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.RowHeight = 21;
chartRange.Value = $"{reportData.Directions[d].Number}: \"{reportData.Directions[d].Name}\"";
chartRange.Characters[0, reportData.Directions[d].Number.ToString().Length + 1].Font.Bold = true;
chartRange.WrapText = true;
chartRange.Font.Size = 9;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"b{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = "№ з/п";
chartRange.Font.Bold = true;
chartRange.Font.Size = 9;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"c{row}", $"e{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = "Найменування послуг";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"f{row}"];
chartRange.WrapText = true;
chartRange.Value = "Одиниця виміру";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"g{row}"];
chartRange.WrapText = true;
chartRange.Value = "Кількість послуг";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"h{row}"];
chartRange.WrapText = true;
chartRange.Value = "Ціна, грн";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"i{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = "Вартість, грн";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
row++;
for (int s = 0; s < reportData.Directions[d].Services.Count; ++s)
{
chartRange = xlWorkSheet.Range[$"a{row}", $"b{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"{s+1}";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"c{row}", $"e{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = reportData.Directions[d].Services[s].Name;
chartRange.Font.Size = 9;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"f{row}"];
chartRange.WrapText = true;
chartRange.Value = "послуга";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"g{row}"];
chartRange.WrapText = true;
chartRange.Value = reportData.Directions[d].Services[s].Amount;
chartRange.Font.Size = 9;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"h{row}"];
chartRange.WrapText = true;
chartRange.Value = Math.Round(reportData.Directions[d].Services[s].Price, 2);
chartRange.Font.Size = 9;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"i{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = Math.Round(reportData.Directions[d].Services[s].Total, 2);
chartRange.Font.Size = 9;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
row++;
}
chartRange = xlWorkSheet.Range[$"a{row}", $"e{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = "Всього";
chartRange.Font.Bold = true;
chartRange.Font.Size = 9;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"f{row}"];
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"g{row}"];
chartRange.WrapText = true;
chartRange.Value = $"=SUM(g{row-reportData.Directions[d].Services.Count}:g{row-1})";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"h{row}"];
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"i{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = CalcValueOfServices(reportData.Directions[d].Services); // $"=SUM(i{row - reportData.Directions[d].Services.Count}:i{row - 1})";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
row++;
}
chartRange = xlWorkSheet.Range[$"a{row}", $"h{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = "Загальна кількість наданих послуг за цим актом прийому - передачі складає, шт";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"i{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = CalcTotalAmountOfServices(reportData.Directions);
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"h{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = "Загальна вартість наданих послуг за цим актом прийому-передачі становить, грн";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range[$"i{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = Math.Round(CalcTotalValueOfServices(reportData.Directions), 2);
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"({ NumberToWords(CalcTotalValueOfServices(reportData.Directions))}) без ПДВ,";
chartRange.Font.Size = 9;
chartRange.Font.Bold = true;
chartRange.RowHeight = 22.50;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"в тому числі відрахування до Державних фондів: податок з доходів" +
$" фізичних осіб {ConfigurationManager.AppSettings["tax"]}% та " +
$"військовий збір {ConfigurationManager.AppSettings["militaryGathering"]}% із суми загальної вартості послуг.";
chartRange.Font.Size = 9;
chartRange.RowHeight = 26;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"h{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"Замовник сплачує до Пенсійного Фонду України Єдиний" +
$" соціальний внесок {ConfigurationManager.AppSettings["socialContribution"]}% на загальну" +
$" суму наданих послуг за цим Договором.";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange = xlWorkSheet.Range[$"i{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"{CalcSocialContribution(reportData.Directions)} грн";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"h{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"Замовник, за надані за цим актом послуги, виплачує Виконавцю суму коштів в розмірі ";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange = xlWorkSheet.Range[$"i{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"{CalcTax(reportData.Directions)} грн";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"Виконавцем було надано Замовнику послуги, які передбачені " +
$"умовами Договору, якісно та у повному обсязі.";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"Сторони претензій одна до одної не мають.";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
row++;
chartRange = xlWorkSheet.Range[$"a{row}"];
chartRange.RowHeight = 7;
row++;
chartRange = xlWorkSheet.Range[$"a{row}", $"d{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"Послуги надав:";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange = xlWorkSheet.Range[$"g{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"Послуги прийняв: ";
chartRange.Font.Size = 9;
chartRange.RowHeight = 22;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
row++;
chartRange = xlWorkSheet.Range[$"d{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"{reportData.Executor.ShortName}";
chartRange.Font.Size = 9;
chartRange.RowHeight = 26;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange = xlWorkSheet.Range[$"g{row}", $"j{row}"];
chartRange.Merge(false);
chartRange.WrapText = true;
chartRange.Value = $"Голова правління __________ {ConfigurationManager.AppSettings["chairmanShortForm"]}";
chartRange.Font.Size = 9;
chartRange.RowHeight = 26;
chartRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlApp.Visible = true;
xlApp.UserControl = true;
}
public static void Send_GW_data(string OutGW, string OutEx, int price, double discount)
{
int k;
int l;
//Поиск пустой строки
for (k = 10; k < 200; k++)
{
Excel.Range cell = (Excel.Range)excel.excelworksheet1.Cells[k, 3];
if (cell.Value == null)
{
break;
}
}
//Очистка на всякий пожарный, перед записью
for (l = k; l < k + 6; l++)
{
for (int i = 2; i < 9; i++)
{
Excel.Range cell = (Excel.Range)excel.excelworksheet1.Cells[l, i];
cell.Value = null;
}
}
Excel.Range cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 1, 2];
cell2.Value = "";
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 1, 3];
cell2.Value = "";
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 2];
cell2.Value = "";
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 3];
cell2.Value = "";
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k, 3];
cell2.Value = OutGW;
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlLeft;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k, 4];
cell2.Value = "шт.";
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlCenter;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k, 5];
cell2.Value = 1;
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlCenter;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k, 7];
cell2.FormulaLocal = "=E" + Convert.ToString(k) + "*F" + Convert.ToString(k);
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlCenter;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k, 8];
cell2.FormulaLocal = "=G" + Convert.ToString(k) + "*" + Convert.ToString(discount / 100);
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k, 6];
cell2.Value = price;
//Ориентация
cell2.HorizontalAlignment = Excel.Constants.xlCenter;
cell2.VerticalAlignment = Excel.Constants.xlCenter;
cell2.WrapText = true;
k = k + 1;
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k, 3];
cell2.Value = OutEx;
cell2 = excel.excelworksheet1.get_Range("C" + (k - 1) + ":C" + k);
cell2.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, 0);
cell2 = excel.excelworksheet1.get_Range("D" + (k - 1) + ":D" + k);
cell2.Merge();
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = excel.excelworksheet1.get_Range("E" + (k - 1) + ":E" + k);
cell2.Merge();
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = excel.excelworksheet1.get_Range("F" + (k - 1) + ":F" + k);
cell2.Merge();
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = excel.excelworksheet1.get_Range("G" + (k - 1) + ":G" + k);
cell2.Merge();
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = excel.excelworksheet1.get_Range("G" + k);
cell2.Font.Bold = false;
// столбец №
cell2 = excel.excelworksheet1.get_Range("B11:B" + k);
cell2.Merge();
cell2.Borders.ColorIndex = 0;
cell2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
cell2.Borders.Weight = Excel.XlBorderWeight.xlThin;
cell2 = excel.excelworksheet1.get_Range("B10", "G" + (k));
cell2.Font.Bold = false;
//
k = k + 1;
cell2 = excel.excelworksheet1.get_Range("F" + k);
cell2.Value = "Итого:";
cell2.Font.Bold = true;
cell2 = excel.excelworksheet1.get_Range("G" + k);
cell2.FormulaLocal = "=СУММ(G11:G" + (k - 1) + ")";
cell2.Font.Bold = true;
cell2 = excel.excelworksheet1.get_Range("F" + (k + 1));
cell2.Value = "Сумма скидки:";
cell2.Font.Bold = true;
cell2 = excel.excelworksheet1.get_Range("G" + (k + 1));
cell2.FormulaLocal = "=СУММ(H11:H" + (k - 1) + ")";
cell2.Font.Bold = true;
cell2 = excel.excelworksheet1.get_Range("F" + (k + 2));
cell2.Value = "Итого со скидкой:";
cell2.Font.Bold = true;
cell2 = excel.excelworksheet1.get_Range("G" + (k + 2));
cell2.FormulaLocal = "=G" + k + "-G" + (k + 1);
cell2.Font.Bold = true;
//Итого прописью:
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 2];
cell2.Value = "Итого: ";
cell2 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 3];
Excel.Range cell3 = (Excel.Range)excel.excelworksheet1.Cells[k + 2, 7];
cell2.Value = NumByWords.RurPhrase(Convert.ToDecimal(cell3.Value));
}
public void WriteResultsTable(int n, bool top, bool isRead)
{
// put the model results data into an object array
//object[,] amodel ;
Excel.Range header = BottomCell().get_Offset(2, 0);
string start = header.get_Address(1, 1, Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing);
ArrayList lst = this.Source.Models.ModelSort();
int first = top ? 0 : Math.Min(n, lst.Count);
int last = top ? Math.Min(n, lst.Count) : lst.Count;
if (first == last)
{
return;
}
// write the column headers
//{ ModelNo, ModelValid, IVNames, IVCoefficients, IVses, IVpVals, R2, adjR2, pVal, RMSError, Residual, AIC, Formula };
foreach (Utilities.Constants.ModelOutputColumns col in System.Enum.GetValues(typeof(Utilities.Constants.ModelOutputColumns)))
{
string label = Globals.ThisAddIn.rsc.GetString("label" + col.ToString()) ?? col.ToString();
header.Value2 = label;
header = header.get_Offset(0, 1);
}
object[,] row;
int rowct = 1;
for (int j = first; j < last; j++)
{
Utilities.Model mdl = this.Source.Models.Item(j);
int varct = mdl.VariableNames.Length;
rowct += varct + 1;
Excel.Range target = BottomCell().get_Offset(1, 0).get_Resize(varct + 1, 13);
row = new object[1, 13];
row[0, 0] = mdl.ModelNumber;
row[0, 1] = mdl.Valid();
row[0, 6] = mdl.R2();
row[0, 7] = mdl.AdjustedR2();
row[0, 8] = mdl.ModelPValue();
row[0, 9] = mdl.RMSError;
row[0, 10] = mdl.ResidualSS();
row[0, 11] = mdl.AICFormula();
row[0, 12] = mdl.Formula();
target.get_Resize(1, 13).Value2 = row;
if (top)
{
Excel.Hyperlink hl = (Excel.Hyperlink)WS.Hyperlinks.Add(target.get_Resize(1, 1), "",
target.get_Resize(1, 1).get_Address(1, 1, Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing), System.Type.Missing, System.Type.Missing);
hl.ScreenTip = "Graph model " + mdl.ModelNumber.ToString();
}
row = new object[varct + 1, 4];
for (int i = 0; i < varct; i++)
{
row[i, 0] = mdl.VariableNames[i];
row[i, 1] = mdl.Coefficients[i];
row[i, 2] = mdl.StandardErrors()[i];
row[i, 3] = mdl.PValues()[i];
}
//row[varct, 0] = "(Intercept)";
row[varct, 1] = mdl.Coefficients[varct];
target.get_Offset(0, 2).get_Resize(varct + 1, 4).Value2 = row;
// put a box around
target.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, System.Type.Missing);
// highlight the best model
if (Source.BestModel() != null)
{
if (mdl.ModelNumber == Source.BestModel().ModelNumber)
{
if (mdl.Valid())
{
target.Font.Color = 0x0000AA;
//target.Style = Globals.ThisAddIn.rsc.GetString("bestModelStyle");
target.Font.Bold = true;
}
else
{
target.Font.Color = 0x0000AA;
//target.Style = "Bad";
target.Font.Bold = true;
}
}
}
}
Excel.Range tbl = WS.get_Range(start, System.Type.Missing).get_Resize(rowct, 13);
Excel.ListObject LO = WS.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tbl, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
LO.TableStyle = "TableStyleLight8";
LO.ShowTableStyleRowStripes = false;
((Excel.Range)LO.Range[0, 4]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 5]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 10]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 11]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 12]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 6]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 7]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 8]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 9]).EntireColumn.NumberFormat = "0.0000";
}
public static void ToExcel <T>(this List <T> list, string path)
{
#region [ تعریفات ]
if (path.IsNullOrEmpty())
{
throw new Exception(CommonConsts.Messages.Exception.InvalidObject);
}
if (list == null)
{
throw new Exception(CommonConsts.Messages.Exception.InvalidObject);
}
Excel.Application excelApp = null;
Excel.Workbooks workBooks = null;
Excel._Workbook workBook = null;
Excel.Sheets sheets = null;
Excel._Worksheet workSheet = null;
Excel.Range range = null;
Excel.Font font = null;
object optionalValue = Missing.Value;
string strHeaderStart = "A2";
string strDataStart = "A3";
#endregion
#region [ پردازش ]
try
{
#region [ ایجاد ]
excelApp = new Excel.Application();
workBooks = (Excel.Workbooks)excelApp.Workbooks;
workBook = (Excel._Workbook)(workBooks.Add(optionalValue));
sheets = (Excel.Sheets)workBook.Worksheets;
workSheet = (Excel._Worksheet)(sheets.get_Item(1));
#endregion
#region [ هدر ]
Dictionary <string, string> objHeaders = new Dictionary <string, string>();
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (var property in headerInfo)
{
var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
.Cast <DisplayNameAttribute>().FirstOrDefault();
objHeaders.Add(property.Name, attribute == null ? property.Name : attribute.DisplayName);
}
range = workSheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(1, objHeaders.Count);
range.set_Value(optionalValue, objHeaders.Values.ToArray());
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
font = range.Font;
font.Bold = true;
range.Interior.Color = Color.LightGray.ToArgb();
#endregion
#region [ نوشتن دیتا در سلول ها ]
int count = list.Count;
object[,] objData = new object[count, objHeaders.Count];
for (int j = 0; j < count; j++)
{
var item = list[j];
int i = 0;
foreach (KeyValuePair <string, string> entry in objHeaders)
{
var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null);
objData[j, i++] = (y == null) ? "" : y.ToString();
}
}
range = workSheet.get_Range(strDataStart, optionalValue);
range = range.get_Resize(count, objHeaders.Count);
range.set_Value(optionalValue, objData);
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range = workSheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(count + 1, objHeaders.Count);
range.Columns.AutoFit();
#endregion
#region [ ذخیره فایل ]
if (!path.IsNullOrEmpty())
{
workBook.SaveAs(path);
}
excelApp.Visible = true;
#endregion
#region Release objects
try
{
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
workSheet = null;
if (sheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
}
sheets = null;
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
workBook = null;
if (workBooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBooks);
}
workBooks = null;
if (excelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
excelApp = null;
}
catch (Exception ex)
{
workSheet = null;
sheets = null;
workBook = null;
workBooks = null;
excelApp = null;
ex.LogToTextFile("ToExcel");
}
finally
{
GC.Collect();
}
#endregion
}
catch (Exception ex)
{
ex.LogToTextFile("ToExcel");
FarsiBox.ShowInformation(ex.Message, "خطا", true);
}
finally
{
GC.Collect();
}
#endregion
}
public static void Example()
{
/* Microsoft.Office.Interop.Excel.Application xlApp;
* Excel.Workbook xlWorkBook;
* Excel.Worksheet xlWorkSheet;
* object misValue = System.Reflection.Missing.Value;
* Excel.Range chartRange;*/
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[4, 2] = "";
xlWorkSheet.Cells[4, 3] = "Student1";
xlWorkSheet.Cells[4, 4] = "Student2";
xlWorkSheet.Cells[4, 5] = "Student3";
xlWorkSheet.Cells[5, 2] = "Term1";
xlWorkSheet.Cells[5, 3] = "80";
xlWorkSheet.Cells[5, 4] = "65";
xlWorkSheet.Cells[5, 5] = "45";
xlWorkSheet.Cells[6, 2] = "Term2";
xlWorkSheet.Cells[6, 3] = "78";
xlWorkSheet.Cells[6, 4] = "72";
xlWorkSheet.Cells[6, 5] = "60";
xlWorkSheet.Cells[7, 2] = "Term3";
xlWorkSheet.Cells[7, 3] = "82";
xlWorkSheet.Cells[7, 4] = "80";
xlWorkSheet.Cells[7, 5] = "65";
xlWorkSheet.Cells[8, 2] = "Term4";
xlWorkSheet.Cells[8, 3] = "75";
xlWorkSheet.Cells[8, 4] = "82";
xlWorkSheet.Cells[8, 5] = "68";
xlWorkSheet.Cells[9, 2] = "Total";
string temp = "=SUM(c5:c8)";
chartRange = xlWorkSheet.Cells[9, 3];
chartRange.Value = temp;
xlWorkSheet.Cells[9, 4] = "299";
xlWorkSheet.Cells[9, 5] = "238";
xlWorkSheet.Range["b2", "e3"].Merge(false);
chartRange = xlWorkSheet.Range["b2", "e3"];
chartRange.FormulaR1C1 = "MARK LIST";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
chartRange.Font.Size = 20;
chartRange = xlWorkSheet.Range["b4", "e4"];
chartRange.Font.Bold = true;
chartRange = xlWorkSheet.get_Range("b9", "e9");
chartRange.Font.Bold = true;
chartRange = xlWorkSheet.get_Range("b2", "e9");
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range["b5", "e5"];
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range["b6", "e6"];
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range["b7", "e7"];
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range["b8", "e8"];
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
chartRange = xlWorkSheet.Range["b9"];
chartRange.ColumnWidth = 31.43;
chartRange = xlWorkSheet.Cells[2, 2];
//chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen);
//chartRange.Value = "Hello World";
//xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlApp.Visible = true;
xlApp.UserControl = true;
//xlWorkBook.Close(true, misValue, misValue);
//xlApp.Quit();
//releaseObject(xlApp);
//releaseObject(xlWorkBook);
//releaseObject(xlWorkSheet);
MessageBox.Show("File created !");
}
public static void Result(string Expected_Result, string Actual_Result, string Comments)
{
Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook objworkbook;
Microsoft.Office.Interop.Excel.Worksheet objworksheet;
try
{
string EndTime = DateTime.Now.ToString("hh:mm:ss tt", System.Globalization.DateTimeFormatInfo.InvariantInfo);
objExcel.DisplayAlerts = false;
objExcel.Visible = false;
Excel.Range range = null;
objworkbook = objExcel.Workbooks.Open(OutputfilePath, true, false,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, true, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
objworksheet = (Microsoft.Office.Interop.Excel.Worksheet)objworkbook.Sheets.get_Item("Results");
TakeScreenShot();
range = objworksheet.UsedRange;
int RowCount = range.Rows.Count;
int G = RowCount + 1;
objworksheet.Cells[G, 1] = RowCount;
objworksheet.Cells[G, 3] = BrowserName;
objworksheet.Cells[G, 4] = Starttime;
objworksheet.Cells[G, 5] = EndTime;
objworksheet.Cells[G, 6] = Sheet_Name;
objworksheet.Cells[G, 7] = Scinario_Name;
objworksheet.Cells[G, 8] = TestDescription;
objworksheet.Cells[G, 9] = FunctionName;
objworksheet.Cells[G, 10] = Expected_Result;
objworksheet.Cells[G, 11] = Actual_Result;
objworksheet.Cells[G, 12] = strScreeShotPath;
objworksheet.Hyperlinks.Add(objworksheet.Cells[G, 12], strScreeShotPath, Type.Missing, Type.Missing, "Snapshot");
if (Comments == "Passed" || Comments == "PASSED" || Comments == "passed")
{
Passed = Passed + 1;
range = objworksheet.get_Range("b" + G);
objworksheet.Cells[G, 2] = Comments;
range.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
range.Font.Size = 10;
}
else if (Comments == "Failed" || Comments == "failed" || Comments == "FAILED")
{
Failed = Failed + 1;
range = objworksheet.get_Range("a" + G, "k" + G);
objworksheet.Cells[G, 2] = Comments;
range.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
range.Font.Size = 10;
}
range = objworksheet.UsedRange;
range.Columns.AutoFit();
range.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
objworksheet.Name = "Results";
object objOpt = Missing.Value;
objworkbook.Save();
objworkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
objExcel.Quit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objExcel = null;
objworkbook = null;
objworksheet = null;
ReleaseComObject(objExcel);
ReleaseComObject(objworkbook);
ReleaseComObject(objworksheet);
}
}
/// <summary>
/// Function to export dataset to excel
/// </summary>
/// <param name="ds"></param>
public static void ExportDataSetToExcel(DataSet ds, string strPath)
{
int inHeaderLength = 0, inColumn = 0, inRow = 0; //Declaration de int.
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
strPath += @"Bilan des appels HNO " + DateTime.Now.ToString("dd-MM-yyyy_hh-mm") + ".xlsx";
OfficeExcel.Application excelApp = new OfficeExcel.Application();
OfficeExcel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
excelApp.Visible = true;
ds.Tables[0].Columns[0].ColumnName = "Date & Heure d'appel";
ds.Tables[0].Columns[1].ColumnName = "Numéro de ticket.";
ds.Tables[0].Columns[2].ColumnName = "Nom du Client";
ds.Tables[0].Columns[3].ColumnName = "Adresse du Client";
ds.Tables[0].Columns[4].ColumnName = "Nom de l'appelant";
ds.Tables[0].Columns[5].ColumnName = "Numéro de téléphone";
ds.Tables[0].Columns[6].ColumnName = "Description de la panne";
ds.Tables[0].Columns[7].ColumnName = "Technicien Appelé";
ds.Tables[0].Columns[8].ColumnName = "Observations";
foreach (System.Data.DataTable dtbl in ds.Tables)
{
//Create Excel WorkSheet
OfficeExcel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = dtbl.TableName = "Resumé";//Name worksheet
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
}
//Write Rows
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
excelWorkSheet.Cells.NumberFormat = "@"; // this allows us to keep the 0 at the start of our text elements!!!! important.
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "I" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
}
}
}
//Excel Header
Range line = (Range)excelWorkSheet.Rows[1];
line.Insert();
// Finding the last elements of my table :
OfficeExcel.Range last = excelWorkSheet.Cells.SpecialCells(OfficeExcel.XlCellType.xlCellTypeLastCell, Type.Missing);
OfficeExcel.Range range = excelWorkSheet.get_Range("A2", last);
//this draws around the range area between A2 and the last element of the table.
range.BorderAround(XlLineStyle.xlDouble);
// Excel table style
OfficeExcel.Range cellRang = excelWorkSheet.get_Range("B1", "H1");
cellRang.Merge(true); // makes a single cell between B1 and H1
cellRang.Interior.Color = System.Drawing.Color.White;
cellRang.Font.Color = System.Drawing.Color.Blue;
cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 26;
// this draws the borders of the excel document you selected.
cellRang.BorderAround(XlLineStyle.xlDouble);
//title of excel file
excelWorkSheet.Cells[1, 2] = "Title of EXCEL FILE";
//autofit errything together
excelWorkSheet.Columns.AutoFit();
////optional if needed
////[if you need it in an int]
//int lastUsedRow = last.Row;
//int lastUsedColumn = last.Column;
//// using last elements of the table.
//Style table column names
//cellRang = excelWorkSheet.get_Range("A4", "G4");
//cellRang.Font.Bold = true;
//cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
//cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
//excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignRight;
////Formate price column
//excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
////Auto fit columns
//excelWorkSheet.Columns.AutoFit();
}
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete(); // Efface la feuille Feuil1 d'excel.
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as OfficeExcel._Worksheet).Activate();
excelWorkBook.SaveAs(strPath, Default, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
excelWorkBook.Close();
excelApp.Quit();
Console.WriteLine("Excel generated successfully \n As " + strPath);
return;
}
public void ExportExcel(DataGridView dgv, string rptName, int inFirstRow, int inFirstCol, string Format, object dtFromDate, object dtToDate, string header)//, string credit, string debit, string closing)
{
try
{
if (CheckWhetherOfficeInstalled())
{
int inColN = 1;
Cursor.Current = Cursors.WaitCursor;
string strName = "", strAddress = "", strPhone = "";
Excel.Range range = null;
Excel.Application excel = new Excel.Application();
Excel.Workbook wb = excel.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet ws = (Excel.Worksheet)excel.ActiveSheet;
CompanySP spCompany = new CompanySP();
DataTable dtblCompany = spCompany.CompanyViewDataTable(1);
//BranchInfo InfoBranch = new BranchInfo();
//BranchSP SpBranch = new BranchSP();
// InfoBranch = SpBranch.BranchView(PublicVariables._branchId);
strAddress = dtblCompany.Rows[0].ItemArray[3].ToString().Replace("\r\n", " ");
strPhone = dtblCompany.Rows[0].ItemArray[4].ToString();
strName = dtblCompany.Rows[0].ItemArray[1].ToString();
//BranchInfo InfoBranch = new BranchInfo();
//BranchSP SpBranch = new BranchSP();
//InfoBranch = SpBranch.BranchView(PublicVariables._branchId);
//strAddress = InfoBranch.Address.Replace("\r\n", " ");
//if (InfoBranch.PhoneNo == "")
//{
// strPhone = InfoBranch.Mobile;
//}
//else
//{
// strPhone = InfoBranch.PhoneNo;
//}
//strName = InfoBranch.BranchName;
//**************Report Header ***************************************
//range = (Excel.Range)ws.Cells[1, 1];
range = ws.get_Range("A1", "I1");
range.MergeCells = true;
range.Font.Size = 15;
range.RowHeight = 27;
range.Interior.Color = ColorTranslator.ToWin32(Color.LightGray);
range.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.Value2 = strName;
range = ws.get_Range("A2", "I2");
range.MergeCells = true;
range.Font.Size = 10;
range.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Value2 = strAddress;
range = ws.get_Range("A3", "I3");
range.MergeCells = true;
range.Font.Size = 10;
range.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Value2 = "Phone No :" + strPhone;
range = ws.get_Range("A5", "G5");
range.MergeCells = true;
range.Font.Size = 11;
range.Value2 = rptName;
range.Font.Underline = true;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range = ws.get_Range("A6", "G6");
range.MergeCells = true;
//range.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Font.Size = 11;
if (dtFromDate != null && dtToDate != null)
{
range.Value2 = "(" + DateTime.Parse(dtFromDate.ToString()).Date.ToString("dd-MMM-yyyy") + " To " + DateTime.Parse(dtToDate.ToString()).Date.ToString("dd-MMM-yyyy") + ")";
}
else if (dtFromDate != null)
{
range.Value2 = DateTime.Parse(dtFromDate.ToString()).Date.ToString("dd-MMM-yyyy");
}
range.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.MergeCells = true;
range.Font.Bold = true;
range = ws.get_Range("H5", "H5");
range.Value2 = "Date :";
range.Font.Bold = true;
range = ws.get_Range("I5", "I5");
range.Value2 = PublicVariables._dtCurrentDate.Date.ToString("dd-MMM-yyyy");
range.Font.Bold = true;
int inNewRow = 0;
inNewRow = inFirstRow;
for (int inRow = inFirstRow; inRow < dgv.Rows.Count; inRow++)
{
if (dgv.Rows[inRow].Visible != false)
{
for (int inCol = inFirstCol; inCol < dgv.Columns.Count; inCol++)
{
if (inRow == 0)
{
if (dgv.Columns[inCol].Visible == true)
{
range = (Excel.Range)ws.Cells[inNewRow + 8, inColN];
range.Font.Bold = true;
range.Interior.Color = ColorTranslator.ToWin32(Color.LightGray);
range.Value2 = dgv.Columns[inCol].HeaderText;
}
}
range = (Excel.Range)ws.Cells[inNewRow + 9, inColN];
if (dgv[inCol, inRow].Style.Font != null)
{
if (dgv[inCol, inRow].Style.Font.Bold)
{
range.Font.Bold = true;
}
}
if (dgv.Rows[inRow].DefaultCellStyle.BackColor == Color.LightSkyBlue)
{
range.Font.Bold = true;
range.Interior.Color = ColorTranslator.ToWin32(Color.LightGray);
}
if (dgv.Rows[inRow].DefaultCellStyle.ForeColor == Color.Red)
{
range.Font.Bold = true;
range.Interior.Color = ColorTranslator.ToWin32(Color.LightGray);
}
//if (dgv.Rows[inRow].Visible != false)
//{
if (dgv.Columns[inCol].Visible == true)
{
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
if (dgv[inCol, inRow].Value != null)
{
string str = dgv[inCol, inRow].Value.ToString();
try
{
if (dgv.Columns[inCol].HeaderText.Replace(" ", "").ToLower() == "phoneno" || dgv.Columns[inCol].HeaderText.Replace(" ", "").ToLower() == "phonenumber")
{
range.NumberFormat = "@";
}
else
{
decimal.Parse(str);
decimal dc = Math.Round(decimal.Parse(str), 2);
str = dc.ToString();
if (dgv.Columns[inCol].Name.ToLower() == "debit" || dgv.Columns[inCol].Name.ToLower() == "credit")
{
range.NumberFormat = "#00.00#";
}
else
{
range.NumberFormat = "General";
}
}
}
catch (Exception)
{
try
{
DateTime.Parse(str);
range.NumberFormat = "dd-MMM-yyyy";
range.NumberFormat = "General";
}
catch (Exception)
{
range.NumberFormat = "@";
}
}
if (str.Contains("Dr") || str.Contains("Cr"))
{
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
}
//try
//{
// if (dgv.Columns[inCol].HeaderText.Replace(" ", "").ToLower() == "phoneno" || dgv.Columns[inCol].HeaderText.Replace(" ", "").ToLower() == "phonenumber")
// range.NumberFormat = "@";
// else
// {
// decimal.Parse(str);
// decimal dc = Math.Round(decimal.Parse(str), 2);
// str = dc.ToString();
// }
//}
//catch (Exception)
//{
// try
// {
// DateTime.Parse(str);
// range.NumberFormat = "dd-MMM-yyyy";
// range.NumberFormat = "General";
// }
// catch (Exception)
// {
// //range.NumberFormat = "@";
// range.NumberFormat = "General";
// }
//}
//------------------------------------
//------------------------------------
range.Value2 = str;// dgv[inCol, inRow].Value;
}
inColN++;
}
//}
}
inColN = 1;
inNewRow++;
}
}
inNewRow = inNewRow + 10;
ws.Columns.AutoFit();
if (Format == "Excel")
{
excel.Visible = true;
}
//else if (Format == "Html")
//{
// //***********Deleting all format*************
// ws.Columns.AutoFit();
// FileInfo infoHtml = new FileInfo(Application.StartupPath + "\\Report.html");
// if (infoHtml.Exists)
// {
// infoHtml.Delete();
// }
// //*******************************************
// ws.SaveAs(Application.StartupPath + "\\Report.html", Excel.XlFileFormat.xlHtml, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
// excel.Quit();
// System.Diagnostics.Process.Start("IExplore.exe", Application.StartupPath + "\\Report.html");
//}
Cursor.Current = Cursors.Default;
}
else
{
MessageBox.Show("Install office", "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void ExportToExcelFun(System.Data.DataTable dt)
{
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
return;
}
System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
saveDia.Filter = "Excel|*.xls";
saveDia.Title = "导出为Excel文件";
if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK &&
!string.Empty.Equals(saveDia.FileName))
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
string fileName = saveDia.FileName;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.DefaultView.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9; //字体大小
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit(); //自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
return;
}
workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
#endregion
MessageBox.Show("导出成功!");
}
}
