【C#】Microsoft.Office.Interop.Excel.dll使用汇总

目录

• 一、大量数据导出到Excel
• 二、单元格格式设置
  • 1、设置单元格类型
  • 2、上下标格式的控制
  • 3、单元格水平垂直对齐方式
  • 4、单元格边框设置
  • 5、单元格合并
  • 6、字体设置
  • 7、冻结行列
  • 8、公式输入栏的隐藏
  • 9、列标题与行标题的隐藏
  • 10、网格的隐藏
  • 11、水平、垂直滚动条的隐藏
  • 12、页Sheet的隐藏
  • 13、电子表格外观控制
  • 14、设置行的颜色
  • 15、Excel单元格赋值
  • 16、保存Excel的时候，不弹出是否保存的窗口直接进行保存
  • 17、选中有数据的Cells

 

---

回到顶部

一、大量数据导出到Excel

https://www.cnblogs.com/xiaoheihei/p/8315856.html

程序由原始的将数据一个表格一个表格地写入到EXCEL中，改变成程序先将数据存入二维数组中，然后再将数组值赋予EXCEL应用程序对象的VALUE属性，这样就使导出的效率提高很多，经测试导出100万数据没问题。

private Excel.Application m_xlApp = new Excel.Application(); 
/// <summary>
/// 将DataTable数据导出到Excel表
/// </summary>
/// <param name="dtTmp">要导出的DataTable</param>
/// <param name="filePath">Excel的保存路径及名称</param>
public void DataTabletoExcel(System.Data.DataTable dtTmp, string filePath)
{
    if (dtTmp == null)
    {
        return;
    }
    long rowNum = dtTmp.Rows.Count;//行数
    int columnNum = dtTmp.Columns.Count;//列数
    m_xlApp = new Excel.Application();
    m_xlApp.DisplayAlerts = false;//不显示更改提示
    m_xlApp.Visible = false;
 
    Excel.Workbooks workbooks = m_xlApp.Workbooks;
    Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得Sheet1
 
    try
    {
        //单张Excel表格最大行数
        if (rowNum > 65536)
        {
            long pageRows = 65535;  //定义每页显示的行数,行数必须小于65536
            int scount = (int)(rowNum / pageRows);  //导出数据生成的表单数
            if (scount * pageRows < rowNum) //当总行数不被pageRows整除时，经过四舍五入可能页数不准
            {
                scount = scount + 1;
            }
            for (int sc = 1; sc <= scount; sc++)
            {
                if (sc > 3) //这里由1改为3，20140922
                {
                    object missing = System.Reflection.Missing.Value;
                    worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
                                missing, missing, missing, missing);    //添加一个sheet
                }
                else
                {
                    worksheet = (Excel.Worksheet)workbook.Worksheets[sc];   //取得sheet1
                }
                string[,] datas = new string[pageRows + 1, columnNum];
 
                for (int i = 0; i < columnNum; i++) //写入字段
                {
                    datas[0, i] = dtTmp.Columns[i].Caption; //表头信息
                }
                Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
                range.Interior.ColorIndex = 15; //15代表灰色
                range.Font.Bold = true;
                range.Font.Size = 9;
 
                int init = int.Parse(((sc - 1) * pageRows).ToString());
                int r = 0;
                int index = 0;
                int result;
                if (pageRows * sc >= rowNum)
                {
                    result = (int)rowNum;
                }
                else
                {
                    result = int.Parse((pageRows * sc).ToString());
                }
 
                for (r = init; r < result; r++)
                {
                    index = index + 1;
                    for (int i = 0; i < columnNum; i++)
                    {
                        object obj = dtTmp.Rows[r][dtTmp.Columns[i].ToString()];
                        datas[index, i] = obj == null ? "" : obj.ToString().Trim();
                    }
                }
 
                Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);
                fchR.Value2 = datas;
                worksheet.Columns.EntireColumn.AutoFit();   //列宽自适应。
                m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;  //Sheet表最大化
                range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);
                range.Font.Size = 9;
                range.RowHeight = 14.25;
                range.Borders.LineStyle = 1;
                range.HorizontalAlignment = 1;
            }
        }
        else
        {
            string[,] datas = new string[rowNum + 1, columnNum];
            for (int i = 0; i < columnNum; i++) //写入字段
            {
                datas[0, i] = dtTmp.Columns[i].Caption;
            }
            Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
            range.Interior.ColorIndex = 15;     //15代表灰色
            range.Font.Bold = true;
            range.Font.Size = 9;
 
            int r = 0;
            for (r = 0; r < rowNum; r++)
            {
                for (int i = 0; i < columnNum; i++)
                {
                    object obj = dtTmp.Rows[r][dtTmp.Columns[i].ToString()];
                    datas[r + 1, i] = obj == null ? "" : obj.ToString().Trim();
                }
            }
            Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
            fchR.Value2 = datas;
 
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
            m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
 
            range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
            range.Font.Size = 9;
            range.RowHeight = 14.25;
            range.Borders.LineStyle = 1;
            range.HorizontalAlignment = 1;
        }
        workbook.Saved = true;
        workbook.SaveCopyAs(filePath);
 
        lblMsg.Text = "导出成功！" + DateTime.Now.ToString();
    }
    catch (Exception ex)
    {
        string dd = Server.MapPath("~/Tmp/1.txt");
        System.IO.File.AppendAllText(dd, "导出异常：" + ex.Message + Environment.NewLine);
    }
    finally
    {
        EndReport();
    }
}
 
