Sub 替换()Dim VCell As Range Dim i As Integer Dim j As IntegerFor i = 1 To 1000 Set VCell = Sheet1.Cells(i, 1) For j = 1 To 12 If VCell.Characters(j, 1).Font.Bold = True Then Select Case VCell.Characters(j, 1).Text Case "0" VCell.Characters(j, 1).Text = "(" Case "1" VCell.Characters(j, 1).Text = "!" Case "2" VCell.Characters(j, 1).Text = "@" Case "3" VCell.Characters(j, 1).Text = "#" Case "4" VCell.Characters(j, 1).Text = "$" Case "5" VCell.Characters(j, 1).Text = "%" Case "6" VCell.Characters(j, 1).Text = "^" Case "7" VCell.Characters(j, 1).Text = "&" Case "8" VCell.Characters(j, 1).Text = "*" Case "9" VCell.Characters(j, 1).Text = "(" End Select End If Next j Next iEnd Sub 这是VBA 大致的一个代码！！提供参考！！
Dim i As Integer
Dim j As IntegerFor i = 1 To 1000
Set VCell = Sheet1.Cells(i, 1)
For j = 1 To 12
If VCell.Characters(j, 1).Font.Bold = True Then
Select Case VCell.Characters(j, 1).Text
Case "0"
VCell.Characters(j, 1).Text = "("
Case "1"
VCell.Characters(j, 1).Text = "!"
Case "2"
VCell.Characters(j, 1).Text = "@"
Case "3"
VCell.Characters(j, 1).Text = "#"
Case "4"
VCell.Characters(j, 1).Text = "$"
Case "5"
VCell.Characters(j, 1).Text = "%"
Case "6"
VCell.Characters(j, 1).Text = "^"
Case "7"
VCell.Characters(j, 1).Text = "&"
Case "8"
VCell.Characters(j, 1).Text = "*"
Case "9"
VCell.Characters(j, 1).Text = "("
End Select
End If
Next j
Next iEnd Sub
这是VBA 大致的一个代码！！提供参考！！
worksheetData.Cells[1, 1]=worksheetData.Cells[1, 1].ToString().Replace('0','('));
/// 导入方法
/// </summary>
/// <param name="fileName">导入文件地址</param>
/// <param name="fileName">读取Excel第几页</param>
private DataTable ReadFile(string filePath, int page)
{
try
{
string filename = filePath;
filename = filename.Substring(filename.LastIndexOf("\\") + 1);//截取文件的名字
string filetype = filePath.Substring(filePath.LastIndexOf(".") + 1);//截取文件的类型 //string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";//office2007
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;IMEX=1'";///office2003
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
Conn.Open(); System.Data.DataTable schemaTable = Conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[page - 1][2].ToString().Trim(); string strCom = "SELECT * FROM [" + tableName + "]";
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = new DataSet();
try
{
myCommand.Fill(ds, "TableName");
}
catch (Exception)
{
MessageBox.Show("Execl表格名称错误，导入失败,请把导入的Execl卡片名写成系统默认的\"sheet1\"！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.None);
return null;
}
Conn.Close();
//这里加循环判断把你该需要替换的都替换了，然后再存到一个DataTable里该怎么处理就怎么处理
DataTable dt=new DataTable();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//..................................处理....
}
return dt; //返回dataset
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
public void 替换()
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue = System.Reflection.Missing.Value;
Excel.Range range;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int i = 0; i < 1000; ++i)
{
range = (Excel.Range)xlWorkSheet.Cells[i, 1];
int TextLength = range.Text.ToString().Length;
for (int CharCount = 1; CharCount <= TextLength; ++CharCount)
{
Excel.Characters charToTest = range.get_Characters(CharCount, 1);
bool IsBold = (bool)charToTest.Font.Bold;
if (IsBold == true)
{
switch (charToTest.Text)
{
case "0":
charToTest.Text = "(";
break;
case "1":
charToTest.Text = "!";
break;
case "2":
charToTest.Text = "@";
break;
case "3":
charToTest.Text = "#";
break;
case "4":
charToTest.Text = "$";
break;
case "5":
charToTest.Text = "%";
break;
case "6":
charToTest.Text = "^";
break;
case "7":
charToTest.Text = "&";
break;
case "8":
charToTest.Text = "*";
break;
case "9":
charToTest.Text = "(";
break;
default:
break;
}
}
// other formatting tests here
}
}
}
NPOI参考
优点：NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。操作步骤：一、下载NPOI：http://down.gougou.com/down?cid=DAEA322D9D7F934B898077FB01C3A8CB02A746E6二、项目添加引用；三、首先把如下代码封装成一个ExcelHelper类；四、调用方法。 代码：（经测试） /// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportDataSetFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
{
DataSet ds = new DataSet();
HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
string sheetname = null;
HSSFSheet sheet = null;
try
{
sheetname = sheetName.Substring(0, sheetName.Length - 1);
sheet = workbook.GetSheet(sheetname);//"BlankSubtraction1" if (sheet != null)
{ DataTable table = new DataTable();
HSSFRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < 20; i++)
{
//string tst1 = headerRow.GetCell(1).ToString();
//string tst2 = headerRow.GetCell(1).StringCellValue.Trim(); if (headerRow.GetCell(i) != null)
{
//// 如果遇到第一个空列，则不再继续向后读取
//cellCount = i + 1;
//break;
DataColumn column = new DataColumn(i.ToString());
table.Columns.Add(column);
}
//DataColumn column = new DataColumn(i.ToString());//headerRow.GetCell(i).StringCellValue
//table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row != null)
{
DataRow dataRow = table.NewRow(); cellCount = row.Cells.Count; for (int j = row.FirstCellNum; j < 15; j++)
{
if (row.GetCell(j) != null)//row.GetCell(j) != null
{
string tst = row.GetCell(j).ToString();
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
}
ds.Tables.Add(table);
}
}
catch
{
}
excelFileStream.Close();
workbook = null;
return ds;
}
/// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary> /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public DataSet ImportDataSetFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
long tt = stream.Length;
//while (stream.Length() != null)
int ss = stream.ReadByte(); return ImportDataSetFromExcel(stream, sheetName, headerRowIndex);
}
}