威尼斯人线上娱乐

格式设置

15 4月 , 2019  

一、增加引用

累加引用:Microsoft   Excel   1一.0   Object   Library ;

///
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Reflection;

Microsoft.Office.Interop.Excel;

添加:using Microsoft.Office.Interop.Excel;

丰硕引用:Microsoft  
Excel   1壹.0   Object   Library ;

namespace DMS
{
/// <summary>
/// C#操作Excel类
/// </summary>
class ExcelOperate
{
//法一
//public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;
//    int columnNumber = dataTable.Columns.Count;

二、使用命名空间

一、打开Excel文件============================

添加:using
Microsoft.Office.Interop.Excel;

//    if (rowNumber == 0)
//    {
//        MessageBox.Show(“没有其他数据可以导入到Excel文件!”);
//        return false;
//    }

 using Microsoft.Office.Interop.Excel;

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

一、打开Excel文件============================

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//    excel.Application.Workbooks.Add(true);
//    excel.Visible = isShowExcle;//是还是不是展开该Excel文件

三、填充EXCEL单元格方法

    Workbook workbook1 = excel1.Workbooks.Open(@”E:/aaa.xls”,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);

   
Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

//    //填充数据
//    for (int c = 0; c < rowNumber; c++)
//    {
//        for (int j = 0; j < columnNumber; j++)
//        {
//            excel.Cells[c + 1, j + 1]格式设置。 =
dataTable.Rows[c].ItemArray[j];
//        }
//    }

 private void OpenExcel()
        {
            Microsoft.Office.Interop.Excel.Application excel = new
ApplicationClass();
            excel.Visible = true;
            excel.DisplayAlerts = false;
            excel.Workbooks.Open(@”D:\EXCEL\IQC查验报表.xlsx”);
            ((_Worksheet) excel.Worksheets[1]).Activate();         
            excel.get_Range(“F七”,Type.Missing).Value =
“测试Excel写入数据”;
            excel.get_Range(“F7”, Type.Missing).Font.Color =
Color.Blue;
            excel.ActiveWorkbook.Save();
            excel.Application.Workbooks.Close();
            excel.Application.Quit();
            excel.Quit();
            GC.Collect();
        }

    excel1.Visible = true;

   
Workbook workbook1 = excel1.Workbooks.Open(@”E:\aaa.xls”, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);

//    return true;
//}

 

二、新建Excel对象============================

   
excel1.Visible = true;

//法二
//public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

二、新建Excel对象============================

//    int rowIndex = 1;
//    int colIndex = 0;

    Workbook workbook1 =
excel1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet或true);

   
Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

//    if (rowNumber == 0)
//    {
//        return false;
//    }

    worksheet1.Activate();//激活sheet1表

   
Workbook workbook1 =
excel1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet或true);

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//    excel.Application.Workbooks.Add(true);
//    excel.Visible = isShowExcle;

    excel1.Visible = true;

   
worksheet1.Activate();//激活sheet1表

//    //生成字段名称
//    foreach (DataColumn col in dataTable.Columns)
//    {
//        colIndex++;
//        excel.Cells[1, colIndex] = col.ColumnName;
//    }

三、新建Excel表============================

   
excel1.Visible = true;

//    //填充数据
//    foreach (DataRow row in dataTable.Rows)
//    {
//        rowIndex++;
//        colIndex = 0;
//        foreach (DataColumn col in dataTable.Columns)
//        {
//            colIndex++;
//            excel.Cells[rowIndex, colIndex] =
row[col.ColumnName];
//        }
//    }

  Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

三、新建Excel表============================

//    return true;
//}

    Workbook workbook1 = excel1.Workbooks.Add(true);

  Microsoft.Office.Interop.Excel.Application
excel1 = new Microsoft.Office.Interop.Excel.Application();