/// <summary>
/// 退出报表时关闭Excel和清理垃圾Excel进程
/// </summary>
private void EndReport()
{
    object missing = System.Reflection.Missing.Value;
    try
    {
        m_xlApp.Workbooks.Close();
        m_xlApp.Workbooks.Application.Quit();
        m_xlApp.Application.Quit();
        m_xlApp.Quit();
    }
    catch
    {
 
    }
    finally
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
            m_xlApp = null;
        }
        catch
        {
 
        }
        try
        {
            //清理垃圾进程
            this.killProcessThread();
        }
        catch
        {
 
        }
        GC.Collect();
    }
}
 
/// <summary>
/// 杀掉不死进程
/// </summary>
private void killProcessThread()
{
    ArrayList myProcess = new ArrayList();
    for (int i = 0; i < myProcess.Count; i++)
    {
        try
        {
            System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
        }
        catch
        {
        }
    }
}

 

回到顶部

二、单元格格式设置

Excel.Range r = mySheet.get_Range(mySheet.Cells[1,1],mySheet.Cells[DT.Rows.Count + 2,DT.Columns.Count - 3]);

 

1、设置单元格类型

r.NumberFormat="@"; //设置单元格格式为文本类型，文本类型可设置上下标

r.NumberForma="0.00_ "//设置单元格格式为数值类型，小数点后2位

r.NumberForma="￥#,##0.00;￥-#,##0.00"//设设置单元格格式为货币类型，小数点后2位

r.NumberForma=_"_ ￥*#,##0.00_;_ ￥*-#,##0.00_ ;_ ￥*""-""??_;_ @_ "//置单元格格式为会计专用类型，小数点后2位

r.NumberForma="yyyy-m-d"//设置单元格格式为日期类型

r.NumberForma="[$-F400]h:mm:ss AM/PM"//设置单元格格式为时间类型

r.NumberForma="0.00%"//设置单元格格式为百分比类型，小数点后2位

r.NumberForma="# ?/?"设置单元格格式为分数类型，分母为一位数

r.NumberForma="0.00E+00"//设置单元格格式为科学技术类型，小数位数为2

r.NumberForma="000000"//设置单元格格式为特殊类型

 

2、上下标格式的控制

////上下标格式的控制
mySheet.get_Range(mySheet.Cells[i+3, DT.Columns.Count - 3],mySheet.Cells[i + 3, DT.Columns.Count -3]).get_Characters(a.Length+ 1,b.Length).Font.Subscript =true;//控制输出样式为下标
mySheet.get_Range(mySheet.Cells[i+3, DT.Columns.Count - 3],mySheet.Cells[i + 3, DT.Columns.Count-3]).get_Characters(a.Length + b.Length+ 1, c.Length).Font.Superscript =true; //控制输出样式为上标

 

3、单元格水平垂直对齐方式

//单元格水平，垂直居中
r.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter;
r.VerticalAlignment=Excel.XlVAlign.xlVAlignCenter;

////上面代码中，枚举XLHAlign的值还有
// 右对齐
xlHAlignRight,
// 左对齐.
xlHAlignLeft,
// 两端对齐.
xlHAlignJustify,
// 分散对齐(缩进)
xlHAlignDistributed,
// 居中对齐
xlHAlignCenter,
// 依照数据类型对齐,常规
xlHAlignGeneral,
// 填充
xlHAlignFill,
// 跨列对齐.
xlHAlignCenterAcrossSelection=7,

////枚举XLVAlign的值还有
// 靠上对齐
xlVAlignTop,
//两端对齐.
xlVAlignJustify=-4130,
//分散对齐.
xlVAlignDistributed,
//居中对齐.
xlVAlignCenter,
//靠下对齐.
xlVAlignBottom=-4107,

 

4、单元格边框设置

//设置边框
Excel.Range r =mySheet.get_Range(mySheet.Cells[1,1],mySheet.Cells[DT.Rows.Count + 2,DT.Columns.Count - 3]);
r.Borders.LineStyle=Excel.XlLineStyle.xlContinuous;

