格式设置

By admin in 4858.com on 2019年3月31日

壹 、添加引用

增加引用:Microsoft   Excel   11.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   11.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(“F7”,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”;

    worksheet1.Name = “工作安排表”;

   
Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1″或1]格式设置。;

return true;
}

    excel1.Visible = true;

   
worksheet1.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)
//    {
//        MessageBox.Show(“没有别的数据足以导入到Excel文件!”);
//        return false;
//    }

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

5、设置或修改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”];

4858.com,   
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文件

⑥ 、设置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();

六 、设置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;
//}

    worksheet1.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;

    worksheet1.Columns.RowHeight = 20;//全局列高

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

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

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

   
worksheet1.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;

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

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

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

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

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

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

    excel1.Visible = true;

   
range1.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();
//    }

柒 、设置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();
}

   
//也可用后边的代码代替上边四项range1.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;//斜杠

   
//也可用前边的代码代替上面四项range1.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(“A2”, “E8”);//选拔操作块

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

    range1.Font.Bold = true;//设置行书

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

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

   
range1.Font.Bold = true;//设置大篆

  range1.Font.Name = “宋体”;//设置字体

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

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

  range1.Font.Name
= “钟鼓文”;//设置字体

    range1.Horizontal阿里gnment =
XlHAlign.xlHAlignCenter;//设置水平对齐方式

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

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

   
range1.HorizontalAlignment =
XlHAlign.xlHAlignCenter;//设置水平对齐方式

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

   
range1.VerticalAlignment =
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;

   
//也可用前面包车型大巴代码代替上边四项range1.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;//块内竖线

   
//也可用前边的代码代替上边四项range1.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”, “E8”);//采纳操作块

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

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

   
Range range1 = worksheet1.get_Range(“A2”, “E8”);//选用操作块

    excel1.Application.DisplayAlerts = false;//使联合操作不提示警示消息

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

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

   
excel1.Application.DisplayAlerts =
false;//使联合操作不晋升警示消息

    excel1.Application.DisplayAlerts = true;

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

    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”;

十① 、页面设置============================

   
excel1.Visible = true;

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

十① 、页面设置============================

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

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

     excel1.Caption = “作者的表格”;

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

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

    
excel1.Caption = “作者的报表”;

  worksheet1.PageSetup.PaperSize = XlPaperSize.xlPaperA3;//纸张大小

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

    worksheet1.PageSetup.PrintTitleRows = “$1:$3”;//顶端题目行

 
worksheet1.PageSetup.PaperSize = XlPaperSize.xlPaperA3;//纸张大小

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

  
 worksheet1.PageSetup.PrintTitleRows = “$1:$3”;//顶端题目行

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

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

      worksheet1.PageSetup.BottomMargin =
excel1.CentimetersToPoints(2);//上边距为2分米(分米转像素)

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

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

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

   worksheet1.PageSetup.RightMargin =
excel1.CentimetersToPoints(1.5);//左边距为1.5分米(厘米转像素)

   
worksheet1.PageSetup.LeftMargin =
excel1.CentimetersToPoints(1.5);//右边距为1.5毫米(毫米转像素)

   worksheet1.PageSetup.HeaderMargin =
excel1.CentimetersToPoints(1.2);//页眉边距为1.2分米(毫米转像素)

  
worksheet1.PageSetup.RightMargin =
excel1.CentimetersToPoints(1.5);//右侧距为1.5毫米(毫米转像素)

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

  
worksheet1.PageSetup.HeaderMargin =
excel1.CentimetersToPoints(1.2);//页眉边距为1.2毫米(分米转像素)

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

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

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

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

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

   
worksheet1.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;//设置背景观为浅青莲,共计有56种

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

发表评论

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

网站地图xml地图
Copyright @ 2010-2019 美高梅手机版4858 版权所有