|
首先要確保Excel完全安裝 在安裝office2003的過程中要選擇增加DoNet支持 然后增加命令空間
using Excel = Microsoft.Office.Interop.Excel;
namespace FJPMIS.priceClient.Public
{
class ExportExcel
{
/// <summary>
/// 將DataGridView中的數(shù)據(jù)導(dǎo)出到Excel中，并加載顯示出來(無加載模板)
/// 只用于一般的導(dǎo)出Excel
/// </summary>
/// <param name="caption">要顯示的頁頭</param>
/// <param name="date">打印日期</param>
/// <param name="dgv">要進(jìn)行導(dǎo)出的DataGridView</param>
public void ExportToExcel(string caption, string date, DataGridView dgv)
{
//DataGridView可見列數(shù)
int visiblecolumncount = 0;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn))
{
visiblecolumncount++;
}
}
try
{
//當(dāng)前操作列的索引
int currentcolumnindex = 1;
//當(dāng)前操作行的索引
Excel.ApplicationClass Mylxls = new Excel.ApplicationClass();
Mylxls.Application.Workbooks.Add(true);
//Mylxls.Cells.Font.Size = 10.5; //設(shè)置默認(rèn)字體大小
//設(shè)置標(biāo)頭
Mylxls.Caption = caption;
//顯示表頭
Mylxls.Cells[1, 1] = caption;
//顯示時間
Mylxls.Cells[2, 1] = date;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) //如果顯示
{
Mylxls.Cells[3, currentcolumnindex] = dgv.Columns[i].HeaderText;
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //設(shè)置邊框
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).ColumnWidth = dgv.Columns[i].Width / 8;
//Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗體
//Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中顯示
currentcolumnindex++;
}
}
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并單元格
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30; //行高
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑體";
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14; //字體大小
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中顯示
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //左邊顯示
//Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12; //列寬度
object[,] dataArray = new object[dgv.Rows.Count, visiblecolumncount];
//當(dāng)前操作列的索引
//int currentcolumnindex = 1;
//當(dāng)前操作行的索引
for (int i = 0; i < dgv.Rows.Count; i++) //循環(huán)填充數(shù)據(jù)
{
currentcolumnindex = 1;
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible == true && (dgv.Columns[j] is DataGridViewTextBoxColumn))
{
if (dgv[j, i].Value != null) //如果單元格內(nèi)容不為空
{
dataArray[i, currentcolumnindex - 1] = dgv[j, i].Value.ToString();
}
currentcolumnindex++;
}
}
}
Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Value2 = dataArray; //設(shè)置邊框
Mylxls.get_Range(Mylxls.Cells[4, 1], Mylxls.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Cells.Borders.LineStyle = 1; //設(shè)置邊框
Mylxls.Visible = true;
}
catch
{
MessageBox.Show("信息導(dǎo)出失敗，請確認(rèn)你的機(jī)器上裝有Microsoft Office Excel 2003！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
}
}
/// <summary>
/// 將DataGridView中的數(shù)據(jù)導(dǎo)出到Excel中，并加載顯示出來(加載模板)
/// 僅用于導(dǎo)出已定義好模版的Excel導(dǎo)出，主要如“旬報表”，“月報表”等
/// 請注意：模板應(yīng)放在應(yīng)程序的PrintTemplate目錄下
/// </summary>
/// <param name="ModelName">模版的名稱</param>
/// <param name="Date">打印日期</param>
/// <param name="dgv">要進(jìn)行導(dǎo)出的DataGridView</param>
public void ExportToExcelByModel(string ModelName, string Date, DataGridView dgv)
{
Excel.Application m_objExcel = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
object m_objOpt = System.Reflection.Missing.Value;
try
{
m_objExcel = new Excel.Application();
string path = System.Windows.Forms.Application.StartupPath.ToString().Replace("http://bin//Debug", "") + "http://PrintTemplate//";
path = path + ModelName;
m_objBook = m_objExcel.Workbooks.Open(path, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
//填充日期
m_objExcel.Cells[2, 1] = Date.ToString();
//當(dāng)前操作列的索引
int currentcolumnindex = 1;
//當(dāng)前操作行的索引
int currentrowindex = 4;
for (int i = 0; i < dgv.Rows.Count; i++) //循環(huán)填充數(shù)據(jù)
{
currentcolumnindex = 1;
currentrowindex = 4 + i;
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible == true)
{
if (dgv[j, i].Value != null) //如果單元格內(nèi)容不為空
{
m_objExcel.Cells[currentrowindex, currentcolumnindex] = dgv[j, i].Value.ToString();
}
m_objExcel.get_Range(m_objExcel.Cells[currentrowindex, currentcolumnindex], m_objExcel.Cells[currentrowindex, currentcolumnindex]).Cells.Borders.LineStyle = 1; //設(shè)置邊框
currentcolumnindex++;
}
}
}
m_objExcel.DisplayAlerts = false;
m_objExcel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch
{
MessageBox.Show("信息導(dǎo)出失敗，請確認(rèn)你的機(jī)器上裝有Microsoft Office Excel 2003并且模版未被刪除！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
GC.Collect();
}
}
/// <summary>
/// 將DataGridView中的數(shù)據(jù)導(dǎo)出到Excel中，并加載顯示出來(無加載模板)
/// 只用于有合并項(xiàng)的導(dǎo)出，我這里所說的合并項(xiàng)是指如果有單元格的值為空時自動與上一行的單位格進(jìn)行合并
/// </summary>
/// <param name="caption">要顯示的頁頭</param>
/// <param name="date">打印日期</param>
/// <param name="dgv">要進(jìn)行導(dǎo)出的DataGridView</param>
/// <param name="MergeCount">合并的列數(shù),沒有用到</param>
public void ExportToExcelNullMerge(string caption, string date, DataGridView dgv, int MergeCount)
{
//DataGridView可見列數(shù)
int visiblecolumncount = 0;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn))
{
visiblecolumncount++;
}
}
try
{
//當(dāng)前操作列的索引
int currentcolumnindex = 1;
//當(dāng)前操作行的索引
int currentrowindex = 4;
Excel.ApplicationClass Mylxls = new Excel.ApplicationClass();
Mylxls.Application.Workbooks.Add(true);
//Mylxls.Cells.Font.Size = 10.5; //設(shè)置默認(rèn)字體大小
//設(shè)置標(biāo)頭
Mylxls.Caption = caption;
//顯示表頭
Mylxls.Cells[1, 1] = caption;
//顯示時間
Mylxls.Cells[2, 1] = date;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) //如果顯示
{
Mylxls.Cells[3, currentcolumnindex] = dgv.Columns[i].HeaderText;
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //設(shè)置邊框
//Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗體
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中顯示
currentcolumnindex++;
}
}
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并單元格
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30; //行高
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑體";
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14; //字體大小
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中顯示
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //左邊顯示
//Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12; //列寬度
for (int i = 0; i < dgv.Rows.Count; i++)
{
currentcolumnindex = 1;
currentrowindex = 4 + i;
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible == true && (dgv.Columns[j] is DataGridViewTextBoxColumn))
{
if (dgv[j, i].Value != null) //如果單元格內(nèi)容不為空
{
Mylxls.Cells[currentrowindex, currentcolumnindex] = dgv[j, i].Value.ToString();
}
Mylxls.get_Range(Mylxls.Cells[1, currentcolumnindex], Mylxls.Cells[1, currentcolumnindex]).ColumnWidth = dgv.Columns[j].Width / 8;
Mylxls.get_Range(Mylxls.Cells[currentrowindex, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).Cells.Borders.LineStyle = 1; //設(shè)置邊框
if (dgv.Rows[i].Cells[j].Value.ToString() == "")
{
Mylxls.get_Range(Mylxls.Cells[currentrowindex - 1, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).MergeCells = true;
//Mylxls.get_Range(Mylxls.Cells[i - 1, col + 2], Mylxls.Cells[rowIndex, col + 2]).MergeCells = true;
//Mylxls.get_Range(Mylxls.Cells[i - 1, col + 3], Mylxls.Cells[rowIndex, col + 3]).MergeCells = true;
}
currentcolumnindex++;
}
}
}
Mylxls.Visible = true;
}
catch
{
MessageBox.Show("信息導(dǎo)出失敗，請確認(rèn)你的機(jī)子上裝有Microsoft Office Excel 2003！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
}
}
/// <summary>
/// 將DataGridView中的數(shù)據(jù)導(dǎo)出到Excel中，并加載顯示出來(無加載模板)
/// 只用于有合并項(xiàng)的導(dǎo)出,并且可根據(jù)前面的DataGridViewCheckBoxColumn是否選擇進(jìn)行導(dǎo)出
/// </summary>
/// <param name="caption">要顯示的頁頭</param>
/// <param name="date">打印日期</param>
/// <param name="dgv">要進(jìn)行導(dǎo)出的DataGridView</param>
/// <param name="MergeCount">合并的列數(shù)</param>
public void ExportToExcelNullMergeHasCheckBox(string caption, string date, DataGridView dgv, int MergeCount)
{
//DataGridView可見列數(shù)
int visiblecolumncount = 0;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn))
{
visiblecolumncount++;
}
}
try
{
//當(dāng)前操作列的索引
int currentcolumnindex = 1;
//當(dāng)前操作行的索引
int currentrowindex = 4;
Excel.ApplicationClass Mylxls = new Excel.ApplicationClass();
Mylxls.Application.Workbooks.Add(true);
//Mylxls.Cells.Font.Size = 10.5; //設(shè)置默認(rèn)字體大小
//設(shè)置標(biāo)頭
Mylxls.Caption = caption;
//顯示表頭
Mylxls.Cells[1, 1] = caption;
//顯示時間
Mylxls.Cells[2, 1] = date;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) //如果顯示
{
Mylxls.Cells[3, currentcolumnindex] = dgv.Columns[i].HeaderText;
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //設(shè)置邊框
//Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗體
Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中顯示
currentcolumnindex++;
}
}
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).MergeCells = true; //合并單元格
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).RowHeight = 30; //行高
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑體";
//Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14; //字體大小
Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, visiblecolumncount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中顯示
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).MergeCells = true; //合并
Mylxls.get_Range(Mylxls.Cells[2, 1], Mylxls.Cells[2, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //左邊顯示
//Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12; //列寬度
currentrowindex = 3;
for (int i = 0; i < dgv.Rows.Count; i++)
{
DataGridViewCheckBoxCell dgvc = new DataGridViewCheckBoxCell();
dgvc = (DataGridViewCheckBoxCell)dgv[0, i];
if (dgvc.FormattedValue.ToString() == "True")
{
currentcolumnindex = 1;
currentrowindex++;
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible == true && (dgv.Columns[j] is DataGridViewTextBoxColumn))
{
if (dgv[j, i].Value != null) //如果單元格內(nèi)容不為空
{
Mylxls.Cells[currentrowindex, currentcolumnindex] = dgv[j, i].Value.ToString();
}
Mylxls.get_Range(Mylxls.Cells[1, currentcolumnindex], Mylxls.Cells[1, currentcolumnindex]).ColumnWidth = dgv.Columns[j].Width / 8;
Mylxls.get_Range(Mylxls.Cells[currentrowindex, currentcolumnindex], Mylxls.Cells[currentrowindex, currentcolumnindex]).Cells.Borders.LineStyle = 1; //設(shè)置邊框
if (dgv.Rows[i].Cells[j].Value.ToString() == "")
{
for (int k = 0; k < MergeCount; k++)
{
Mylxls.get_Range(Mylxls.Cells[currentrowindex - 1, currentcolumnindex + k], Mylxls.Cells[currentrowindex, currentcolumnindex + k]).MergeCells = true;
}
//Mylxls.get_Range(Mylxls.Cells[i - 1, col + 2], Mylxls.Cells[rowIndex, col + 2]).MergeCells = true;
//Mylxls.get_Range(Mylxls.Cells[i - 1, col + 3], Mylxls.Cells[rowIndex, col + 3]).MergeCells = true;
}
currentcolumnindex++;
}
}
}
}
Mylxls.Visible = true;
}
catch
{
MessageBox.Show("信息導(dǎo)出失敗，請確認(rèn)機(jī)器已經(jīng)完全安裝Microsoft Office Excel 2003！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
}
}
}
}
private void Btn_Export_Click(object sender, EventArgs e)
{
FJPMIS.priceClient.Public.ExportExcel ExpExcel = new FJPMIS.priceClient.Public.ExportExcel();
ExpExcel.ExportToExcel("數(shù)據(jù)導(dǎo)出",DateTime.Now.ToString(),this.dataGridView1);
}
|
