/// <summary>
/// 导出gridview为EXCEL
/// </summary>
/// <param name="GridView1"></param>
public void Export(GridView GridView1)
{
//Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
object oMissing = System.Reflection.Missing.Value;
oExcel.Workbooks.Add(oMissing);
Excel.Workbook oBook = oExcel.Workbooks[1];
Excel.Worksheet oSheet = (Excel.Worksheet)oBook.Sheets[1];
oSheet.Name = "最终名单";//this.Title;
Excel.Range rg;
//String test = GridView1.Rows[0].Cells[0].Text;
for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
{
rg = ((Excel.Range)oSheet.Cells[1, j + 1]);
rg.FormulaR1C1 = GridView1.HeaderRow.Cells[j].Text;
}
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int j = 0; j < GridView1.Rows[0].Cells.Count; j++)
{
rg = ((Excel.Range)oSheet.Cells[i + 2, j + 1]);
rg.NumberFormatLocal = "@";//设置单元格格式为文本
rg.FormulaR1C1 = GridView1.Rows[i].Cells[j].Text;
}
}
rg = null;
string VirFileName = Guid.NewGuid().ToString() + ".xls";
oBook.SaveAs(Server.MapPath(VirFileName), Excel.XlFileFormat.xlExcel9795, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive,
oMissing, oMissing, oMissing, oMissing, oMissing);
oExcel.Workbooks.Close();
oExcel.Quit();
oSheet = null;
oBook = null;
oExcel = null;
GC.Collect();
Response.Redirect(VirFileName);
}
/// <summary>
/// Excel文档另存为
/// </summary>
/// <param name="fileName">保存完整路径加文件名</param>
/// <returns>保存成功返回True</returns>
public bool SaveAs(string fileName)
{
try
{
myWorkBook.SaveAs(fileName, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
return(true);
}
catch
{
return(false);
}
}
public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
{
//文件存在时先删除文件后再进行下一步操作
FileInfo file = new FileInfo(strFullFilePath);
if (file.Exists)
{
file.Delete();
}
int rowIndex = 3; //开始写入数据的单元格行
int colIndex = 0; //开始写入数据的单元格列
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.ApplicationClass mExcel = new Excel.ApplicationClass();
mExcel.Visible = false;
Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
Excel.Workbook mBook = (Excel.Workbook)(mBooks.Add(miss));
Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
er.Value2 = title;
try
{
foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
{
colIndex++;
mSheet.Cells[3, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows) //同样方法处理数据
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
if (colIndex == 2)
{
mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
}
else
{
mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//保存工作已写入数据的工作表
mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
// return true;
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
finally //finally中的代码主要用来释放内存和中止进程()
{
mBook.Close(false, miss, miss);
mBooks.Close();
mExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
GC.Collect();
}
//return false;
}
/// <summary>
/// 将二维数组数据写入Excel文件（套用模板并分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2...”</param>
public void ArrayToExcel(string[,] arr, int rows, int top, int left, string sheetPrefixName)
{
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
{
sheetPrefixName = "Sheet";
}
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
}
#region 将二维数组数据写入Excel
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
{
endRow = rowCount;
}
//获取要写入数据的WorkSheet对象，并重命名
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
sheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
txtAuthor.Text = "lingyun_k";
txtDate.Text = DateTime.Now.ToShortDateString();
txtVersion.Text = "1.0.0.0";
}
#endregion
//输出Excel文件并退出
try
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, 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 e)
{
throw e;
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
public void SaveAs(string path)
{
wb.SaveAs(path);
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public static bool saveDtToExcel(System.Data.DataTable dt, String filePath)
{
ApplicationClass app = new ApplicationClass();
app.Visible = false;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = (Worksheet)wBook.Worksheets[1];
if (dt == null)
{
MessageBox.Show("数据源为空，无法导出。", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
//修改wSheet的名称。
if (!String.IsNullOrEmpty(dt.TableName))
{
wSheet.Name = dt.TableName;
}
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
//写标题
try
{
//每行格式设置，注意标题占一行。
Range range = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[rowCount + 1, colCount + 1]);
//设置单元格为文本。
range.NumberFormatLocal = "@";
//水平对齐方式
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
for (int j = 0; j < colCount; j++)
{
wSheet.Cells[1, j + 1] = dt.Columns[j].ColumnName.ToString();
}
if (rowCount > 0)
{
//逐行写数据。
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
String str = dt.Rows[i][j].ToString();
wSheet.Cells[i + 2, j + 1] = str;
}
}
}
//自动调整列宽
range.EntireColumn.AutoFit();
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存excel文档并关闭
wBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wBook.Close(true, filePath, Type.Missing);
//退出Excel程序
app.Quit();
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//调用GC的垃圾收集方法
GC.Collect();
GC.WaitForPendingFinalizers();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "提示消息:", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
finally
{
}
}
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
public static void DataGridViewToExcel(MultiColHeaderDgv dgv)
{
#region
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Excel.Application objExcel = new Excel.Application();
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Excel.ApplicationClass();
//objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
//objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
//Excel.Range
Excel.Range range1 = objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[3, 1]);
Excel.Range range2 = objExcel.get_Range(objExcel.Cells[1, 2], objExcel.Cells[3, 2]);
Excel.Range range3 = objExcel.get_Range(objExcel.Cells[1, 3], objExcel.Cells[3, 3]);
Excel.Range range4 = objExcel.get_Range(objExcel.Cells[1, 4], objExcel.Cells[3, 4]);
Excel.Range range5 = objExcel.get_Range(objExcel.Cells[1, 5], objExcel.Cells[3, 5]);
Excel.Range range6 = objExcel.get_Range(objExcel.Cells[1, 6], objExcel.Cells[3, 6]);
Excel.Range range7 = objExcel.get_Range(objExcel.Cells[1, 7], objExcel.Cells[3, 7]);
Excel.Range range8 = objExcel.get_Range(objExcel.Cells[2, 8], objExcel.Cells[3, 8]);
Excel.Range range9 = objExcel.get_Range(objExcel.Cells[1, 8], objExcel.Cells[1, 11]);
Excel.Range range10 = objExcel.get_Range(objExcel.Cells[2, 9], objExcel.Cells[2, 11]);
range1.Merge(0);
range2.Merge(0);
range3.Merge(0);
range4.Merge(0);
range5.Merge(0);
range6.Merge(0);
range7.Merge(0);
range8.Merge(0);
range9.Merge(0);
range9.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range10.Merge(0);
range10.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range1.NumberFormatLocal = "@"; //设置单元格格式为文本
range2.NumberFormatLocal = "@"; //设置单元格格式为文本
range3.NumberFormatLocal = "@"; //设置单元格格式为文本
range4.NumberFormatLocal = "@"; //设置单元格格式为文本
range5.NumberFormatLocal = "@"; //设置单元格格式为文本
range6.NumberFormatLocal = "@"; //设置单元格格式为文本
range7.NumberFormatLocal = "@"; //设置单元格格式为文本
range8.NumberFormatLocal = "@"; //设置单元格格式为文本
range9.NumberFormatLocal = "@"; //设置单元格格式为文本
objsheet.Cells[1, 1] = "财务分类";
objsheet.Cells[1, 2] = "项目代码";
objsheet.Cells[1, 3] = "项目名称";
objsheet.Cells[1, 4] = "项目内涵";
objsheet.Cells[1, 5] = "除外内容";
objsheet.Cells[1, 6] = "计价单位";
objsheet.Cells[1, 7] = "说明";
objsheet.Cells[2, 8] = "省定价";
objsheet.Cells[3, 9] = "三档";
objsheet.Cells[3, 10] = "二档";
objsheet.Cells[3, 11] = "一档";
objsheet.Cells[1, 8] = "价格（元）";
objsheet.Cells[2, 9] = "市定价格";
range2.ColumnWidth = 12; //设置单元格的宽度
range3.ColumnWidth = 20; //设置单元格的宽度
range4.ColumnWidth = 35; //设置单元格的宽度
range5.ColumnWidth = 25; //设置单元格的宽度
range7.ColumnWidth = 25; //设置单元格的宽度
range2.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range4.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range5.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range7.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
Excel.Range rangGol = objsheet.get_Range("A1", "K" + dgv.RowCount.ToString());
rangGol.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
rangGol.WrapText = true;
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
//tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 4, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
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);
}