C#操作EXCEL
来源:互联网 发布:淘宝基础版不能设背景 编辑:程序博客网 时间:2024/06/11 02:09
using Excel11=Microsoft.Office.Interop.Excel;
全表自动列宽
mysheet.Cells.Select();
mysheet.Cells.Columns.AutoFit();
合并
myRange.Merge(Missing.Value);
粗体设置
myRange.Font.Bold = true;
字体大小设置
myRange.Font.Size = 12;
水平对齐设置
myRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
垂直对齐设置
myRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
公式设置
myRange.FormulaR1C1 =
列宽设置
myRange.ColumnWidth =
行高
myRange.RowHeight =
格式
myrange.NumberFormatLocal="@";
添加行
((Excel.Range)mysheet.Cells[15,3]).EntireRow.Insert(0);
设置第10行为红色
mysheet.get_Range((Excel.Range)mysheet.Cells[10,1],(Excel.Range)mysheet.Cells[10,200]).Select();
mysheet.get_Range((Excel.Range)mysheet.Cells[10,1],(Excel.Range)mysheet.Cells[10,200]).Interior.ColorIndex=3;
单元格自动换行
myrange.WrapText = true;
单元格行高自动调整
myrange.EntireRow.AutoFit();
排序
private void SortNamedRange()
{
// Set some data in a range of cells.
this.Range["A1", missing].Value2 = 30;
this.Range["A2", missing].Value2 = 10;
this.Range["A3", missing].Value2 = 20;
this.Range["A4", missing].Value2 = 50;
this.Range["A5", missing].Value2 = 40;
// Create a NamedRange that refers to the range.
Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
this.Controls.AddNamedRange(this.Range["A1", "A5"],
"namedRange1");
// Sort the NamedRange.
namedRange1.Sort(this.Range["A1", "A5"],
Excel.XlSortOrder.xlAscending,
missing,
missing,
Excel.XlSortOrder.xlAscending,
missing,
Excel.XlSortOrder.xlAscending,
Excel.XlYesNoGuess.xlNo,
missing,
missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlStroke,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
//全表自动列宽
mysheet.Cells.Select();
mysheet.Cells.Columns.AutoFit();
//合并
excelRangeParm.Merge(Missing.Value);
//粗体设置
excelRangeParm.Font.Bold = true;
//字体大小设置
excelRangeParm.Font.Size = 12;
//水平对齐设置
excelRangeParm.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//垂直对齐设置
excelRangeParm.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//公式设置
excelRangeParm.FormulaR1C1 = 公式;
//列宽设置
excelRange.ColumnWidth = 宽度;
//行高
excelRange.RowHeight = 行高;
//设置列格式
Excel.Range myrange=mysheet.get_Range(mysheet.Cells[1,1],mysheet.Cells[5,1]);
//文本格式
myrange.NumberFormatLocal="@";
//通用格式
style.NumberFormatLocal = "[DBNum2][$-804]G/通用格式";
或
range.NumberFormatLocal = "G/通用格式";
xlsheet.Cells[1,1]="''+txtKey.Text;
//添加行
((Excel.Range)mysheet.Cells[15,3]).EntireRow.Insert(0);
//设置第10行为红色
mysheet.get_Range((Excel.Range)mysheet.Cells[10,1],(Excel.Range)mysheet.Cells[10,200]).Select();
mysheet.get_Range((Excel.Range)mysheet.Cells[10,1],(Excel.Range)mysheet.Cells[10,200]).Interior.ColorIndex=3;
//添加行
((Excel.Range)mysheet.Cells[15,3]).EntireRow.Insert(0);
}
//获取和设置当前目录(即该进程从中启动的目录)的完全限定路径。
string str = System.Environment.CurrentDirectory;
MessageBox.Show(str);
//result: X:\xxx\xxx (.exe文件所在的目录)
}
private void button1_Click(object sender, EventArgs e)
{
Excel11.Application app = new Excel11.Application();
//加载模板
Excel11.Workbook workbook = app.Application.Workbooks.Open(System.Environment.CurrentDirectory+@"\mydata", Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
//获取模板中第一个sheet页
Excel11.Worksheet worksheet = workbook.Sheets.get_Item(1) as Excel11.Worksheet;
//向Excel单元格填充数据
worksheet.Cells[7,4] = "AAAA";
//获取单元格内容
string str = worksheet.Cells[7, 4]).Text.ToString();
workbook.Save();
/* //利用公式计算满足某条街的总和,单独分出3是为了可以在程序中替换该值
worksheet.Cells[2, 4] = "=COUNTIF(C2:C" + 3 + ",\"男\")";
//在结尾添加一特殊行,插入值
Range range = worksheet.get_Range(worksheet.Cells[4, 1], worksheet.Cells[4, 2]);
//合并单元格
range.Merge(0);
//设置单元格文字对齐方式
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
range.Cells[1, 1] = "Hello";
worksheet.Cells[4, 3] = "0";
//设置单元格背景色
range = worksheet.Cells[4, 3] as Range;
range.Interior.Color = System.Drawing.Color.LightSeaGreen;
//画边框
range = worksheet.get_Range(worksheet.Cells[4, 1], worksheet.Cells[4, 3]);
range.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
range = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[3, 2]);
range.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
//保存
workbook.Save(); */
}
finally
{
//资源清理
workbook.Close();
app.Quit();
}
- c#如何操作excel??
- C#操作Excel!
- C# 操作 Excel
- C#访问Excel操作
- C# 操作 Excel
- C# 操作 Excel
- C#操作Excel
- C#操作Excel
- C# 操作 Excel
- C#操作EXCEL
- C#对Excel操作
- c#操作EXcel
- C# 操作 Excel
- C#操作Excel文档
- C# 操作Excel
- C#操作MS Excel
- C#操作Excel
- .Net C# 操作Excel
- C/C++中的取整运算
- LVS--lvs-dr实现wordpress
- (转)ICO是区块链与生俱来的特性,是金融深化的终局
- spring整合quartz
- java学习之静态内部类
- C#操作EXCEL
- python中文件读取操作及注意事项
- Android平台dalvik模式下java Hook框架ddi的分析(1)
- 我的CV路
- ActivityManager框架之简要
- 关于java中的文件读写java io【复习1】
- RecyclerView 的简单使用
- HDU4325-Flowers
- 【Linux基础系列之】同步机制介绍