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);
}
/// <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="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="_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 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;
}
///////////////////////////////////////////////////////////////
//メソッド名 ： 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);
}
}
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();
}
}
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);
}
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;
}
}
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);
}
}