/// <summary>
/// EXCEL画线
/// </summary>
/// <param name="excel"></param>
public static void DrawExcelBorders(Excel.Application excel, object cell1, object cell2)
{
Excel.Range range = excel.get_Range(cell1, cell2);
//range.Select();
Excel.Borders borders = range.Borders;
borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
if (sheet == null)
return;
range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
private String GetSheetValue(Excel.Worksheet worksheet, string field)
{
Excel.Range range = worksheet.get_Range(field, field);
if (range == null) return "";
if (range.Cells.Value2 == null) return "";
return range.Cells.Value2.ToString();
}
/// <summary>
/// 将 DataTable 导出到 Excel
/// </summary>
/// <param name="fApplication"></param>
/// <param name="fWorkBook"></param>
/// <param name="fOption"></param>
private static void ExportToExcel(Excel.Application fApplication, Excel._Workbook fWorkBook, Excel.Worksheet worksheet, DataTableExportOptions fOption)
{
Excel.Range range;
worksheet.Name = fOption.WorkSheetName;
if (fOption.DataTable == null) return;
int rowCount = fOption.DataTable.Rows.Count;
int colCount = fOption.VisibleColumnOptions.Count;
int colIndex = 0;
int rowIndex = 0;
#region " Set Header Values "
object[,] colValues = new object[1, colCount];
foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
{
if (!option.Visible) continue;
colValues[0, colIndex] = option.Caption;
colIndex++;
}
range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, 1));
range.Value2 = colValues;
#endregion
#region " Header Style "
range.Font.Bold = true;
//range.Font.Name = "Georgia";
range.Font.Name = "宋体";
range.Font.Size = 10;
range.RowHeight = 26;
range.EntireColumn.AutoFit();
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//range.Locked = true;
#endregion
#region " Set Row Values "
object[,] rowValues = new object[rowCount, colCount];
rowIndex = 0;
foreach (DataRow dataRow in fOption.DataTable.Rows)
{
colIndex = 0;
foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
{
//rowValues[rowIndex, colIndex] = dataRow[option.ColumnName]; //防止自动将
rowValues[rowIndex, colIndex] ="'"+dataRow[option.ColumnName];
colIndex++;
}
rowIndex++;
}
range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1));
// worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)).Merge(
range.Value2 = rowValues;
#region 测试合并自定的单元格 20120612 CS
//int tempRowCountStart = 1;
//int tempRowCountEnd = 1;
////for (int i = 2; i <=rowCount+1; i++)
//Excel.Range tempRange;
//// int tempi = 0;
//string startValue = ""; ;
//fApplication.DisplayAlerts = false; //取消合并CELL的时候弹出提示框
//foreach (DataRow dr in fOption.DataTable.Rows)
//{
// //tempi = tempi + 1;
// if (dr[0].ToString() == startValue)
// {
// tempRowCountEnd = tempRowCountEnd + 1;
// startValue = dr[0].ToString();
// }
// else
// {
// if (tempRowCountEnd - tempRowCountStart > 0)
// {
// //合并本次付款金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]);
// tempRange.Merge(tempRange.MergeCells);
// //订单金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]);
// tempRange.Merge(tempRange.MergeCells);
// //发票总金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]);
// tempRange.Merge(tempRange.MergeCells);
// //财务最后付款日期
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]);
// tempRange.Merge(tempRange.MergeCells);
// //财务付款总额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]);
// tempRange.Merge(tempRange.MergeCells);
// }
// tempRowCountStart = tempRowCountEnd + 1;
// tempRowCountEnd = tempRowCountStart;
// startValue = dr[0].ToString();
// }
//}
//#region 解决最后全相同的时候，不合并的情况
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]);
//tempRange.Merge(tempRange.MergeCells);
////订单金额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]);
//tempRange.Merge(tempRange.MergeCells);
////发票总金额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]);
//tempRange.Merge(tempRange.MergeCells);
////财务最后付款日期
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]);
//tempRange.Merge(tempRange.MergeCells);
////财务付款总额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]);
//tempRange.Merge(tempRange.MergeCells);
//fApplication.DisplayAlerts = true; //取消警告弹出提示框
//#endregion
#endregion
#region " Row Style "
range.Font.Name = "宋体";
range.Font.Size = 9;
// range.RowHeight = 18;
range.EntireColumn.AutoFit();
//range.Borders.ColorIndex = 2;
#endregion
#endregion
#region " Set Borders "
range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1));
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.ColumnWidth = 12;
range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, rowCount + 1));
range.Borders.Weight = Excel.XlBorderWeight.xlThin;
range.Borders.Color = Color.Black.ToArgb();
#endregion
}
/// <summary>
/// 取得Excel单元格的值
/// </summary>
/// <param name="xApp">Excel应用程序</param>
/// <param name="xSheet">工作表</param>
/// <param name="row">行</param>
/// <param name="col">列</param>
/// <returns>返回一个字符串</returns>
public static string GetCellData(Excel.Application xApp, Excel.Worksheet xSheet, int row, int col, bool txtFlag)
{
if(!txtFlag)
{
object obj = xApp.get_Range(xSheet.Cells[row, col], xSheet.Cells[row, col]).Value2;
if (obj != null)
return xApp.get_Range(xSheet.Cells[row, col], xSheet.Cells[row, col]).Value2.ToString();
else
return "";
}
else
{
object obj = xApp.get_Range(xSheet.Cells[row, col], xSheet.Cells[row, col]).Text;
if (obj != null)
return xApp.get_Range(xSheet.Cells[row, col], xSheet.Cells[row, col]).Text.ToString();
else
return "";
}
}
private void StringToExcelCell(Excel.Worksheet WorksheetObj, string RowCell, string Content, int LCR)
{
Excel.Range ExcelRange = WorksheetObj.get_Range(RowCell, Missing.Value);
ExcelRange.Value2 = Content;
ExcelRange.ColumnWidth = 15;
if (LCR == 1)
ExcelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
else
{
if (LCR == 2)
ExcelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
else
ExcelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
}
private void ExportSingle(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
using (DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty, string.Empty, string.Empty, string.Empty, startDate, endDate, 100).Tables[0])
{
if (dt != null && dt.Rows.Count > 0)
{
DataView dv = dt.DefaultView;
using (DataTable dtBaseInfo = new TeacherBaseDataAccess().GetTcBaseInfo(string.Empty, string.Empty, string.Empty, string.Empty).Tables[0])
{
object[, ] objData = null;
foreach(DataRow dr in dtBaseInfo.Rows)
{
dv.RowFilter = "T_Number = " + dr[1].ToString();
objData = new object[dv.Count, 6];
if (dv.Count > 0)
{
for (int i = 0; i < dv.Count; i++)
{
objData[i,0] = dv[i][3].ToString();
objData[i,1] = dv[i][2].ToString();
objData[i,2] = dv[i][0].ToString();
objData[i,3] = dv[i][1].ToString();
objData[i,4] = dv[i][4].ToString();
objData[i,5] = dv[i][5].ToString();
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
m_objSheet.Copy(Type.Missing, m_objSheet);
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(5));
m_objSheet.Name = objData[0, 0].ToString() + string.Format("({0})", objData[0, 1]);
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(dv.Count, 6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+4).ToString(),m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+4).ToString(),m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+5).ToString(),m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+5).ToString(),m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+6).ToString(),m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+6).ToString(),m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
m_objSheet.Delete();
}
}
}
/// <summary>
/// 调用EXCEL打印
/// 例：ClassCustom.PrintF(ClassCustom.ExportDataGridview1(this.dataGridView1), Excel.XlPaperSize.xlPaperA4, Excel.XlPageOrientation.xlLandscape);
/// </summary>
/// <param name="excel">EXCEL对象</param>
/// <param name="papersize">纸张 A4 A3....</param>
/// <param name="orientation">纸张方向</param>
public static void PrintE(Excel.Application excel, DataGridView dgv, Excel.XlPaperSize papersize, Excel.XlPageOrientation orientation)
{
Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet;
sheet1.PageSetup.PaperSize = papersize;
sheet1.PageSetup.Orientation = orientation;
sheet1.Name = "打印预览";
excel.get_Range("A1", excel.Cells[dgv.Rows.Count + 1, dgv.Columns.Count + 1]).EntireColumn.AutoFit();
DrawExcelBorders(excel, "A1", excel.Cells[dgv.Rows.Count + 3, dgv.Columns.Count - 1]);
sheet1.PageSetup.PrintTitleRows = "$1:$1";
(excel.Workbooks[1].Worksheets[1] as Excel.Worksheet).PrintPreview(true);
excel.Workbooks[1].Close(false, null, null);
excel.Quit();
excel = null;
}
private void ExportSingleStat(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
object[,] objData;
using(DataTable dt = new DutyInfoDA().GetTeacherStatSingle(startDate, endDate))
{
if (dt != null && dt.Rows.Count > 0 )
{
objData = new Object[dt.Rows.Count + 1,6];
int days = SetAttendDays(startDate, endDate);
for( int i=0; i<dt.Rows.Count; i++ )
{
double attendCount = Convert.ToInt32(dt.Rows[i][2]);
double shouldAttendCount = days;
double absenceCount = shouldAttendCount - attendCount;
objData[i, 0] = dt.Rows[i][0].ToString();
objData[i, 1] = dt.Rows[i][1].ToString();
objData[i, 2] = shouldAttendCount;
objData[i, 3] = attendCount;
objData[i, 4] = absenceCount < 0 ? 0 : absenceCount ;
objData[i, 5] = shouldAttendCount == 0 ? attendCount.ToString("0.00%") : (attendCount / shouldAttendCount).ToString("0.00%");
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(3));
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(dt.Rows.Count,6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+4).ToString(),m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+4).ToString(),m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+5).ToString(),m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+5).ToString(),m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
private void ExportAllNormal(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
object[,] objData;
using(DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty,string.Empty,string.Empty,string.Empty,startDate,endDate,100).Tables[0])
{
if ( dt != null && dt.Rows.Count > 0 )
{
objData = new Object[dt.Rows.Count,6];
for( int i=0; i<dt.Rows.Count; i++ )
{
objData[i,0] = dt.Rows[i][3].ToString();
objData[i,1] = dt.Rows[i][2].ToString();
objData[i,2] = dt.Rows[i][0].ToString();
objData[i,3] = dt.Rows[i][1].ToString();
objData[i,4] = dt.Rows[i][4].ToString();
objData[i,5] = dt.Rows[i][5].ToString();
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(dt.Rows.Count,6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+4).ToString(),m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+4).ToString(),m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+5).ToString(),m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+5).ToString(),m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
/// <summary>
/// Export data table to Excel
/// </summary>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public void ExportToExcel(DataView view, string[] headers, int[] indexes, int startRow, int startCol, Excel.Worksheet sheet)
{
Excel.Range range = null;
object obj = null;
int i = 0;
int j = 0;
DataColumnCollection cols = view.Table.Columns;
//DataRowCollection rows = dt.Rows;
DataView rows = view;
int rowCout = rows.Count;
int colCount = indexes.Length;
for(i = 0; i < rowCout; i ++)
{
for(j = 0; j < colCount; j ++)
{
obj = rows[i][indexes[j]];
sheet.get_Range(COL_NAME[j + startCol] + (i + startRow + EXCEL_COL_SPACE), missing).NumberFormat = "@";
sheet.get_Range(COL_NAME[j + startCol] + (i + startRow + EXCEL_COL_SPACE), missing).Value2 = obj.ToString();
}
}
//Export header
colCount = headers.Length;
for(i = 0; i < colCount; i ++)
{
range = sheet.get_Range(COL_NAME[i + startCol] + (startRow + 1), missing);
range.Font.Bold = true;
range.Value2 = headers[i];
range.EntireColumn.AutoFit();
}
}
private void SetCells(Excel.Worksheet sheet, List<ArrayList> dataList)
{
object misValue = System.Reflection.Missing.Value;
int i = _index_row_template;
Excel.Range rangTemplate = null;
foreach (ArrayList row in dataList.ToList())
{
int j = -1;
foreach (string letter in _colum_letters)
{
string strCell = letter + _index_row_template;
rangTemplate = sheet.Cells.get_Range(strCell);
Excel.Range range = sheet.get_Range(letter + i);
range.BorderAround();
range.Interior.Color = rangTemplate.Interior.Color;
range.Font.Color = rangTemplate.Font.Color;
range.Font.Size = rangTemplate.Font.Size;
range.Font.FontStyle = rangTemplate.Font.FontStyle;
range.Font.Name = rangTemplate.Font.Name;
j++;
string value = row[j] == null ? "" : row[j].ToString();
range.set_Value(misValue, value);
}
i++;
}
}
/// <summary>
/// Copy the row to a new worksheet
/// </summary>
/// <param name="sheet">The sheet to copy to</param>
/// <param name="row">The row to copy</param>
/// <param name="count">The times to copy</param>
/// <param name="index">The index of row to copy to</param>
protected void CopyFormat(Excel.Worksheet sheet, Excel.Range row, int count, int index)
{
int rowindex = index;
for (int i = 0; i < count; i++)
{
row.Copy(sheet.get_Range(sheet.Cells[rowindex, 1], sheet.Cells[rowindex, 1]));
rowindex++;
}
}