public static void DoForExcel(DataGridView dgv, string reportTitle)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
int rowIndex = 2;
int colIndex = 0;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, dgv.ColumnCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = reportTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
foreach (DataGridViewColumn column in dgv.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = column.HeaderText;
}
for (int row = 0; row < dgv.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
for (int col = 0; col < dgv.Columns.Count; col++)
{
xlApp.Cells[rowIndex, col + 1] = dgv.Rows[row].Cells[col].Value.ToString();
}
}
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, dgv.Columns.Count]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
try
{
xlApp.Save(System.DateTime.Now.Millisecond.ToString());
}
catch
{
}
finally
{
xlApp.Quit();
}
}
private void btnExport_Click(object sender, EventArgs e)
{
Excel.ApplicationClass excelSheet = new Excel.ApplicationClass();
excelSheet.Application.Workbooks.Add(true);
int columnIndex = 0;
foreach (DataGridViewColumn column in dgridResult.Columns)
{
if (column.HeaderText.Trim() != "" && column.Visible == true)
{
columnIndex++;
excelSheet.Cells[1, columnIndex] = column.HeaderText;
}
}
int rowIndex = 0;
foreach (DataGridViewRow row in dgridResult.Rows)
{
rowIndex++;
columnIndex = 0;
foreach (DataGridViewColumn column in dgridResult.Columns)
{
if (column.HeaderText.Trim() != "" && column.Visible == true)
{
columnIndex++;
excelSheet.Cells[rowIndex + 1, columnIndex] = row.Cells[column.Name].FormattedValue;
}
}
}
excelSheet.Visible = true;
Excel.Worksheet workSheet = (Excel.Worksheet)excelSheet.ActiveSheet;
Excel.Range titleRange = excelSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, columnIndex]);
titleRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
}
public static void Generate(ExportTable export)
{
var headers = export.Headers;
var data = export.Data;
var excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
for (int j = 0; j < headers.Count; j++)
{
excel.Cells[1, j + 1] = headers[j].Name;
}
excel.Cells.NumberFormat = "@";
for (int rowNum = 0; rowNum < data.Count; rowNum++)
{
for (int colNum = 0; colNum < headers.Count; colNum++)
{
excel.Cells[rowNum + 2, colNum + 1] = data[rowNum][colNum];
}
}
excel.Columns.HorizontalAlignment = true;
var range2 = excel.get_Range(excel.Cells[1, 1],
excel.Cells[data.Count + 1, headers.Count + 1]);
range2 = range2.get_Resize(data.Count + 1, headers.Count + 1);
range2.Columns.AutoFit();
for (int j = 0; j < headers.Count - 1; j++)
{
Excel.Range Range12 = excel.get_Range(excel.Cells[1, j + 1], excel.Cells[1, j + 1]);
var w = (double)Range12.ColumnWidth;
if (w > 40)
{
Range12.ColumnWidth = 40;
}
}
excel.Columns.WrapText = true;
excel.Visible = true;
var worksheet = (Excel.Worksheet)excel.ActiveSheet;
#pragma warning disable 467
worksheet.Activate();
#pragma warning restore 467
}
public void DeleteRange(int startRow, int startColumn, int endRow, int endColumn, bool IsDeleteEntireRow)
{
excel.Range range = App.get_Range(App.Cells[startRow, startColumn], App.Cells[endRow, endColumn]);
range.Select();
if (IsDeleteEntireRow)
{
range.EntireRow.Delete(excel.XlDeleteShiftDirection.xlShiftUp); //是否整行删除
}
else
{
range.Delete(excel.XlDeleteShiftDirection.xlShiftUp);
}
}
/// <summary>
/// 报表导出excel共通函数（适用于DataTable绑定DataGridView）
/// </summary>
/// <param name="Grid">数据源</param>
/// <param name="ReportTitle">报表名</param>
/// <param name="DPTMessage">信息栏信息</param>
public static void ExportDataTableToExcel(System.Data.DataTable dbSource, string[] header,string ReportTitle, string DPTMessage)
{
System.Data.DataTable myTable = dbSource;
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//定义起始行,列
int rowIndex;
int colIndex;
rowIndex = 2;
colIndex = 0;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, myTable.Columns.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
xlApp.ActiveCell.Font.Size = 14;
xlApp.ActiveCell.Font.Bold = true;
//设置禁止弹出保存和覆盖的询问提示框
xlApp.DisplayAlerts = true;
//将表中的栏位名称填到Excel的第一行
//foreach (DataColumn Col in myTable.Columns)
//{
// colIndex = colIndex + 1;
// xlApp.Cells[2, colIndex] = Col.ColumnName;
//}
for (int i = 0; i < header.Length; i++)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = header[i].ToString();
}
//得到的表所有行,赋值给单元格
for (int row = 0; row < myTable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < myTable.Columns.Count; col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = dbSource.Rows[row][col].ToString();
}
}
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Visible = true;
//
//加载一个合计行
//
int rowSum = rowIndex;
Microsoft.Office.Interop.Excel.Range Totle = xlApp.get_Range(xlApp.Cells[rowSum + 1, 1], xlApp.Cells[rowSum + 1, dbSource.Columns.Count]);
Totle.MergeCells = true;
xlApp.Cells[rowSum + 1, 1] = DPTMessage;
xlApp.ActiveCell.Font.Bold = true;
//字体
xlApp.get_Range(xlApp.Cells[rowSum + 1, 1], xlApp.Cells[rowSum + 1, 1]).Font.Size = 10;
//对齐方式
xlApp.get_Range(xlApp.Cells[rowSum + 1, 1], xlApp.Cells[rowSum + 1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
//
//绘制边框
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[rowSum, 2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin;//设置左边线加粗
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[4, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThin;//设置上边线加粗
xlApp.get_Range(xlApp.Cells[3, colIndex], xlApp.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin;//设置右边线加粗
xlApp.get_Range(xlApp.Cells[rowSum, 1], xlApp.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;//设置下边线加粗
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 报表导出excel共通函数（适用于DataTable绑定DataGridView）
/// </summary>
/// <param name="Grid">数据源</param>
/// <param name="ReportTitle">报表名</param>
/// <param name="DPTMessage">信息栏信息</param>
public static void ExportDataTableToExcel(System.Data.DataTable dbSource, string[] header, string ReportTitle, string DPTMessage)
{
System.Data.DataTable myTable = dbSource;
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//定义起始行,列
int rowIndex;
int colIndex;
rowIndex = 2;
colIndex = 0;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, myTable.Columns.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
xlApp.ActiveCell.Font.Size = 14;
xlApp.ActiveCell.Font.Bold = true;
//设置禁止弹出保存和覆盖的询问提示框
xlApp.DisplayAlerts = true;
//将表中的栏位名称填到Excel的第一行
//foreach (DataColumn Col in myTable.Columns)
//{
// colIndex = colIndex + 1;
// xlApp.Cells[2, colIndex] = Col.ColumnName;
//}
for (int i = 0; i < header.Length; i++)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = header[i].ToString();
}
//得到的表所有行,赋值给单元格
for (int row = 0; row < myTable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < myTable.Columns.Count; col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = dbSource.Rows[row][col].ToString();
}
}
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Visible = true;
//
//加载一个合计行
//
int rowSum = rowIndex;
Microsoft.Office.Interop.Excel.Range Totle = xlApp.get_Range(xlApp.Cells[rowSum + 1, 1], xlApp.Cells[rowSum + 1, dbSource.Columns.Count]);
Totle.MergeCells = true;
xlApp.Cells[rowSum + 1, 1] = DPTMessage;
xlApp.ActiveCell.Font.Bold = true;
//字体
xlApp.get_Range(xlApp.Cells[rowSum + 1, 1], xlApp.Cells[rowSum + 1, 1]).Font.Size = 10;
//对齐方式
xlApp.get_Range(xlApp.Cells[rowSum + 1, 1], xlApp.Cells[rowSum + 1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
//
//绘制边框
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[rowSum, 2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin; //设置左边线加粗
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[4, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThin; //设置上边线加粗
xlApp.get_Range(xlApp.Cells[3, colIndex], xlApp.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin; //设置右边线加粗
xlApp.get_Range(xlApp.Cells[rowSum, 1], xlApp.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; //设置下边线加粗
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
}
catch (Exception e)
{
throw e;
}
}
protected void ImgBtnExport_Click(object sender, ImageClickEventArgs e)
{
//HttpContext.Current.Response.Clear();
//HttpContext.Current.Response.AddHeader(
// "content-disposition", string.Format("attachment; filename={0}", "PurchaseOrder"));
//HttpContext.Current.Response.ContentType = "application/ms-excel";
// using (System.IO.StringWriter sw = new System.IO.StringWriter())
// {
// using (HtmlTextWriter htw = new HtmlTextWriter(sw))
// {
//Create a form to contain the grid
//Table table = new Table();
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
try
{
dttable = (DataTable)ViewState["ImportExel"];
dttable1 = (DataTable)ViewState["TermConditn"];
#region [For CompanyDetails-Excel]
#region [Image]
Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "G1");
range.Font.Size = 12;
range.Font.Bold = true;
range.Locked = true;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.RowHeight = 30;
range.Merge(true);
range.Value2 = imgAntTime.ImageUrl;
range.EntireColumn.ColumnWidth = 20;
range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
#endregion
#region [Company Name]
Microsoft.Office.Interop.Excel.Range range1 = ExcelApp.get_Range("A2", "G2");
range1.Font.Size = 12;
range1.Font.Bold = true;
range1.Locked = true;
range1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range1.RowHeight = 65;
range1.Merge(true);
range1.Value2 = lblCompanyName.Text + "\n" + lblCompanyAddress.Text + "\n" + "Phone No :" + lblPhnNo.Text + "\n" + "Fax No :" + lblFaxNo.Text;
range1.EntireColumn.ColumnWidth = 20;
range1.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range1.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
#endregion
#region [ReportName:PurchaseOrderDetails]
Microsoft.Office.Interop.Excel.Range range2 = ExcelApp.get_Range("A3", "G3");
range2.Font.Size = 12;
range2.Font.Bold = true;
range2.Locked = true;
range2.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range2.RowHeight = 15;
range2.Merge(true);
range2.Value2 = "Purchase Order Details";
range2.EntireColumn.ColumnWidth = 20;
range2.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range2.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
#endregion
#region [PODetails]
Microsoft.Office.Interop.Excel.Range range3 = ExcelApp.get_Range("A4", "D4");
range3.Font.Size = 12;
range3.Font.Bold = true;
range3.Locked = true;
range3.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range3.RowHeight = 30;
range3.Merge(true);
range3.Value2 = "To :" + "\n" + lblSuplier.Text;
range3.EntireColumn.ColumnWidth = 20;
#endregion
#region [PODetails2]
Microsoft.Office.Interop.Excel.Range range4 = ExcelApp.get_Range("E4", "G4");
range4.Font.Size = 12;
range4.Font.Bold = true;
range4.Locked = true;
range4.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range4.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range4.RowHeight = 30;
range4.Merge(true);
range4.Value2 = "PO No :" + lblPono.Text + "\n" + "PO Date:" + lblPODate.Text;
range4.EntireColumn.ColumnWidth = 20;
#endregion
#region [For Adding From Grid To Excel]
// Storing header part in Excel
for (int i = 1; i < dttable.Columns.Count + 1; i++)
{
ExcelApp.Cells[5, i] = dttable.Columns[i - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dttable.Rows.Count; i++)
{
for (int j = 0; j < dttable.Columns.Count; j++)
{
ExcelApp.Cells[i + 6, j + 1] = dttable.Rows[i][j].ToString();
}
}
#endregion
#region [ReportName:Terms And Conditions]
Cnt1 = dttable.Rows.Count + 7;
Cnt2 = Convert.ToInt32(dttable.Rows.Count) - 1;
ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1] = "Terms And Condition";
ExcelApp.get_Range(ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1], ExcelApp.Cells[Cnt1 + 1, Cnt2 + 1]).Font.Bold = true;
#endregion
#region [For Adding From Grid To Excel]
if (ViewState["TermConditn"] != null)
{
if (dttable1.Rows.Count > 0)
{
// Storing header part in Excel
for (int i = 1; i < dttable1.Columns.Count + 1; i++)
{
if (i == 2)
{
Microsoft.Office.Interop.Excel.Range Headr = ExcelApp.get_Range(ExcelApp.Cells[dttable.Rows.Count + 7, 2], ExcelApp.Cells[dttable.Rows.Count + 7, 8]);
Headr.RowHeight = 30;
Headr.Merge(true);
Headr.Value2 = dttable1.Columns[i - 1].ColumnName;
}
else
{
ExcelApp.Cells[dttable.Rows.Count + 7, i] = dttable1.Columns[i - 1].ColumnName;
}
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dttable1.Rows.Count; i++)
{
for (int j = 0; j < dttable1.Columns.Count; j++)
{
if (j == 1)
{
Microsoft.Office.Interop.Excel.Range TermsData = ExcelApp.get_Range(ExcelApp.Cells[dttable.Rows.Count + 8, 2], ExcelApp.Cells[dttable.Rows.Count + 8, 7]);
TermsData.RowHeight = 140;
TermsData.Merge(true);
TermsData.Value2 = dttable1.Rows[i][j].ToString();
}
else
{
//Microsoft.Office.Interop.Excel.Range Term = ExcelApp.get_Range(ExcelApp.Cells[i + dttable.Rows.Count + 8, j + 1],ExcelApp.Cells[i + dttable.Rows.Count + 8, j ]);
ExcelApp.Cells[i + dttable.Rows.Count + 8, j + 1] = dttable1.Rows[i][j].ToString();
//Term.RowHeight = 140;
//Term.Value2 = dttable1.Rows[i][j].ToString();
}
}
}
}
}
else
{
ExcelApp.Cells[dttable.Rows.Count + 7, 16] = "No Terms And Conditons";
}
#endregion
//ExcelApp.ActiveWorkbook.SaveCopyAs(@"E:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
// Obj_Comm.Export("Purchase Order" + DateTime.Now.ToString("dd-MMM-yyyy") + ".xls", ExcelApp);
//HttpContext.Current.Response.Write(sw.ToString());
//HttpContext.Current.Response.End();
//ExcelApp.ActiveWorkbook.Saved = true;
//ExcelApp.Quit();
// }
//}
//********
// render the table into the htmlwriter
//sw.Write(ExcelApp);
//htw.Write(ExcelApp);
//HttpContext.Current.Response.Write(sw.ToString());
//HttpContext.Current.Response.End();
ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
#endregion
}
catch (ThreadAbortException tex)
{
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
//}
//}
}
public void dgvtoExcel(DataGridView dataGridView1)
{
SaveFileDialog sfdg = new SaveFileDialog();
sfdg.DefaultExt = "xls";
sfdg.Filter = "Excel(*.xls)|*.xls";
//sfdg.RestoreDirectory = true;
sfdg.FileName = "扫描明细";
//sfdg.CreatePrompt = true;
sfdg.Title = "導出到EXCEL";
int n, w;
n = dataGridView1.RowCount;
w = dataGridView1.ColumnCount;
if (sfdg.ShowDialog() == DialogResult.OK)
{
try
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
excel.Cells[1, j + 1] = dataGridView1.Columns[j].HeaderText;
}
for (int i = 0; i < dataGridView1.RowCount; i++)
{
for (int x = 0; x < dataGridView1.ColumnCount; x++)
{
if (dataGridView1[x, i].Value != null)
{
if (dataGridView1[x, i].ValueType == typeof(string))
{
excel.Cells[i + 2, x + 1] = "'" + dataGridView1[x, i].Value.ToString();
}
else
{
excel.Cells[i + 2, x + 1] = dataGridView1[x, i].Value.ToString();
}
}
}
}
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, w]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
//excel.get_Range(excel.Cells[2, 3], excel.Cells[n, 3]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
excel.get_Range(excel.Cells[1, 1], excel.Cells[n, w]).Borders.LineStyle = 1;
excel.get_Range(excel.Cells[1, 1], excel.Cells[n, w]).Select();
excel.get_Range(excel.Cells[1, 1], excel.Cells[n, w]).Columns.AutoFit();
excel.Visible = false;
excel.ExtendList = false;
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.ActiveWorkbook.SaveAs(sfdg.FileName, Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Quit();
excel = null;
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
GC.Collect();
}
}
}
protected void ImgBtnExport_Click(object sender, ImageClickEventArgs e)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
try
{
dttable = (DataTable)ViewState["ImportExel"];
#region [For CompanyDetails-Excel]
#region [Image]
Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "K1");
range.Font.Size = 12;
range.Font.Bold = true;
range.Locked = true;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.RowHeight = 30;
range.Merge(true);
range.Value2 = imgAntTime.ImageUrl;
range.EntireColumn.ColumnWidth = 20;
range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//range.CopyPicture(
//string path = string.Format("http://*****:*****@"D:\AntLOGO", Microsoft.Office.Core.MsoTriState.msoFalse,
//Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 100, 100);
//ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
catch (ThreadAbortException tex)
{
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
protected void ImgBtnExport_Click1(object sender, ImageClickEventArgs e)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
try
{
dttable = (DataTable)ViewState["ImportExel"];
#region [For RequisitionDetails-Excel]
#region [Image]
Microsoft.Office.Interop.Excel.Range range = ExcelApp.get_Range("A1", "I1");
range.Font.Size = 12;
range.Font.Bold = true;
range.Locked = true;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.RowHeight = 30;
range.Merge(true);
range.Value2 = imgAntTime.ImageUrl;
range.EntireColumn.ColumnWidth = 20;
range.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//range.CopyPicture(
//string path = string.Format("http://*****:*****@"D:\AntLOGO", Microsoft.Office.Core.MsoTriState.msoFalse,
//Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 100, 100);
ExcelApp.ActiveWorkbook.SaveCopyAs(@"D:\" + DateTime.Today.ToString("dd MMM yyyy") + ".xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
#endregion
}
catch (ThreadAbortException tex)
{
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/**/
/// <summary>
/// ��DataGridView�е����ݵ�����Excel�У���������ʾ����(����ģ��)
/// ֻ�����кϲ���ĵ�������������˵�ĺϲ�����ָ����е�Ԫ���ֵΪ��ʱ�Զ�����һ�еĵ�λ����кϲ�
/// </summary>
/// <param name="caption">Ҫ��ʾ��ҳͷ</param>
/// <param name="date">��ӡ����</param>
/// <param name="dgv">Ҫ���е�����DataGridView</param>
/// <param name="MergeCount">�ϲ�������,û���õ�</param>
public void ExportToExcelNullMerge(string caption, string date, DataGridView dgv, int MergeCount)
{
//DataGridView�ɼ�����
int visiblecolumncount = 0;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn))
{
visiblecolumncount++;
}
}
try
{
//��ǰ�����е�����
int currentcolumnindex = 1;
//��ǰ�����е�����
int currentrowindex = 4;
Microsoft.Office.Interop.Excel.ApplicationClass Mylxls = new Microsoft.Office.Interop.Excel.ApplicationClass();
Mylxls.Application.Workbooks.Add(true);
//Mylxls.Cells.Font.Size = 10.5; //����Ĭ�������С
//���ñ�ͷ
Mylxls.Caption = caption;
//��ʾ��ͷ
Mylxls.Cells[1, 1] = caption;
//��ʾʱ��
Mylxls.Cells[2, 1] = date;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) //�����ʾ
{
Mylxls.Cells[3, currentcolumnindex] = dgv.Columns[i].HeaderText;
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //���ñ߿�
//Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //����
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //������ʾ
currentcolumnindex++;
}
}
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //�ϲ���Ԫ��
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30; //�и�
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "����";
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14; //�����С
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //������ʾ
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //�ϲ�
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //�����ʾ
//Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12; //��
for (int i = 0; i < dgv.Rows.Count; i++)
{
currentcolumnindex = 1;
currentrowindex = 4 + i;
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible == true && (dgv.Columns[j] is DataGridViewTextBoxColumn))
{
if (dgv[j, i].Value != null) //�����Ԫ�����ݲ�Ϊ��
{
Mylxls.Cells[currentrowindex, currentcolumnindex] = dgv[j, i].Value.ToString();
}
Mylxls.get_Range(Mylxls.Cells[1, currentcolumnindex], Mylxls.Cells[1, currentcolumnindex]).ColumnWidth = dgv.Columns[j].Width / 8;
Mylxls.get_Range(Mylxls.Cells[currentrowindex, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).Cells.Borders.LineStyle = 1; //���ñ߿�
if (dgv.Rows[i].Cells[j].Value.ToString() == "")
{
Mylxls.get_Range(Mylxls.Cells[currentrowindex - 1, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).MergeCells = true;
//Mylxls.get_Range(Mylxls.Cells[i - 1, col + 2], Mylxls.Cells[rowIndex, col + 2]).MergeCells = true;
//Mylxls.get_Range(Mylxls.Cells[i - 1, col + 3], Mylxls.Cells[rowIndex, col + 3]).MergeCells = true;
}
currentcolumnindex++;
}
}
}
Mylxls.Visible = true;
}
catch
{
MessageBox.Show("��Ϣ����ʧ�ܣ���ȷ����Ļ�����װ��Microsoft Office Excel 2003��", "����", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
}
}
/**/
/// <summary>
/// ��DataGridView�е����ݵ�����Excel�У���������ʾ����(����ģ��)
/// ֻ�����кϲ���ĵ���,���ҿɸ���ǰ���DataGridViewCheckBoxColumn�Ƿ�ѡ����е���
/// </summary>
/// <param name="caption">Ҫ��ʾ��ҳͷ</param>
/// <param name="date">��ӡ����</param>
/// <param name="dgv">Ҫ���е�����DataGridView</param>
/// <param name="MergeCount">�ϲ�������</param>
public void ExportToExcelNullMergeHasCheckBox(string caption, string date, DataGridView dgv, int MergeCount)
{
//DataGridView�ɼ�����
int visiblecolumncount = 0;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn))
{
visiblecolumncount++;
}
}
try
{
//��ǰ�����е�����
int currentcolumnindex = 1;
//��ǰ�����е�����
int currentrowindex = 4;
Microsoft.Office.Interop.Excel.ApplicationClass Mylxls = new Microsoft.Office.Interop.Excel.ApplicationClass();
Mylxls.Application.Workbooks.Add(true);
//Mylxls.Cells.Font.Size = 10.5; //����Ĭ�������С
//���ñ�ͷ
Mylxls.Caption = caption;
//��ʾ��ͷ
Mylxls.Cells[1, 1] = caption;
//��ʾʱ��
Mylxls.Cells[2, 1] = date;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) //�����ʾ
{
Mylxls.Cells[3, currentcolumnindex] = dgv.Columns[i].HeaderText;
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //���ñ߿�
//Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //����
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //������ʾ
currentcolumnindex++;
}
}
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //�ϲ���Ԫ��
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30; //�и�
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "����";
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14; //�����С
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //������ʾ
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //�ϲ�
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //�����ʾ
//Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12; //��
currentrowindex = 3;
for (int i = 0; i < dgv.Rows.Count; i++)
{
DataGridViewCheckBoxCell dgvc = new DataGridViewCheckBoxCell();
dgvc = (DataGridViewCheckBoxCell)dgv[0, i];
if (dgvc.FormattedValue.ToString() == "True")
{
currentcolumnindex = 1;
currentrowindex++;
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible == true && (dgv.Columns[j] is DataGridViewTextBoxColumn))
{
if (dgv[j, i].Value != null) //�����Ԫ�����ݲ�Ϊ��
{
Mylxls.Cells[currentrowindex, currentcolumnindex] = dgv[j, i].Value.ToString();
}
Mylxls.get_Range(Mylxls.Cells[1, currentcolumnindex], Mylxls.Cells[1, currentcolumnindex]).ColumnWidth = dgv.Columns[j].Width / 8;
Mylxls.get_Range(Mylxls.Cells[currentrowindex, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).Cells.Borders.LineStyle = 1; //���ñ߿�
if (dgv.Rows[i].Cells[j].Value.ToString() == "")
{
for (int k = 0; k < MergeCount; k++)
{
Mylxls.get_Range(Mylxls.Cells[currentrowindex - 1, currentcolumnindex + k], Mylxls.Cells[currentrowindex, currentcolumnindex + k]).MergeCells = true;
}
//Mylxls.get_Range(Mylxls.Cells[i - 1, col + 2], Mylxls.Cells[rowIndex, col + 2]).MergeCells = true;
//Mylxls.get_Range(Mylxls.Cells[i - 1, col + 3], Mylxls.Cells[rowIndex, col + 3]).MergeCells = true;
}
currentcolumnindex++;
}
}
}
}
Mylxls.Visible = true;
}
catch
{
MessageBox.Show("��Ϣ����ʧ�ܣ���ȷ����Ļ�����װ��Microsoft Office Excel 2003��", "����", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
}
}
/// <summary>
/// 将DataTable的数据导出到Excel中。
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="xlsFileDir">导出的Excel文件存放目录（绝对路径，最后带“\”）</param>
/// <param name="nameList">DataTable中列名的中文对应表</param>
/// <param name="strTitle">Excel表的标题</param>
/// <returns>Excel文件名</returns>
public void ExportDataToExcel(DataSet ds, string xlsFileDir, Hashtable nameList1, Hashtable nameList2,
Hashtable nameList3, string[] titlelist, string xlsName)
{
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Visible = true;
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(true);
workbook.Sheets.Add(Missing.Value, workbook.Sheets[1], ds.Tables.Count - 1, Missing.Value);
for (int d = 0; d < ds.Tables.Count; d++)
{
Worksheet ws = workbook.Worksheets[d + 1] as Worksheet;
ws.Name = titlelist[d];
Hashtable nameList = new Hashtable();
if (d == 0)
{
nameList = nameList1;
}
if (d == 1)
{
nameList = nameList2;
}
if (d == 2)
{
nameList = nameList3;
}
int titleRowsCount = 0;
if (titlelist[d] != null && titlelist[d].Trim() != "")
{
titleRowsCount = 1;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, ds.Tables[d].Columns.Count]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, ds.Tables[d].Columns.Count]).Font.Size = 16;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, ds.Tables[d].Columns.Count]).MergeCells = true;
ws.Cells[1, 1] = titlelist[d];
}
if (!System.IO.Directory.Exists(xlsFileDir))
{
System.IO.Directory.CreateDirectory(xlsFileDir);
}
string strFileName = xlsName + ".xls";
string tempColumnName = "";
for (int i = 0; i < ds.Tables[d].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[d].Columns.Count; j++)
{
if (i == 0)
{
tempColumnName = ds.Tables[d].Columns[j].ColumnName.Trim();
if (nameList != null)
{
IDictionaryEnumerator Enum = nameList.GetEnumerator();
while (Enum.MoveNext())
{
if (Enum.Key.ToString().Trim() == tempColumnName)
{
tempColumnName = Enum.Value.ToString();
}
}
}
ws.Cells[titleRowsCount + 1, j + 1] = tempColumnName;
}
ws.Cells[i + titleRowsCount + 2, j + 1] = ds.Tables[d].Rows[i][j].ToString();
}
}
excel.get_Range(excel.Cells[titleRowsCount + 1, 1], excel.Cells[titleRowsCount + 1, ds.Tables[d].Columns.Count]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + ds.Tables[d].Rows.Count, ds.Tables[d].Columns.Count]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + ds.Tables[d].Rows.Count, ds.Tables[d].Columns.Count]).EntireColumn.AutoFit();
}
//Microsoft.Office.Interop.Excel.Workbooks workBooks = excel.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];
//int titleRowsCount = 0;
//if (strTitle != null && strTitle.Trim() != "")
//{
// titleRowsCount = 1;
// excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true;
// excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Size = 16;
// excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).MergeCells = true;
// workSheet.Cells[1, 1] = strTitle;
//}
//if (!System.IO.Directory.Exists(xlsFileDir))
//{
// System.IO.Directory.CreateDirectory(xlsFileDir);
//}
//string strFileName = xlsName + ".xls";
//string tempColumnName = "";
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// if (i == 0)
// {
// tempColumnName = dt.Columns[j].ColumnName.Trim();
// if (nameList != null)
// {
// IDictionaryEnumerator Enum = nameList.GetEnumerator();
// while (Enum.MoveNext())
// {
// if (Enum.Key.ToString().Trim() == tempColumnName)
// {
// tempColumnName = Enum.Value.ToString();
// }
// }
// }
// workSheet.Cells[titleRowsCount + 1, j + 1] = tempColumnName;
// }
// workSheet.Cells[i + titleRowsCount + 2, j + 1] = dt.Rows[i][j].ToString();
// }
//}
//excel.get_Range(excel.Cells[titleRowsCount + 1, 1], excel.Cells[titleRowsCount + 1, dt.Columns.Count]).Font.Bold = true;
//excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + dt.Rows.Count, dt.Columns.Count]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
//excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + dt.Rows.Count, dt.Columns.Count]).EntireColumn.AutoFit();
workbook.Saved = true;
workbook.SaveCopyAs(xlsFileDir + xlsName + ".xls");
for (int d = 0; d < ds.Tables.Count; d++)
{
Worksheet ws = workbook.Worksheets[d + 1] as Worksheet;
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
//((Worksheet)workbook.Sheets[1]).Select(true);//选中第一个worksheet
//workbook.Close(true, Missing.Value, Missing.Value);//关闭workbook, 并保存对workbook的所有修改
//System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
//workbook = null;
//excel.Quit();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
//excel = null;
//GC.Collect();
//GC.WaitForPendingFinalizers();
FileInfo fi = new FileInfo(xlsFileDir + xlsName + ".xls");
Response.Clear();
Response.Buffer = true; //设置<span style="color: rgb(51, 51, 51); line-height: 24px; white-space: pre-wrap;">输出页面是否被缓冲</span>
Response.Charset = "GB2312"; //设置了类型为中文防止乱码的出现
Response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", HttpUtility.UrlEncode(xlsName + ".xls"))); //定义输出文件和文件名
Response.AppendHeader("Content-Length", fi.Length.ToString());
Response.ContentEncoding = Encoding.Default;
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
Response.WriteFile(fi.FullName);
Response.Flush();
Response.End();
//var stream = System.IO.File.OpenRead(xlsFileDir+strFileName);//excel表转换成流
//return File(stream, "application/vnd.android.package-archive", Path.GetFileName(xlsFileDir+strFileName));//进行浏览器下载
//直接获取字节数组
}
//REPORTE EN EXCEL
public void exporta_a_excel()
{
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
int ColumnIndex = 0;
//excel.Cells[1, 1] = "INSTRUCTOR: "+CbxInstructores.Text;
excel.get_Range("A4", "G4").Font.Bold = true; //Letra negrita
excel.get_Range("A4", "A17").Font.Bold = true; //Letra negrita
excel.get_Range("A4", "G4").Interior.ColorIndex = 10; //Color de Fondo, 9 es rojo oscuro,
excel.get_Range("A4", "A17").Interior.ColorIndex = 10; //Color de Fondo, 9 es rojo oscuro,
//excel.get_Range("A1", "G1").Font.ColorIndex = 2; //Color de letra, 2 es blanco, entre 0-56
//excel.get_Range("A1", "A14").Font.ColorIndex = 2; //Color de letra, 2 es blanco, entre 0-56
excel.get_Range("A4", "G4").ColumnWidth = 25; //Ancho de la columna
excel.get_Range("A4", "G4").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A5", "G5").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A6", "G6").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A7", "G7").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A8", "G8").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A9", "G9").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A10", "G10").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A11", "G11").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A12", "G12").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A13", "G13").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A14", "G14").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A15", "G15").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A16", "G16").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A17", "G17").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A18", "G18").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("A4", "A19").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("B4", "B19").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("C4", "C19").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("D4", "D19").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("E4", "E19").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("F4", "F19").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
excel.get_Range("G4", "G19").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); //Borde
foreach (DataGridViewColumn col in dataGridView1.Columns)
{
ColumnIndex++;
excel.Cells[4, ColumnIndex] = col.Name;
}
int rowIndex = 3;
foreach (DataGridViewRow row in dataGridView1.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataGridViewColumn col in dataGridView1.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value;
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
//worksheet.Activate();
}
