导入导出excel表
c# 怎样导入导出excel表？（ 最好提供一个示例代码及教程） --------------------编程问答-------------------- 可以引用EXCEL的Com控件，但很麻烦。网上很多第三方操作的控件，自己可以找一下，比如Aspose.Cell，FarPoint,等 --------------------编程问答-------------------- 先要添加com控件，然后才能使用。以下是我做的一个系统输出到Execl表格的代码，供你参考：private void toolStripButton_print_Click(object sender, EventArgs e)
{
int RCount = dataGridView1.Rows.Count;
int CCount = dataGridView1.Columns.Count;
// 从工具箱中添加一个“保存”对话框
saveFileDialog1.DefaultExt = "xls";
saveFileDialog1.Filter = "EXCEL文件(*.xls)|*.xls ";
// EXcel文件名称
saveFileDialog1.FileName = "横向课题信息表";
saveFileDialog1.InitialDirectory = Directory.GetCurrentDirectory();
// 取消
if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
return;
}
string fileNameString = saveFileDialog1.FileName;
if (fileNameString.Length ==0)
{
MessageBox.Show("文件名不能为空！");
return;
}
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
/* if (MessageBox.Show("文件已存在，要替换吗？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.Cancel)
{
return;
}*/
try
{
file.Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
// 注意引用的添加
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
objExcel = new Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Type.Missing);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
objExcel.Visible = true;
Excel.Range myrang13 = objsheet.get_Range(objExcel.Cells[2,1],objExcel.Cells[1,8]);
myrang13.Font.Bold = true;
myrang13.Font.Size = "18";
objExcel.Cells[1,8] = "横向课题信息";
objExcel.Cells[2, 1] = "打印日期：" + DateTime.Now.ToShortDateString();
//向Excel中写入表格的表头
int displayColumnsCount = 1;
for (int i = 0; i <= dataGridView1.ColumnCount - 1; i++)
{
if (dataGridView1.Columns[i].Visible == true)
{
objExcel.Cells[3, displayColumnsCount] = dataGridView1.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
//向Excel中写入数据
for (int row = 0; row <= dataGridView1.RowCount - 1; row++)
{
displayColumnsCount = 1;
for (int col = 0; col < CCount; col++)
{
if (dataGridView1.Columns[col].Visible == true)
{
try
{
string value;
if (col == 4||col==7||col==11||col==12||col==14||col==16||col==18||col==20)
{
value = "'"+this.dataGridView1.Rows[row].Cells[col].Value.ToString().Trim();
}
else
value = this.dataGridView1.Rows[row].Cells[col].Value.ToString().Trim();
objExcel.Cells[row + 4, displayColumnsCount] =value;
//objExcel.Cells[row +4, displayColumnsCount] = this.dataGridView1.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
objWorkbook.SaveAs(fileNameString, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null) objWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "\n\n导出完毕！ ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
} --------------------编程问答-------------------- 也可以不用添加excel引用，而是直接用流来实现：
private void button3_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File To";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
//写标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dataGridView1.Columns[i].HeaderText;
}
sw.WriteLine(str);
//写内容
for (int j = 0; j < dataGridView1.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dataGridView1.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
}
//数据放在datagridview中，如果不需要datagridview则可直接放在dataset中，若用datagridview需加上这两句dataGridView1.AllowUserToAddRows = false;//在datagridview中不显示最下面带*的行
dataGridView1.AllowUserToDeleteRows = false;//不允许用户删除行这两行
--------------------编程问答-------------------- 楼上两位正解。 --------------------编程问答--------------------
这里有一个可以直接使用的导出Excel的方法，用html填充资料的
public void ExportToExcel(DataTable dt)
{
Response.Clear();
Response.Buffer= true;
Response.Charset="UTF-8";
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
string colHeaders="";
int colCount=dt.Columns.Count-1;
StringBuilder sb = new StringBuilder();
colHeaders="<table style=\"FONT-SIZE: 15px\" border=\"1\">";
sb.Append(colHeaders);
sb.Append("<tr >");
for(int i=0;i<=colCount;i++)
{
sb.Append("<td align=middle>");
sb.Append(dt.Columns[i].ColumnName.ToString());
sb.Append("</td>");
}
sb.Append("</tr >");
for(int i=0;i<dt.Rows.Count;i++)
{
sb.Append("<tr align=left>");
for(int j=0;j<=colCount;j++)
{
sb.Append("<td >");
sb.Append(BlankString(dt.Rows[i][j].ToString().Trim()));
//sb.Append(dt.Rows[i][j].ToString());
sb.Append("</td >");
}
sb.Append("</tr>");
}
colHeaders = sb.ToString();
colHeaders = colHeaders+ "\n";
Response.Write(colHeaders);
Response.Write("</body></html>");
Response.End();
} --------------------编程问答--------------------
不需要添加引用 --------------------编程问答-------------------- 学习............................. --------------------编程问答-------------------- .Net组件里面有Microsoft.Office.Interop.Excel，在引用里加进去，并using一下。就可以用Microsoft.Office.Interop.Excel.Application对象了
补充：.NET技术 , C#