//法三(速度最快)
/// <summary>
/// 将数据集中的多寡导出到EXCEL文件
/// </summary>
/// <param name=”dataSet”>输入数据集</param>
/// <param name=”isShowExcle”>是还是不是出示该EXCEL文件</param>
/// <returns></returns>
public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
{
DataTable dataTable = dataSet.Tables[0];
int rowNumber = dataTable.Rows.Count;//不包涵字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

   
Workbook workbook1 = excel1.Workbooks.Add(true);

if (rowNumber == 0)
{
return false;
}

    Worksheet worksheet1
=(Worksheet)workbook1.Worksheets.Add(Type.Missing,workbook1.Worksheets[1],
1, Type.Missing);

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1”];

//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook =
excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = isShowExcle;
//Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;

    excel1.Visible = true;

   
Worksheet worksheet1
=(Worksheet)workbook1.Worksheets.Add(Type.Missing,workbook1.Worksheets[1],
1, Type.Missing);

//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}

四、重命名Excel表名============================

   
excel1.Visible = true;

object[,] objData = new object[rowNumber, columnNumber];

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

四、重命名Excel表名============================

for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
//Application.DoEvents();
}

    Workbook workbook1 = excel1.Workbooks.Add(true);

   
Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

// 写入Excel
range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber

    Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1″或1];

   
Workbook workbook1 = excel1.Workbooks.Add(true);

  • 1, columnNumber]);
    //range.NumberFormat = “@”;//设置单元格为文本格式
    range.Value2 = objData;
    worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1,
    1]).NumberFormat = “yyyy-m-d h:mm”;

    worksheet一.Name = “工作布置表”;

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1″或1];

威尼斯人线上娱乐,return true;
}

    excel1.Visible = true;

   
worksheet一.Name = “工作布置表”;

//法四
//public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;
//    int columnNumber = dataTable.Columns.Count;
//    String stringBuffer = “”;

5、设置或修改Excel表单元格内容========================

   
excel1.Visible = true;

//    if (rowNumber == 0)
//    {
//        Message博克斯.Show(“未有其余数据能够导入到Excel文件!”);
//        return false;
//    }

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

伍、设置或修改Excel表单元格内容========================

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//    excel.Application.Workbooks.Add(true);
//    excel.Visible = isShowExcle;//是不是张开该Excel文件

    Workbook workbook1 = excel1.Workbooks.Add(true);

   
Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

//    //填充数据
//    for (int i = 0; i < rowNumber; i++)
//    {
//        for (int j = 0; j < columnNumber; j++)
//        {
//            stringBuffer +=
dataTable.Rows[i].ItemArray[j].ToString();
//            if (j < columnNumber – 1)
//            {
//                stringBuffer += “\t”;
//            }
//        }
//        stringBuffer += “\n”;
//    }
//    Clipboard.Clear();
//    Clipboard.SetDataObject(stringBuffer);
//    ((Microsoft.Office.Interop.Excel.Range)excel.Cells[1,
1]).Select();
//   
((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.ActiveSheet).Paste(Missing.Value,
Missing.Value);
//    Clipboard.Clear();

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

   
Workbook workbook1 = excel1.Workbooks.Add(true);

//    return true;
//}

    worksheet1.Cells[1, 1] = “姓名”;

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1”];

//public bool DataSetToExcel(DataSet dataSet, string fileName, bool
isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;
//    int columnNumber = dataTable.Columns.Count;

    worksheet1.Cells[1, 2] = “性别”;

   
worksheet1.Cells[1, 1] = “姓名”;

//    if (rowNumber == 0)
//    {
//        MessageBox.Show(“未有别的数据足以导入到Excel文件!”);
//        return false;
//    }

    excel1.Visible = true;

   
worksheet1.Cells[1, 2] = “性别”;

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//    Microsoft.Office.Interop.Excel.Workbook workBook =
excel.Application.Workbooks.Add(true);
//    excel.Visible = false;//是还是不是展开该Excel文件

6、设置Excel表行宽和列高===========================

   
excel1.Visible = true;

//    //填充数据
//    for (int i = 0; i < rowNumber; i++)
//    {
//        for (int j = 0; j < columnNumber; j++)
//        {
//            excel.Cells[i + 1, j + 1] =
dataTable.Rows[i].ItemArray[j];
//        }
//    }

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

