public void Open(string FileName)//open a Microsoft.Office.Interop.Excel file
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
mFilename = FileName;
}
public List <string> ExcelInfo(string path)
{
List <string> output = new List <string>();
try {
var excelApp = new Microsoft.Office.Interop.Excel.Application();
//Dont show Excel when open
excelApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks books = excelApp.Workbooks;
//Open Excel file by Path provided by the user
Microsoft.Office.Interop.Excel.Workbook sheet = books.Open(path);
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in sheet.Worksheets)
{
output.Add(worksheet.Name.ToString());
}
// accessing the desired worksheet in the dictionary
sheet.Close(true);
excelApp.Quit();
}
catch
{
//To be implemented
}
return(output);
}
//导出Execl明细按钮点击事件
private void button1_Click(object sender, EventArgs e)
{
if (tower_list.Rows.Count <= 0)
{
MessageBox.Show("该表中没有需要导出的数据！", "提示");
return;
}
string fileName = "楼盘信息表";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < tower_list.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = tower_list.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < tower_list.Rows.Count; r++)
{
for (int i = 0; i < tower_list.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = tower_list.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)//异常处理
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
public void ExcelWrite(string path, string worksheet, int Row, int Column, List <object> Values)
{
try
{
List <string> output = new List <string>();
var excelApp = new Microsoft.Office.Interop.Excel.Application();
//Dont show Excel when open
excelApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks books = excelApp.Workbooks;
//Open Excel file by Path provided by the user
Microsoft.Office.Interop.Excel.Workbook sheet = books.Open(path);
//Select worksheet by the name provided by the user
Microsoft.Office.Interop.Excel.Worksheet indsheet = sheet.Sheets[worksheet];
Microsoft.Office.Interop.Excel.Range activecell = indsheet.Cells[Row, Column];
for (int i = 0; i < Values.Count; i += 1)
{
indsheet.Cells[Row + i, Column] = Values[i].ToString();
}
sheet.Close(true);
excelApp.Quit();
}
catch
{ }
}
public bool DeleteExcelSheet(string pExcelPath, string pSheetName, out string pDeleteSheet)
{
try
{
object objOpt = Missing.Value;
//打开一个Excel应用
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook wb = wbs.Open(pExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.EnableAutoRecover = false;
Microsoft.Office.Interop.Excel.Sheets shs = wb.Sheets;
Microsoft.Office.Interop.Excel._Worksheet sh = (Microsoft.Office.Interop.Excel._Worksheet)shs.get_Item(pSheetName);
app.DisplayAlerts = false;
sh.Delete();
wb.Save();
pDeleteSheet = string.Empty;
return(true);
}
catch (Exception vErr)
{
pDeleteSheet = vErr.Message;
return(false);
}
finally
{
KillProcess();
}
}
public void Create()
{
app = new Microsoft.Office.Interop.Excel.Application();
//app.Visible = true;
wbs = app.Workbooks;
wb = wbs.Add(true);
ws = (Excel.Worksheet)wb.Sheets[1];
}
public void Open(string FileName)
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
}
public static void DataGridViewToExcel(DataGridView dataGridView) //点击导出到Excel表按钮
{
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//写入标题
for (int i = 0; i < dataGridView.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView.Rows.Count; r++)
{
for (int i = 0; i < dataGridView.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
private void ExportExcels(string fileName, DataGridView gridview)
{
try
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
// saveDialog.Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*","xls","Excel");
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Name = "吊运实绩";
Save2ExcelSheet(gridview, worksheet);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件导出保存成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// public void PrintExcel(string FileName, string ConnectionString, string coTitle, string sFontName, string Lang, string where,
// string where1, string where2, string where3, string where4, string where5, string where6, string where7, string where8,
// string where9, string where10, string User)
// {
// object oMissing = Missing.Value;
// Excel.Application oExcel = new Excel.ApplicationClass();
// Excel.Workbooks oBooks = oExcel.Workbooks;
// Excel._Workbook oBook = null;
//
// System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
// System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
// try
// {
// oBook = oBooks.Open(FileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
// oMissing, oMissing, oMissing, oMissing, oMissing);
// oExcel.Visible = true;
//
// //Cap nhat properties
// SetProperty(oBook, "ConnectionString", ConnectionString);
// SetProperty(oBook, "CoTitle", coTitle);
// SetProperty(oBook, "sFontName", sFontName);
// SetProperty(oBook, "Lang", Lang);
// SetProperty(oBook, "where", where);
// SetProperty(oBook, "where1", where1);
// SetProperty(oBook, "where2", where2);
// SetProperty(oBook, "where3", where3);
// SetProperty(oBook, "where4", where4);
// SetProperty(oBook, "where5", where5);
// SetProperty(oBook, "where6", where6);
// SetProperty(oBook, "where7", where7);
// SetProperty(oBook, "where8", where8);
// SetProperty(oBook, "where9", where9);
// SetProperty(oBook, "where10", where10);
// SetProperty(oBook, "User", User);
//
// try
// {
// RunMacro(oExcel, new object[]{"CreatReport"});
// }
// catch{}
// }
// catch(Exception ex)
// {
// System.Windows.Forms.MessageBox.Show(ex.Message, "Error!");
// }
// finally
// {
// System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
// System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
// GC.Collect();
// }
// }
public static void PrintExcel(string FileName, string ConnectionString, string coTitle, string sFontName, string Lang, string where,
string where1, string where2, string where3, string where4, string where5, string where6, string where7, string where8,
string where9, string where10, string where11, string User)
{
object oMissing = Missing.Value;
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks oBooks = oExcel.Workbooks;
Microsoft.Office.Interop.Excel._Workbook oBook = null;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
try
{
oBook = oBooks.Open(FileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing);
oExcel.Visible = true;
//Cap nhat properties
SetProperty(oBook, "ConnectionString", ConnectionString);
SetProperty(oBook, "CoTitle", coTitle);
SetProperty(oBook, "sFontName", sFontName);
SetProperty(oBook, "Lang", Lang);
SetProperty(oBook, "where", where);
SetProperty(oBook, "where1", where1);
SetProperty(oBook, "where2", where2);
SetProperty(oBook, "where3", where3);
SetProperty(oBook, "where4", where4);
SetProperty(oBook, "where5", where5);
SetProperty(oBook, "where6", where6);
SetProperty(oBook, "where7", where7);
SetProperty(oBook, "where8", where8);
SetProperty(oBook, "where9", where9);
SetProperty(oBook, "where10", where10);
SetProperty(oBook, "where11", where11);
SetProperty(oBook, "User", User);
try
{
RunMacro(oExcel, new object[] { "CreatReport" });
}
catch {}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "Error!");
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
GC.Collect();
}
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="pSaveFileName">默认文件名</param>
/// <param name="pDGVQ">数据源，一个页面上的DataGridView控件</param>
static public bool ExportExcel(string pSaveFileName, DataGridView pDGVQ, bool pMessageBox = true, string pWookSheetName = "Sheet")
{
Microsoft.Office.Interop.Excel.Application xlApp;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
//设置禁止弹出保存和覆盖的询问提示框
xlApp.DisplayAlerts = false;
xlApp.AlertBeforeOverwriting = false;
}
catch (Exception)
{
MessageBox.Show("无法创建Excel对象,请确认已安装Excel。", "对象创建错误", MessageBoxButtons.OK);
return(false);
}
finally
{
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
workbook.Worksheets.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet1.Name = pWookSheetName;
writeData(worksheet1, pDGVQ);
bool fileSaved = false;
if (pSaveFileName != "")
{
try
{
workbook.SaveAs(pSaveFileName, 56);
workbook.Saved = true;
fileSaved = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " 文件可能正在使用。", "导出错误", MessageBoxButtons.OK);
return(false);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && pMessageBox)
{
MessageBox.Show(pSaveFileName + " 导出成功", "提示", MessageBoxButtons.OK);
}
return(true);
}
public void Close()
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
/// <summary>
/// datagridview导出Excel
/// </summary>
/// <param name="fileName">文件夹名称</param>
/// <param name="myDGV">表格名称</param>
private void ExportExcels(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//写入标题
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public void Close()
//destory Microsoft.Office.Interop.Excel object
{
wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
public void Close()
//关闭一个Microsoft.Office.Interop.Excel对象，销毁对象
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
public List <List <string> > ExcelInfo(string path)
{
List <string> output = new List <string>();
try
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
//Dont show Excel when open
excelApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks books = excelApp.Workbooks;
//Open Excel file by Path provided by the user
Microsoft.Office.Interop.Excel.Workbook sheet = books.Open(path);
//Select worksheet by the name provided by the user
//Microsoft.Office.Interop.Excel.Worksheet indsheet = sheet.Sheets[worksheet];
Microsoft.Office.Interop.Excel.Worksheet indsheet = sheet.Worksheets[1];
//Microsoft.Office.Interop.Excel.Range range = indsheet.get_Range(CellStart, CellEnd);
//Get the used cell range in Excel
Microsoft.Office.Interop.Excel.Range range = indsheet.UsedRange;
object[,] cellValues = (object[, ])range.Value2;
output = cellValues.Cast <object>().ToList().ConvertAll(x => Convert.ToString(x));
//Get number of used columns
int columnCount = range.Columns.Count;
//Split list by column count
List <List <string> > list = new List <List <string> >();
for (int i = 0; i < output.Count; i += columnCount)
{
list.Add(output.GetRange(i, Math.Min(columnCount, output.Count - i)));
}
sheet.Close(true);
excelApp.Quit();
return(list);
}
catch
{
return(null);
}
}
/// <summary>
/// 将数据表 DataTable 转换为 Excel 工作簿
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="Cells">指定字段名</param>
public static Microsoft.Office.Interop.Excel.Workbook DataTableToWorkBook(System.Data.DataTable dt, string[] Cells)
{
if (dt.Columns.Count < 1)
{
throw new Exception("_Excel.DataTableToWorkBook 方法提供的 DataTable 参数找不到任何可用的列。");
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
// 写入列标题
for (int i = 0; i < dt.Columns.Count; i++)
{
if ((Cells != null) && (Cells.Length > i) && (!String.IsNullOrEmpty(Cells[i])))
{
worksheet.Cells[1, i + 1] = Cells[i].Trim();
}
else
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; //自动调整行高
}
// 写入内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
return(workbook);
}
private void SaveWorkbook()
{
string path = Path.GetDirectoryName(iFileName);
System.IO.DirectoryInfo dirInfo = new System.IO.DirectoryInfo(path);
if (!dirInfo.Exists)
{
dirInfo.Create();
}
System.IO.FileInfo fileInfo = new System.IO.FileInfo(iFileName);
if (fileInfo.Exists)
{
try
{
fileInfo.Delete();
}
catch (Exception)
{
}
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = iExcelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.get_Item(workbooks.Count);
try
{
workbook.SaveAs(iFileName,
Excel.XlFileFormat.xlExcel9795,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
}
catch (System.IO.IOException)
{
}
workbook.Close(false, Type.Missing, Type.Missing);
}
protected void ExportExcel(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp =
new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI =
System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture =
new System.Globalization.CultureInfo("th-TH");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
xlApp.Visible = true;
}
public void ExportExcel(string filename, DataTable data, System.Collections.Generic.IList <string> columnNames)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
worksheet.Cells.NumberFormatLocal = "@";
for (int i = 0; i < columnNames.Count; i++)
{
worksheet.Cells[1, i + 1] = columnNames[i].ToString();
}
for (int j = 1; j <= data.Rows.Count; j++)
{
for (int n = 1; n <= data.Columns.Count; n++)
{
//worksheet.Cells[j + 1, n] = "'"+data.Rows[j - 1][n - 1].ToString();
worksheet.Cells[j + 1, n] = data.Rows[j - 1][n - 1].ToString();
}
}
object objOpt = System.Reflection.Missing.Value;
workbook.SaveAs(filename, objOpt, objOpt, objOpt, objOpt, objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, objOpt, objOpt, objOpt, objOpt, objOpt);
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
xlApp.Quit();
xlApp = null;
workbook = null;
worksheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
public static void DataGridExport(DataGrid dataGrid, string fileName, string sheetName = "查询结果")
{
string tempPath = Environment.CurrentDirectory + "/temp";
if (!Directory.Exists(tempPath))
{
Directory.CreateDirectory(tempPath);
}
fileName = tempPath + "/" + fileName;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
worksheet.Name = sheetName;
//写入行
//+++++++++为提高效率，采用复制粘贴的方式写入数据+++++++++++++
for (int i = 0; i < dataGrid.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dataGrid.Columns[i].Header;
}
for (int r = 0; r < dataGrid.Items.Count; r++)
{
for (int i = 0; i < dataGrid.Columns.Count; i++)
{
string value = (dataGrid.Columns[i].GetCellContent(dataGrid.Items[r]) as TextBlock)?.Text ?? (r + 1).ToString(); //读取DataGrid某一行某一列的信息内容，与DataGridView不同的地方
worksheet.Cells[r + 2, i + 1] = "'" + value;
}
}
worksheet.Columns.EntireColumn.AutoFit();
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
xlApp.Visible = true;
}
public string ExportExcel(DataSet ds, string saveFileName)
{
try
{
if (ds == null)
{
return("数据库为空");
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return("无法创建Excel对象，可能您的机子未安装Excel");
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入字段
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
}
//写入数值
for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
return("成功保存到Excel");
}
catch (Exception ex)
{
return(ex.ToString());
}
}
/// <summary>
/// 导出Excel 的方法
/// </summary>
private void tslExport_Excel(string fileName, DataGridView myDGV)
{
string saveFileName = "";
//bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
rang.NumberFormat = "000000000000";
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
// if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
}
private void button9_Click(object sender, EventArgs e)
{
string connStr = "Data Source=WHM;Initial Catalog=A;Integrated Security=True";
string _sql = "SELECT dbo.xiangxidingdan.商品名称, dbo.xiangxidingdan.商品数量, "
+ " dbo.xiangxidingdan.商品价格," + "dbo.xiangxidingdan.商品数量*dbo.xiangxidingdan.商品价格 as '小计' FROM dbo.dingdan RIGHT OUTER JOIN dbo.xiangxidingdan ON dbo.dingdan.订单编号 = dbo.xiangxidingdan.订单编号"
+ " where 是否结账='是'";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sda = new SqlDataAdapter(_sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//双击查看原图行销毁
}
private void button2_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
if (!Directory.Exists(@"D:\AOI\ImageFile"))
{
Directory.CreateDirectory(@"D:\AOI\ImageFile");
}
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.InitialDirectory = @"D:\AOI\ImageFile";
saveDialog.Filter = "Excel文件|*.xlsx";
// saveDialog.FileName = fileName;
saveDialog.FileName = "ImageFile_" + DateTime.Now.ToLongDateString().ToString();
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
this.Cursor = Cursors.Default;
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[8, 1]];
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
if (this.dataGridView1.Rows[r].Cells[i].Style.BackColor == Color.Red)
{
range = worksheet.Range[worksheet.Cells[r + 2, i + 1], worksheet.Cells[r + 2, i + 1]];
range.Interior.ColorIndex = 10;
}
/* if (r == 0 & i == 1)
* {
* worksheet.Cells[r + 2, i + 1] = DateTime.Now.ToLocalTime().ToString();
* }*/
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
this.Cursor = Cursors.Default;
}
catch
{
this.Cursor = Cursors.Default;
MessageBox.Show("处理异常3");
}
}
public void PrintOrder(string path, System.Data.DataTable datatable, int LorR)
{
//Excel模板文件
string strFilePath = path;
if (!File.Exists(strFilePath))
{
throw new Exception("Excel条码模版不存在，无法导出");
}
//定义
Microsoft.Office.Interop.Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的电脑未安装Excel");
}
xlApp.Visible = false;
xlApp.UserControl = true;
xlApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(strFilePath); //目标文件
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//赋值
System.Data.DataTable dt = datatable;
if (dt.Rows.Count > 0)
{
if (Convert.ToInt32(dt.Rows[0]["LorR"]) == 0)
{
worksheet.Cells[1, 1] = "【左】"; //左座椅还是右
}
else
{
worksheet.Cells[1, 1] = "【右】";
}
string carType = dt.Rows[0]["CarType"].ToString();
worksheet.Cells[2, 2] = carType + "座椅分装单"; //第一行
worksheet.Cells[4, 2] = dt.Rows[0]["ProductNo"].ToString(); //第二行
BLL.T_JISA t_JISA = new T_JISA();
string JISASer = t_JISA.GetJISASer(dt.Rows[0]["ProductNo"].ToString());
worksheet.Cells[5, 2] = JISASer;
worksheet.Cells[6, 2] = dt.Rows[0]["CreateTime"].ToString(); //第二行
//worksheet.Cells[8,2] = dt.Rows[0]["ProductNo"].ToString(); //第二行
worksheet.Cells[11, 2] = dt.Rows[0]["CarModelName"].ToString(); //第二行
worksheet.Cells[13, 2] = dt.Rows[0]["Color"].ToString() + dt.Rows[0]["ColorCode"].ToString(); //第二行
int row = 15;
if (dt.Rows[0]["MasterBarCodeL"].ToString() != "")
{
worksheet.Cells[row++, 2] = "左前";
}
if (dt.Rows[0]["MasterBarCodeR"].ToString() != "")
{
worksheet.Cells[row++, 2] = "右前";
}
if (dt.Rows[0]["MasterBarCodeC"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后座椅整垫";
}
if (dt.Rows[0]["MasterBarCode40"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后背40%";
}
if (dt.Rows[0]["MasterBarCode60"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后背60%";
}
if (dt.Rows[0]["MasterBarCodeB"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后整背";
}
DataMatrix.net.DmtxImageEncoder dataMatix = new DataMatrix.net.DmtxImageEncoder();
string QRCodepath = "";
if (LorR == 0)
{
Bitmap dataMatixCode = dataMatix.EncodeImage(0 + dt.Rows[0]["ProductNo"].ToString(), 15);
QRCodepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\" + dt.Rows[0]["ProductNo"].ToString();
dataMatixCode.Save(QRCodepath);
}
else if (LorR == 1)
{
Bitmap dataMatixCode = dataMatix.EncodeImage(1 + dt.Rows[0]["ProductNo"].ToString(), 15);
QRCodepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\" + dt.Rows[0]["ProductNo"].ToString();
dataMatixCode.Save(QRCodepath);
}
Microsoft.Office.Interop.Excel.Range m_objRange = worksheet.get_Range("B6", Type.Missing);
m_objRange.Select();
Excel.Pictures pics = (Excel.Pictures)worksheet.Pictures();
pics.Insert(QRCodepath, m_objRange);
Dictionary <int, string> DictPrinterName = new Dictionary <int, string>();
worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //纸张大小
//worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; 页面横向
worksheet.PageSetup.CenterHorizontally = true; //文字水平居中
xlApp.Visible = true;
System.Windows.Forms.Application.DoEvents();
//开始打印
worksheet.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//打印预览
//worksheet.PrintPreview();
//打印结束后清除Excel内存
workbooks.Close();
xlApp.Application.Quit();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();//强行销毁
}
}
/// <summary>
/// 把DataTable导出到EXCEL
/// </summary>
/// <param name="reportName">报表名称</param>
/// <param name="dt">数据源表</param>
/// <param name="saveFileName">Excel全路径文件名</param>
/// <returns>导出是否成功</returns>
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -1;
_ReturnMessage = "数据集为空！";
return(false);
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
return(false);
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 10;
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Cells[1, 1] = reportName;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
//保存文件
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
}
}
else
{
fileSaved = false;
}
//释放Excel对应的对象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return(fileSaved);
}
private void picLoadOut_Click(object sender, EventArgs e)
{
#region 导出信息保存为Excel表
DialogResult ret = MessageBox.Show("导出信息为敏感操作，确定要继续导出吗？(此步操作将写入操作日志)", "信息提醒", MessageBoxButtons.YesNo);
if (ret == DialogResult.Yes)
{
//Response.ContentEncoding = System.Text.Encoding.UTF8;
string fileName = "";
string saveFileName = "";
//fileName.Charset = "GB2312";
SaveFileDialog saveDialog = new SaveFileDialog();
//saveDialog.DefaultExt = "xls";
saveDialog.FileName = fileName;
saveDialog.Filter = "2003~2007工作表*.xls|*.xls|2010及以上版本工作表*.xlsx|*.xlsx";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,您的电脑可能未安装Excel！", "来自T仔的提醒");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
for (int i = 0; i < this.dgvCustomerList.Columns.Count; i++)
{
xlApp.Cells[1, i + 1] = dgvCustomerList.Columns[i].HeaderText;
}
for (int i = 0; i < dgvCustomerList.Rows.Count; i++)//添加每一项
{
for (int j = 0; j < dgvCustomerList.Columns.Count; j++)
{
xlApp.Cells[i + 2, j + 1] = dgvCustomerList.Rows[i].Cells[j].Value.ToString();
}
}
System.Windows.Forms.Application.DoEvents();
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "信息导出成功", "来自T仔提示", MessageBoxButtons.OK);
#region 获取添加操作日志所需的信息
Operation o = new Operation();
o.OperationTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd,HH:mm:ss"));
o.Operationlog = LoginInfo.WorkerClub + LoginInfo.WorkerName + LoginInfo.WorkerPosition + LoginInfo.WorkerName + "于" + DateTime.Now + "导出了" + "用户信息!";
o.OperationAccount = LoginInfo.WorkerClub + LoginInfo.WorkerName + LoginInfo.WorkerPosition;
#endregion
OperationManager.InsertOperationLog(o);
System.Diagnostics.Process.Start("Explorer.exe", saveFileName);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();
#endregion
}
}
public void Create()//create a Microsoft.Office.Interop.Excel object
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
private void ExportExcels(string fileName, DataGridView gridview)
{
try
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Worksheet objSheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Excel.Worksheet objSheet2 = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
worksheet.Name = "自动率";
objSheet.Name = "行车自动率";
objSheet2.Name = "行车结果自动率";
Save2ExcelSheet(gridview, worksheet);
Save2ExcelSheet(this.dataGridView3, objSheet);
Save2ExcelSheet(this.dataGridView3, objSheet2);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
