• C# worksheet设置Excel样式


    1.例子导出Excel的样式

    样式代码

    public void Exportdatagridviewtoexcel(string Textname)
            {
                
    
                    SaveFileDialog savedialog = new SaveFileDialog();
                    savedialog.DefaultExt = "xlsx";
                    savedialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx";
                    savedialog.FilterIndex = 0;
                    savedialog.RestoreDirectory = true;
                    savedialog.Title = "导出数据到excel表格";
                    savedialog.ShowDialog();
                    if (savedialog.FileName.IndexOf(":") < 0) return; //被点了取消  
                    //Microsoft.office.interop.excel.application xlapp = new microsoft.office.interop.excel.application();
                    Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
                    if (xlapp == null)
                    {
                        MessageBox.Show("可能您的机子未安装excel,无法创建excel对象!", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
    
                    Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.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];//取得sheet1  
    
                   
    
                    string ADD = "盘点录入明细表  时间:" + DateTime.Now.ToShortDateString();
                   
                    worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).MergeCells = true;
                    //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).Font.Bold = true;//
                    worksheet.Cells[1, 1] = " " + Textname;
                    worksheet.Cells[2, 1] = "" + "使用科室";
                    worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[2, 3]).MergeCells = true;
                    worksheet.get_Range(worksheet.Cells[2, 5], worksheet.Cells[2, 7]).MergeCells = true;
                    //worksheet.Cells[2, 5] = "" + "年月日";
                    worksheet.Cells[3, 1] = "患者姓名";
                    worksheet.Cells[3, 2] = " ";
                    worksheet.Cells[3, 3] = "性别";
                    worksheet.Cells[3, 4] = " ";
                    worksheet.Cells[3, 5] = "年龄";
                    worksheet.Cells[4, 1] = "住院号";
                    worksheet.Cells[4, 2] = " ";
                    worksheet.Cells[4, 3] = "床位号";
                    worksheet.Cells[4, 4] = " ";
                    worksheet.Cells[4, 5] = "手术名称";
                    worksheet.Cells[5, 1] = "使用部位";
                    worksheet.Cells[5, 2] = " ";
                    worksheet.Cells[5, 3] = "手术日期";
                    worksheet.Cells[5, 4] = " ";
                    worksheet.Cells[5, 5] = "医保类型";
                  
                    worksheet.get_Range(worksheet.Cells[3, 6], worksheet.Cells[3, 7]).MergeCells = true;
                    worksheet.get_Range(worksheet.Cells[4, 6], worksheet.Cells[4, 7]).MergeCells = true;
                    worksheet.get_Range(worksheet.Cells[5, 6], worksheet.Cells[5, 7]).MergeCells = true;
                    worksheet.Cells[5, 6] = " ";
                    worksheet.Cells[4, 6] = " ";
                    worksheet.Cells[3, 6] = " ";
                    worksheet.Cells[6, 1] = "生产厂家";
                    worksheet.get_Range(worksheet.Cells[6, 2], worksheet.Cells[6, 3]).MergeCells = true;
                    worksheet.Cells[6, 4] = "注册证号";
                    worksheet.get_Range(worksheet.Cells[6, 5], worksheet.Cells[6, 7]).MergeCells = true;
                    worksheet.Cells[7, 1] = "生产日期";
                    worksheet.Cells[7, 2] = " ";
                    worksheet.Cells[7, 3] = "灭菌日期";
                    worksheet.Cells[7, 4] = " ";
                    worksheet.Cells[7, 5] = "有效日期";
                    worksheet.get_Range(worksheet.Cells[7, 6], worksheet.Cells[7, 7]).MergeCells = true;
                    worksheet.Cells[7, 6] = " ";
                    worksheet.Cells[8, 1] = "供货单位";
                    worksheet.get_Range(worksheet.Cells[8, 2], worksheet.Cells[8, 3]).MergeCells = true;
                    worksheet.Cells[8, 4] = "供货商签字";
                    worksheet.Cells[8, 5] = " ";
                    worksheet.Cells[8, 6] = "联系电话";
                    worksheet.Cells[8, 7] = " ";
                    worksheet.Cells[9, 1] = "产品名称";
                    worksheet.Cells[9, 2] = "规格型号";
                    worksheet.Cells[9, 3] = "产品批号";
                    worksheet.Cells[9, 4] = "单价";
                    worksheet.Cells[9, 5] = "数量";
                    worksheet.Cells[9, 6] = "单位";
                    worksheet.Cells[9, 7] = "金额    ";
                    
                    worksheet.Cells[10, 1] = " ";
                    worksheet.Cells[10, 2] = " ";
                    worksheet.Cells[10, 3] = " ";
                    worksheet.Cells[10, 4] = " ";
                    worksheet.Cells[10, 5] = " ";
                    worksheet.Cells[10, 6] = " ";
                    worksheet.Cells[10, 7] = " ";
                    worksheet.Cells[11, 1] = " ";
                    worksheet.Cells[11, 2] = " ";
                    worksheet.Cells[11, 3] = " ";
                    worksheet.Cells[11, 4] = " ";
                    worksheet.Cells[11, 5] = " ";
                    worksheet.Cells[11, 6] = " ";
                    worksheet.Cells[11, 7] = " ";
                    worksheet.Cells[12, 1] = " ";
                    worksheet.Cells[12, 2] = " ";
                    worksheet.Cells[12, 3] = " ";
                    worksheet.Cells[12, 4] = " ";
                    worksheet.Cells[12, 5] = " ";
                    worksheet.Cells[12, 6] = " ";
                    worksheet.Cells[12, 7] = " ";
                    worksheet.Cells[13, 1] = " ";
                    worksheet.Cells[13, 2] = " ";
                    worksheet.Cells[13, 3] = " ";
                    worksheet.Cells[13, 4] = " ";
                    worksheet.Cells[13, 5] = " ";
                    worksheet.Cells[13, 6] = " ";
                    worksheet.Cells[13, 7] = " ";
    
                    worksheet.Cells[14, 1] = "高值耗材使用医生签字";
                    worksheet.Cells[14, 5] = "手术护士";
                    worksheet.Cells[14, 6] = "  ";
                    worksheet.Cells[14, 7] = "  ";
                    worksheet.get_Range(worksheet.Cells[14, 6], worksheet.Cells[14, 7]).MergeCells = true;
                    worksheet.get_Range(worksheet.Cells[14, 1], worksheet.Cells[14, 3]).MergeCells = true;
                    worksheet.Cells[15, 1] = "收费人员(记账人员)签字";
                    worksheet.Cells[15, 5] = "收费价格";
                    worksheet.Cells[15, 6] = "  ";
                    worksheet.Cells[15, 7] = "  ";
                    worksheet.get_Range(worksheet.Cells[15, 6], worksheet.Cells[15, 7]).MergeCells = true;
                    worksheet.get_Range(worksheet.Cells[15, 1], worksheet.Cells[15, 3]).MergeCells = true;
                    worksheet.Cells[16, 1] = "科室负责人签字";
                    worksheet.Cells[16, 3] = " ";
                    worksheet.Cells[16, 4] = " ";
                    worksheet.Cells[16, 5] = "设备采购签字";
                    worksheet.Cells[16, 6] = " ";
                //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 10]).Text = ADD;
                    worksheet.get_Range(worksheet.Cells[16, 1], worksheet.Cells[16, 2]).MergeCells = true;
                    worksheet.get_Range(worksheet.Cells[16, 5], worksheet.Cells[16, 6]).MergeCells = true;
                  
                    //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                    //worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 8]).Font.Bold = true;
                    //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
                    //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
                    //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
                    //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 8]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
                 worksheet.Cells[17, 1] = "高值耗材唯一性标示";
                 //worksheet.get_Range(worksheet.Cells[17, 1], worksheet.Cells[17, 1]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; 
                 worksheet.get_Range(worksheet.Cells[17, 1], worksheet.Cells[27, 1]).MergeCells = true;
                 worksheet.get_Range(worksheet.Cells[17, 2], worksheet.Cells[27, 7]).MergeCells = true;
                 worksheet.get_Range(worksheet.Cells[17, 1], worksheet.Cells[27, 1]).WrapText = true;//  
                 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[27, 7]).Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
             
                    if (savedialog.FileName != "")
                    {
                        try
                        {
                            workbook.Saved = true;
                            workbook.SaveCopyAs(savedialog.FileName);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("导出文件时出错,文件可能正被打开!..." + ex.Message, "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
    
                    }
                    //GC.Collect();//强行销毁  
                    MessageBox.Show("数据导出成功! ", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
    
                }

    2.单元格样式设置

    Excel.Range r =mySheet.get_Range(mySheet.Cells[1,1],mySheet.Cells[DT.Rows.Count + 2,DT.Columns.Count - 3]);
    
    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"//设置单元格格式为特殊类型
    
    B、上下标格式的控制;
    
    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; //控制输出样式为上标
    
    C、单元格水平垂直对齐方式;
    
    //单元格水平,垂直居中
    
    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,
    
    D、 单元格边框设置;
    
    //设置边框
    
    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,
    
    E、单元格合并 
    
    用get_Range方法获取要合并的单元格,再设置MergeCells属性的值进行合并。
    
    代码:
    
    //合并单元格
    
    myExcel.get_Range(myExcel.Cells[1,1],myExcel.Cells[1,4]).MergeCells =true;
    
    F、字体设置
    
    先用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;
    
    G、冻结行列 
    
    用get_Range方法获取单元格,再设置其Freezepanes属性为true,就把该单元格右上方的行和列都冻结了,取消冻结将其值设为false即可。
    
    //冻结行列
    
    myExcel.get_Range(myExcel.Cells[3,1],myExcel.Cells[3,1]).Activate();
    
    myExcel.ActiveWindow.FreezePanes=true;
    
    H、 公式输入栏的隐藏
    
    //设置是否显示Excel公式输入栏,默认为true
    
    myExcel.DisplayFormulaBar=false;
    
    I、 列标题与行标题的隐藏
    
    //设置是否显示行和列的标题,默认为true
    
    myExcel.ActiveWindow.DisplayHeadings=false;
    
    J、网格的隐藏
    
    //设置是否显示网格,默认为true
    
    myExcel.ActiveWindow.DisplayGridlines=false;
    
    K、 水平、垂直滚动条的隐藏
    
    //设置是否显示水平滚动条
    
    myExcel.ActiveWindow.DisplayHorizontalScrollBar=false;
    
    //设置是否显示垂直滚动条
    
    myExcel.ActiveWindow.DisplayVerticalScrollBar=false;
    
    L、页的隐藏Sheet
    
    //设置是否显示Sheet页
    
    myExcel.ActiveWindow.DisplayWorkbookTabs=false;
    
    M、 电子表格外观控制:
    
    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
    
    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); //设置行的颜色
    
    }
    
    worksheet.Cells[1, 1]= "Excel单元格赋值";//Excel单元格赋值
     range.Font.Size = 15; //设置字体大小
    range.Font.Underline=true; //设置字体是否有下划线
    range.Font.Name="黑体";设置字体的种类
    range.HorizontalAlignment=XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式
    range.ColumnWidth=15; //设置单元格的宽度
    range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();//设置单元格的背景色
    range.Borders.LineStyle=1; //设置单元格边框的粗细
    range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());//给单元格加边框
    range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;//设置单元格上边框为无边框
    range.EntireColumn.AutoFit(); //自动调整列宽
    Range.HorizontalAlignment= xlCenter; // 文本水平居中方式
     Range.VerticalAlignment= xlCenter //文本垂直居中方式 
     Range.WrapText=true; //文本自动换行
    Range.Interior.ColorIndex=39; //填充颜色为淡紫色
    Range.Font.Color=clBlue; //字体颜色
    xlsApp.DisplayAlerts=false; //保存Excel的时候,不弹出是否保存的窗口直接进行保存
    
    向Excel中填充数据,并保存为临时文件
    
    Excel.ApplicationmyExcel =newExcel.Application();
    
    //打开模板文件
    
    myExcel.Application.Workbooks.Open(mode.FullName,missing,missing,missing, missing, missing, missing, missing, missing,missing, missing,missing,missing, missing, missing);
    
    //选中有数据的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();
    
    //不单独显示Excel,最后在IE中显示
    
    myExcel.Visible=false;
    
    //第一行为报表的标题
    
    myExcel.Cells[1,1]= "用模板导出的报表";
    
    //逐行写入数据,数组中第一行为报表的列标题
    
    for (int i = 0; i <DT.Columns.Count - 3; i++)
    
    {
    
    myExcel.Cells[2,1+ i] =DT.Columns[i].Caption; ;
    
    }
    
    //在当前目录下指定一个临时文件
    
    string FileName =Server.MapPath("~")+"""Temp.xls";
    
    if (File.Exists(FileName))
    
    {
    
    File.Delete(FileName);
    
    }
    
    myExcel.Save(FileName);
    
    mySheet.Cells.Clear();
    
    //设置不出现保存提示框
    
    myBook.Saved=true;
    
    myExcel.Application.Workbooks.Close();

    框架4.0之上的导出  引用 Aspose.cell

    public void importExcel(DataTable dt, string fileName, string filePath)
            {
                int posStart = 2;//报表内容起始
                //设置字体样式
                Style style1 = new Style();
                style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style1.VerticalAlignment = TextAlignmentType.Left;
                style1.Font.Name = "宋体";
                style1.Font.IsBold = true;//设置粗体
                style1.Font.Size = 12;//设置字体大小
    
                Style style2 = new Style();
                style2.HorizontalAlignment = TextAlignmentType.Center;
                style2.Font.Size = 10;
    
                Workbook wb = new Workbook();
                Worksheet ws = wb.Worksheets[0];
                Cells cell = ws.Cells;
    
                int colCount = dt.Columns.Count;
                int rowCount = dt.Rows.Count;
                //合并第一行单元格,显示标题
                Range range = cell.CreateRange(0, 0, 1, colCount);
                range.Merge();
                cell["A1"].PutValue(fileName); //标题
                cell.SetRowHeight(0, 20);//设置行高
                                         //给单元格关联样式
                cell["A1"].SetStyle(style1); //报表名字 样式
                                             //设置Execl列名
                for (int i = 0; i < colCount; i++)
                {
                    cell[1, i].PutValue(dt.Columns[i].ColumnName);
                    //cell[1, i].SetStyle(style2);
                }
                //设置单元格内容
                for (int i = 0; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        cell[i + posStart, j].PutValue((dt.Rows[i][j] + "").Trim());
                        //cell[i + posStart, j].SetStyle(style2);
                    }
                }
                //设置列宽
                //for (int i = 1; i < dt.Columns.Count; i++)
                //{
                //    cell.SetColumnWidthPixel(i - 1, int.Parse(dataGridView1.Columns[i].Width.ToString()));
                //}
                //保存excel表格
                wb.Save(filePath);
    
            }
  • 相关阅读:
    Singleton模式
    Factory模式
    AbstactFactory模式
    Maven的介绍及使用
    MySQL索引分析及使用
    Runnable接口和Callable接口的区别
    Java中的常见数学运算
    mkdir()和mkdirs()区别
    面试小问题——Object中有哪些常用方法?
    面试小问题——什么是多态?
  • 原文地址:https://www.cnblogs.com/singov/p/10978236.html
Copyright © 2020-2023  润新知