6、设置Excel表行宽和列高===========================

//    //string fileName = path + “\\” +
DateTime.Now.ToString().Replace(‘:’, ‘_’) + “.xls”;
//    workBook.SaveAs(fileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);

    Workbook workbook1 = excel1.Workbooks.Add(true);

   
Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

//    try
//    {
//        workBook.Saved = true;
//        excel.UserControl = false;
//        //excelapp.Quit();
//    }
//    catch (Exception exception)
//    {
//        MessageBox.Show(exception.Message);
//    }
//    finally
//    {
//       
workBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges,
Missing.Value, Missing.Value);
//        excel.Quit();
//    }

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

   
Workbook workbook1 = excel1.Workbooks.Add(true);

//    if (isShowExcle)
//    {
//        System.Diagnostics.Process.Start(fileName);
//    }
//    return true;
//}

    worksheet一.Columns.ColumnWidth = 20;//全局行宽

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1”];

//public bool DataSetToExcel(DataSet dataSet, string fileName, bool
isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;//不包涵字段名
//    int columnNumber = dataTable.Columns.Count;
//    int colIndex = 0;

    worksheet壹.Columns.RowHeight = 20;//全局列高

   
worksheet1.Columns.ColumnWidth = 20;//全局行宽

//    if (rowNumber == 0)
//    {
//        MessageBox.Show(“未有其它数据能够导入到Excel文件!”);
//        return false;
//    }

    Range range1 = (Range) worksheet1.Cells[2, 1];

   
worksheet一.Columns.RowHeight = 20;//全局列高

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//    //excel.Application.Workbooks.Add(true);
//    Microsoft.Office.Interop.Excel.Workbook workbook =
excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//    Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//    excel.Visible = isShowExcle;
//    //Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
//    worksheet.Name = “挠度数据”;
//    Microsoft.Office.Interop.Excel.Range range;

    range一.Columns.ColumnWidth = 40;//单元格行宽

   
Range range1 = (Range) worksheet1.Cells[2, 1];

//    //生成字段名称
//    foreach (DataColumn col in dataTable.Columns)
//    {
//        colIndex++;
//        excel.Cells[1, colIndex] = col.ColumnName;
//    }

    range壹.Columns.RowHeight = 40;//单元格列高

   
range壹.Columns.ColumnWidth = 40;//单元格行宽

//    object[,] objData = new object[rowNumber, columnNumber];

    excel1.Visible = true;

   
range壹.Columns.RowHeight = 40;//单元格列高

//    for (int r = 0; r < rowNumber; r++)
//    {
//        for (int c = 0; c < columnNumber; c++)
//        {
//            objData[r, c] = dataTable.Rows[r][c];
//        }
//        //Application.DoEvents();
//    }

7、设置Excel表单元格边框===========================

   
excel1.Visible = true;

//    // 写入Excel
//    range = worksheet.get_Range(excel.Cells[2, 1],
excel.Cells[rowNumber + 1, columnNumber]);
//    //range.NumberFormat = “@”;//设置单元格为文本格式
//    range.Value2 = objData;
//    worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber +
1, 1]).NumberFormat = “yyyy-m-d h:mm”;

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

柒、设置Excel表单元格边框===========================

//    //string fileName = path + “\\” +
DateTime.Now.ToString().Replace(‘:’, ‘_’) + “.xls”;
//    workbook.SaveAs(fileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);

    Workbook workbook1 = excel1.Workbooks.Add(true);

   
Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

//    try
//    {
//        workbook.Saved = true;
//        excel.UserControl = false;
//        //excelapp.Quit();
//    }
//    catch (Exception exception)
//    {
//        MessageBox.Show(exception.Message);
//    }
//    finally
//    {
//       
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges,
Missing.Value, Missing.Value);
//        excel.Quit();
//    }

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

   
Workbook workbook1 = excel1.Workbooks.Add(true);

