C# 操作Excel代码总结

2023-09-18 07:17:05 408
本文分享了c#操作Excel的相关代码，还是比较全面的，其实无外乎存取，增删改查等操作，参考下。
具体代码：
//引用Microsoft.Office.Interop.Excel.dll文件
//添加using
usingMicrosoft.Office.Interop.Excel;
usingExcel=Microsoft.Office.Interop.Excel;

//设置程序运行语言
System.Globalization.CultureInfoCurrentCI=System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture=newSystem.Globalization.CultureInfo("en-US");
//创建Application
Excel.ApplicationxlApp=newExcel.Application();
//设置是否显示警告窗体
excelApp.DisplayAlerts=false;
//设置是否显示Excel
excelApp.Visible=false;
//禁止刷新屏幕
excelApp.ScreenUpdating=false;
//根据路径path打开
Excel.WorkbookxlsWorkBook=excelApp.Workbooks.Open(path,System.Type.Missing,System.Type.Missing,System.Type.Missing,
System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,
System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing);
//获取Worksheet对象
Excel.WorksheetxlsWorkSheet=(Worksheet)xlsWorkBook.Worksheets["salesplan"];

***获取最后一行、一列的两种方法***
//获取已用的范围数据
introwsCount=xlsWorkSheet.UsedRange.Rows.Count;
intcolsCount=xlsWorkSheet.UsedRange.Columns.Count;
introwsCount=xlsWorkSheet.get_Range("A65536","A65536").get_End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row;
intcolsCount=xlsWorkSheet.get_Range("ZZ1","ZZ1").get_End(Microsoft.Office.Interop.Excel.XlDirection.xlToLeft).Column;

***将Excel数据存入二维数组***
//rowsCount：最大行colsCount：最大列
Microsoft.Office.Interop.Excel.Rangec1=(Microsoft.Office.Interop.Excel.Range)xlsWorkSheet.Cells[1,1];
Microsoft.Office.Interop.Excel.Rangec2=(Microsoft.Office.Interop.Excel.Range)xlsWorkSheet.Cells[rowsCount,colsCount];
Rangerng=(Microsoft.Office.Interop.Excel.Range)xlsWorkSheet.get_Range(c1,c2);
object[,]exceldata=(object[,])rng.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);

//在第一列的左边插入一列
Excel.RangexlsColumns=(Excel.Range)xlsWorkSheet.Columns[1,System.Type.Missing];
xlsColumns.Insert(XlInsertShiftDirection.xlShiftToRight,Type.Missing);
//xlsSheetTemplateMajor_Meisai.Cells.get_Range(xlsSheetTemplateMajor_Meisai.Cells[1,1],xlsSheetTemplateMajor_Meisai.Cells[65535,1]).Insert(Type.Missing,Type.Missing);
Excel.Rangerng;
rng=worksheet.get_Range("A:A","A:A");
rng.Insert(Excel.XlDirection.xlToRight,Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
Excel.Rangerng=(Microsoft.Office.Interop.Excel.Range)xlsWorkSheet.Columns[12,Type.Missing];
rng.Insert(XlInsertShiftDirection.xlShiftToRight,XlInsertFormatOrigin.xlFormatFromLeftOrAbove);


//删除行
RangedeleteRng=(Range)xlsWorkSheetSapExcel.Rows[2,System.Type.Missing];
deleteRng.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);

//删除一列数据
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1,11]).Select();
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1,11]).EntireColumn.Delete(0);
((Excel.Range)xlsSheetShareMajor_Meisai.Cells[1,3]).EntireColumn.Delete(0);

//设置背景色为红色
xlsWorkSheet.get_Range("A1","A1").Interior.ColorIndex=3;

//设置Format属性属性值可以通过在vba中录宏得到
Microsoft.Office.Interop.Excel.Rangerange1=xlsWorkSheetAdd.get_Range("J1","J65535");
range1.NumberFormat="@";//文本格式
range1=xlsWorkSheetAdd.get_Range("L1","L65535");
range1.NumberFormat="0.00";//保留两位小数
Excel.Rangerng=xlsSheetShareMajor_Meisai.Columns["I",System.Type.Missing]asExcel.Range;
rng.NumberFormatLocal=@"yyyy/m/d";//设置日期格式


