详情请查看:http://www.cnblogs.com/lingyun_k/archive/2005/07/12/191740.aspx
这里我只描述我学到的一部分知识
主要包括3个代码快:
1.读取excel文件中的数据
2.将DataGridView控件中的数据导出到Excel,其他数据列表控件的操作本质一样
3.打开指定Excel文件并将数据导入到指定Excel文件中保存
1. 读取Excel
private DataSet GetExcel(string Path)
{
if (arratlist != null)
{
arratlist.Clear();
}
OleDbConnection objConn = null;
DataSet data = new DataSet();
try
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Path + ";Extended Properties='Excel 8.0'";
//;HDR=Yes;IMEX=1'";
objConn = new OleDbConnection(strConn);
objConn.Open();
System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
tableName = schemaTable.Rows[0][2].ToString().Trim();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
arratlist.Add(schemaTable.Rows[i][2].ToString().TrimStart('/'').Trim('/'', '$'));//读取品牌列表
}
arratlist.Sort();
string strSql = "Select * From [" + tableName + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter sqlada = new OleDbDataAdapter();
sqlada.SelectCommand = objCmd;
sqlada.Fill(data);
objConn.Close();
}
catch (Exception ex)
{
objConn.Close();
MessageBox.Show(ex.Message);
}
return data;
}
2.将dataGridView中的数据导出到Excel
private void button1_Click(object sender, EventArgs e)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbooks workbooks = excel.Workbooks;
Excel._Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Sheets sheets = workbook.Worksheets;
Excel._Worksheet worksheet = (Excel._Worksheet)sheets.get_Item(1);
int rowIndex = 1;
int colIndex = 1;
System.Data.DataTable dt = sysdt;
int row_cnt = dataGridResult.Rows.Count;
int col_cnt = dataGridResult.Columns.Count;
// 适用于用DataGridTableStyle自定义DataGrid时。
for (int j = 0; j < col_cnt; j++)
{
excel.Cells[rowIndex, j + 1] = dataGridResult.Columns[j].HeaderText;
}
rowIndex++;
//同样方法处理数据
for (int row = 0; row < row_cnt; row++)
{
colIndex = 1;
for (int col = 0; col < col_cnt; col++)
{
excel.Cells[rowIndex, colIndex] = dataGridResult[col, row].Value;
colIndex++;
}
rowIndex++;
}
//设置单元格数字内容显示格式
worksheet.Name = tableName.TrimEnd('$');
Excel.Range range;
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[row_cnt + 1, 1]);
range.NumberFormat = "0000"; //这里是因为需要把0012等在Excel上正确显示,默认会变成12,按需要可以忽略.
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //第一列居中
//range = worksheet.get_Range(worksheet.Cells[0, 0], worksheet.Cells[row_cnt + 1, col_cnt]);
// 内框及外框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
excel.Columns.AutoFit();
excel.Visible = true;
}
3.保存文件并退出excel
private void saveExcel(object sender, EventArgs e)
{
//输出Excel文件并退出
try
{
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
templetFile = filePath;//需要打开的excel文件的路径,套用代码请自行修改
outputFile = "F://12345.xls";//保存文件路径
Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing,missing,missing);
//得到WorkSheet对象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing);
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null;
workBook = null;
app = null;
GC.Collect();
}
catch (Exception ex)
{
throw ex;
}
}