//    //if (isShowExcle)
//    //{
//    //    System.Diagnostics.Process.Start(fileName);
//    //}
//    return true;
//}

    Range range1 = (Range)worksheet1.Cells[2, 2];

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1”];

/// <summary>
/// 将数据汇总的数目保存到EXCEL文件
/// </summary>
/// <param name=”dataSet”>输入数据集</param>
/// <param
name=”fileName”>保存EXCEL文件的相对路径名</param>
/// <param name=”isShowExcle”>是还是不是展开EXCEL文件</param>
/// <returns></returns>
public bool DataSetToExcel(DataSet dataSet, string fileName, bool
isShowExcle)
{
DataTable dataTable = dataSet.Tables[0];
int rowNumber = dataTable.Rows.Count;//不蕴涵字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;

    range1.Borders.Color =
System.Drawing.ColorTranslator.ToOle(Color.Red);

   
Range range1 = (Range)worksheet1.Cells[2, 2];

if (rowNumber == 0)
{
MessageBox.Show(“未有别的数据能够导入到Excel文件!”);
return false;
}

    range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Borders.Color =
System.Drawing.ColorTranslator.ToOle(Color.Red);

//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new
Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook =
excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = false;
//Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle =
XlLineStyle.xlContinuous;

//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}

    range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle =
XlLineStyle.xlContinuous;

object[,] objData = new object[rowNumber, columnNumber];

    range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle =
XlLineStyle.xlContinuous;

for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
//Application.DoEvents();
}

   
//也可用后边的代码替代上面肆项range一.BorderAround(XlLineStyle.xlContinuous,
XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);

   
range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle =
XlLineStyle.xlContinuous;

// 写入Excel
range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber

    range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle =
XlLineStyle.xlContinuous;//斜杠

   
//也可用前面包车型客车代码代替上边四项range一.BorderAround(XlLineStyle.xlContinuous,
XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);

  • 1, columnNumber]);
    //range.NumberFormat = “@”;//设置单元格为文本格式
    range.Value2 = objData;
    worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1,
    1]).NumberFormat = “yyyy-m-d h:mm”;

    range1.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle =
XlLineStyle.xlContinuous;//反斜杠

   
range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle =
XlLineStyle.xlContinuous;//斜杠

//string fileName = path + “\\” + DateTime.Now.ToString().Replace(‘:’,
‘_’) + “.xls”;
workbook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);

    range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).Color =
System.Drawing.ColorTranslator.ToOle(Color.Gold);

   
range1.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle =
XlLineStyle.xlContinuous;//反斜杠

try
{
workbook.Saved = true;
excel.UserControl = false;
//excelapp.Quit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges,
Missing.Value, Missing.Value);
excel.Quit();
}

    excel1.Visible = true;

   
range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).Color =
System.Drawing.ColorTranslator.ToOle(Color.Gold);

if (isShowExcle)
{
System.Diagnostics.Process.Start(fileName);
}
return true;
}
}
}

八、Excel表块操作============================

   
excel1.Visible = true;

  Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

八、Excel表块操作============================

    Workbook workbook1 = excel1.Workbooks.Add(true);

  Microsoft.Office.Interop.Excel.Application
excel1 = new Microsoft.Office.Interop.Excel.Application();

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

   
Workbook workbook1 = excel1.Workbooks.Add(true);

    Range range1 = worksheet1.get_Range(“A二”, “E8”);//选拔操作块

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1”];

    range1.Font.Bold = true;//设置金鼎文

   
Range range1 = worksheet1.get_Range(“A二”, “E捌”);//选拔操作块

    range壹.Font.Size = 1八;//设置字体大小

   
range1.Font.Bold = true;//设置石籀文

  range一.Font.Name = “金鼎文”;//设置字体

   
range壹.Font.Size = 18;//设置字体大小

    range1.Font.Color =
System.Drawing.ColorTranslator.ToOle(Color.Blue);//设置字体颜色

  range一.Font.Name
= “燕书”;//设置字体

    range1.HorizontalAlignment =
