问题描述
100分，.net中如何实现遍历excel中所有sheet，把全角转成半角？
解决方案
解决方案二：
操作excel的一些函数，你也许用的到ImportsSystemImportsSystem.DataImportsSystem.ConfigurationImportsSystem.WebNamespaceExcelEditPublicClassExcelEditPublicmFilenameAsStringPublicappAsExcel.ApplicationPublicwbsAsExcel.WorkbooksPublicwbAsExcel.WorkbookPublicwssAsExcel.WorksheetsPublicwsAsExcel.WorksheetPublicSubNew()EndSubPublicSubCreate()'创建一个Excel对象app=NewExcel.Applicationwbs=app.Workbookswb=wbs.Add(True)EndSubPublicSubOpen(ByValFileNameAsString)'打开一个Excel文件app=NewExcel.Applicationwbs=app.Workbookswb=wbs.Add(FileName)'wb=wbs.Open(FileName,0,true,5,"","",true,Excel.XlPlatform.xlWindows,"t",false,false,0,true,Type.Missing,Type.Missing);'wb=wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);mFilename=FileNameEndSubPublicFunctionGetSheet(ByValSheetNameAsString)AsExcel.Worksheet'获取一个工作表DimsAsExcel.Worksheet=DirectCast(wb.Worksheets(SheetName),Excel.Worksheet)ReturnsEndFunctionPublicFunctionAddSheet(ByValSheetNameAsString)AsExcel.Worksheet'添加一个工作表DimsAsExcel.Worksheet=DirectCast(wb.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing),Excel.Worksheet)s.Name=SheetNameReturnsEndFunctionPublicSubDelSheet(ByValSheetNameAsString)'删除一个工作表DirectCast(wb.Worksheets(SheetName),Excel.Worksheet).Delete()EndSubPublicFunctionReNameSheet(ByValOldSheetNameAsString,ByValNewSheetNameAsString)AsExcel.Worksheet'重命名一个工作表一DimsAsExcel.Worksheet=DirectCast(wb.Worksheets(OldSheetName),Excel.Worksheet)s.Name=NewSheetNameReturnsEndFunctionPublicFunctionReNameSheet(ByValSheetAsExcel.Worksheet,ByValNewSheetNameAsString)AsExcel.Worksheet'重命名一个工作表二Sheet.Name=NewSheetNameReturnSheetEndFunctionPublicSubSetCellValue(ByValwsAsExcel.Worksheet,ByValxAsInteger,ByValyAsInteger,ByValvalueAsObject)'ws：要设值的工作表X行Y列value值ws.Cells(x,y)=valueEndSubPublicSubSetCellValue(ByValwsAsString,ByValxAsInteger,ByValyAsInteger,ByValvalueAsObject)'ws：要设值的工作表的名称X行Y列value值GetSheet(ws).Cells(x,y)=valueEndSubPublicSubSetCellProperty(ByValwsAsExcel.Worksheet,ByValStartxAsInteger,ByValStartyAsInteger,ByValEndxAsInteger,ByValEndyAsInteger,ByValsizeAsInteger,_ByValnameAsString,ByValcolorAsExcel.Constants,ByValHorizontalAlignmentAsExcel.Constants)'设置一个单元格的属性字体，大小，颜色，对齐方式name="宋体"size=12color=Excel.Constants.xlAutomaticHorizontalAlignment=Excel.Constants.xlRightws.get_Range(ws.Cells(Startx,Starty),ws.Cells(Endx,Endy)).Font.Name=namews.get_Range(ws.Cells(Startx,Starty),ws.Cells(Endx,Endy)).Font.Size=sizews.get_Range(ws.Cells(Startx,Starty),ws.Cells(Endx,Endy)).Font.Color=colorws.get_Range(ws.Cells(Startx,Starty),ws.Cells(Endx,Endy)).HorizontalAlignment=HorizontalAlignmentEndSubPublicSubSetCellProperty(ByValwsnAsString,ByValStartxAsInteger,ByValStartyAsInteger,ByValEndxAsInteger,ByValEndyAsInteger,ByValsizeAsInteger,_ByValnameAsString,ByValcolorAsExcel.Constants,ByValHorizontalAlignmentAsExcel.Constants)'name="宋体";'size=12;'color=Excel.Constants.xlAutomatic;'HorizontalAlignment=Excel.Constants.xlRight;DimwsAsExcel.Worksheet=GetSheet(wsn)ws.get_Range(ws.Cells(Startx,Starty),ws.Cells(Endx,Endy)).Font.Name=namews.get_Range(ws.Cells(Startx,Starty),ws.Cells(Endx,Endy)).Font.Size=sizews.get_Range(ws.Cells(Startx,Starty),ws.Cells(Endx,Endy)).Font.Color=colorws.get_Range(ws.Cells(Startx,Starty),ws.Cells(Endx,Endy)).HorizontalAlignment=HorizontalAlignmentEndSubPublicSubUniteCells(ByValwsAsExcel.Worksheet,ByValx1AsInteger,ByValy1AsInteger,ByValx2AsInteger,ByValy2AsInteger)'合并单元格ws.get_Range(ws.Cells(x1,y1),ws.Cells(x2,y2)).Merge(Type.Missing)EndSubPublicSubUniteCells(ByValwsAsString,ByValx1AsInteger,ByValy1AsInteger,ByValx2AsInteger,ByValy2AsInteger)'合并单元格GetSheet(ws).get_Range(GetSheet(ws).Cells(x1,y1),GetSheet(ws).Cells(x2,y2)).Merge(Type.Missing)EndSubPublicSubInsertTable(ByValdtAsSystem.Data.DataTable,ByValwsAsString,ByValstartXAsInteger,ByValstartYAsInteger)'将内存中数据表格插入到Excel指定工作表的指定位置为在使用模板时控制格式时使用一ForiAsInteger=0Todt.Rows.Count-1ForjAsInteger=0Todt.Columns.Count-1GetSheet(ws).Cells(startX+i,j+startY)=dt.Rows(i)(j).ToString()NextNextEndSubPublicSubInsertTable(ByValdtAsSystem.Data.DataTable,ByValwsAsExcel.Worksheet,ByValstartXAsInteger,ByValstartYAsInteger)'将内存中数据表格插入到Excel指定工作表的指定位置二ForiAsInteger=0Todt.Rows.Count-1ForjAsInteger=0Todt.Columns.Count-1ws.Cells(startX+i,j+startY)=dt.Rows(i)(j)NextNextEndSubPublicSubAddTable(ByValdtAsSystem.Data.DataTable,ByValwsAsString,ByValstartXAsInteger,ByValstartYAsInteger)'将内存中数据表格添加到Excel指定工作表的指定位置一ForiAsInteger=0Todt.Rows.Count-1ForjAsInteger=0Todt.Columns.Count-1GetSheet(ws).Cells(i+startX,j+startY)=dt.Rows(i)(j)NextNextEndSub
解决方案三：
接楼上PublicSubAddTable(ByValdtAsSystem.Data.DataTable,ByValwsAsExcel.Worksheet,ByValstartXAsInteger,ByValstartYAsInteger)'将内存中数据表格添加到Excel指定工作表的指定位置二ForiAsInteger=0Todt.Rows.Count-1ForjAsInteger=0Todt.Columns.Count-1ws.Cells(i+startX,j+startY)=dt.Rows(i)(j)NextNextEndSubPublicSubInsertPictures(ByValFilenameAsString,ByValwsAsString,ByValleftAsInteger,ByValtopAsInteger,ByValHeightAsInteger,ByValWidthAsInteger)'//插入图片操作三GetSheet(ws).Shapes.AddPicture(Filename,Office.MsoTriState.msoFalse,Office.MsoTriState.msoTrue,10,10,150,150)GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left)GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top)GetSheet(ws).Shapes.get_Range(Type.Missing).Height=HeightGetSheet(ws).Shapes.get_Range(Type.Missing).Width=WidthEndSubPublicSubInsertline(ByValwsAsString,ByValstartxAsInteger,ByValstartyAsInteger,ByValendxAsInteger,ByValendyAsInteger)'//插入图片操作三GetSheet(ws).Shapes.AddLine(startx,starty,endx,endy).Select()GetSheet(ws).Selection.ShapeRange.Line.EndArrowheadStyle=Office.MsoArrowheadStyle.msoArrowheadDiamondGetSheet(ws).Selection.ShapeRange.Line.EndArrowheadLength=Office.MsoArrowheadLength.msoArrowheadLengthMediumGetSheet(ws).Selection.ShapeRange.Line.EndArrowheadWidth=Office.MsoArrowheadWidth.msoArrowheadWidthMediumGetSheet(ws).Selection.ShapeRange.Flip(Office.MsoFlipCmd.msoFlipHorizontal)app.CommandBars("AutoShapes").Visible=FalseDimaAsInteger=GetSheet(ws).get_Range(GetSheet(ws).Cells(startx,starty),GetSheet(ws).Cells(endx,endy)).widthEndSubPublicSubInsertActiveChart(ByValChartTypeAsExcel.XlChartType,ByValwsAsString,ByValDataSourcesX1AsInteger,ByValDataSourcesY1AsInteger,ByValDataSourcesX2AsInteger,ByValDataSourcesY2AsInteger,_ByValChartDataTypeAsExcel.XlRowCol)'插入图表操作ChartDataType=Excel.XlRowCol.xlColumnswb.Charts.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing)wb.ActiveChart.ChartType=ChartTypewb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells(DataSourcesX1,DataSourcesY1),GetSheet(ws).Cells(DataSourcesX2,DataSourcesY2)),ChartDataType)wb.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject,ws)EndSubPublicFunctionSave()AsBoolean'保存文档IfmFilename=""ThenReturnFalseElseTrywb.Save()ReturnTrueCatchexAsExceptionReturnFalseEndTryEndIfEndFunction'PublicFunctionSaveAs(ByValFileNameAsObject)AsBoolean''文档另存为'Try'wb.SaveAs(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,_'Excel.XlSaveAsAccessMode.xlExclusive,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing)'ReturnTrue'CatchexAsException'ReturnFalse'EndTry'EndFunctionPublicSubClose()'关闭一个Excel对象，销毁对象'wb.Save();wb.Close(Type.Missing,Type.Missing,Type.Missing)wbs.Close()app.Quit()wb=Nothingwbs=Nothingapp=NothingGC.Collect()EndSubEndClassEndNamespace
解决方案四：
参考http://topic.csdn.net/u/20081115/19/5860e8cb-78e1-4b54-b0f3-2f9da918bef6.htmlhttp://blog.csdn.net/brightgems/archive/2008/09/23/2968574.aspx全角转成半角http://www.cnblogs.com/onekey/archive/2007/07/11/814289.htmlhttp://www.cnblogs.com/jguangliang/articles/1328765.html