//替换
RangeDrng=xlsWorkSheetTemplate.get_Range("D1","D65535");
Drng.Replace("","",XlLookAt.xlPart,XlSearchOrder.xlByColumns,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing);
Drng.TextToColumns(Drng,Excel.XlTextParsingType.xlDelimited,Excel.XlTextQualifier.xlTextQualifierSingleQuote,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing);

//分列处理设置为文本
RangeTextToColumnRng=xlsWorkSheet.get_Range("E1","E65535");
xlsWorkSheet.get_Range("E1","E65535").TextToColumns(TextToColumnRng,Excel.XlTextParsingType.xlDelimited,Excel.XlTextQualifier.xlTextQualifierSingleQuote,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing);

//设置公式
rng=xlMergeFileWorkSheet.get_Range("D2","D"+rowcount);//设置列范围
rng.Formula=@"=IF(RC[-3]=""H"",""Survivor"",""Donor"")";//设置公式@的问题
//rng.NumberFormat="$0.00";//设置格式
copyRng=xlsSheetTemplateMajor_Meisai.get_Range("N3","N"+lastRowTemplate);
copyRng.Formula="=VLOOKUP(RC[-12],AR残!C[-13]:C[-11],2,0)";

//通过行、列的索引获取值
stringf=Convert.ToString(xlsSheetShareMajor_Meisai.get_Range(xlsSheetShareMajor_Meisai.Cells[2,1],xlsSheetShareMajor_Meisai.Cells[2,1]).Value2);

//筛选
//确定筛选范围
D1_rng=D1_TemSheet.Cells.get_Range(D1_TemSheet.Cells[1,1],D1_TemSheet.Cells[1,50]);
//执行筛选动作
rng.AutoFilter(5,"S",Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues,Type.Missing,true);
rng.AutoFilter(6,"H",Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr,"F",true);
D1_rng.AutoFilter(D1_Column+2,"#N/A",Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues,"#N/A",false);

//复制粘贴
D2_rng.Copy(Type.Missing);
D2_TemSheet.Cells.get_Range(D2_TemSheet.Cells[2,(D2_Column+1)],D2_TemSheet.Cells[2,(D2_Column+1)]).PasteSpecial(Excel.XlPasteType.xlPasteValues,Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,false,false);


//Find查找
rng=mySheet.get_Range("A1","IV10").Find(arrLabel[j],Type.Missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext,false,Type.Missing,Type.Missing);

//文字占满单元格
range.EntireColumn.AutoFit();

//另存
xlsWorkBook.SaveAs(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,XlSaveAsAccessMode.xlNoChange,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

***关闭对象***
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkBook);
excelApp.Quit();
Kill(excelApp);//调用方法关闭进程
GC.Collect();

///
///关闭Excel进程
///
publicclassKeyMyExcelProcess
{
[DllImport("User32.dll",CharSet=CharSet.Auto)]
publicstaticexternintGetWindowThreadProcessId(IntPtrhwnd,outintID);
publicstaticvoidKill(Microsoft.Office.Interop.Excel.Applicationexcel)
{
try
{
IntPtrt=newIntPtr(excel.Hwnd);//得到这个句柄，具体作用是得到这块内存入口
intk=0;
GetWindowThreadProcessId(t,outk);//得到本进程唯一标志k
System.Diagnostics.Processp=System.Diagnostics.Process.GetProcessById(k);//得到对进程k的引用
p.Kill();//关闭进程k
}
catch(System.Exceptionex)
{
throwex;
}
}
}


//关闭打开的Excel方法
publicvoidCloseExcel(Microsoft.Office.Interop.Excel.ApplicationExcelApplication,Microsoft.Office.Interop.Excel.WorkbookExcelWorkbook)
{
ExcelWorkbook.Close(false,Type.Missing,Type.Missing);
ExcelWorkbook=null;
ExcelApplication.Quit();
GC.Collect();
KeyMyExcelProcess.Kill(ExcelApplication);
}
总结
以上就是本文关于C#操作Excel代码总结的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！
C# 操作Excel代码总结

热门推荐

随机推荐