////枚举XlLineStyle中还有下面线形
//没边框线
xlLineStyleNone,
//双线.
xlDouble,
//点状线.
xlDot,
//虚线.
xlDash,
//连续线.
xlContinuous,
//点线交互型
xlDashDot,
//两点一线型
xlDashDotDot,
//斜线.
xlSlantDashDot,

 

5、单元格合并

//用get_Range方法获取要合并的单元格，再设置MergeCells属性的值进行合并。
//合并单元格
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,4]).MergeCells =true;

 

6、字体设置

//先用get_Range方法选中要设置字体的某个单元格或者或者直接用get_Characters方法直接选中要设置的字符进行设置

//加粗字体
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Bold =true;

//设置字体大小
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Size=16;

//设置字体的颜色
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.ColorIndex = 3;

//设置字体
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Name ="隶书";

//设置成斜体
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Italic =true;

//设置下划线
myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,1]).Font.Underline =true;

 

或者：

r.Font.Size = 15; //设置字体大小
r.Font.Underline=true; //设置字体是否有下划线
r.Font.Name="黑体";//设置字体的种类
r.HorizontalAlignment=XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式
r.ColumnWidth=15; //设置单元格的宽度
r.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();//设置单元格的背景色
r.Borders.LineStyle=1; //设置单元格边框的粗细
r.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());//给单元格加边框
r.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;//设置单元格上边框为无边框
r.EntireColumn.AutoFit(); //自动调整列宽
r.HorizontalAlignment= xlCenter; // 文本水平居中方式
r.VerticalAlignment= xlCenter //文本垂直居中方式
r.WrapText=true; //文本自动换行
r.Interior.ColorIndex=39; //填充颜色为淡紫色
r.Font.Color=clBlue; //字体颜色

 

 

7、冻结行列

//用get_Range方法获取单元格，再设置其Freezepanes属性为true，就把该单元格右上方的行和列都冻结了，取消冻结将其值设为false即可。

//冻结行列
myExcel.get_Range(myExcel.Cells[3,1],myExcel.Cells[3,1]).Activate();
myExcel.ActiveWindow.FreezePanes=true;

 

8、公式输入栏的隐藏

 

//设置是否显示Excel公式输入栏，默认为true
myExcel.DisplayFormulaBar=false;

 

 

9、列标题与行标题的隐藏

//设置是否显示行和列的标题，默认为true
myExcel.ActiveWindow.DisplayHeadings=false;

 

10、网格的隐藏

//设置是否显示网格，默认为true
myExcel.ActiveWindow.DisplayGridlines=false;

 

11、水平、垂直滚动条的隐藏

//设置是否显示水平滚动条
myExcel.ActiveWindow.DisplayHorizontalScrollBar=false;

//设置是否显示垂直滚动条
myExcel.ActiveWindow.DisplayVerticalScrollBar=false;

 

12、页Sheet的隐藏

//设置是否显示Sheet页
myExcel.ActiveWindow.DisplayWorkbookTabs=false;

 

13、电子表格外观控制

Sheet.DisplayToolbar= false;//是否显示工具栏默认值为 true

Sheet.DisplayOfficeLogo= false;// 取消显示Office图标

Sheet.DisplayColumnHeadings= false;// 是否显示列标题，默认是true

Sheet.DisplayRowHeadings= false;// 是否显示行标题，默认是true

Sheet.DisplayHorizontalScrollBar= false;// 是否显示水平滚动条，默认为true

Sheet.DisplayVerticalScrollBar= false;// 是否显示垂直滚动条，默认为true

Sheet.DisplayGridlines= false;// 电子表格是否显示网格，默认为true

 

 

14、设置行的颜色

for (int i = 1; i < DT.Columns.Count- 2; i += 2)
{
　　Sheet.get_Range(Sheet.Rows.Cells[i,1],Sheet.Rows.Cells[i, DT.Columns.Count - 3]).Interior.set_ColorIndex(2); 
}

 

15、Excel单元格赋值

//Excel单元格赋值
worksheet.Cells[1, 1]= "Excel单元格赋值";

 

16、保存Excel的时候，不弹出是否保存的窗口直接进行保存

xlsApp.DisplayAlerts=false;

 

17、选中有数据的Cells

//选中有数据的Cells
Excel.WorkbookmyBook = myExcel.Workbooks[1];
Excel.WorksheetmySheet = (Excel.Worksheet)myBook.Worksheets[1];
Excel.Ranger = mySheet.get_Range(mySheet.Cells[1,1],mySheet.Cells[DT.Rows.Count + 2,DT.Columns.Count - 3]);
r.Select();

 

https://www.cnblogs.com/noteTech/p/14876702.html