怎么将griview中的数据导入到excel中？？？并且提示保存路径
大家帮我一个忙呗，怎么将gridview中的数据导入到excel中去》？尽量有代码，有图有真相！ 我在自学，可是在网上找了好多资料，还是没有办法解决？
我现在的代码如下：
protected void Button1_Click(object sender, EventArgs e)
{
Export("application/ms-excel", "客户信息.xls");
}
private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
} gridview excel 数据
[解决办法]
你这个不是已经实现了吗？
[解决办法]
public class ExcelHelper
{
private Excel.Application _excelApp = null;
private Excel.Workbooks _books = null;
private Excel._Workbook _book = null;
private Excel.Sheets _sheets = null;
private Excel._Worksheet _sheet = null;
private Excel.Range _range = null;
private Excel.Font _font = null;
private object _optionalValue = Missing.Value;
private void ReleaseCOM(object pObj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
}
catch
{
throw new Exception("释放资源时发生错误！");
}
finally
{
pObj = null;
}
}
public void SaveToExcel(string excelName, DataTable dataTable)
{
try
{
if (dataTable != null)
{
if (dataTable.Rows.Count != 0)
{
Mouse.SetCursor(Cursors.Wait);
CreateExcelRef();
FillSheet(dataTable);
SaveExcel(excelName);
Mouse.SetCursor(Cursors.Arrow);
}
}
}
catch (Exception e)
{
ErrorFrame eFrame = new ErrorFrame("Error while generating Excel report");
eFrame.ShowDialog();
}
finally
{
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
ReleaseCOM(_excelApp);
}
}
private void SaveExcel(string excelName)
{
_excelApp.Visible = true;
_book.SaveAs(excelName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
//将数据填充到内存Excel的工作表
private void FillSheet(DataTable dataTable)
{
object[] header = CreateHeader(dataTable);
WriteData(header, dataTable);
}
// 绘制表头
private void WriteData(object[] header, DataTable dataTable)
{
object[,] objData = new object[dataTable.Rows.Count, header.Length];
for (int j = 0; j < dataTable.Rows.Count; j++)
{
var item = dataTable.Rows[j];
for (int i = 0; i < header.Length; i++)
{
var y = dataTable.Rows[j][i];
objData[j, i] = (y == null) ? "" : y.ToString();
}
}
AddExcelRows("A2", dataTable.Rows.Count, header.Length, objData);
AutoFitColumns("A1", dataTable.Rows.Count + 1, header.Length);
}
private void AutoFitColumns(string startRange, int rowCount, int colCount)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.Columns.AutoFit();
}
private object[] CreateHeader(DataTable dataTable)
{
List<object> objHeaders = new List<object>();
for (int n = 0; n < dataTable.Columns.Count; n++)
{
objHeaders.Add(dataTable.Columns[n].ColumnName);
}
var headerToAdd = objHeaders.ToArray();
//工作表的单元是从“A1”开始
AddExcelRows("A1", 1, headerToAdd.Length, headerToAdd);
SetHeaderStyle();
return headerToAdd;
}
/// 将表头加粗显示
private void SetHeaderStyle()
{
_font = _range.Font;
_font.Bold = true;
}
/// 将数据填充到Excel工作表的单元格中
private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.set_Value(_optionalValue, values);
}
/// 创建一个Excel程序实例
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
public DataTable LoadExcel(string pPath)
{
string strCon = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", pPath);
OleDbConnection myConn = new OleDbConnection(strCon);
string sheetName = this.GetExcelSheetName(pPath);
string sql = "select * from [" + sheetName.Replace('.', '#') + "$]";
myConn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(sql, myConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
return ds.Tables[0];
}
catch (Exception x)
{
ds = null;
ErrorFrame eFrame = new ErrorFrame("从Excel文件中获取数据时发生错误！");
eFrame.ShowDialog();
return new System.Data.DataTable();
}
finally
{
myConn.Close();
}
}
private string GetExcelSheetName(string pPath)
{
_excelApp = new Excel.Application();
if (_excelApp == null)
{
ErrorFrame eFrame = new ErrorFrame("打开Excel应用时发生错误！");
eFrame.ShowDialog();
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
// 将Excel另存为Excel文件
private string XmlChangeXls(string filename)
{
object missing = System.Reflection.Missing.Value;
Excel.Application excel = new Excel.Application();
excel.Visible = false;
Excel.Workbooks oBooks = excel.Workbooks;
oBooks.Open(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Excel.Workbook oBook = excel.ActiveWorkbook;
string newfilename = System.Windows.Forms.Application.StartupPath + @"\TempFile" + Guid.NewGuid().ToString() + ".xls";//filename.Substring(0, filename.LastIndexOf('.')) + "-B.xls";
oBook.SaveAs(newfilename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
excel.DisplayAlerts = false;
oBook.Close(false, missing, false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
return newfilename;
}
// 将Excel另存为标准的Excel并加载
public DataTable ReadExcelFile(string fileName)
{
string newFileName = XmlChangeXls(fileName);
return LoadExcel(newFileName);
}
}
[解决办法]
http://blog.csdn.net/jim_qiang/article/details/8694172
[解决办法]
http://blog.csdn.net/rui_china/article/details/10405069