XlHAlign.xlHAlignCenter;//设置水平对齐格局

   
range1.Font.Color =
System.Drawing.ColorTranslator.ToOle(Color.Blue);//设置字体颜色

    range一.VerticalAlignment =
XlVAlign.xlVAlignCenter;//设置垂直对齐情势   

   
range一.HorizontalAlignment =
XlHAlign.xlHAlignCenter;//设置水平对齐格局

    range1.Value2 = “123/r/n456”;

   
range一.Vertical阿里gnment =
XlVAlign.xlVAlignCenter;//设置垂直对齐方式   

    range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Value2 = “123\r\n456”;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle =
XlLineStyle.xlContinuous;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle =
XlLineStyle.xlContinuous;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle =
XlLineStyle.xlContinuous;

   
//也可用后边的代码替代上边肆项range壹.BorderAround(XlLineStyle.xlContinuous,
XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);

   
range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle =
XlLineStyle.xlContinuous;

   
range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle =
XlLineStyle.xlContinuous;//块内竖线

   
//也可用前面的代码取代上面四项range一.BorderAround(XlLineStyle.xlContinuous,
XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);

    range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle
= XlLineStyle.xlContinuous;//块内横线

   
range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle =
XlLineStyle.xlContinuous;//块内竖线

    excel1.Visible = true;

   
range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle =
XlLineStyle.xlContinuous;//块内横线

九、Excel表单元格合并============================

   
excel1.Visible = true;

  Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

九、Excel表单元格合并============================

    Workbook workbook1 = excel1.Workbooks.Add(true);

  Microsoft.Office.Interop.Excel.Application
excel1 = new Microsoft.Office.Interop.Excel.Application();

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

   
Workbook workbook1 = excel1.Workbooks.Add(true);

    Range range1 = worksheet1.get_Range(“A2”, “E八”);//采用操作块

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1”];

    range1.Value2 = “123/r/n456”;

   
Range range1 = worksheet1.get_Range(“A二”, “E8”);//选取操作块

    excel一.Application.DisplayAlerts = false;//使联合操作不提示警示新闻

   
range1.Value2 = “123\r\n456”;

    range一.Merge(false);//参数为True则为每一行统①为1个单元格

   
excel一.Application.DisplayAlerts =
false;//使联合操作不晋升警示新闻

    excel1.Application.DisplayAlerts = true;

   
range一.Merge(false);//参数为True则为每1行统1为三个单元格

    excel1.Visible = true;

   
excel1.Application.DisplayAlerts = true;

十、复制Excel表============================

   
excel1.Visible = true;

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

十、复制Excel表============================

    Workbook workbook1 = excel1.Workbooks.Add(true);

   
Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

   
Workbook workbook1 = excel1.Workbooks.Add(true);

    worksheet1.Cells[1, 1] = “123”;

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1”];

    worksheet1.Copy(Type.Missing, worksheet1);

   
worksheet1.Cells[1, 1] = “123”;

    Worksheet worksheet2 =(Worksheet)worksheet1.Next;

   
worksheet1.Copy(Type.Missing, worksheet1);

    //worksheet2.Name = “Sheet2”;

   
Worksheet worksheet2 =(Worksheet)worksheet1.Next;

    excel1.Visible = true;

   
//worksheet2.Name = “Sheet2”;

拾1、页面设置============================

   
excel1.Visible = true;

 Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

十1、页面设置============================

     Workbook workbook1 = excel1.Workbooks.Add(true);

 Microsoft.Office.Interop.Excel.Application
excel1 = new Microsoft.Office.Interop.Excel.Application();

     excel一.Caption = “笔者的报表”;

    
Workbook workbook1 = excel1.Workbooks.Add(true);

     Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

    
excel一.Caption = “作者的报表”;

  worksheet壹.PageSetup.PaperSize = XlPaperSize.xlPaperA三;//纸张大小

    
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1”];

    worksheet1.PageSetup.PrintTitleRows = “$壹:$三”;//顶端标题行

 
