/// <summary>
/// �����ݼ�������Ϊexcel
/// </summary>
/// <param name="name">����excel������</param>
/// <param name="ds">�����������ݼ�</param>
public static void AddExcel(string name, DataTable dt)
{
string fileName = name + ".xls";
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
int rowIndex = 1;
int colIndex = 0;
excel.Application.Workbooks.Add(true);
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
}
excel.Visible = false;
excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
excel.Quit();
excel = null;
GC.Collect();//��������
}
public static void ExportGridViewToExcel(string FileName, GridView gv)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp =
new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
ExcelApp.Application.Workbooks.Add(Type.Missing);
//匯出全部欄位資料
//for (int i = 1; i < gv.Columns.Count + 1; i++)
//{
// ExcelApp.Cells[1, i] = gv.Columns[i - 1].HeaderText;
//}
//for (int i = 0; i < gv.Rows.Count - 1; i++)
//{
// for (int j = 0; j < gv.Columns.Count; j++)
// {
// ExcelApp.Cells[i + 2, j + 1] = gv.Rows[i].Cells[j].Text.ToString();
// }
//}
//隱藏第一個欄位資料
for (int i = 1; i < gv.Columns.Count ; i++)
{
ExcelApp.Cells[1, i] = gv.Columns[i].HeaderText;
}
for (int i = 0; i < gv.Rows.Count - 1; i++)
{
for (int j = 1; j < gv.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j] = gv.Rows[i].Cells[j].Text.ToString().Replace(" ", "");
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(FileName);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
ExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
GC.Collect();
}
/// <summary>
/// 把DataTable导出Excel表
/// </summary>
/// <param name="dataTable">DataTable</param>
/// <returns>true为成功，false为失败</returns>
private static bool _ExportExcel(DataTable dataTable, string fileName)
{
bool Flag = false;
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
//没有数据的话就不往下执行
if (dataTable.Rows.Count == 0)
{
return(false);
}
excel.Application.Workbooks.Add();
excel.Visible = false;//要不要显示Excel表
//判断电脑是否有Excel
if (excel == null)
{
return(false);
}
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;//
// 生成字段名称
for (int i = 0; i < dataTable.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, i + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
//填充数据
for (int i = 0; i < dataTable.Rows.Count - 1; i++)
{
for (int j = 0; j < dataTable.Columns.Count; j++)
{
excel.Cells[i + 2, j + 1] = "" + dataTable.Rows[i][j].ToString();
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[i + 2, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
SetColumnFit(sheet);//设置自适应宽度
//保存Excel表
excel.Save(fileName);
Flag = true;
}
catch
{
throw;
}
finally
{
//设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = true;
excel.AlertBeforeOverwriting = true;
excel.Workbooks.Close();
//确保Excel进程关闭
excel.Quit();
excel = null;
//垃圾回收
GC.Collect();
}
return(Flag);
}
private void buttonX1_Click(object sender, EventArgs e)
{
String temp = "";
temp = "TK_YTQT.xls";
saveFileDialog1.FileName = temp.Replace("/", "-");
saveFileDialog1.Filter = " Excel (*.xls)|*.xls|Tất cả (*.*)|*.*";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;
string path = "";
if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
//Cursor.Current = Cursors.WaitCursor;
path = saveFileDialog1.FileName;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 30;
for (int i = 0; i < dataGridViewX2.Rows.Count; i++)
{
DataGridViewRow row = dataGridViewX2.Rows[i];
for (int j = 0; j < row.Cells.Count; j++)
{
ExcelApp.Cells[i + 1, j + 1] = row.Cells[j].Value.ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(path);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Đã Lưu");
}
}
/// <summary>
/// 具体导出的方法
/// </summary>
/// <param name="listView">ListView</param>
/// <param name="strFileName">导出到的文件名</param>
private void DoExport(string strFileName)
{
List <Lecturer> list = GetAllLecturer();
int rowNum = list.Count;
int rowIndex = 1;
int columnIndex = 0;
if (rowNum == 0 || string.IsNullOrEmpty(strFileName))
{
return;
}
if (rowNum > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("无法创建excel对象，可能您的系统没有安装excel");
return;
}
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//将ListView的列名导入Excel表第一行
foreach (ColumnHeader dc in lecturerList.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.Text;
}
for (int i = 0; i < list.Count; i++)
{
rowIndex++;
Lecturer model = list[i];
xlApp.Cells[rowIndex, 1] = Convert.ToString(model.No);
xlApp.Cells[rowIndex, 2] = Convert.ToString(model.Name);
xlApp.Cells[rowIndex, 3] = Convert.ToString(model.Title);
xlApp.Cells[rowIndex, 4] = Convert.ToString(model.Price);
xlApp.Cells[rowIndex, 5] = Convert.ToString(model.Phone1);
xlApp.Cells[rowIndex, 6] = Convert.ToString(model.Phone2);
xlApp.Cells[rowIndex, 7] = Convert.ToString(model.Area);
xlApp.Cells[rowIndex, 8] = Convert.ToString(model.From);
xlApp.Cells[rowIndex, 9] = Convert.ToString(model.DetialLink);
}
xlBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlBook.Close();
xlApp.Quit();
xlApp = null;
xlBook = null;
GC.Collect();
MessageBox.Show("导出完成");
}
}
/// <summary>
/// 列数
/// </summary>
/// <param name="_filename">文件名（含地址）</param>
/// <param name="sheetname">工作表名</param>
/// <returns></returns>
public int colcount(string _filename, string sheetname) //列数
{
Microsoft.Office.Interop.Excel.ApplicationClass myExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook excel_wb;
excel_wb = myExcelApp.Workbooks.Open(_filename, 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);
int counts = ((Microsoft.Office.Interop.Excel.Worksheet)excel_wb.Worksheets.get_Item(sheetname)).UsedRange.Columns.Count;
myExcelApp.Workbooks.Close();
myExcelApp.Quit();
myExcelApp = null;
return(counts);
}
/// <summary>
/// 获取Excel里面的数据
/// </summary>
/// <param name="strPath">文件路径</param>
/// <param name="col">表示数据有多少列</param>
/// <param name="row">表示从第几行开始读数据</param>
/// <returns></returns>
public static DataTable GetDataFromFile(string strPath, int col, int row)
{
DataTable dtRes = MakeTableForImport(col);
if (!string.IsNullOrEmpty(strPath))
{
dtRes.Clear();
//应将引用的Mircosoft.Office.Intercrop.Excel的引用属性-嵌入式互操作类型 设置为false
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xApp.Visible = false;
try
{
Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(strPath,
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];
int iCount = xSheet.UsedRange.Rows.Count - 1;
if (iCount < 1)
{
return(dtRes);
}
for (int i = 0; i < iCount; i++)
{
int iRowIndex = i + row; // //Excel起始行从1开始计数，首列为标题行，因此数据行计数应先加
DataRow dr = dtRes.NewRow();
for (int j = 1; j <= col; j++)
{
Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)xSheet.Cells[iRowIndex, j];
if (rng.Text != null)
{
dr[j - 1] = rng.Text.ToString().Trim();
}
}
dtRes.Rows.Add(dr);
}
}
catch (Exception ex)
{
SMT.Foundation.Log.Tracer.Debug(" GetDataFromFile获取Excel数据错误:" + ex.ToString());
}
finally
{
xApp.Quit();
}
}
return(dtRes);
}
/// <summary>
/// 数据库转为excel表格
/// </summary>
/// <param name="dataTable">数据库数据</param>
/// <param name="SaveFile">导出的excel文件</param>
public static void DataSetToExcel(DataTable dataTable, string SaveFile)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel._Workbook workBook;
Microsoft.Office.Interop.Excel._Worksheet workSheet;
object misValue = System.Reflection.Missing.Value;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
int rowIndex = 1;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//取得表格中的数据
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] =
row[col.ColumnName].ToString().Trim();
//设置表格内容居中对齐
//excel.Cells[rowIndex, colIndex].HorizontalAlignment =
//Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
excel.Visible = false;
workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
dataTable = null;
workBook.Close(true, misValue, misValue);
excel.Quit();
//PublicMethod.Kill(excel);//调用kill当前excel进程
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
private void exportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
var dialog = new SaveFileDialog();
dialog.Title = Resources.TextViewForm_ChoiseFileToSave;
dialog.Filter = "Excel .xls|*.xls";
//dialog.FileName = "TextView-" + DateTime.Now.ToString("dd-MM-yyyy") + ".xls";
if (dialog.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// Change properties of the Workbook
ExcelApp.Columns.ColumnWidth = 20;
// Storing header part in Excel
for (int i = 1; i < dataGridView.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dataGridView.Columns[i - 1].HeaderText;
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dataGridView.Rows[i].Cells[j].Value.ToString().Replace(",", ".");
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(dialog.FileName);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
if (ShowConfirmMessage(Resources.Message_ExportSuccess) == DialogResult.OK)
{
// Open file
System.Diagnostics.Process.Start(dialog.FileName);
}
}
}
catch (Exception exception)
{
ShowErrorMessage(exception.Message);
}
}
private bool XLSConvertToPDF(string sourcePath, string targetPath)
{
// MessageBox.Show(sourcePath+"\n"+targetPath);
bool result = false;
Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass application = null;
Microsoft.Office.Interop.Excel.Workbook workBook = null;
try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(targetType, target, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
result = false;
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
private void LoadFile2()
{
OpenFileDialog pOpen;
pOpen = new OpenFileDialog();
pOpen.Filter = "Excel files (*.xlsx)|*.xlsx;*.xls";
pOpen.FilterIndex = 1;
if (pOpen.ShowDialog() == DialogResult.OK)
{
txtFilePath.Text = pOpen.FileName;
System.DateTime 开始时间1 = System.DateTime.Now;
Microsoft.Office.Interop.Excel.ApplicationClass oApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.DateTime 开始时间2 = System.DateTime.Now;
System.Diagnostics.Process[] excel进程 = System.Diagnostics.Process.GetProcessesByName("EXCEL");
//立即获取进程创建时间
int m, killId = 0;
for (m = 0; m < excel进程.Length; m++)
{
if (开始时间1 < excel进程[m].StartTime && 开始时间2 > excel进程[m].StartTime)
{
killId = m;
}
}
//获取最后进程创建时间成功
Microsoft.Office.Interop.Excel.Workbook oBook = oApp.Workbooks._Open(pOpen.FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
comboBox1.Items.Clear();
comboBox1.Tag = pOpen.FileName;
foreach (Microsoft.Office.Interop.Excel.Worksheet sSheet in oBook.Sheets)
{
comboBox1.Items.Add(sSheet.Name);
}
oBook = null;
oApp.Quit();
oApp = null;
if (excel进程[killId].HasExited == false)
{
excel进程[killId].Kill();
}
comboBox1.SelectedIndex = 0;
}
}
/// <summary>
/// 数据库转为excel表格
/// </summary>
/// <param name="dataTable">数据库数据</param>
/// <param name="SaveFile">导出的excel文件</param>
public static void DataSetToExcel(DataTable dataTable, string SaveFile)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel._Workbook workBook;
Microsoft.Office.Interop.Excel._Worksheet workSheet;
object misValue = System.Reflection.Missing.Value;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
int rowIndex = 1;
int colIndex = 0;
//取得标题
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//取得表格中的数据
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();
//设置表格内容居中对齐
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[colIndex, rowIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//设置表格自动适应大小
workSheet.Cells.Columns.AutoFit();
}
}
excel.Visible = false;
workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
dataTable = null;
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.Kill(excel);//调用kill当前excel进程
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
/// <summary>
/// 把Microsoft.Office.Interop.Excel文件转换成PDF格式文件
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns>true=转换成功</returns>
public static bool ExcelToPDF(string sourcePath, string targetPath)
{
bool result = false;
Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass application = null;
Microsoft.Office.Interop.Excel.Workbook workBook = null;
try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass {
Visible = false
};
workBook = application.Workbooks.Open(sourcePath);
workBook.SaveAs();
workBook.ExportAsFixedFormat(targetType, targetPath);
result = true;
}
catch (Exception e)
{
result = false;
throw new Exception(e.Message);
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
public void ToXLS(DataGridView dGV, string fileName)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// Change properties of the Workbook
ExcelApp.Columns.ColumnWidth = 20;
// Storing header part in Excel
for (int i = 1; i < dGV.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dGV.Columns[i - 1].HeaderText;
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dGV.Rows.Count; i++)
{
for (int j = 0; j < dGV.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dGV.Rows[i].Cells[j].Value.ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(fileName);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
/// <summary>
/// 将数据集导出成为Excel，不需求要Excel模板
/// </summary>
/// <param name="name">导出excel的名称</param>
/// <param name="ds">所导出的数据集</param>
public static void AddExcel(string name, DataTable dt)
{
string fileName = name;
if (File.Exists(fileName))
{
try
{
File.Delete(fileName);
}
catch
{
return;
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
int rowIndex = 1;
int colIndex = 0;
excel.Application.Workbooks.Add(true);
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
}
excel.Visible = false;
excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
// M E T O D O S **************************************
#region Exportar
// Exportar Clientes
public void exportarClientes()
{
Clientes.frmListaClientes frmListaClientes = new frmListaClientes();
frmListaClientes.Show();
frmListaClientes.Hide();
Microsoft.Office.Interop.Excel.ApplicationClass ExcelAppCli = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelAppCli.Application.Workbooks.Add(Type.Missing);
SaveFileDialog drCli = new SaveFileDialog();
drCli.FileName = "Clientes " + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString();
drCli.Filter = "Excel files (*.xls)|*.xls";
drCli.Title = "CLIENTES";
if (drCli.ShowDialog() == DialogResult.OK)
{
for (int i = 1; i < frmListaClientes.dgvListadoClientes.Columns.Count + 1; i++)
{
ExcelAppCli.Cells[1, i] = frmListaClientes.dgvListadoClientes.Columns[i - 1].HeaderText;
}
for (int i = 0; i < frmListaClientes.dgvListadoClientes.Rows.Count; i++)
{
for (int j = 0; j < frmListaClientes.dgvListadoClientes.Columns.Count; j++)
{
ExcelAppCli.Cells[i + 2, j + 1] = frmListaClientes.dgvListadoClientes.Rows[i].Cells[j].Value.ToString();
}
}
ExcelAppCli.ActiveWorkbook.SaveCopyAs(drCli.FileName);
ExcelAppCli.ActiveWorkbook.Saved = true;
ExcelAppCli.Quit();
}
int cantCli = frmListaClientes.dgvListadoClientes.Rows.Count;
}
private void btnExcel_Click(object sender, EventArgs e)
{
String temp = "DS KHTN dang duoc theo doi";
saveFileDialog1.FileName = temp.Replace("/", "-");
saveFileDialog1.Filter = "Excel (*.xls)|*.xls|Tất cả (*.*)|*.*";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;
string path = "";
if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
path = saveFileDialog1.FileName;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 10;
for (int i = 0; i < dgvDanhsach.Columns.Count; i++)
{
ExcelApp.Cells[1, i + 1] = dgvDanhsach.Columns[i].Name;
}
for (int i = 1; i <= dgvDanhsach.Rows.Count; i++)
{
DataGridViewRow row = dgvDanhsach.Rows[i - 1];
for (int j = 1; j <= row.Cells.Count; j++)
{
ExcelApp.Cells[i + 1, j] = row.Cells[j - 1].Value.ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(path);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Đã xuất ra file excel.");
}
Cursor.Current = Cursors.Default;
}
//_________________________________________________________________________________
/// <summary>
/// 返回工作表名
/// </summary>
/// <param name="_filename">文件名（含地址）</param>
/// <returns>工作表名集</returns>
public StringCollection countexcel(string _filename) //
{
if (System.IO.File.Exists(_filename))
{
Microsoft.Office.Interop.Excel.ApplicationClass myExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook excel_wb;
excel_wb = myExcelApp.Workbooks.Open(_filename, 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);
StringCollection a = new StringCollection();
for (int i = 1; i <= excel_wb.Worksheets.Count; i++)
{
a.Add(((Microsoft.Office.Interop.Excel.Worksheet)excel_wb.Worksheets[i]).Name);
}
myExcelApp.Workbooks.Close();
myExcelApp.Quit();
myExcelApp = null;
return(a);
}
else
{
return(null);
}
}
private void button1_Click(object sender, EventArgs e)
{
saveFileDialog1.InitialDirectory = "C:";
saveFileDialog1.Title = "Save as Excel File";
saveFileDialog1.FileName = "";
saveFileDialog1.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*.xlsx|Excel Files(2010)|*.xlsx|Excel Files(2013)|*.xlsx|Excel Files(2016)|*.xlsx";
if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
//Change Properties of the WorkBook
ExcelApp.Columns.ColumnWidth = 20;
//Storing the Header Value in Excel
for (int i = 1; i < dgvCategories.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dgvCategories.Columns[i - 1].HeaderText;
}
//Storing Each row and column value to excel sheet
for (int i = 0; i < dgvCategories.Rows.Count; i++)
{
for (int j = 0; j < dgvCategories.Columns.Count; j++)
{
if (dgvCategories.Rows[i].Cells[j].Value != null)
{
ExcelApp.Cells[i + 2, j + 1] = dgvCategories.Rows[i].Cells[j].Value.ToString();
}
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString());
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
}
// Exportar Clientes
public void exportarClientes()
{
Clientes.frmListaClientes frmListaClientes = new frmListaClientes();
frmListaClientes.Show();
frmListaClientes.Hide();
Microsoft.Office.Interop.Excel.ApplicationClass ExcelAppCli = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelAppCli.Application.Workbooks.Add(Type.Missing);
SaveFileDialog drCli = new SaveFileDialog();
drCli.FileName = "Clientes " + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString();
drCli.Filter = "Excel files (*.xls)|*.xls";
drCli.Title = "CLIENTES";
if (drCli.ShowDialog() == DialogResult.OK)
{
for (int i = 1; i < frmListaClientes.dgvListadoClientes.Columns.Count + 1; i++)
{
ExcelAppCli.Cells[1, i] = frmListaClientes.dgvListadoClientes.Columns[i - 1].HeaderText;
}
for (int i = 0; i < frmListaClientes.dgvListadoClientes.Rows.Count; i++)
{
for (int j = 0; j < frmListaClientes.dgvListadoClientes.Columns.Count; j++)
{
ExcelAppCli.Cells[i + 2, j + 1] = frmListaClientes.dgvListadoClientes.Rows[i].Cells[j].Value.ToString();
}
}
ExcelAppCli.ActiveWorkbook.SaveCopyAs(drCli.FileName);
ExcelAppCli.ActiveWorkbook.Saved = true;
ExcelAppCli.Quit();
}
int cantCli = frmListaClientes.dgvListadoClientes.Rows.Count;
}
private void Print()
{
string fileName = "";
Microsoft.Office.Interop.Excel.ApplicationClass ExcelAp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks books = ExcelAp.Workbooks;
books.Open(exportPath, 3, false, 3, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
";", false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ExcelAp.ActiveWorkbook.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Cleanup:
GC.Collect();
GC.WaitForPendingFinalizers();
ExcelAp.ActiveWorkbook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelAp.ActiveWorkbook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(books);
ExcelAp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelAp);
}
//-----------------------------------------------
//CreateExcelFile
//-----------------------------------------------
public static void CreateExcelFile(string excelFilePath, ArrayList ArticlesNos)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Article ID";
for (int i = 0; i < ArticlesNos.Count; i++)
{
xlWorkSheet.Cells[i + 2, 1] = ArticlesNos[i];
}
xlWorkBook.SaveAs(excelFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
}
/// <summary>
/// 将DataTable的数据导出显示为报表
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public static void OutputExcel(System.Data.DataTable dt, string strTitle, string filePath)
{
var beforeTime = DateTime.Now;
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel._Workbook xBk;
Microsoft.Office.Interop.Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = 15;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
//取得整个报表的标题
excel.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置下边线加粗
var afterTime = DateTime.Now;
//显示效果
//excel.Visible = true;
//excel.Sheets[0] = "sss";
// ClearFile(FilePath);
//string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
//excel.ActiveWorkbook.SaveAs(filePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
try
{
excel.Save();
}
catch (Exception)
{
return;
}
#region 结束Excel进程
//需要对Excel的DCOM对象进行配置:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//垃圾回收
#endregion
return;
}
public static bool SetDataToExcel(string pathToExcelFile, string sheetName, DataTable data, string[] intro, string title)
{
try
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook wb;
if (!System.IO.File.Exists(pathToExcelFile))
{
//15/03/2011 Template ?
System.IO.File.Copy(SystemHelper.GetConfigValue("appStartupPath") + "\\template\\newWorkbook.xlsx", pathToExcelFile);
}
wb = app.Workbooks.Open(pathToExcelFile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Find sheetname
int index = 1;
for (index = 1; index <= wb.Sheets.Count; index++)
{
if (((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Name.ToLower().Trim() == sheetName.ToLower().Trim())
break;
}
if (index > wb.Sheets.Count)
{
wb.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Activate();
}
Microsoft.Office.Interop.Excel.Worksheet activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
activeSheet.Name = sheetName;
//activeSheet.Cells.FormatConditions =
if (!System.IO.File.Exists(pathToExcelFile))
{
//wb.SaveAs(pathToExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs(pathToExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
wb.Save();
}
// exit excel, still now work so far
wb.Close(true, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app.Workbooks);
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(app) != 0)
{ };
//System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Close();
//System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Kill();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathToExcelFile, true))
{
IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0) // the sheet with that name couldn't be found
{
return false;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
//sheetData.RemoveAllChildren<Row>();
//SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
int lastRowIndex = sheetData.ChildElements.Count;
int k = 0; //step in intro
for (int i = lastRowIndex; i < data.Rows.Count + 4 + lastRowIndex + intro.Length; i++) // +1 for the header label, + 2 for spacing row, + 1 for title
{
DataRow dataRow = null;
if (i > lastRowIndex + 3 + intro.Length)
{
dataRow = data.Rows[i - 4 - lastRowIndex - intro.Length];
}
Row aRow = new Row();
aRow.RowIndex = (UInt32)i + 1;
bool isRowEnd = false;//Fix the excel expand
for (int j = 0; j < data.Columns.Count; j++)
{
if (isRowEnd)
{
break;//Fix the excel expand
}
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = GetAlpha(j + 1) + (i + 1);
InlineString inlineString = new InlineString();
Text t = new Text();
if (i <= lastRowIndex + intro.Length - 1)//Intro
{
if (j == 0)
{
t.Text = intro[k];
k++;
cell.StyleIndex = (UInt32Value)3U;
isRowEnd = true;
}
}
else if (i == lastRowIndex + intro.Length + 1)//Title
{
if (j == 0)
{
t.Text = title;
cell.StyleIndex = (UInt32Value)4U;
isRowEnd = true;
}
}
else if (i == lastRowIndex + 3 + intro.Length)//Header
{
t.Text = data.Columns[j].ToString();
cell.StyleIndex = (UInt32Value)1U;
}
else
{
if (i != intro.Length + lastRowIndex && i != intro.Length + lastRowIndex + 2)//Null spacing row
t.Text = dataRow[j].ToString();//Data row
}
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
aRow.AppendChild(cell);
}
sheetData.AppendChild(aRow);
}
System.Xml.XmlWriter test = System.Xml.XmlWriter.Create("Test.xml");
worksheetPart.Worksheet.WriteTo(test);
test.Close();
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
document.Close();
}
}
catch (Exception e)
{
SystemHelper.LogEntry(string.Format("Error occurs on method {0} - Message {1}", "GetDataFromExcel", e.Message));
return false;
}
return true;
}
/// <summary>
/// 创建一个空的Excel文件(2003版的Excel)
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string CreateBlankExcel(string path)
{
Microsoft.Office.Interop.Excel.Application excelApp;
Microsoft.Office.Interop.Excel.Workbook excelDoc;
DateTime excelBeginCreatingTime = DateTime.Now;
excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
DateTime excelAfterCreatingTime = DateTime.Now;
if (File.Exists(path))
{
File.Delete(path);
}
Object nothing = Missing.Value;
excelDoc = excelApp.Workbooks.Add(nothing);
Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal;
excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing);
excelDoc.Close(nothing, nothing, nothing);
excelApp.Quit();
// 关闭进程
foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
DateTime proStartCreatingTime = proc.StartTime;
if ((proStartCreatingTime >= excelBeginCreatingTime) && (proStartCreatingTime <= excelAfterCreatingTime))
{
proc.Kill();
}
}
return path;
}
public static bool DataGridViewExportToExcel(DataTable mDataTable, String strFileName, ref String strMsg)
{
strMsg = "";
// 创建Excel对象
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
strMsg = "Excel无法启动";
return(false);
}
// 创建Excel工作薄
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = null;
/*
* // 设置标题
* range = xlSheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,ts.GridColumnStyles.Count]);
* range.MergeCells = true;
* xlApp.ActiveCell.FormulaR1C1 = p_ReportName;
* xlApp.ActiveCell.Font.Size = 20;
* xlApp.ActiveCell.Font.Bold = true;
* xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
*/
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = mDataTable.Columns.Count;
int RowCount = mDataTable.Rows.Count + 1;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount];
// 获取列标题
foreach (DataColumn dc in mDataTable.Columns)
{
objData[RowIndex, colIndex++] = dc.ColumnName;
}
// 获取数据
for (RowIndex = 1; RowIndex < RowCount; RowIndex++)
{
for (colIndex = 0; colIndex < colCount; colIndex++)
{
objData[RowIndex, colIndex] = mDataTable.Rows[RowIndex - 1][colIndex].ToString();
}
}
// 写入Excel
//((Excel.Range)xlSheet.Columns["A:A ",System.Reflection.Missing.Value]).ColumnWidth = 16;
range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount, colCount]);
range.Value2 = objData;
range = (Microsoft.Office.Interop.Excel.Range)xlSheet.Columns["A:B", System.Type.Missing];
range.ColumnWidth = 20;
// 保存
try
{
xlBook.Saved = true;
xlBook.SaveCopyAs(strFileName);
MessageBox.Show("数据转存为Excel成功");
}
catch (Exception ee)
{
strMsg = ee.Message;
return(false);
}
finally
{
range = null;
xlSheet = null;
xlBook = null;
xlApp.Quit();
xlApp = null;
GC.Collect();
}
return(true);
}
private void UpdateSitelogin()
{
string fullPath = txtFilePath.Text.Trim();
string sheetName = comboBox1.Text.Trim() + "$";
Microsoft.Office.Interop.Excel.ApplicationClass oApp = null;
Microsoft.Office.Interop.Excel.Workbook oBook = null;
System.Diagnostics.Process[] excel进程 = null;
int m, killId = 0;
try
{
System.DateTime 开始时间1 = System.DateTime.Now;
oApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.DateTime 开始时间2 = System.DateTime.Now;
excel进程 = System.Diagnostics.Process.GetProcessesByName("EXCEL");
//立即获取进程创建时间
for (m = 0; m < excel进程.Length; m++)
{
if (开始时间1 < excel进程[m].StartTime && 开始时间2 > excel进程[m].StartTime)
{
killId = m;
}
}
//获取最后进程创建时间成功
oBook = oApp.Workbooks.Open(fullPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
#region 更新Excel
Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets[sheetName.Replace("$", "")];
Microsoft.Office.Interop.Excel.Range r;
int countColumn = oSheet.UsedRange.Columns.Count;
int countRow = oSheet.UsedRange.Rows.Count;
this.progressBar1.Maximum = countRow - 1;
this.progressBar1.Value = 0;
countColumn = 1;
#region 执行结果
r = (Microsoft.Office.Interop.Excel.Range)(oSheet.Cells[1, countColumn + 1]);
r.Value2 = "执行结果";
//找到库里最大值再加2
int maxnum = GetMaxnum() + 2;
int num = maxnum;
#region SDK方式更新
for (int i = 2; i < countRow + 1; i++)
{
this.progressBar1.Value++;
r = (Microsoft.Office.Interop.Excel.Range)(oSheet.Cells[i, 1]);//读取第一列数据
string cellValue = r.Text.ToString().Trim();
r = (Microsoft.Office.Interop.Excel.Range)(oSheet.Cells[i, countColumn + 1]);
string resultValue = r.Text.ToString().Trim();
num++;
if (cellValue == "" || resultValue == "生成失败")
{
continue;
}
else
{
try
{
EncodingOptions options = null;
BarcodeWriter writer = null;
options = new QrCodeEncodingOptions
{
DisableECI = true,
CharacterSet = "UTF-8",
Width = 500,
Height = 500,
Margin = 1
};
writer = new BarcodeWriter();
writer.Format = BarcodeFormat.QR_CODE;
writer.Options = options;
Bitmap bitmap = writer.Write(cellValue);
System.Drawing.Image Img = bitmap;
string msms = "2017-10-17";
string VCardSrc = CardImage.SaveVCardCodeImages(Img, 300, 300, msms, cellValue.Trim());//图片保存路径
if (!string.IsNullOrEmpty(VCardSrc))
{
r.Value2 = VCardSrc + "|" + cellValue;
//加入数据库
//ADDData(num, cellValue.Trim(), VCardSrc);
}
else
{
r.Value2 = "生成失败";
}
}
catch (Exception ex)
{ r.Value2 = ex.Message; }
}
}
#endregion
#endregion
r = null;
oBook.Save();
oBook.Close();
#endregion
oBook = null;
oApp.Quit();
oApp = null;
if (excel进程[killId].HasExited == false)
{
excel进程[killId].Kill();
}
this.progressBar1.Value = 0;
MessageBox.Show("生成完毕！");
}
catch (Exception ex)
{
oBook = null;
oApp.Quit();
oApp = null;
if (excel进程[killId].HasExited == false)
{
excel进程[killId].Kill();
}
MessageBox.Show(ex.Message);
}
finally
{
this.txtFilePath.Enabled = true;
this.comboBox1.Enabled = true;
this.button1.Enabled = true;
}
}
private void exportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
var dialog = new SaveFileDialog();
dialog.Title = Resources.TextViewForm_ChoiseFileToSave;
dialog.Filter = "Excel .xls|*.xls";
//dialog.FileName = "TextView-" + DateTime.Now.ToString("dd-MM-yyyy") + ".xls";
if (dialog.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// Change properties of the Workbook
ExcelApp.Columns.ColumnWidth = 20;
// Storing header part in Excel
for (int i = 1; i < dataGridView.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dataGridView.Columns[i - 1].HeaderText;
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dataGridView.Rows[i].Cells[j].Value.ToString().Replace(",", ".");
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(dialog.FileName);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
if (ShowConfirmMessage(Resources.Message_ExportSuccess) == DialogResult.OK)
{
// Open file
System.Diagnostics.Process.Start(dialog.FileName);
}
}
}
catch (Exception exception)
{
ShowErrorMessage(exception.Message);
}
}
/// <summary>
/// 导入文本
/// </summary>
private void ImportText(string fileName)
{
Microsoft.Office.Interop.Excel.Application xApp = null;
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
// 显示进度条
this.ResetProcessBar(this.fileList.Items.Count);
try
{
StringBuilder failFiles = new StringBuilder();
// 创建Application对象
xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// 得到WorkBook对象, 打开已有的文件
xBook = xApp.Workbooks._Open(
fileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = xBook.Sheets.Count; i >= 1; i--)
{
// 取得相应的Sheet
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i];
int sheetIndex = -1;
string sheetName = string.Empty;
for (int j = 0; j < this.fileList.Items.Count; j++)
{
sheetName = Util.GetShortFileName(this.textFiles[j].File);
if (xSheet.Name.IndexOf(sheetName) >= 0)
{
if (xSheet.Name.IndexOf("main.dol") >= 0)
{
if ((xSheet.Name == "main.dol" && string.IsNullOrEmpty(this.textFiles[j].SubIndex)) ||
(!string.IsNullOrEmpty(this.textFiles[j].SubIndex) && xSheet.Name.IndexOf(this.textFiles[j].SubIndex) >= 0))
{
sheetIndex = j;
break;
}
}
else
{
sheetIndex = j;
break;
}
}
}
if (sheetIndex > -1)
{
// 更新当前文本
this.fileList.SelectedIndex = sheetIndex;
// 取得当前Sheet的中文文本
int lineNum = 1;
int blankNum = 0;
StringBuilder sb = new StringBuilder();
while (blankNum < 4)
{
string cellValue = xSheet.get_Range("G" + lineNum, Missing.Value).Value2 as string;
sb.Append(cellValue).Append("\n");
if (string.IsNullOrEmpty(cellValue))
{
blankNum++;
}
else
{
blankNum = 0;
}
lineNum++;
}
sb = sb.Replace("\n\n\n\n\n", "\n");
this.txtCn.Text = sb.ToString();
// 保存
if (!this.Save())
{
//return;
throw new Exception("有文件长度检查失败");
//failFiles.Append("\n").Append(sheetName);
}
}
// 更新进度条
this.ProcessBarStep();
}
// 隐藏进度条
this.CloseProcessBar();
// 显示保存完成信息
if (failFiles.Length == 0)
{
MessageBox.Show("完全成功导入！");
}
else
{
MessageBox.Show("导入完成，下面文件失败" + failFiles.ToString());
}
// 重新读取字库信息
this.ReadFontChar();
}
catch (Exception me)
{
MessageBox.Show(this.baseFile + "\n" + me.Message);
}
finally
{
// 隐藏进度条
this.CloseProcessBar();
// 清空各种对象
xSheet = null;
xBook = null;
if (xApp != null)
{
xApp.Quit();
xApp = null;
}
}
}
public DataTable Excel2DataTable(string FileName)
{
//创建Application对象
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xApp.Visible = false;
//Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(@"C:\医药制造业\Sample.xls",
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(FileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
//指定要操作的Sheet，两种方式：
Microsoft.Office.Interop.Excel.Worksheet xSheetSample = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];
Microsoft.Office.Interop.Excel.Range range = xSheetSample.UsedRange;
DataTable dt = new DataTable();
for (int j = 1; j <= range.Columns.Count; j++)
{
DataColumn dc = new DataColumn(((Microsoft.Office.Interop.Excel.Range)range.Cells[1, j]).Value2.ToString());
dt.Columns.Add(dc);
}
for (int i = 2; i <= range.Rows.Count; i++)
{
DataRow tmprow = dt.NewRow();
for (int j = 1; j <= range.Columns.Count; j++)
{
tmprow[j - 1] = ((Microsoft.Office.Interop.Excel.Range)range.Cells[1, j]).Value2;
}
dt.Rows.Add(tmprow);
}
xSheetSample = null;
xBook = null;
xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出
xApp = null;
GC.Collect();
KillProcess("EXCEL");
System.Threading.Thread.Sleep(1000 * 3); ;
return dt;
}
static void ExcelRefresh(string Filename, string targetServer, string loadBalance)
{
object NullValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
excelApp.DisplayAlerts = false;
excelApp.AskToUpdateLinks = false;
excelApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
Filename, NullValue, false, NullValue, NullValue,
NullValue, true, NullValue, NullValue, NullValue,
NullValue, NullValue, NullValue, NullValue, NullValue);
if (loadBalance.Equals("Load Balance"))
{
for (int i = 1; i <= Workbook.Connections.Count; i++)
{
var con = Workbook.Connections.Item(i);
var oleDBCon = con.OLEDBConnection;
var conStr = (string)oleDBCon.Connection;
var newConStr = Regex.Replace(conStr, @"Data Source=.*?;", String.Format(@"Data Source={0};", targetServer));
con.OLEDBConnection.Connection = newConStr;
}
}
Workbook.RefreshAll();
/*
if (loadBalance.Equals("Load Balance"))
{
for (int i = 1; i <= Workbook.Connections.Count; i++)
{
var con = Workbook.Connections.Item(i);
var oleDBCon = con.OLEDBConnection;
var conStr = (string)oleDBCon.Connection;
var newConStr = Regex.Replace(conStr, @"Data Source=.*?;", String.Format(@"Data Source=tratab.tyson.com;"));
con.OLEDBConnection.Connection = newConStr;
}
}
*/
Workbook.Save();
Workbook.Close(false, Filename, null);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Workbook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
//Workbook = null;
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("RefreshExcelReport", ex.Message,
System.Diagnostics.EventLogEntryType.Warning);
System.Diagnostics.EventLog.WriteEntry("RefreshExcelReport", ex.StackTrace,
System.Diagnostics.EventLogEntryType.Warning);
throw ex;
}
finally
{
excelApp = null;
}
//excelApp.Quit();
}
/// <summary>
/// 导出文本
/// </summary>
private void ExoprtText()
{
Microsoft.Office.Interop.Excel.Application xApp = null;
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
// 设定保存的文件名
string fileName = @"E:\My\Hanhua\testFile\bioCv\BioCvTextNgc.xls";
//string fileName = @"D:\game\iso\wii\生化危机维罗妮卡汉化\Bio0Text_" + this.exportName + ".xls";
// 先删除原来的文件
File.Delete(fileName);
// 显示进度条
this.ResetProcessBar(this.fileList.Items.Count);
try
{
// 创建Application对象
xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//xApp.Visible = true;
// 追加一个WorkBook
xBook = xApp.Workbooks.Add(Missing.Value);
for (int j = 0; j < this.fileList.Items.Count; j++)
{
// 追加一个Sheet
FilePosInfo filePosInfo = this.textFiles[j];
// 更新当前文本
this.fileList.SelectedIndex = j;
// 取得日文、中文文本
string jpText = this.txtJp.Text;
string cnText = this.txtCn.Text;
string sheetName = Util.GetShortFileName(filePosInfo.File);
int sameNameCount = 0;
for (int i = 0; i < j; i++)
{
if (Util.GetShortFileName(this.textFiles[i].File).IndexOf(sheetName) >= 0)
{
sameNameCount++;
}
}
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xSheet.Name = sheetName + (sameNameCount > 0 ? "_" + sameNameCount.ToString().PadLeft(2, '0') : string.Empty);
// 将每行文本保存到Sheet中
string[] jpTexts = jpText.Split('\n');
string[] cnTexts = cnText.Split('\n');
for (int i = 0; i < jpTexts.Length; i++)
{
// 写入日文文本
Microsoft.Office.Interop.Excel.Range rngJp = xSheet.get_Range("A" + (i + 1), Missing.Value);
rngJp.Value2 = jpTexts[i];
}
for (int i = 0; i < cnTexts.Length; i++)
{
// 写入中文文本
Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("G" + (i + 1), Missing.Value);
rngCn.Value2 = cnTexts[i];
}
// 更新进度条
this.ProcessBarStep();
}
// 保存
xSheet.SaveAs(
fileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// 隐藏进度条
this.CloseProcessBar();
// 显示保存完成信息
MessageBox.Show("导出完成！");
}
catch (Exception me)
{
MessageBox.Show(this.baseFile + "\n" + me.Message);
}
finally
{
// 隐藏进度条
this.CloseProcessBar();
// 清空各种对象
xSheet = null;
xBook = null;
if (xApp != null)
{
xApp.Quit();
xApp = null;
}
}
}
//将listview中的数据导出到excel中
private void DoExport(ListView listView, string strFileName)
{
int rowNum = listView.Items.Count;//行数
int columnNum = listView.Items[0].SubItems.Count;//列数
int rowIndex = 1;
int columnIndex = 0;
if (rowNum == 0 || string.IsNullOrEmpty(strFileName))
{
return;
}
if (rowNum > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
object missing = System.Reflection.Missing.Value;//作为缺省值参数传给word或excel对象的某个函数
Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
if (xlApp == null)
{
MessageBox.Show("无法创建excel对象，可能您的系统没有安装excel");
return;
}
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
//将ListView的列名导入Excel表第一行
foreach (ColumnHeader dc in listView.Columns)
{
columnIndex++;
xlSheet.Cells[rowIndex, columnIndex] = dc.Text;
}
//将ListView中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
xlSheet.Cells[rowIndex, columnIndex] = Convert.ToString(listView.Items[i].SubItems[j].Text) + "\t";
range = xlSheet.get_Range(xlSheet.Cells[rowIndex, columnIndex], xlSheet.Cells[rowIndex, columnIndex]);//表示获取要设置的单元格或单元格范围
range.Columns.AutoFit(); // 设置列宽为自动适应
}
}
xlSheet.SaveAs(strFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
MessageBox.Show("日志导出成功");
xlApp.Quit();
}
}
/// <summary>
/// OEM销售成绩表
/// </summary>
public static int ExportOEMSales(string templetFile, string outFile, DataTable dt, Hashtable ht)
{
object missing = Missing.Value;
DateTime beforeTime;
DateTime afterTime;
#region Excel文件初始化
//只读属性的变更
FileInfo fi = new FileInfo(templetFile);
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
//拷贝模版文件生成新文件
if (File.Exists(templetFile))
{
try
{
File.Copy(templetFile, outFile, true);
}
catch (Exception ex)
{
Logger.Error("文件正在运行，重新生成文件失败。", ex);
return(CConstant.EXPORT_RUNNING);
}
}
else
{
Logger.Error("模版文件不存在。", null);
return(CConstant.EXPORT_TEMPLETE_FILE_NOT_EXIST);
}
beforeTime = DateTime.Now;
//创建一个Application对象并使其可见
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = false;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(outFile, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
//特定内容的替换
foreach (DictionaryEntry de in ht)
{
try
{
workSheet.Cells.Replace(de.Key, de.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch
{
continue;
}
}
#endregion
//数据
int startRow = 8;
int endRow = dt.Rows.Count + startRow;
int[] str = { 1, 2, 3, 5, 4, 6, 7, 10, 13, 14, 20 };
for (int i = startRow; i < endRow; i++)
{
DataRow dr = dt.Rows[i - startRow];
for (int j = 0; j < dt.Columns.Count; j++)
{
workSheet.Cells[i, str[j]] = dr[j];
}
}
//输出Excel文件并退出
try
{
workBook.Save();
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 ex)
{
try
{
File.Delete(outFile);
}
catch (Exception e)
{
Logger.Error("Excel文件保存,失败后复制文件的删除。", e);
}
Logger.Error("Excel文件保存失败。", null);
return(CConstant.EXPORT_FAILURE);
}
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();
}
}
}
return(CConstant.EXPORT_SUCCESS);
}
/// <summary>
/// 创建一个空的Excel文件(2003版的Excel)
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string CreateBlankExcel(string path)
{
Microsoft.Office.Interop.Excel.Application excelApp;
Microsoft.Office.Interop.Excel.Workbook excelDoc;
excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (File.Exists(path))
{
File.Delete(path);
}
Object nothing = Missing.Value;
excelDoc = excelApp.Workbooks.Add(nothing);
Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal;
excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing);
excelDoc.Close(nothing, nothing, nothing);
excelApp.Quit();
return path;
}
/// <summary>
/// 把Microsoft.Office.Interop.Excel文件转换成PDF格式文件
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns>true=转换成功</returns>
public static bool ExcelToPDF(string sourcePath, string targetPath)
{
targetPath = Regex.Replace(targetPath, " ", "", RegexOptions.IgnoreCase);//去除字符串中间的空格
bool result = false;
Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass application = null;
Microsoft.Office.Interop.Excel.Workbook workBook = null;
try
{
bool TimeOver = false; //转换时间到了
System.Timers.Timer timer = new System.Timers.Timer(30000);
timer.AutoReset = false; //只循环一次
timer.Elapsed += delegate(object sender, System.Timers.ElapsedEventArgs e)
{
TimeOver = true;
};
timer.Start();
Thread th = new Thread(new ThreadStart(delegate()
{
try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
application.Visible = false;
workBook = application.Workbooks.Open(sourcePath);
workBook.Application.DisplayAlerts = false;
workBook.SaveAs();
workBook.ExportAsFixedFormat(targetType, targetPath);
}
catch
{
}
}));
th.IsBackground = true;//后台线程
th.Start();
//如果转换没有成功就一直循环
while (th.ThreadState != System.Threading.ThreadState.Stopped)
{
//如果一直没有转换成功，但是最大的转换时间到了也直接跳出While循环
if (TimeOver)
{
break;
}
}
if (th.ThreadState != System.Threading.ThreadState.Stopped)
{
th.Abort();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
result = false;
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
//判断文件是否转换成功
if (File.Exists(targetPath))
{
return(true);
}
else
{
return(false);
}
}
public static Workbook Open(string excelFilename)
{
Workbook wb = new Workbook();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(excelFilename,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
// Loop throught Worksheets
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = null;
for(int w = 1; w <= excelWorkbook.Worksheets.Count; ++w)
{
excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Worksheets[w];
Worksheet ws = new Worksheet();
ws.Name = excelWorksheet.Name;
Microsoft.Office.Interop.Excel.Range usedRange = excelWorksheet.UsedRange;
// convert the range to a System.Array
System.Array array = (System.Array)usedRange.Cells.Value2;
System.Diagnostics.Debug.WriteLine("Worksheet of name " + ws.Name);
if (array != null)
{
System.Diagnostics.Debug.WriteLine("Rows range from " + array.GetLowerBound(0) + " to " + array.GetUpperBound(0));
System.Diagnostics.Debug.WriteLine("Columns range from " + array.GetLowerBound(1) + " to " + array.GetUpperBound(1));
for (int i = array.GetLowerBound(0); i <= array.GetUpperBound(0); ++i)
{
for (int j = array.GetLowerBound(1); j <= array.GetUpperBound(1); ++j)
{
if (array.GetValue(i, j) == null)
{
//System.Diagnostics.Debug.WriteLine("cell (" + i + "," + j + ") = null");
}
else
{
//System.Diagnostics.Debug.WriteLine("cell (" + i + "," + j + ") = " + array.GetValue(i, j).ToString());
}
// Note: Excel uses 1 based indices
if (array.GetValue(i, j) != null)
{
string sText = array.GetValue(i, j).ToString();
ws.SetText(i - 1, j - 1, sText);
}
}
}
wb.AddWorkSheet(ws);
}
}
// Clean up
excelWorkbook.Close(false, null, null);
excelApp.Workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorksheet = null;
excelWorkbook = null;
if (excelApp != null)
{
System.Diagnostics.Process[] pProcess;
pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
pProcess[0].Kill();
}
excelApp = null;
GC.Collect();
return wb;
}
public bool DataSet2ExcelFile(DataSet ds, string OpenFileName, string SaveFileName)
{
string FilePath = SaveFileName;
if (!Directory.Exists(Path.GetDirectoryName(FilePath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(FilePath));
}
if (File.Exists(FilePath)) File.Delete(FilePath);
//创建Application对象
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xApp.Visible = false;
//Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(@"C:\医药制造业\Sample.xls",
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(OpenFileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
//指定要操作的Sheet，两种方式：
Microsoft.Office.Interop.Excel.Worksheet xSheetSample;
List<int> savesheetid = new List<int>();
foreach (DataTable dt in ds.Tables)
{
string tablename = dt.TableName;
xSheetSample = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[tablename];
//利用二维数组批量写入 ;
string[,] ss = new string[dt.Rows.Count + 1, dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
ss[0, i] = dt.Columns[i].ColumnName;
}
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int k = 0; k < dt.Columns.Count; k++)
{
if (string.IsNullOrEmpty(dt.Rows[j][k].ToString()))
{
ss[j + 1, k] = "";
}
else
{
if (dt.Rows[j][k].ToString().Length > 3000)
{
ss[j + 1, k] = dt.Rows[j][k].ToString().Substring(0, 3000);
}
else
{
ss[j + 1, k] = dt.Rows[j][k].ToString();
}
}
}
}
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xSheetSample.Cells[1, 1];
range = range.get_Resize(dt.Rows.Count + 1, dt.Columns.Count);
range.Value2 = ss;
range.Value2 = range.Value2;
}
int sheetcount = xBook.Sheets.Count;
xApp.DisplayAlerts = false;
for (int i = sheetcount; i >= 1; i--)
{
if (!savesheetid.Contains(i))
{
((Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i]).Delete();
}
}
xApp.DisplayAlerts = false;
//保存方式一：保存WorkBook
xBook.SaveAs(FilePath,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
////保存方式二：保存WorkSheet
//xSheet.SaveAs(@"C:\CData2.xls",
//Missing.Value, Missing.Value, Missing.Value, Missing.Value,
//Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//保存方式三
xBook.Save();
xSheetSample = null;
xBook = null;
xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出
xApp = null;
GC.Collect();
KillProcess("EXCEL");
System.Threading.Thread.Sleep(1000 * 3);
return true;
}
protected void m_exportButton_Click(object sender, EventArgs e)
{
//取得所有学生的数据
string commandText = "select * from Stu";
System.Data.DataTable dt = m_sqlHelper.Query(commandText);
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel._Workbook xBk;
Microsoft.Office.Interop.Excel._Worksheet xSt;
xBk = excel.Workbooks.Add(true);
xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;
int i = 1;
foreach (DataColumn col in dt.Columns)
{
xSt.Cells[1, i] = col.ColumnName.ToString();
i++;
}
int dataRow = 2;
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int k = 0; k < dt.Columns.Count; k++)
{
xSt.Cells[dataRow, k + 1] = dt.Rows[j][k].ToString();
}
dataRow++;
}
xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "Stu2014.xls");
dt = null;
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
string path = Server.MapPath("Stu2014.xls");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
}
public bool DataTable2ExcelFile(DataTable dt, string filename)
{
string FilePath = filename;
if (!Directory.Exists(Path.GetDirectoryName(FilePath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(FilePath));
}
if (File.Exists(FilePath)) File.Delete(FilePath);
//创建Application对象
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xApp.Visible = false;
//Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(@"C:\医药制造业\Sample.xls",
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks.Add(Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet xSheetSample = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];
//利用二维数组批量写入 ;
string[,] ss = new string[dt.Rows.Count + 1, dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
ss[0, i] = dt.Columns[i].ColumnName;
}
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int k = 0; k < dt.Columns.Count; k++)
{
if (string.IsNullOrEmpty(dt.Rows[j][k].ToString()))
{
ss[j + 1, k] = "";
}
else
{
if (dt.Rows[j][k].ToString().Length > 800)
{
ss[j + 1, k] = "'" +dt.Rows[j][k].ToString().Substring(0, 800);
}
else
{
ss[j + 1, k] = "'" + dt.Rows[j][k].ToString();
}
}
}
}
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xSheetSample.Cells[1, 1];
range = range.get_Resize(dt.Rows.Count + 1, dt.Columns.Count);
range.Value2 = ss;
//range.Value2 = range.Value2;
//保存方式一：保存WorkBook
xBook.SaveAs(FilePath,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
////保存方式二：保存WorkSheet
//xSheet.SaveAs(@"C:\CData2.xls",
//Missing.Value, Missing.Value, Missing.Value, Missing.Value,
//Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//保存方式三
xBook.Save();
xSheetSample = null;
xBook = null;
xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出
xApp = null;
GC.Collect();
//KillProcess("EXCEL");
//System.Threading.Thread.Sleep(1000 * 3);
return true;
}
/// <summary>
/// 数据库转为excel表格
/// </summary>
/// <param name="dataTable">数据库数据</param>
/// <param name="SaveFile">导出的excel文件</param>
public static void DataSetToExcel(MyDataBaseClass DataBase, string SaveFile)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel._Workbook workBook;
Microsoft.Office.Interop.Excel._Worksheet workSheet;
object misValue = System.Reflection.Missing.Value;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
int rowIndex =1;
int colIndex = 1;
try
{
//取得标题
foreach (var col in DataBase.ColumnMember)
{
string value = col as string;
excel.Cells[1, colIndex] = value;
colIndex++;
}
int temp = DataBase.RowCount;
//取得表格中的数据
for (int i =1; i <= DataBase.RowCount; i++)
{
for (int j = 0; j < DataBase.ColumnCount; j++)
{
colIndex =i+1;
rowIndex = j+1 ;
int index = (i-1) * DataBase.ColumnCount + j;
string value = DataBase.RowMember[index] as string;
excel.Cells[colIndex, rowIndex] = value;
//设置表格内容居中对齐
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[colIndex, rowIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//设置表格自动适应大小
workSheet.Cells.Columns.AutoFit();
}
}
workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}
excel.Visible = false;
workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.Kill(excel);//调用kill当前excel进程
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
/// <summary>
/// datagridview导出Excel,
/// </summary>
/// <param name="fname">保存的文件名</param>
/// <param name="strCaption">标题行</param>
/// <param name="myDGV">datagridview</param>
/// <returns></returns>
public static string ExportExcel(string fname, string strCaption, DataGridView myDGV)
{
string result = "";
// 列索引，行索引，总列数，总行数
int ColIndex = 0;
int RowIndex = 0;
int ColCount = myDGV.ColumnCount;
int RowCount = myDGV.RowCount + 1;
if (myDGV.RowCount == 0)
{
result = "没有数据可供导出";
}
// 创建Excel对象
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
result = "创建Excel文件失败";
}
try
{
// 创建Excel工作薄
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
// 设置标题
Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //标题所占的单元格数与DataGridView中的列数相同
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = strCaption;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, ColCount];
//获取列标题
foreach (DataGridViewColumn col in myDGV.Columns)
{
objData[RowIndex, ColIndex++] = col.HeaderText;
}
// 获取数据
for (RowIndex = 1; RowIndex < RowCount; RowIndex++)
{
for (ColIndex = 0; ColIndex < ColCount; ColIndex++)
{
if (myDGV[ColIndex, RowIndex - 1].ValueType == typeof(string)
|| myDGV[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))//这里就是验证DataGridView单元格中的类型,如果是string或是DataTime类型,则在放入缓存时在该内容前加入" ";
{
objData[RowIndex, ColIndex] = "'" + myDGV[ColIndex, RowIndex - 1].Value;
}
else
{
objData[RowIndex, ColIndex] = myDGV[ColIndex, RowIndex - 1].Value;
}
}
System.Windows.Forms.Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount, ColCount]);
range.Value2 = objData;
//保存
xlBook.Saved = true;
xlBook.SaveCopyAs(fname);
//返回值
result = "成功导出，保存在c:\\";
}
catch (Exception)
{
result = "出现错误";
}
finally
{
xlApp.Quit();
GC.Collect(); //强制回收
}
return result;
}
///////////////////////////////////////////////////////////////
//メソッド名 ： OutputExcel
/// <summary>
/// 指定GridviewのデータをExcelに出力する
/// </summary>
/// <param name="targetDataGridView">対象DataGridView</param>
/// <param name="outputFilename"></param>
/// <history>
/// 日付 担当者 内容
/// 2014/06/30 DatNT 新規作成
/// </history>
///////////////////////////////////////////////////////////////
private static void OutputExcel(DataGridView targetDataGridView,string outputFilename)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;
try
{
// 保存確認ダイアログの表示
SaveFileDialog dlg = new SaveFileDialog();
dlg.FileName = outputFilename;
dlg.Filter = "Excel files (*.xls)|*.xls";
dlg.FilterIndex = 1;
// ＯＫボタンで終了した場合
if (dlg.ShowDialog() == DialogResult.OK)
{
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= targetDataGridView.Rows.Count - 1; i++)
{
for (j = 0; j <= targetDataGridView.Columns.Count - 1; j++)
{
if (targetDataGridView.Columns[j].Visible)
{
DataGridViewCell cell = targetDataGridView[j, i];
if (i == 0)
{
DataGridViewHeaderCell h = targetDataGridView.Columns[j].HeaderCell;
xlWorkSheet.Cells[i + 1, j + 1] = h.Value;
}
// Code columns format
if (targetDataGridView.Columns[j].Name.Length > 5
&& targetDataGridView.Columns[j].Name.Substring(targetDataGridView.Columns[j].Name.Length - 5) == "CdCol")
{
xlWorkSheet.Cells[i + 2, j + 1] = "'" + cell.Value;
Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 2, j + 1];
rng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
continue;
}
if (targetDataGridView.Columns[j].Name == "NameKbnCol")
{
xlWorkSheet.Cells[i + 2, j + 1] = cell.FormattedValue.ToString();
continue;
}
if (targetDataGridView.Columns[j].Name == "NameCol")
{
xlWorkSheet.Cells[i + 2, j + 1] = "'" + cell.Value;
continue;
}
xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
}
}
}
xlWorkBook.SaveAs(dlg.FileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
misValue,
misValue,
misValue,
misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue,
misValue,
misValue,
misValue,
misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
}
catch
{
throw;
}
finally
{
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
private void ListToko()
{
DataTable da = dtToko.DefaultView.ToTable().Copy();
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
progressBar1.Visible = true;
progressBar1.Maximum = dataGridToko.Rows.Count;
progressBar1.Value = 0;
//for (int i = 1; i < dataGridToko.Columns.Count + 1; i++)
//{
// //ExcelApp.Cells[1, i] = dataGridToko.Columns[i - 1].HeaderText;
// ExcelApp.Cells[1, 1] = dataGridToko.Columns[0].HeaderText;
// ExcelApp.Cells[1, 2] = dataGridToko.Columns[1].HeaderText;
//}
ExcelApp.Cells[1, 1] = "Nama Toko";
ExcelApp.Cells[1, 2] = "Id.Wil";
ExcelApp.Cells[1, 3] = "Id.Toko";
ExcelApp.Cells[1, 4] = "K";
ExcelApp.Cells[1, 5] = "Alamat";
ExcelApp.Cells[1, 6] = "Kota";
ExcelApp.Cells[1, 7] = "Daerah";
ExcelApp.Cells[1, 8] = "Propinsi";
ExcelApp.Cells[1, 9] = "No.Telp";
ExcelApp.Cells[1, 10] = "Penanggung Jawab";
ExcelApp.Cells[1, 11] = "Hari Sales";
ExcelApp.Cells[1, 12] = "Hari Kirim";
ExcelApp.Cells[1, 13] = "Status";
int x = 0;
int i = 0;
foreach (DataRow dr in da.Rows)
{
Application.DoEvents();
this.Invalidate();
progressBar1.Value = x;
x++;
ExcelApp.Cells[i + 2, 1] = Tools.isNull(dr["NamaToko"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 2] = Tools.isNull(dr["WilID"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 3] = Tools.isNull("'" + dr["TokoID"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 4] = Tools.isNull(dr["Cabang2"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 5] = Tools.isNull(dr["Alamat"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 6] = Tools.isNull(dr["Kota"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 7] = Tools.isNull(dr["Daerah"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 8] = Tools.isNull(dr["Propinsi"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 9] = Tools.isNull("'" + dr["Telp"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 10] = Tools.isNull(dr["PenanggungJawab"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 11] = Tools.isNull(dr["HariSales"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 12] = Tools.isNull(dr["JangkaWaktuKredit"], "").ToString().Trim();
ExcelApp.Cells[i + 2, 13] = Tools.isNull(dr["StatusAktif"], "").ToString().Trim();
i++;
}
//for (int i = 0; i < dataGridToko.Rows.Count; i++)
//{
// Application.DoEvents();
// this.Invalidate();
// progressBar1.Value = x;
// x++;
// for (int j = 0; j < dataGridToko.Columns.Count; j++)
// {
// //ExcelApp.Cells[i + 2, j + 1] = dataGridToko.Rows[i].Cells[j].Value.ToString();
// //ExcelApp.Cells[i + 2, j + 1] = Tools.isNull(dataGridToko.Rows[i].Cells[j].Value,"").ToString();
// ExcelApp.Cells[i + 2, 1] = Tools.isNull(dataGridToko.Rows[i].Cells[0].Value, "").ToString();
// ExcelApp.Cells[i + 2, 2] = Tools.isNull(dataGridToko.Rows[i].Cells[1].Value, "").ToString();
// }
//}
ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Temp\\Toko.xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
progressBar1.Visible = false;
}
public static string ExcelMultiSheet(DataSet ds)
{
string filepath;
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Application.Workbooks.Add();
DataTable table100 = ds.Tables[0];
DataTable table50first = ds.Tables[1];
DataTable table50second = ds.Tables[2];
DataTable table50full = ds.Tables[3];
Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[1];
sheet1.Name = "100% MPA";
for (int i = 0; i < table100.Columns.Count; i++)
{
sheet1.Cells[1, i + 1] = table100.Columns[i].ColumnName;
}
for (int i = 0; i < table100.Rows.Count; i++)
{
for (int j = 0; j < table100.Columns.Count; j++)
{
sheet1.Cells[i + 2, j + 1] = table100.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[2];
sheet2.Name = "50% MPA 1st 50%";
for (int i = 0; i < table50first.Columns.Count; i++)
{
sheet2.Cells[1, i + 1] = table50first.Columns[i].ColumnName;
}
for (int i = 0; i < table50first.Rows.Count; i++)
{
for (int j = 0; j < table50first.Columns.Count; j++)
{
sheet2.Cells[i + 2, j + 1] = table50first.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet3 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[3];
sheet3.Name = "50% MPA 2nd 50%";
for (int i = 0; i < table50second.Columns.Count; i++)
{
sheet3.Cells[1, i + 1] = table50second.Columns[i].ColumnName;
}
for (int i = 0; i < table50second.Rows.Count; i++)
{
for (int j = 0; j < table50second.Columns.Count; j++)
{
sheet3.Cells[i + 2, j + 1] = table50second.Rows[i][j].ToString();
}
}
excelApp.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[4];
//Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets.Add(After: excelApp.Sheets[excelApp.Sheets.Count]);
sheet4.Name = "50% MPA 100% 2pcs & 1+1 pcs";
for (int i = 0; i < table50full.Columns.Count; i++)
{
sheet4.Cells[1, i + 1] = table50full.Columns[i].ColumnName;
}
for (int i = 0; i < table50full.Rows.Count; i++)
{
for (int j = 0; j < table50full.Columns.Count; j++)
{
sheet4.Cells[i + 2, j + 1] = table50full.Rows[i][j].ToString();
}
}
filepath = @"C:\ReceivedPO\" + DateTime.Today.ToString("yyyyMMdd") + "_ReceivedPO.xls";
string filename = "";
SaveFileDialog sfd = new SaveFileDialog()
{
DefaultExt = "xls",
Filter = "EXCEL Files (*.xls)|*.xls",
FilterIndex = 1,
FileName = DateTime.Today.ToString("yyyyMMdd") + "_ReceivedPO"// + year + month + day,
};
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
filename = sfd.FileName;
excelApp.ActiveWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//excelApp.ActiveWorkbook.Saved = true;
//excelApp.AlertBeforeOverwriting = false;
excelApp.Quit();
MessageBox.Show("Record saved to " + filename);
}
catch (COMException ex)
{
MessageBox.Show(ex.Message + ex.StackTrace);
Debug.WriteLine(ex.Message + ex.StackTrace);
}
}
return(filename);
}
private void CsvExportButton_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
//Обрати таблицю з ComboBox
ComboBoxItem comboItem = (ComboBoxItem)ComboBox.SelectedItem;
string name = comboItem.Name;
//запит для виводу всіх даних з таблиці
string cmdText = String.Format("SELECT * FROM {0}", name);
SqlCommand cmd = new SqlCommand(cmdText, con);
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
DataColumnCollection dcCollection = dt.Columns;
//ЕКспорт даних в лист EXCEL
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Application.Workbooks.Add(Type.Missing);
// ExcelApp.Cells.CopyFromRecordset(objRS);
for (int i = 1; i < dt.Rows.Count + 1; i++)
{
for (int j = 1; j < dt.Columns.Count + 1; j++)
{
if (i == 1)
{
excelApp.Cells[i, j] = dcCollection[j - 1].ToString();
}
else
{
excelApp.Cells[i, j] = dt.Rows[i - 1][j - 1].ToString();
}
}
}
//Діалог збереження файлу
Microsoft.Win32.SaveFileDialog dialog = new Microsoft.Win32.SaveFileDialog();
//Ім'я файлу за замовчуванням
dialog.Title = "Збереження";
dialog.FileName = comboItem.Name + " " + DateTime.Now.ToString("d-M-yyyy");
dialog.Filter = "Таблиця Excel|*.xlsx";
if (dialog.ShowDialog() == true)
{
//збереження xlsx файлу
excelApp.ActiveWorkbook.SaveCopyAs(dialog.FileName);
excelApp.ActiveWorkbook.Saved = true;
excelApp.Quit();
//Повідомлення про успішне експортування
MessageBox.Show("Експорт виконано успішно!", "Повідомлення",
MessageBoxButton.OK, MessageBoxImage.Information);
}
}
}
}
}
/// <summary>
/// 采购注文书国外
/// </summary>
public static int ExportPurchaseOverseas(string templetFile, string outFile, DataTable dt, Hashtable ht)
{
object missing = Missing.Value;
DateTime beforeTime;
DateTime afterTime;
#region Excel文件初始化
//只读属性的变更
FileInfo fi = new FileInfo(templetFile);
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
//拷贝模版文件生成新文件
if (File.Exists(templetFile))
{
try
{
File.Copy(templetFile, outFile, true);
}
catch (Exception ex)
{
Logger.Error("文件正在运行，重新生成文件失败。", ex);
return(CConstant.EXPORT_RUNNING);
}
}
else
{
Logger.Error("模版文件不存在。", null);
return(CConstant.EXPORT_TEMPLETE_FILE_NOT_EXIST);
}
beforeTime = DateTime.Now;
//创建一个Application对象并使其可见
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = false;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(outFile, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
//特定内容的替换
foreach (DictionaryEntry de in ht)
{
try
{
workSheet.Cells.Replace(de.Key, de.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch
{
continue;
}
}
#endregion
//数据
int startRow = 19;
int endRow = dt.Rows.Count + startRow;
int addLine = 0;
for (int i = startRow; i < endRow; i++)
{
if (i >= 36 && i < endRow - 1)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
addLine++;
}
DataRow dr = dt.Rows[i - startRow];
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName.Contains("X_"))
{
continue;
}
if (dr[j].GetType() == typeof(string))
{
workSheet.Cells[i, j + 1] = "'" + Convert.ToString(dr[j]);
}
else
{
workSheet.Cells[i, j + 1] = Convert.ToString(dr[j]);
}
}
}
startRow = 78 + addLine;
endRow = dt.Rows.Count + startRow;
for (int i = startRow; i < endRow; i++)
{
if (i >= (95 + addLine) && i < endRow - 1)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
DataRow dr = dt.Rows[i - startRow];
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName.Contains("X_"))
{
continue;
}
if (dr[j].GetType() == typeof(string))
{
workSheet.Cells[i, j + 1] = "'" + Convert.ToString(dr[j]);
}
else
{
workSheet.Cells[i, j + 1] = Convert.ToString(dr[j]);
}
}
}
#region 输出Excel文件并退出
//输出Excel文件并退出
try
{
workBook.Save();
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 ex)
{
try
{
File.Delete(outFile);
}
catch (Exception e)
{
Logger.Error("Excel文件保存,失败后复制文件的删除。", e);
}
Logger.Error("Excel文件保存失败。", null);
return(CConstant.EXPORT_FAILURE);
}
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();
}
}
}
#endregion
return(CConstant.EXPORT_SUCCESS);
}
public static void SaveAsExcel(string name, DataTable dt)
{
OleDbConnection cnnxls = new OleDbConnection(string.Format(excelstring, name));
string insertsql = "";
string insertcolumnstring = "";
string insertvaluestring = "";
string fileName = name + ".xls";
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
int colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
insertcolumnstring += string.Format("{0},", col.ColumnName);
}
excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//������¼
//conn.execute(sql);
insertcolumnstring = insertcolumnstring.Trim(',');
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
row[dc].ToString();
insertvaluestring += string.Format("'{0}',", row[dc].ToString().Replace("'", "''"));
}
string sql = string.Format("insert into [Sheet1$]({0}) values({1})", insertcolumnstring, insertvaluestring);
OleDbDataAdapter myDa = new OleDbDataAdapter(sql, cnnxls);
myDa.InsertCommand.ExecuteNonQuery();
}
excel.Visible = false;
//excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
excel.Quit();
excel = null;
GC.Collect();//��������
}
/// <summary>
/// 导出Excel文件
/// </summary>
public static int DataTableToExcel(DataTable dt, string title, string columns, string sheetName, string fileName)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.FileName = "LZ_" + fileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);;
excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
if (excel == null)
{
Logger.Error("Excel文件保存失败。", null);
return(CConstant.EXPORT_FAILURE);
}
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = sheetName;
int m = 0, n = 0;
//生成列名称 这里i是从1开始的 因为我第0列是个隐藏列ID 没必要写进去
string[] strHeader = title.Split(',');
string[] strColumns = columns.Split(',');
for (int i = 0; i < strHeader.Length; i++)
{
excel.Cells[1, i + 1] = strHeader[i].ToString();
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
//j也是从1开始 原因如上 每个人需求不一样
int j = 1;
foreach (string str in strColumns)
{
try
{
if (dr[str.Trim()].GetType() == typeof(string))
{
excel.Cells[i + 2, j] = "'" + dr[str.Trim()].ToString();
j++;
}
else
{
excel.Cells[i + 2, j] = dr[str.Trim()].ToString();
j++;
}
}
catch
{
j++;
continue;
}
}
}
try
{
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
//MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//System.Diagnostics.Process.Start(strName);
}
catch (Exception ex)
{
Logger.Error("Excel文件保存失败。", ex);
return(CConstant.EXPORT_FAILURE);
}
}
return(CConstant.EXPORT_SUCCESS);
}
else
{
return(CConstant.EXPORT_CANCEL);
}
return(CConstant.EXPORT_SUCCESS);
}
