public static System.Data.DataTable Import(String path)
{
Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
object rowIndex = 1;
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Velina");
dt.Columns.Add("Contratto");
dt.Columns.Add("StatoAgente");
dt.Columns.Add("Banca");
DataRow row;
while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2 != null)
{
rowIndex = 1 + index;
row = dt.NewRow();
if ((((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2 != null))
{
row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2);
row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 27]).Value2);
row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
index++;
dt.Rows.Add(row);
}
}
app.Workbooks.Close();
return dt;
}
public bool CreateReport()
{
bool rtn = false;
try
{
if (File.Exists(sourceFile))
{
File.Delete(sourceFile);
}
File.Copy(testFile, sourceFile);
copyTemp();
Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Visible = false;
excel.Workbooks.Open(reportFile, 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);
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
rtn = true;
File.Delete(sourceFile);
}
catch (Exception ex)
{
File.Delete(sourceFile);
//WriteLog(sourceFileName + "-Excel权限问题或没有安装，数据没有上传!");
//MessageBox.Show("Excel问题导致上传失败,请重试", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
rtn = false;
}
return(rtn);
}
private void btn_Excele_Aktar_Click(object sender, EventArgs e)
{
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);
for (int i = 0; i < lb_Dosyalar.Items.Count; i++)
{
xlWorkSheet.Cells[i + 2, 2] = lb_Dosyalar.Items[i].ToString();
//Dosyaya hyperlink at
xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[i + 2, 2], tb_Secilen_Dosya_Yolu.Text + "/" + lb_Dosyalar.Items[i].ToString());
}
string kaydedilecek_dosya_yolu = fbr_Dosya_Tarayici_Diyalog.SelectedPath + "/literatur.xls";
xlWorkBook.SaveAs(kaydedilecek_dosya_yolu, 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);
}
public static System.Data.DataTable Import(String path)
{
Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
object rowIndex = 1;
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Velina");
dt.Columns.Add("Contratto");
dt.Columns.Add("StatoAgente");
dt.Columns.Add("Banca");
DataRow row;
while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2 != null)
{
rowIndex = 1 + index;
row = dt.NewRow();
if ((((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2 != null))
{
row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2);
row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 27]).Value2);
row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
index++;
dt.Rows.Add(row);
}
}
app.Workbooks.Close();
return(dt);
}
private void ThreadFun()
{
try
{
Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Visible = false;
excel.Workbooks.Open(reportFile, 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);
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
catch (Exception ex)
{
File.Delete(sourceFile);
if (File.Exists(reportFile))
{
File.Delete(reportFile);
}
WriteLog(sourceFileName + "-Excel权限问题或没有安装，数据没有上传!");
MessageBox.Show("Excel问题导致上传失败,请重试", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
File.Delete(sourceFile);
bool uploaded = false;
int loadCount = 0;
while (!uploaded && loadCount < 3)
{
uploaded = uploadData();
loadCount++;
string tt = uploaded ? "-数据上传成功!" : "-数据上传失败!";
WriteLog(sourceFileName + tt + "-" + loadCount);
}
//if (uploaded)
//{
// WriteLog(sourceFileName + "-数据上传成功!");
//}
//else
//{
// WriteLog(sourceFileName + "-数据上传失败!");
//}
Thread.Sleep(3000);
File.Delete(resultFile);
WriteLog("上传数据结束");
if (!uploaded)
{
if (File.Exists(reportFile))
{
File.Delete(reportFile);
}
MessageBox.Show(sourceFileName + "---数据上传失败,请联系管理员查找原因！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private bool ThreadFun()
{
bool rtn = true;
try
{
Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Visible = false;
excel.Workbooks.Open(reportFile, 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);
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
catch (Exception ex)
{
if (File.Exists(reportFile))
{
File.Delete(reportFile);
}
File.Delete(sourceFile);
LogHelper.WriteLog(sourceFileName + "-Excel权限问题或没有安装，云端处理数据失败!");
// XtraMessageBox.Show("Excel问题导致云端处理数据失败,请重试", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
File.Delete(sourceFile);
bool uploaded = false;
int loadCount = 0;
while (!uploaded && loadCount < 3)
{
uploaded = uploadData();
loadCount++;
string tt = uploaded ? "-云端处理数据成功!" : "-云端处理数据失败!";
LogHelper.WriteLog(sourceFileName + tt + "-" + loadCount);
}
File.Delete(resultFile);
LogHelper.WriteLog("云端处理数据结束");
if (!uploaded)
{
LogHelper.WriteLog(sourceFileName + "---云端数据处理分析失败,请联系管理员查找原因！！！");
//XtraMessageBox.Show(sourceFileName + "---云端数据处理分析失败,请联系管理员查找原因！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
rtn = false;
}
else
{
LogHelper.WriteLog("云端数据处理成功！");
//XtraMessageBox.Show("云端数据处理成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
rtn = true;
}
return(rtn);
}
// 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文档转换成PDF格式
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <param name="targetType"></param>
/// <returns></returns>
public bool ExcelConvertPDF(string sourcePath, string targetPath = null)
{
if (sourcePath == null)
{
throw new ArgumentNullException("wpsFilename");
}
//保存路径为空时，保存在原始文件目录
if (targetPath == null)
{
targetPath = Path.ChangeExtension(sourcePath, "pdf");
}
bool result;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass application = null;
Workbook workBook = null;
try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
object target = targetPath;
object type = XlFixedFormatType.xlTypePDF;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, target, XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch (Exception ex)
{
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);
}
/// <summary>
/// 转换excel为pdf
/// </summary>
/// <param name="filename">doc文件路径</param>
/// <param name="savefilename">pdf保存路径</param>
public static void ConvertExcelPDF(string filename, string PDFFileName)
{
//先引入：Microsoft.Office.Interop
//再 using Microsoft.Office.Interop.Excel;
Microsoft.Office.Interop.Excel.ApplicationClass cExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
cExcel.Visible = true;
object missing = Type.Missing;
//excel 文件名：
string excelFileName = filename.ToString();
Int32 intLastDot = excelFileName.LastIndexOf(".");
string tmp_FilePath = excelFileName.Substring(0, intLastDot);
Microsoft.Office.Interop.Excel.Workbook book = null;
try
{
book = cExcel.Workbooks.Open(excelFileName, missing, missing, missing, missing
, missing, missing, missing, missing, missing, missing
, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1];
//参数1
var formatType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
//参数3
var quarlity = Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard;
//倒数第二个参数控制是否用pdf软件打开，false会在后台处理，不打开文件
sheet.ExportAsFixedFormat(formatType, PDFFileName, quarlity, true, true, missing, missing, false, missing);
}
catch (Exception ex)
{
}
finally
{
if (book != null)
{
book.Close(missing, missing, missing);
book = null;
}
cExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(cExcel);
cExcel = null;
GC.Collect();
}
}
/// <summary>
/// 将EXCEL文档转换成PDF格式
/// </summary>
/// <param name="_lstrInputFile"></param>
/// <param name="_lstrOutFile"></param>
/// <returns></returns>
public static bool ConvertExcelToPdf(string _lstrInputFile, string _lstrOutFile)
{
Excel.XlFixedFormatType parameter = Excel.XlFixedFormatType.xlTypePDF;
bool result;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass application = null;
Excel.Workbook workBook = null;
try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
object target = _lstrOutFile;
object type = parameter;
workBook = application.Workbooks.Open(_lstrInputFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(parameter, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch (Exception ex)
{
result = false;
throw new Exception(ex.Message, ex);
}
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);
}
/// <summary>
/// XLS 2 PDF
/// </summary>
/// <param name="sourcePath">源路径</param>
/// <param name="targetPath">目标路径</param>
/// <returns></returns>
public bool XLSToPDF(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();
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
{
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);
}
public string ReadXlsRangeValue(string path, int sheetIndex, int rownumber, int colnumber)
{
if (!File.Exists(path)) { return ""; }
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook book = null;
Excel.Worksheet sheet = null;
Excel.Range range = null;
object oV = System.Reflection.Missing.Value;
try
{
// 步骤1：打开某人的表xls
book = excel.Workbooks.Open(path, oV, oV, oV, oV,
oV, oV, oV, oV, oV, oV, oV, oV, oV, oV);
sheet = (Excel.Worksheet)excel.Sheets.get_Item(sheet);
sheet.Name = "Salary详细";
excel.Visible = false;
// 步骤2：读取数据
range = (Excel.Range)sheet.Cells[rownumber, colnumber];
string value = range.Value2.ToString();
// 步骤3：保存表格
book.Save();
// 步骤4：关闭book
excel.Workbooks.Close();
return value;
}
catch (Exception ex)
{
return "";
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
book = null;
sheet = null;
range = null;
excel = null;
GC.Collect();
}
}
public static string ReadExcel(string excelFileName)
{
string text = string.Empty;
Excel.ApplicationClass app = null;
Excel.Workbook book = null;
object readOnly = true;
object missing = System.Reflection.Missing.Value;
object fileName = excelFileName;
try
{
app = new Microsoft.Office.Interop.Excel.ApplicationClass();
book = app.Workbooks.Open(fileName.ToString(), missing, readOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
foreach (Excel.Worksheet sheet in book.Sheets)
{
text += "<table>";
for (int i = 1; i <= sheet.UsedRange.Cells.Rows.Count; i++)
{
text += "<tr><td style=\"padding:6px;border:1px solid #ccc;margin:none;margin:0px;\">" + (i - 1) + "</td>";
for (int j = 1; j <= sheet.UsedRange.Cells.Columns.Count; j++)
{
text += "<td style=\"padding:6px;border:1px solid #ccc;margin:none;margin:0px;\">" + ((Excel.Range)sheet.Cells[i, j]).Text.ToString().Replace("\r", string.Empty).Replace("\n", string.Empty).Replace("\t", string.Empty) + "</td>";
}
text += "</td>";
}
text += "</table>";
}
}
catch
{
}
finally
{
book.Close(missing, fileName, missing);
book = null;
app.Quit();
app = null;
}
return(text);
}
private void btnExcel_Click(object sender, EventArgs e)
{
String temp = "Khach hang WU";
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;
//Xuat tieu de cot
for (int i = 0; i < dtKHWU.Columns.Count; i++)
{
ExcelApp.Cells[1, i + 1] = dtKHWU.Columns[i].ColumnName;
}
//Xuat du lieu
for (int j = 0; j < dtKHWU.Rows.Count; j++)
{
for (int i = 0; i < dtKHWU.Columns.Count; i++)
{
ExcelApp.Cells[j + 2, i + 1] = dtKHWU.Rows[j][i].ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(path);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Đã xuất ra file excel.");
}
Cursor.Current = Cursors.Default;
}
private void btnExcel_Click(object sender, EventArgs e)
{
String temp = "Ke hoach cham soc KH";
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;
}
private void uploadInfo()
{
string code = "";
bool isCorrect = false;
string errormsg = "";
if (File.Exists(testFile))
{
if (File.Exists(sourceFile))
{
File.Delete(sourceFile);
}
File.Copy(testFile, sourceFile);
FileInfo fileInfo = new FileInfo(sourceFile);
string timeStr = ReadAppSetting("ChangeTime");// ConfigurationManager.AppSettings["ChangeTime"].ToString();
//文件没有更新，不处理
if (timeStr.Length == 0)
{
SetValue("ChangeTime", fileInfo.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss"));
File.Delete(sourceFile);
return;
}
DateTime dtNow = DateTime.Parse(fileInfo.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss"));
DateTime dtOld = DateTime.Parse(ReadAppSetting("ChangeTime"));
//有最新文件，开始上传数据
if (DateTime.Compare(dtNow, dtOld) > 0)
{
latestTime = dtNow.ToString("yyyy-MM-dd HH:mm:ss");
SetValue("ChangeTime", latestTime);
Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//MessageBox.Show(excel.Version);
excel.Visible = false;
excel.Workbooks.Open(sourceFile, Type.Missing, Type.Missing, Type.Missing, "halleluja", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet)excel.Worksheets[2];
string name = ((Excel.Range)ws.Cells[3, 3]).Text.ToString();
sourceFileName = name;
excel.Quit();
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
if (name.IndexOf("_") > 0)
{
string[] nameCode = name.Split('_');
if (nameCode.Length == 2)
{
code = nameCode[1].Trim();
verifyCode = code;
try
{
string strOrder = webService.isExistOrder(code, CurrentUser.UserName);
if (strOrder.Length > 2)
{
vjList = getValidate(strOrder);
if (vjList.name.Length > 0)
{
isCorrect = true;
}
string pType = vjList.peopleType;
//pType = "1";
if (pType == "1")
{
isAdult = false;
}
else
{
isAdult = true;
}
}
else if (strOrder == "-1")
{
errormsg = "验证码不存在，或者被使用,";
}
else if (strOrder == "-2")
{
errormsg = "门店用户未设置所属门店,";
}
else if (strOrder == "-3")
{
errormsg = "订单预约门店与使用门店不同,";
}
else if (strOrder == "-4")
{
errormsg = "订单状态不可用,";
}
}
catch (Exception ex)
{
string er = ex.ToString();
}
}
else
{
errormsg = "验证码录入有误,";
}
}
else
{
errormsg = "验证码录入有误,";
}
}
//没有更新文件
else
{
File.Delete(sourceFile);
WriteLog("没有更新文件，数据没有上传！");
return;
}
//上传文件到服务器
//verifyCode = "hahahaha";
FTPUpLoad();
//有更新文件，但是文件有问题
if (!isCorrect)
{
File.Delete(sourceFile);
//SetValue("ChangeTime", latestTime);
WriteLog(sourceFileName + "-" + errormsg + "文件存在问题，数据没有上传！");
MessageBox.Show(sourceFileName + "-" + errormsg + "数据没有上传！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//SetValue("ChangeTime", latestTime);
WriteLog("开始上传数据");
//copyTemp();
copyStream();
//上传数据
Thread thdSub = new Thread(new ThreadStart(ThreadFun));
thdSub.Start();
}
}
public static void ExportToExcel_FromVS(C1FlexGrid vs, ArrayList arColsVisible)
{
object oMissing = System.Reflection.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");
oBook = oBooks.Add(oMissing);
Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets[1];
oExcel.Visible = true;
try
{
// Dinh dang cot
int iCol = 1;
for (int i = 1; i < vs.Cols.Count; i++)
{
if (arColsVisible[i] + "" == "True")
{
Microsoft.Office.Interop.Excel.Range rg = oSheet.get_Range(GetExcelColumnName(iCol) + ":" + GetExcelColumnName(iCol), Missing.Value);
switch (vs.Cols[i].DataType.Name)
{
case "Double":
case "Decimal":
rg.EntireColumn.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)";
break;
case "Int16":
case "Int32":
case "Int64":
case "Single":
rg.EntireColumn.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)";
break;
case "Boolean":
break;
case "DateTime":
break;
case "String":
rg.EntireColumn.NumberFormat = "@";
break;
default:
break;
}
iCol++;
}
}
oExcel.ScreenUpdating = false;
for (int i = 0; i < vs.Rows.Count; i++)
{
iCol = 1;
for (int j = 0; j < vs.Cols.Count; j++)
{
if (arColsVisible[j] + "" == "True")
{
oSheet.Cells[i + 2, iCol] = vs.Rows[i][j];
iCol++;
}
}
}
oSheet.Columns.AutoFit();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "Error!");
}
finally
{
oExcel.ScreenUpdating = true;
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
GC.Collect();
}
}
private string DownLoadExcel_Crosscheckfile(List <clsFAinfo> Result)
{
try
{
ProcessLogger.Fatal("8948 Down Initial" + DateTime.Now.ToString());
#region 获取模板路径
string fullPath = AppDomain.CurrentDomain.BaseDirectory + "System\\confing.xls";
SaveFileDialog sfdDownFile = new SaveFileDialog();
sfdDownFile.OverwritePrompt = false;
string DesktopPath = Convert.ToString(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop));
sfdDownFile.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx";
string fileinfo = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\");
sfdDownFile.FileName = Path.Combine(fileinfo, "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls");
string strExcelFileName = string.Empty;
string ResaveName = AppDomain.CurrentDomain.BaseDirectory + "Resources\\" + "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls";
#endregion
#region 导出前校验模板信息
if (string.IsNullOrEmpty(sfdDownFile.FileName))
{
MessageBox.Show("File name can't be empty, please Check, thanks!");
return("");
}
if (!File.Exists(fullPath))
{
MessageBox.Show("Template file does not exist, please Check, thanks!");
return("");
}
else
{
strExcelFileName = sfdDownFile.FileName;
}
#endregion
#region Excel 初始化
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Range rng;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel._Workbook ExcelBook =
ExcelApp.Workbooks.Open(fullPath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
#endregion
ProcessLogger.Fatal("8949 Input Initial" + DateTime.Now.ToString());
#region 导入
try
{
#region Sheet 初始化
Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
#region 填充数据
int RowIndex = 6;
string fapiaoleixingin = "";
string guidangren = "";
string danghao = "";
string guidangshijian = "";
foreach (clsFAinfo item in Result)
{
fapiaoleixingin = item.fapiaoleixing;
guidangren = item.guidangrenzhanghao;
danghao = item.danganhao;
if (item.Input_Date != null && item.Input_Date.Length > 8)
{
guidangshijian = item.Input_Date.Substring(0, 8);
}
RowIndex++;
ExcelSheet.Cells[RowIndex, 1] = "'" + item.fapiaohao;
ExcelSheet.Cells[RowIndex, 2] = "'" + item.bianhao;
//ExcelSheet.Cells[RowIndex, 3] = item.fapiaohao;
}
//ExcelApp.Visible = true;
//ExcelApp.ScreenUpdating = true;
ExcelSheet.Cells[1, 1] = "总件数：" + Result.Count.ToString();
ExcelSheet.Cells[2, 1] = "发票类型：" + fapiaoleixingin;
ExcelSheet.Cells[3, 1] = "归档人：" + guidangren;
ExcelSheet.Cells[4, 1] = "档号：" + danghao;
ExcelSheet.Cells[5, 1] = "归档时间：" + guidangshijian;
//直接打印
//ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
//ExcelSheet.PageSetup.PaperSize =Excel.XlPaperSize.xlPaperA4;
//ExcelBook.PrintOut();
#region 写入文件
ProcessLogger.Fatal("8950 Output Initial" + DateTime.Now.ToString());
ExcelApp.DisplayAlerts = false;
ExcelApp.ScreenUpdating = true;
ExcelBook.SaveAs(ResaveName, missingValue, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missingValue, missingValue, missingValue, missingValue, missingValue);
ExcelApp.DisplayAlerts = false;
ExcelBook.Close(false, missingValue, missingValue);
ExcelBook = null;
#endregion
//转换成PDF 文件
//if (XLSConvertToPDF(ResaveName, ResaveName.Replace("xlsx", "pdf")))
//{
// // FilePath.Add(ResaveName.Replace("xlsx", "pdf"));
//}
return(ResaveName);
#endregion
#endregion
}
#endregion
#region 异常处理
catch (Exception ex)
{
ExcelApp.DisplayAlerts = false;
ExcelApp.Quit();
ExcelBook = null;
ExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
throw ex;
}
#endregion
#region Finally垃圾回收
finally
{
//ExcelBook.Close(false, missingValue, missingValue);
//ExcelBook = null;
ExcelApp.DisplayAlerts = true;
ExcelApp.Quit();
clsKeyMyExcelProcess.Kill(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet GetExcel(string fileName)
{
Microsoft.Office.Interop.Excel.Application oXL;
Workbook oWB;
Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
try
{
// creat a Application object
oXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
// get WorkBook object
oWB = oXL.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,
Missing.Value, Missing.Value);
// get WorkSheet object
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
DataSet ds = new DataSet();
ds.Tables.Add(dt);
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = oSheet.UsedRange.Cells.Columns.Count;
int iValue = oSheet.UsedRange.Cells.Rows.Count;
// get data columns
for (int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
//string colString = sb.ToString().Trim();
//string[] colArray = colString.Split(':');
// get data in cell
for (int i = 1; i <= iValue; i++)
{
dr = ds.Tables["dtExcel"].NewRow();
for (int j = 1; j <= jValue; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
string strValue = oRng.Text.ToString();
dr["column" + j] = strValue;
}
ds.Tables["dtExcel"].Rows.Add(dr);
}
return(ds);
}
catch (Exception ex)
{
return(null);
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private bool XLSConvertToPDF(string sourcePath, string targetPath)
{
bool result = false;
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = null;
Microsoft.Office.Interop.Excel._Workbook ExcelBook = null;
try
{
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);
// sourcePath = "C:\\Users\\IBM_ADMIN\\Desktop\\newadd.xlsx";
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
//ExcelApp.Visible = true;
//ExcelApp.ScreenUpdating = true;
////ActiveWindow.SmallScroll Down:=6;
//ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
////ExcelApp.ActiveWindow.SmallScroll = Excel.;
//ExcelApp.ActiveWindow.Zoom = 80;
////ActiveWindow.SmallScroll Down:=-3
//// excelRange.WrapText = true;
////ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
//ActiveWindow.SmallScroll Down:=30
//Set ActiveSheet.HPageBreaks(1).Location = Range("A67")
//ActiveWindow.SmallScroll Down:=-75
//Microsoft.Office.Interop.Excel.Worksheet WS2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[2];
////上边距
//double top = 0;
////左边距
//double left = 0;
////右边距
//double right = 0;
////下边距
//double footer = 0;
//WS2.DisplayAutomaticPageBreaks = false;//显示分页线
//WS2.PageSetup.CenterFooter = "第 &P 页，共 &N 页";
//WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(top / 2.54);//上
//WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(footer / 15.54);//下
//WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(left / 2.54);//左
//WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(right / 2.54);//右
//WS2.PageSetup.CenterHorizontally = true;//水平居中 xlSheet.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行
//WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3;//A3纸张大小 xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//纸张方向.横向
//Excel.Range excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[64, 24]);
//自动调整列宽
//// excelRange.EntireColumn.AutoFit();
//// excelRange.WrapText = false; //文本自动换行
//excelRange.ShrinkToFit = false;
////设置字体在单元格内的对其方式
//excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//// 文本水平居中方式
//excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//////设置为横向打印
////WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
////ExcelApp.ActiveWindow.FreezePanes = true;
////excelRange.EntireColumn.AutoFit();
////WS2.PageSetup.Orientation = 2;
//WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
//WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.HeaderMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.FooterMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.CenterHorizontally = true;
//// WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
//WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
//excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[2, 20]);
//WS2.PageSetup.PrintTitleRows = excelRange.get_Address(excelRange.Row, excelRange.Column, Excel.XlReferenceStyle.xlA1, 1, 1);
ExcelBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
if (ExcelBook != null)
{
ExcelBook.Close(true, missing, missing);
ExcelBook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
ExcelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
public static void ExcelExport(DataGridView Dg, string TypePass)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
Excel.ApplicationClass oExcel = null; //Excel Application
Excel.Workbook oBook = null; // Excel Workbook
Excel.Sheets oSheetsColl = null; // Excel Worksheets collection
Excel.Worksheet oSheet = null; // Excel Worksheet
Excel.Range oRange = null; // Cell or Range in worksheet
Object oMissing = System.Reflection.Missing.Value;
oExcel = new Excel.ApplicationClass();
oExcel.UserControl = true;
oBook = oExcel.Workbooks.Add(oMissing);
oSheetsColl = oExcel.Worksheets;
oSheet = (Excel.Worksheet)oSheetsColl.get_Item("Sheet1");
oRange = (Excel.Range)oSheet.Cells[1, 1];
oRange.Value2 = clsGeneral.CompanyName;
oRange.Font.Name = "Tahoma";
oRange.Font.Size = 12;
//(oRange).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
//(oRange).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
if (TypePass.Trim().Length > 0)
{
oRange = (Excel.Range)oSheet.Cells[2, 1];
oRange.Value2 = TypePass;
oRange.Font.Name = "Tahoma";
oRange.Font.Size = 10;
}
int c = 0;
if (Dg.ColumnHeadersVisible == true)
{
for (int j = 0; j < Dg.Columns.Count; j++)
{
if (Dg.Columns[j].Visible == true)
{
oRange = (Excel.Range)oSheet.Cells[4, c + 1];
oRange.Value2 = Dg.Columns[j].HeaderText + " ";
oRange.Font.Bold = true;
oRange.Font.Name = "Tahoma";
oRange.Font.Size = 9;
(oRange).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
(oRange).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Teal);
oExcel.Columns.AutoFit();
c++;
}
}
}
c = 0;
for (int i = 0; i < Dg.Rows.Count; i++)
{
for (int j = 0; j < Dg.Columns.Count; j++)
{
if (Dg.Columns[j].Visible == true)
{
oRange = (Excel.Range)oSheet.Cells[i + 5, c + 1];
if (Dg[j, i].Value == null)
{
oRange.Value2 = " ";
}
else
{
oRange.Value2 = Dg[j, i].Value.ToString().Replace('\n', ' ') + " ";
}
oRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
oRange.Font.Name = "Tahoma";
oRange.Font.Size = 8;
oExcel.Columns.AutoFit();
// oRange.NumberFormat = "dd/MM/yyyy";
c++;
}
}
c = 0;
}
oExcel.Visible = true;
oBook = null;
oExcel = null;
GC.Collect();
}
public void exportDataExcel(DataSet dsTemp, string reportName)
{
ArrayList paramArraylist = new ArrayList();
string Excelpath = ConfigurationSettings.AppSettings["ExcelFile"] + "CarrierReports.xlsx";
string dateTime = "CarrierReports " + System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + System.DateTime.Now.Hour.ToString() + System.DateTime.Now.Minute.ToString() + System.DateTime.Now.Second.ToString() + ".xlsx";
string Destpath = ConfigurationSettings.AppSettings["ExcelFile"] + dateTime;
DataSet DsExportedData = (DataSet)dsTemp;
File.Copy(Excelpath, Destpath);
Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Workbook workbookDummy = XcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
Microsoft.Office.Interop.Excel.Range chartrange;
#region Excel
//workbook = XcelApp.Workbooks.Open(Destpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true); SVN
try
{
if (DsExportedData != null)
{
if (DsExportedData.Tables[0] != null)
{
#region TemplateData
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateData"];
//worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
DataTable dtTemplateData = (DataTable)DsExportedData.Tables[0];
for (int j = 0; j < dtTemplateData.Rows.Count; j++)
{
for (int k = 0; k < dtTemplateData.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = dtTemplateData.Rows[j].ItemArray[k].ToString();
}
}
chartrange = worksheet.get_Range("A1", "D1");
chartrange.Font.Bold = true;
chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
XcelApp.Columns.AutoFit();
workbook.Save();
#endregion
}
if (DsExportedData.Tables[1] != null)
{
#region TemplateBillingData
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["TemplateBillingData"];
//worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
DataTable dtTemplateBillingData = (DataTable)DsExportedData.Tables[1];
for (int j = 0; j < dtTemplateBillingData.Rows.Count; j++)
{
for (int k = 0; k < dtTemplateBillingData.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = dtTemplateBillingData.Rows[j].ItemArray[k].ToString();
}
}
chartrange = worksheet.get_Range("A1", "E1");
chartrange.Font.Bold = true;
chartrange.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
chartrange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
chartrange.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
XcelApp.Columns.AutoFit();
workbook.RefreshAll();
workbook.Save();
#endregion
}
else
{
}
}
else
{
}
// workbook.Close(); SVN
}
catch (Exception ex)
{
// workbook.Close(); SVN
ErrorHandler.WriteError(ex.Message);
}
#endregion
}
///////////////////////////////////////////////////////////////
//メソッド名 ： FlushGridviewDataToExcel
/// <summary>
/// 指定GridviewのデータをExcelに出力する
/// </summary>
/// <param name="targetDataGridView">対象DataGridView</param>
/// <history>
/// 日付 担当者 内容
/// 2014/06/23 YS.CHEW 新規作成
/// </history>
///////////////////////////////////////////////////////////////
public static void FlushGridviewDataToExcel(
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 + "_" + Common.GetCurrentTimestamp().ToString("yyyyMMddHHmmss");
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.RowCount - 1; i++)
{
for (j = 0; j <= targetDataGridView.ColumnCount - 1; j++)
{
// Skip hidden columns
if (!targetDataGridView.Columns[j].Visible) continue;
DataGridViewCell cell = targetDataGridView[j, i];
if (i == 0)
{
DataGridViewHeaderCell h = targetDataGridView.Columns[j].HeaderCell;
xlWorkSheet.Cells[i + 1, j + 1] = h.Value;
}
// Code & No columns format
if (targetDataGridView.Columns[j].Name.Length > 5
&& (targetDataGridView.Columns[j].Name.Substring(targetDataGridView.Columns[j].Name.Length - 5) == "CdCol"
|| targetDataGridView.Columns[j].Name.Substring(targetDataGridView.Columns[j].Name.Length - 5) == "NoCol"))
{
xlWorkSheet.Cells[i + 2, j + 1] = "'" + cell.Value;
Excel.Range rng = (Excel.Range)xlWorkSheet.Cells[i + 2, j + 1];
//rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
continue;
}
// Date columns format
if (targetDataGridView.Columns[j].Name.Length > 5
&& targetDataGridView.Columns[j].Name.Substring(targetDataGridView.Columns[j].Name.Length - 5) == "DtCol")
{
xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
Excel.Range rng = (Excel.Range)xlWorkSheet.Cells[i + 2, j + 1];
rng.EntireColumn.NumberFormat = "yyyy/MM/dd";
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);
}
}
/// <summary>
/// Hàm này đóng vai trò như mồi cho việc kiểm tra Excel (COM+) có tồn tại hay chưa
/// </summary>
private static void TestCalculatorExcel()
{
Excel.ApplicationClass excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
CloseExcelApplicationClass(excelApplication);
}
protected void btnFileRead_Click(object sender, EventArgs e)
{
try
{
string thisDir = Server.MapPath("~");
string epath = thisDir + "\\MarketingExcel\\" + Convert.ToString(Session["MarketingUser"]) + "\\";
string Name = epath + Convert.ToString(Session["MarketingUser"]);
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range range;
DataTable dt = new DataTable();
dt.Columns.Add("fName");
dt.Columns.Add("lName");
dt.Columns.Add("mobileNo");
dt.Columns.Add("pinNo");
DataTable dt1 = new DataTable();
dt1.Columns.Add("fName");
dt1.Columns.Add("lName");
dt1.Columns.Add("mobileNo");
dt1.Columns.Add("pinNo");
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(Name, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
int index = 0;
object rowIndex = 2;
while (((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 1]).Value2 != null)
{
urUserRegBLLObj.usrFirstName = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 2]).Value2);
urUserRegBLLObj.usrLastName = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 3]).Value2);
urUserRegBLLObj.usrMobileNo = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 4]).Value2);
urUserRegBLLObj.usrPIN = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 5]).Value2);
urUserRegBLLObj.usrAddress = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 6]).Value2);
urUserRegBLLObj.usrCityId = Convert.ToInt32(Session["CityIdN"]);
urUserRegBLLObj.frnrelGroup = "1";
string mobNo = urUserRegBLLObj.usrMobileNo;
int count = Convert.ToInt32(mobNo.Length);
if (urUserRegBLLObj.usrFirstName != "")
{
if (urUserRegBLLObj.usrLastName != "")
{
if (count == 10)
{
status = urUserRegBLLObj.BLLIsExistUserRegistrationInitial(urUserRegBLLObj);
if (status > 0)
{
urUserRegBLLObj.usrUserId = System.Guid.NewGuid().ToString();
Random rnd = new Random();
urUserRegBLLObj.usrPassword = cc.DESEncrypt(Convert.ToString(rnd.Next(10001, 99999)));
status = urUserRegBLLObj.BLLInsertUserRegistrationInitial(urUserRegBLLObj);
if (status > 0)
{
DataRow drN;
drN = dt.NewRow();
string fName = Convert.ToString(urUserRegBLLObj.usrFirstName);
string lName = Convert.ToString(urUserRegBLLObj.usrLastName);
string mobileNo = Convert.ToString(urUserRegBLLObj.usrMobileNo);
string pinNo = Convert.ToString(urUserRegBLLObj.usrPIN);
drN[0] = fName;
drN[1] = lName;
drN[2] = mobileNo;
drN[3] = pinNo;
dt.Rows.Add(drN);
gvUserRegistered.DataSource = dt;
gvUserRegistered.DataBind();
urUserRegBLLObj.frnrelUserId = Convert.ToString(Session["User"]);
urUserRegBLLObj.frnrelFriendId = urUserRegBLLObj.usrUserId;
urUserRegBLLObj.frnrelFrnRelName = Convert.ToString(urUserRegBLLObj.usrFirstName + " " + urUserRegBLLObj.usrLastName);
int frcount = urUserRegBLLObj.BLLInsertUserFriendRelative(urUserRegBLLObj);
if (frcount > 0)
{
usrFrndRelSms(urUserRegBLLObj.usrMobileNo);
}
}
}
else
{
DataRow drN;
drN = dt1.NewRow();
string fName = Convert.ToString(urUserRegBLLObj.usrFirstName);
string lName = Convert.ToString(urUserRegBLLObj.usrLastName);
string mobileNo = Convert.ToString(urUserRegBLLObj.usrMobileNo);
string pinNo = Convert.ToString(urUserRegBLLObj.usrPIN);
drN[0] = fName;
drN[1] = lName;
drN[2] = mobileNo;
drN[3] = pinNo;
dt1.Rows.Add(drN);
gvUserAlreadyRegistered.DataSource = dt1;
gvUserAlreadyRegistered.DataBind();
}
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "msg", "alert('Mobile No should Be 10 Digits for Mob NO.:" + mobNo + "')", true);
}
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "msg", "alert('Please Eneter the Last Name of Mobile No :" + mobNo + "')", true);
}
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "msg", "alert('Please Eneter the First Name of Mobile No :" + mobNo + "')", true);
}
index++;
rowIndex = 2 + index;
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
}
catch (Exception ex)
{
throw ex;
//string msg = ex.Message;
//Response.Write(ex.Message);
//ScriptManager.RegisterStartupScript(this, typeof(Page), "msg", "alert('Please Eneter the First Name of Mobile No ')", true);
}
}