worksheet一.PageSetup.PaperSize = XlPaperSize.xl帕佩rA三;//纸张大小

      worksheet壹.PageSetup.Orientation =
XlPageOrientation.xlLandscape;//页面方向为横向

  
 worksheet一.PageSetup.PrintTitleRows = “$一:$三”;//顶端标题行

      worksheet1.PageSetup.TopMargin =
excel一.CentimetersToPoints(二);//上边距为2分米(毫米转像素)

     
worksheet一.PageSetup.Orientation =
XlPageOrientation.xlLandscape;//页面方向为横向

      worksheet1.PageSetup.BottomMargin =
excel一.CentimetersToPoints(2);//下面距为二毫米(毫米转像素)

     
worksheet一.PageSetup.TopMargin =
excel一.CentimetersToPoints(二);//上边距为2毫米(分米转像素)

    worksheet1.PageSetup.LeftMargin =
excel一.CentimetersToPoints(一.五);//左侧距为一.五毫米(分米转像素)

     
worksheet一.PageSetup.BottomMargin =
excel1.CentimetersToPoints(二);//上面距为二毫米(毫米转像素)

   worksheet1.PageSetup.RightMargin =
excel一.CentimetersToPoints(一.伍);//右侧距为一.5毫米(毫米转像素)

   
worksheet壹.PageSetup.LeftMargin =
excel壹.CentimetersToPoints(1.伍);//左侧距为1.5分米(分米转像素)

   worksheet一.PageSetup.HeaderMargin =
excel一.CentimetersToPoints(一.二);//页眉边距为一.二毫米(毫米转像素)

  
worksheet壹.PageSetup.RightMargin =
excel一.CentimetersToPoints(壹.5);//左边距为一.五厘米(毫米转像素)

    worksheet一.PageSetup.FooterMargin =
excel1.CentimetersToPoints(壹);//页脚边距为1分米(毫米转像素)

  
worksheet一.PageSetup.HeaderMargin =
excel一.CentimetersToPoints(一.二);//页眉边距为一.2厘米(厘米转像素)

    worksheet一.PageSetup.CenterHorizontally = true;//页面水平居中

   
worksheet壹.PageSetup.FooterMargin =
excel一.CentimetersToPoints(1);//页脚边距为1毫米(分米转像素)

    worksheet一.PageSetup.CenterVertically = false;//页面不垂直居中

   
worksheet1.PageSetup.CenterHorizontally = true;//页面水平居中

    worksheet一.PageSetup.CenterFooter = “第&P页,共&N页”;//中间页脚内容

   
worksheet一.PageSetup.CenterVertically = false;//页面不垂直居中

    excel1.Visible = true;

   
worksheet1.PageSetup.CenterFooter =
“第&P页,共&N页”;//中间页脚内容


   
excel1.Visible = true;

出于实在供给供给ASP.net中导出excel并调控EXCEL格式,以下是落实代码

private void DataGrid1_ItemDataBound(object sender,
System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType ==
ListItemType.AlternatingItem)
{
e.Item.Cells[0].Attributes.Add(“style”,”vnd.ms-excel.numberformat:@”);
e.Item.Cells[3].Attributes.Add(“style”,”vnd.ms-excel.numberformat:¥#,###.00″);
}
}

Excel.Application excel;// = new Application(); 
            int rowIndex = 2;
            int colIndex = 0;

            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            excel = new Excel.ApplicationClass();

            xBk = excel.Workbooks.Add(true);

            xSt = (Excel._Worksheet)xBk.ActiveSheet;

 

             //合并单元格
            xSt.get_Range(excel.Cells[3, 3], excel.Cells[4,
4]).Merge(true);//

            xSt.get_Range(excel.Cells[7, 7], excel.Cells[8,
8]).Interior.ColorIndex = 20;//设置背景象为浅朱红,共计有5多种

  //标题栏固定即为冻结窗口,一下代码完结
xSt.get_Range(“B2”, “B2”).Select();
            excel.ActiveWindow.FreezePanes = true;


相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图