在项目中经常遇到读写EXCEL文件的需求。其实读取EXCEL很简单, 首先使用命名空间NExcel,然后加载文件到Workbook,循环读取就行了。
using NExcel;
Workbook wb = Workbook.getWorkbook(fileStream);
Sheet sheet = wb.Sheets[0];
int rowsCount = sheet.Rows;
for (int row = 2; row < rowsCount; row++)
{
string sheet.getCell(0, row).Contents;
decimal TotalPrice = Convert.ToDecimal(sheet.getCell(1, row).Value);
}
1:工程对excel类库的导入,如:c:\program files\Microsoft office\offiece11\excel.exe
2:命名控件的引入: using Microsoft.office.Interop.Excel;
3:如果是对一个已经存在的excel文件进行操作则:
Application app=new Application();
Workbook wbook=app.Workbooks.Open("c:\\temp.xls",Type.Missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing);
Worksheet worksheet=(Worksheet)wbook.Worksheets[1];
4:如果是新建一个excel文件:
Application app=new Application();
Workbook wbook=app.Workbook.Add(Type.missing);
Worksheet worksheet=(Worksheet)wbook.Worksheets[1];
5:设置某个单元格里的内容:
worksheet.Cells[1,2]="列内容"
6读取某个单元格里的内容
string temp=((Range)worksheet.Cells[1,2]).Text;
7设置某个单元格里的格式
Excel.Range rtemp=worksheet.get_Range("A1","A1");
rtemp.Font.Name="宋体";
rtemp.Font.FontStyle="加粗";
rtemp.Font.Size=5;
8 保存新建的内容:
worksheet.SaveAs("c:\\temp.xls",Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing);
2:命名控件的引入: using Microsoft.office.Interop.Excel;
3:如果是对一个已经存在的excel文件进行操作则:
Application app=new Application();
Workbook wbook=app.Workbooks.Open("c:\\temp.xls",Type.Missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing);
Worksheet worksheet=(Worksheet)wbook.Worksheets[1];
4:如果是新建一个excel文件:
Application app=new Application();
Workbook wbook=app.Workbook.Add(Type.missing);
Worksheet worksheet=(Worksheet)wbook.Worksheets[1];
5:设置某个单元格里的内容:
worksheet.Cells[1,2]="列内容"
6读取某个单元格里的内容
string temp=((Range)worksheet.Cells[1,2]).Text;
7设置某个单元格里的格式
Excel.Range rtemp=worksheet.get_Range("A1","A1");
rtemp.Font.Name="宋体";
rtemp.Font.FontStyle="加粗";
rtemp.Font.Size=5;
8 保存新建的内容:
worksheet.SaveAs("c:\\temp.xls",Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing,Type.missing);
4.C#读Excel并存入数据库
本文将介绍C#读Excel并存入数据库。直接从Excel中读取出来,然后对"供应商" select distinct,然后分别对每个供应商做表.
AD:
直接让C#读Excel,然后对"供应商" select distinct,然后分别对每个供应商做表。这样C#读Excel并存入数据库就完成了。
- protected void Page_Load(object sender, EventArgs e)
- {
- DataSet ds = ImportExcel(Server.MapPath("ExcelFile/供应商违约扣款.xls")); //C#读Excel
- GridView1.DataSource = ds.Tables["ExcelInfo"].DefaultView;
- GridView1.DataBind();
- ToDataBase(ds);
- }
- private DataSet ImportExcel(string strFileName)
- {
- if (strFileName == "") return null;
- string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
- "Data Source=" + strFileName + ";" +
- "Extended Properties=Excel 8.0;HDR=Yes;IMEX=1";
- OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT trim(供应商) as 供应商,零件名称,型号,批量,下线数,下线率,不合格原因,考核原因,考核金额 FROM [Sheet1$]", strConn);
- DataSet ExcelDs = new DataSet();
- try
- {
- ExcelDA.Fill(ExcelDs, "ExcelInfo");
- }
- catch (Exception err)
- {
- System.Console.WriteLine(err.ToString());
- }
- return ExcelDs;
- }
- private bool ToDataBase(DataSet ds)
- {
- DataTable dtSupplier = new DataTable("dtSupplier");
- DataView dv = ds.Tables[0].DefaultView;
- string[] column = { "供应商" };
- dtSupplier = dv.ToTable(true, column);
- for (int i = 0; i < dtSupplier.Rows.Count; i++)
- {
- DataRow[] r = ds.Tables[0].Select("供应商='" + dtSupplier.Rows[i]["供应商"].ToString() + "'");
- //插父表
- for (int j = 0; j < r.Length; j++)
- {
- string ItemName = r[j]["零件名称"].ToString();
- string scale = r[j]["型号"].ToString();
- string batch = r[j]["批量"].ToString();
- string downLine = r[j]["下线数"].ToString();
- string downPercent = r[j]["下线率"].ToString();
- string outReason = r[j]["不合格原因"].ToString();
- string reason = r[j]["考核原因"].ToString();
- string amt = r[j]["考核金额"].ToString();
- //插子表
- }
- //save
- }
- return true;
- }