/// <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 Export_To_Excel(DataGridView dtGridView, string filename, string sheetName)
{
int i, j;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass excellApp;
excellApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excellApp.Application.Workbooks.Add(true);
try
{
// Add columns name to excel file
for (i = 0; i < dtGridView.Columns.Count; i++)
{
excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name;
}
for (i = 0; i < dtGridView.Rows.Count; i++)
{
for (j = 0; j < dtGridView.Columns.Count; j++)
{
excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value;
}
}
//excellApp.Save(("Loinhuan.xls");
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)excellApp.ActiveSheet;
worksheet.Activate();
worksheet.Name = sheetName;
worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
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();
}
private void button4_Click(object sender, EventArgs e)
{
if (bankImportYear.SelectedItem == null || bankImportMonth.SelectedItem == null)
{
MessageBox.Show("请选择导入的年份或月份");
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();//lauch excel application
if (ElectriCharacter.SelectedIndex == 0)
{
importCityExcel(); //导入城网数据
}
else {
importRuralExcel(); //导入农网数据
}
}
/// <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);
}
public void CreateUpdateFile(string filename, bool addData)
{
// Get data
var cDemo = demo.GetCountryDemoRecent();
var levels = demo.GetRecentDemography(locationType.LevelNumber, cDemo.DateDemographyData.Year);
DataTable data = new DataTable();
if (addData)
data = CreateUpdateDataTable(levels);
else
data = CreateInsertDataTable(locationType);
// Create excel
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlsWorkbook;
Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet;
object oMissing = System.Reflection.Missing.Value;
//Create new workbook
xlsWorkbook = xlsApp.Workbooks.Add(true);
//Get the first worksheet
xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]);
// Load data into excel worksheet
AddTableToWorksheet(data, xlsWorksheet);
xlsApp.DisplayAlerts = false;
xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing,
oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
oMissing, oMissing, oMissing);
xlsApp.Visible = true;
xlsWorksheet = null;
xlsWorkbook = null;
xlsApp = null;
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
// 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 buttonExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // Creates a new Excel Application
excelApp.Visible = true; // Makes Excel visible to the user.
// The following line adds a new workbook
Microsoft.Office.Interop.Excel.Workbook newWorkbook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// The following code opens an existing workbook
string workbookPath = Application.StartupPath + "\\template.xls"; // Add your own path here
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
// The following gets the Worksheets collection
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
// The following gets Sheet1 for editing
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
// The following gets cell A1 for editing
//Microsoft.Office.Interop.Excel.Range excelCell = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A1", "A1");
// The following sets cell A1's value to "Hi There"
//excelCell.Value2 = "Hi There";
_Ekskursovody a;
int n = queueAccounts.Count;
for (int i = 0; i < n; i++)
{
a = (_Ekskursovody)queueAccounts.Dequeue();
queueAccounts.Enqueue(a);
if (a.id == m_journal.account_id.ToString())
{
Microsoft.Office.Interop.Excel.Range excelCellAccount = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("C8", "C8");
excelCellAccount.Value2 = a.name;
break;
}
}
Microsoft.Office.Interop.Excel.Range excelCellID = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("D11", "D11");
excelCellID.Value2 = m_journal.id.ToString();
Microsoft.Office.Interop.Excel.Range excelCellStudent = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E14", "F14");
excelCellStudent.Value2 = m_journal.student;
_PaymentNames pn;
n = queuePaymentNames.Count;
for (int i = 0; i < n; i++)
{
pn = (_PaymentNames)queuePaymentNames.Dequeue();
queuePaymentNames.Enqueue(pn);
if (pn.id == m_journal.payment_name_id)
{
Microsoft.Office.Interop.Excel.Range excelCellPaymentName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B19", "C19");
excelCellPaymentName.Value2 = pn.name;
break;
}
}
_Semester s;
n = queueSemesters.Count;
for (int i = 0; i < n; i++)
{
s = (_Semester)queueSemesters.Dequeue();
queueSemesters.Enqueue(s);
if (s.id == m_journal.semester)
{
Microsoft.Office.Interop.Excel.Range excelCellSemester = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B20", "C20");
excelCellSemester.Value2 = s.name;
break;
};
}
_Pdv p;
n = queuePdv.Count;
for (int i = 0; i < n; i++)
{
p = (_Pdv)queuePdv.Dequeue();
queuePdv.Enqueue(p);
if (p.id == m_journal.pdv)
{
Microsoft.Office.Interop.Excel.Range excelCellPdv = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E22", "E22");
excelCellPdv.Value2 = p.name;
break;
};
}
_EduYears ey;
n = queueEduYears.Count;
for (int i = 0; i < n; i++)
{
ey = (_EduYears)queueEduYears.Dequeue();
queueEduYears.Enqueue(ey);
if (ey.id.ToString() == m_journal.edu_year_id.ToString())
{
Microsoft.Office.Interop.Excel.Range excelCellSemester = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B21", "C21");
excelCellSemester.Value2 = ey.name;
break;
};
}
Microsoft.Office.Interop.Excel.Range excelCellSum = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F21", "F21");
excelCellSum.Value2 = m_journal.sum;
String date = "";
if(DateTime.Parse(m_journal.date).Day<10)
date += "0";
date += DateTime.Parse(m_journal.date).Day;
date += "/";
if(DateTime.Parse(m_journal.date).Month<10)
date += "0";
date += DateTime.Parse(m_journal.date).Month;
date += "/";
date += DateTime.Parse(m_journal.date).Year;
Microsoft.Office.Interop.Excel.Range excelCellDate = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F11", "F11");
excelCellDate.Value2 = date;
}
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();
}
public void CreateImportFile(string filename, bool importDemography, int rows, AdminLevel filterLevel)
{
// Get data
filterBy = filterLevel;
int dropdownCol = GetParams();
DataTable data = CreateNewImportDataTable(importDemography);
DemoRepository demo = new DemoRepository();
CountryDemography recentCountryDemo = demo.GetCountryDemoRecent();
// Create excel
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlsWorkbook;
Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet;
Microsoft.Office.Interop.Excel.Worksheet xlsValidation;
object oMissing = System.Reflection.Missing.Value;
validationRanges = new Dictionary<string, string>();
//Create new workbook
xlsWorkbook = xlsApp.Workbooks.Add(true);
//Get the first worksheet
xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]);
// add hidden validation worksheet
xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorkbook.Worksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing);
xlsValidation.Name = validationSheetName;
xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
// Add columns
int iCol = 0;
foreach (DataColumn c in data.Columns)
{
iCol++;
xlsWorksheet.Cells[1, iCol] = c.ColumnName;
}
string totalPopColumn = "F";
// Add rows
for (int r = 1; r <= rows + 1; r++)
{
for (int i = 1; i < data.Columns.Count + 1; i++)
{
if (r == 1)
{
// Add the header the first time through
xlsWorksheet.Cells[1, i] = data.Columns[i - 1].ColumnName;
}
else
{
if (i == 1 && filterByType != null)
xlsWorksheet.Cells[r, i] = filterLevel.Name;
if (dropdownCol == i && dropdownValues.Count > 0)
{
AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(i), r, dropdownBy.DisplayName, TranslationLookup.GetValue("PleaseSelect"), dropdownValues, oldCI);
}
if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("UrbanOrRural"))
AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(i), r, TranslationLookup.GetValue("UrbanOrRural"), TranslationLookup.GetValue("PleaseSelect"),
new List<string> { TranslationLookup.GetValue("AdminUnitUrban"), TranslationLookup.GetValue("AdminUnitRural"), TranslationLookup.GetValue("AdminUnitPeriRural")}, oldCI);
if (importDemography)
{
if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("YearCensus"))
xlsWorksheet.Cells[r, i] = recentCountryDemo.YearCensus;
if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("GrowthRate"))
xlsWorksheet.Cells[r, i] = recentCountryDemo.GrowthRate;
if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("TotalPopulation"))
totalPopColumn = Util.GetExcelColumnName(i);
if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("Pop0Month") && recentCountryDemo.Percent6mos.HasValue)
xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.Percent6mos, r, totalPopColumn);
if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopPsac") && recentCountryDemo.PercentPsac.HasValue)
xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentPsac, r, totalPopColumn);
if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("PopSac") && recentCountryDemo.PercentSac.HasValue)
xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentSac, r, totalPopColumn);
if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("Pop5yo") && recentCountryDemo.Percent5yo.HasValue)
xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.Percent5yo, r, totalPopColumn);
if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopAdult") && recentCountryDemo.PercentAdult.HasValue)
xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentAdult, r, totalPopColumn);
if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopFemale") && recentCountryDemo.PercentFemale.HasValue)
xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentFemale, r, totalPopColumn);
if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopMale") && recentCountryDemo.PercentMale.HasValue)
xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentMale, r, totalPopColumn);
if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PercentRural") && recentCountryDemo.PercentRural.HasValue)
xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentRural, r, totalPopColumn);
}
}
}
}
var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
var range = xlsWorksheet.get_Range("A1", last);
range.Columns.AutoFit();
xlsApp.DisplayAlerts = false;
xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing,
oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
oMissing, oMissing, oMissing);
xlsApp.Visible = true;
xlsWorksheet = null;
xlsValidation = null;
xlsWorkbook = null;
xlsApp = null;
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
/// <summary>
/// Xuất ra file dữ liệu trên gridView và định dạng dựa vào ext
/// </summary>
public static bool exportFile(GridView gridView, String ext, bool openFile, ref string outFileName, bool useHeader)
{
bool flag = false;
bool succ = false;
string filePath = null;
bool flagShowView = gridView.OptionsView.ShowViewCaption;
try
{
SaveFileDialog f = new SaveFileDialog();
f.Title = "Chọn tên tập tin muốn lưu";
if (ext.Equals("xls"))
f.Filter = "Excel 97 - 2003 files (*.xls)|*.xls";
else if (ext.Equals("xlsx"))
f.Filter = "Excel 2007 files (*.xlsx)|*.xlsx";
else if (ext.Equals("pdf"))
f.Filter = "PDF (*.pdf)|*.pdf";
else if (ext.Equals("htm"))
f.Filter = "Web Pages (*.htm)|*.htm";
else if (ext.Equals("rtf"))
f.Filter = "Rich Text files (*.rtf)|*.rtf";
f.ShowDialog();
PrintableComponentLink link = null;
if (useHeader && gridView.GridControl != null)
{
if (FrameworkParams.headerLetter != null)
{
gridView.OptionsView.ShowViewCaption = false;
link = FrameworkParams.headerLetter.Draw(gridView.GridControl, gridView.ViewCaption,
"Ngày xuất báo cáo: " + DateTime.Today.ToString(FrameworkParams.option.dateFormat));
}
}
if (f.FileName != "")
{
filePath = f.FileName;
if (FrameworkParams.wait == null)
{
FrameworkParams.wait = new WaitingMsg();
flag = true;
}
if (ext.Equals("xls"))
{
if (gridView is PLGridView)
{
if (link != null)
link.PrintingSystem.ExportToXls(f.FileName, new XlsExportOptions(((PLGridView)gridView)._TextExportMode));
else
gridView.ExportToXls(f.FileName, new XlsExportOptions(((PLGridView)gridView)._TextExportMode));
}
else
{
if (link != null)
link.PrintingSystem.ExportToXls(f.FileName, new XlsExportOptions(TextExportMode.Text));
else
gridView.ExportToXls(f.FileName, new XlsExportOptions(TextExportMode.Text));
}
succ = true;
}
else if (ext.Equals("xlsx"))
{
if (gridView is PLGridView)
{
if (link != null)
link.PrintingSystem.ExportToXlsx(f.FileName, new XlsxExportOptions(((PLGridView)gridView)._TextExportMode));
else
gridView.ExportToXlsx(f.FileName, new XlsxExportOptions(((PLGridView)gridView)._TextExportMode));
}
else
{
if (link != null)
link.PrintingSystem.ExportToXlsx(f.FileName, new XlsxExportOptions(TextExportMode.Text));
else
gridView.ExportToXlsx(f.FileName, new XlsxExportOptions(TextExportMode.Text));
}
succ = true;
}
else if (ext.Equals("pdf"))
{
if (link != null)
link.PrintingSystem.ExportToPdf(f.FileName);
else
gridView.ExportToPdf(f.FileName);
succ = true;
}
else if (ext.Equals("rtf"))
{
if (link != null)
link.PrintingSystem.ExportToRtf(f.FileName);
else
gridView.ExportToRtf(f.FileName);
succ = true;
}
else if (ext.Equals("htm"))
{
if (link != null)
link.PrintingSystem.ExportToHtml(f.FileName);
else
gridView.ExportToHtml(f.FileName);
succ = true;
}
}
}
catch (Exception ex)
{
PLException.AddException(ex);
return false;
}
finally
{
if (FrameworkParams.wait != null && flag == true)
FrameworkParams.wait.Finish();
if (succ == true)
{
outFileName = filePath;
if (ext.Equals("xls") || ext.Equals("xlsx"))
{
try
{
System.Globalization.CultureInfo oldCi = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(filePath,
0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.ActiveSheet;
excelSheet.Columns.AutoFit();
excelWorkbook.Save();
System.Threading.Thread.CurrentThread.CurrentCulture = oldCi;
if (openFile && PLMessageBox.ShowConfirmMessage("Bạn có muốn mở tập tin này không?") == DialogResult.Yes)
{
excelApp.Visible = true;
}
else
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelSheet = null;
excelWorkbook = null;
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
catch
{
if (openFile && PLMessageBox.ShowConfirmMessage("Bạn có muốn mở tập tin này không?") == DialogResult.Yes)
{
if (!HelpFile.OpenFile(filePath))
HelpMsgBox.ShowNotificationMessage("Mở tập tin không thành công");
}
}
}
else if (openFile && PLMessageBox.ShowConfirmMessage("Bạn có muốn mở tập tin này không?") == DialogResult.Yes)
{
if (!HelpFile.OpenFile(filePath))
HelpMsgBox.ShowNotificationMessage("Mở tập tin không thành công");
}
}
gridView.OptionsView.ShowViewCaption = flagShowView;
}
return true;
}
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();
}
}
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);
}
/// <summary>
/// 输出Excel
/// </summary>
/// <param name="Report"></param>
public void ReportToExcel(DataTable Report)
{
try
{
int totalColumn = Report.Columns.Count;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
#region 填充数据
int row = 3;
Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1];
Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn];
for (int i = 0; i < Report.Columns.Count; i++)
{
excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString();
}
row = row + 1;
for (int i = 0; i < Report.Rows.Count; i++)
{
for (int j = 0; j < Report.Columns.Count; j++)
{
object objValue = Report.Rows[i][Report.Columns[j].ColumnName];
if (Convert.IsDBNull(objValue))
{
continue;
}
excel.Cells[row + i, j + 1] = objValue.ToString();
}
}
#endregion
#region 画网格线
object obj = excel.get_Range(startCell, endCell).Select();
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
#endregion
excel.ActiveWindow.DisplayGridlines = false;
excel.Visible = true;
}
catch (Exception err)
{
MessageBox.Show("输出到Excel发生错误！", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
GC.Collect();
}
}
protected void DataTableToExcel2(System.Data.DataTable qingjiadantbl, string fileName, bool flag)
{
if (qingjiadantbl == null)
{
return;
}
int rowNum = qingjiadantbl.Rows.Count;
int columnNum = qingjiadantbl.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
var excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook newBook = excelApp.Workbooks.Add();
//newBook.SaveAs(fileName);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn col in qingjiadantbl.Columns)
{
columnIndex++;
excelApp.Cells[rowIndex, columnIndex] = col.Caption;
}
//将DataTable中的数据导入Excel中
Microsoft.Office.Interop.Excel.Range r = excelApp.get_Range(excelApp.Cells[1, 3], excelApp.Cells[rowNum + 1, columnIndex]);
r.NumberFormat = "@";
r.NumberFormatLocal = "@";
for (int i = 0; i < rowNum; i++)
{
rowIndex++;//数据从第二行开始
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
//if (columnIndex == 3||columnIndex == 18)
//{
// Microsoft.Office.Interop.Excel.Range r = excelApp.get_Range(excelApp.Cells[rowIndex, columnIndex], excelApp.Cells[rowIndex, columnIndex]);
// r.NumberFormat = "@";
// r.NumberFormatLocal = "@";
//}
excelApp.Cells[rowIndex, columnIndex] = qingjiadantbl.Rows[i][j].ToString();
}
}
excelApp.Cells.Columns.AutoFit();
newBook.SaveCopyAs(fileName);
newBook.Close();
excelApp.Workbooks.Close();
excelApp.Quit();
//Kill打开的Excel进程
//Process[] excelApps;
//excelApps = Process.GetProcessesByName("EXCEL");
//foreach (Process p in excelApps)`1q
//{
// p.Kill();
//}//End
if (flag)
{
AddAtt(fileName); //加入附件中
}
}
public string getVersion()
{
Microsoft.Office.Interop.Excel.Application excelApplication;
excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
return(excelApplication.Version);
}
private void dataToExelMainMenuItem_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;
object obj = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(obj);
Microsoft.Office.Interop.Excel.Sheets ss = wb.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)ss.get_Item(1);
//object[] headers = { "First", "Second", "Third1111111111" };
//Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A1", "C1");
//range.Value2 = headers;
//range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatReport1, obj, obj, obj, obj, obj, obj);
//range.Font.Bold = true;
//range = ws.get_Range("A2", obj);
//range = range.get_Resize(3, 3);
//range.Borders.Value = true;
////range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple, obj, obj, obj, obj, obj, obj);
//object[,] data = { { "1", "2", "3" }, { "4", "5", "6" }, { "7", "8", "9" } };
//range.Value2 = data;
//excel.Visible = true;
DataGridView grid = tabControl1.SelectedTab.Controls[0] as DataGridView;
Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A1", obj);
range = range.get_Resize(grid.Rows.Count, 6);
object[,] data = new object[grid.Rows.Count, 6];
data[0, 0] = "Дата";
data[0, 1] = "Время";
data[0, 2] = "Широта";
data[0, 3] = "Долгота";
data[0, 4] = "От предыдущей, м";
data[0, 5] = "Всего, м";
for (int i = 0; i < grid.Rows.Count - 1; i++)
{
for (int j = 0; j < 6; j++)
{
data[i + 1, j] = grid[j, i].Value;
}
}
range.Value2 = data;
range.AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple, obj, obj, obj, obj, obj, obj);
excel.Visible = true;
}
#pragma warning disable 168
catch (NullReferenceException except)
{
MessageBox.Show("Нет данных для отображения", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception except)
{
MessageBox.Show("При передаче данных в Excel произошла ошибка", "Ошибка-", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
#pragma warning restore 168
}
/// <summary>
/// 输出Excel
/// </summary>
/// <param name="Report"></param>
public void ReportToExcel(DataTable Report)
{
try
{
int totalColumn = Report.Columns.Count;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
#region 填充数据
excel.Cells[1, 1] = HIS.SYSTEM.BussinessLogicLayer.Classes.BaseData.WorkName + "门诊收费项目收入报表";
Microsoft.Office.Interop.Excel.Range titleStartcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, 1];
Microsoft.Office.Interop.Excel.Range titleEndcell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, totalColumn];
excel.get_Range(titleStartcell, titleEndcell).Merge(0);
excel.get_Range(titleStartcell, titleEndcell).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
excel.get_Range(titleStartcell, titleEndcell).Font.Name = "宋体";
excel.get_Range(titleStartcell, titleEndcell).Font.Size = 15;
excel.get_Range(titleStartcell, titleEndcell).Font.Bold = true;
excel.Cells[2, 1] = "统计时间：";
excel.Cells[2, 2] = dtpFrom.Value.ToString("yyyy-MM-dd HH:mm:ss") + " -- " + dtpTo.Value.ToString("yyyy-MM-dd HH:mm:ss");
excel.get_Range((Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 2], (Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 6]).Merge(0);
excel.Cells[2, totalColumn - 2] = "制表人：";
excel.Cells[2, totalColumn - 1] = _user.Name;
int row = 3;
Microsoft.Office.Interop.Excel.Range startCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row, 1];
Microsoft.Office.Interop.Excel.Range endCell = (Microsoft.Office.Interop.Excel.Range)excel.Cells[row + Report.Rows.Count, totalColumn];
for (int i = 0; i < Report.Columns.Count; i++)
{
excel.Cells[row, i + 1] = Report.Columns[i].ColumnName.ToString();
}
row = row + 1;
for (int i = 0; i < Report.Rows.Count; i++)
{
for (int j = 0; j < Report.Columns.Count; j++)
{
object objValue = Report.Rows[i][Report.Columns[j].ColumnName];
if (Convert.IsDBNull(objValue))
{
continue;
}
excel.Cells[row + i, j + 1] = objValue.ToString();
}
}
#endregion
#region 画网格线
object obj = excel.get_Range(startCell, endCell).Select();
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excel.get_Range(startCell, endCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
row = row + Report.Rows.Count + 1;
excel.Cells[row, 1] = "审核人";
excel.Cells[row, totalColumn - 2] = "打印日期：";
excel.Cells[row, totalColumn] = HIS.SYSTEM.PubicBaseClasses.XcDate.ServerDateTime.ToString("yyyy-MM-dd");
#endregion
excel.ActiveWindow.DisplayGridlines = false;
excel.Visible = true;
}
catch
{
MessageBox.Show("输出到Excel发生错误！", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
GC.Collect();
}
}
/// <summary>
/// Click event to handle the export to excel
/// </summary>
/// <param name="sender">Sender object</param>
/// <param name="e">event data</param>
private void Export_Click(object sender, EventArgs e)
{
try
{
var table = (DataTable)dataGridViewMembers.DataSource;
Microsoft.Office.Interop.Excel.ApplicationClass excel
= new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
int columnIndex = 0;
foreach (DataColumn col in table.Columns)
{
columnIndex++;
excel.Cells[1, columnIndex] = col.ColumnName;
}
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
columnIndex = 0;
foreach (DataColumn col in table.Columns)
{
columnIndex++;
if (columnIndex == 4 || columnIndex == 5 || columnIndex == 6)
{
if (columnIndex == 4)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(Occupation), int.Parse(row[col.ColumnName].ToString()));
}
if (columnIndex == 5)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(MaritalStatus), int.Parse(row[col.ColumnName].ToString()));
}
if (columnIndex == 6)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(HealthStatus), int.Parse(row[col.ColumnName].ToString()));
}
}
else
{
excel.Cells[rowIndex + 1, columnIndex] = row[col.ColumnName].ToString();
}
}
}
excel.Visible = true;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
catch (Exception ex)
{
this.ShowErrorMessage(ex);
}
}
public void GenerateExcel()
{
Microsoft.Office.Interop.Excel.ApplicationClass xl;
xl = new Microsoft.Office.Interop.Excel.ApplicationClass();
xl.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)xl.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = books.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "Касса";
Microsoft.Office.Interop.Excel.Range rd;
Microsoft.Office.Interop.Excel.Range rd1;
Microsoft.Office.Interop.Excel.Range rd2;
Microsoft.Office.Interop.Excel.Range rd3;
Microsoft.Office.Interop.Excel.Range rd4;
Microsoft.Office.Interop.Excel.Range rd5;
Microsoft.Office.Interop.Excel.Range rd6;
Microsoft.Office.Interop.Excel.Range rd7;
Microsoft.Office.Interop.Excel.Range rd8;
Microsoft.Office.Interop.Excel.Range rd9;
Microsoft.Office.Interop.Excel.Range rd10;
Microsoft.Office.Interop.Excel.Range rd11;
Microsoft.Office.Interop.Excel.Range rd12;
Microsoft.Office.Interop.Excel.Range rd13;
Microsoft.Office.Interop.Excel.Range rd14;
Microsoft.Office.Interop.Excel.Range rd15;
Microsoft.Office.Interop.Excel.Range rd16;
Microsoft.Office.Interop.Excel.Range rd17;
Microsoft.Office.Interop.Excel.Range rd18;
Microsoft.Office.Interop.Excel.Range rd19;
Microsoft.Office.Interop.Excel.Range rd20;
Microsoft.Office.Interop.Excel.Range rd21;
Microsoft.Office.Interop.Excel.Range rd22;
Microsoft.Office.Interop.Excel.Range rd23;
Microsoft.Office.Interop.Excel.Range rd24;
Microsoft.Office.Interop.Excel.Range rd25;
Microsoft.Office.Interop.Excel.Range rd26;
Microsoft.Office.Interop.Excel.Range rd27;
Microsoft.Office.Interop.Excel.Range rd28;
Microsoft.Office.Interop.Excel.Range rd29;
Microsoft.Office.Interop.Excel.Range rd30;
Microsoft.Office.Interop.Excel.Range rd31;
Microsoft.Office.Interop.Excel.Range rd32;
Microsoft.Office.Interop.Excel.Range rd33;
Microsoft.Office.Interop.Excel.Range rd34;
Microsoft.Office.Interop.Excel.Range rd35;
Microsoft.Office.Interop.Excel.Range rd36;
Microsoft.Office.Interop.Excel.Range rd37;
Microsoft.Office.Interop.Excel.Range rd38;
Microsoft.Office.Interop.Excel.Range rd39;
Microsoft.Office.Interop.Excel.Range rd40;
Microsoft.Office.Interop.Excel.Range rd41;
Microsoft.Office.Interop.Excel.Range rd42;
Microsoft.Office.Interop.Excel.Range rd43;
Microsoft.Office.Interop.Excel.Range rd44;
Microsoft.Office.Interop.Excel.Range rd45;
rd = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, "C"];
rd.Value2 = "Касса";
rd.Font.Bold = true;
rd1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, "B"];
rd1.Value2 = "за период с";
rd1.ColumnWidth = 12;
rd2 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, "C"];
rd2.Value2 = this.Date1.ToString("dd-MMM-yyyy");
rd3 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, "D"];
rd3.Value2 = " по ";
rd3.ColumnWidth = 3;
rd4 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[2, "E"];
rd4.Value2 = this.Date2.ToString("dd-MMM-yyyy");
rd5 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[4, "A"], sheet.Cells[4, "D"]);
rd5.MergeCells = true;
rd5.Font.Bold = true;
rd5.Value2 = "Абонементы";
rd6 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[4, "E"];
rd6.Value2 = this.Abonements.ToString();
int ind = 6;
rd30 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "A"];
rd30.Value2 = "Абонемент";
rd30.Font.Bold = true;
rd30.ColumnWidth = 35;
rd31 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "B"];
rd31.Value2 = "Дата";
rd31.Font.Bold = true;
rd31.ColumnWidth = 10;
rd32 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "C"];
rd32.Value2 = "Стоимость";
rd32.Font.Bold = true;
rd32.ColumnWidth = 10;
for (int i = 0; i < advBandedGridView1.RowCount; i++)
{
if (Convert.ToInt32(advBandedGridView1.GetRowCellValue(i, "Type")) == 0)
{
rd7 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[6 + i, "A"];
rd7.Value2 = advBandedGridView1.GetRowCellValue(i, "Good");
rd7.ColumnWidth = 35;
rd8 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[6 + i, "B"];
rd8.Value2 = Convert.ToDateTime(advBandedGridView1.GetRowCellValue(i, "Date")).ToString("dd-MMM-yyyy");
rd8.ColumnWidth = 10;
rd9 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[6 + i, "C"];
rd9.Value2 = advBandedGridView1.GetRowCellValue(i, "Cost");
rd9.ColumnWidth = 10;
ind += 1;
}
}
rd10 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[ind + 2, "A"], sheet.Cells[ind + 2, "D"]);
rd10.MergeCells = true;
rd10.Font.Bold = true;
rd10.Value2 = "Товары";
rd11 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + 2, "E"];
rd11.Value2 = this.Goods.ToString();
ind += 4;
rd33 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "A"];
rd33.Value2 = "Товар";
rd33.Font.Bold = true;
rd33.ColumnWidth = 35;
rd34 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "B"];
rd34.Value2 = "Дата продажи";
rd34.Font.Bold = true;
rd34.ColumnWidth = 12;
rd35 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "C"];
rd35.Font.Bold = true;
rd35.Value2 = "Цена";
rd35.ColumnWidth = 10;
rd36 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "D"];
rd36.Font.Bold = true;
rd36.Value2 = "Количество";
rd36.ColumnWidth = 12;
rd37 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "E"];
rd37.Value2 = "Итого";
rd37.Font.Bold = true;
rd37.ColumnWidth = 12;
int newInt = ind;
int servInd = 0;
for (int i = 0; i < advBandedGridView1.RowCount; i++)
{
if (Convert.ToInt32(advBandedGridView1.GetRowCellValue(i, "Type")) == 1)
{
rd12 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "A"];
rd12.Value2 = advBandedGridView1.GetRowCellValue(i, "Good");
rd12.ColumnWidth = 35;
rd13 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "B"];
rd13.Value2 = Convert.ToDateTime(advBandedGridView1.GetRowCellValue(i, "Date")).ToString("dd-MMM-yyyy");
rd13.ColumnWidth = 10;
rd14 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "C"];
rd14.Value2 = advBandedGridView1.GetRowCellValue(i, "Cost");
rd14.ColumnWidth = 10;
rd14 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "D"];
rd14.Value2 = advBandedGridView1.GetRowCellValue(i, "Quantity");
rd14.ColumnWidth = 10;
rd28 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd, "E"];
rd28.Value2 = Convert.ToDouble(advBandedGridView1.GetRowCellValue(i, "Quantity")) * Convert.ToDouble(advBandedGridView1.GetRowCellValue(i, "Cost"));
rd28.ColumnWidth = 10;
newInt += 1;
}
else
{
servInd += 1;
}
}
ind = newInt;
rd15 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[ind + 2, "A"], sheet.Cells[ind + 2, "D"]);
rd15.MergeCells = true;
rd15.Font.Bold = true;
rd15.Value2 = "Услуги";
rd16 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + 2, "E"];
rd16.Value2 = this.Services.ToString();
ind = ind + 4;
rd38 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "A"];
rd38.Value2 = "Услуга";
rd38.Font.Bold = true;
rd38.ColumnWidth = 35;
rd39 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "B"];
rd39.Value2 = "Дата продажи";
rd39.Font.Bold = true;
rd39.ColumnWidth = 12;
rd40 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "C"];
rd40.Value2 = "Цена";
rd40.Font.Bold = true;
rd40.ColumnWidth = 12;
rd41 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "D"];
rd41.Value2 = "Количество";
rd41.Font.Bold = true;
rd41.ColumnWidth = 12;
rd42 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "E"];
rd42.Value2 = "Итого";
rd42.ColumnWidth = 12;
rd42.Font.Bold = true;
int newInt2 = ind;
int servInd2 = 0;
for (int i = 0; i < advBandedGridView1.RowCount; i++)
{
if (Convert.ToInt32(advBandedGridView1.GetRowCellValue(i, "Type")) == 2)
{
rd17 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "A"];
rd17.Value2 = advBandedGridView1.GetRowCellValue(i, "Good");
rd17.ColumnWidth = 35;
rd18 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "B"];
rd18.Value2 = Convert.ToDateTime(advBandedGridView1.GetRowCellValue(i, "Date")).ToString("dd-MMM-yyyy");
rd18.ColumnWidth = 12;
rd19 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "C"];
rd19.Value2 = advBandedGridView1.GetRowCellValue(i, "Cost");
rd19.ColumnWidth = 12;
rd20 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "D"];
rd20.Value2 = advBandedGridView1.GetRowCellValue(i, "Quantity");
rd20.ColumnWidth = 12;
rd29 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd2, "E"];
rd29.Value2 = Convert.ToDouble(advBandedGridView1.GetRowCellValue(i, "Quantity")) * Convert.ToDouble(advBandedGridView1.GetRowCellValue(i, "Cost"));
rd29.ColumnWidth = 12;
newInt2 += 1;
}
else
{
servInd2 += 1;
}
}
ind = newInt2 + 2;
rd21 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[ind, "A"], sheet.Cells[ind, "D"]);
rd21.MergeCells = true;
rd21.Font.Bold = true;
rd21.Value2 = "Расходы";
rd22 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind, "E"];
rd22.Value2 = this.Charges.ToString();
int val = ind;
ind = ind + 2;
int newInt3 = ind;
int servInd3 = 0;
rd43 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "A"];
rd43.Value2 = "Название";
rd43.Font.Bold = true;
rd43.ColumnWidth = 35;
rd44 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "B"];
rd44.Value2 = "Дата";
rd44.Font.Bold = true;
rd44.ColumnWidth = 10;
rd45 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind - 1, "C"];
rd45.Value2 = "Сумма";
rd45.Font.Bold = true;
rd45.ColumnWidth = 10;
for (int i = 0; i < advBandedGridView1.RowCount; i++)
{
if (Convert.ToInt32(advBandedGridView1.GetRowCellValue(i, "Type")) == 3)
{
rd23 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd3, "A"];
rd23.Value2 = advBandedGridView1.GetRowCellValue(i, "Good");
rd23.ColumnWidth = 35;
rd24 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd3, "B"];
rd24.Value2 = Convert.ToDateTime(advBandedGridView1.GetRowCellValue(i, "Date")).ToString("dd-MMM-yyyy");
rd24.ColumnWidth = 10;
rd25 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + i - servInd3, "C"];
rd25.Value2 = advBandedGridView1.GetRowCellValue(i, "Total");
rd25.ColumnWidth = 10;
newInt3 += 1;
}
else
{
servInd3 += 1;
}
}
ind = newInt3 + 2;
int valFinish = ind;
Microsoft.Office.Interop.Excel.Range rd47 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[val, "A"], sheet.Cells[valFinish, "F"]);
rd47 = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(sheet.Cells[ind, "A"], sheet.Cells[ind, "E"]);
rd47.MergeCells = true;
rd47.Font.Bold = true;
rd47.Value2 = "Сальдо";
rd47 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[ind + 1, "G"];
rd47.Value2 = (this.Abonements + this.Goods + this.Services + this.Charges).ToString();
xl.Visible = true;
}
public void CreateUpdateFile(string filename, List<IHaveDynamicIndicatorValues> forms)
{
ReloadDropdownValues();
LoadRelatedLists();
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlsWorkbook;
Microsoft.Office.Interop.Excel.Workbooks xlsWorkbooks;
Microsoft.Office.Interop.Excel.Sheets xlsWorksheets;
Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet;
Microsoft.Office.Interop.Excel.Worksheet xlsValidation;
object oMissing = System.Reflection.Missing.Value;
validationRanges = new Dictionary<string, string>();
//Create new workbook
xlsWorkbooks = xlsApp.Workbooks;
xlsWorkbook = xlsWorkbooks.Add(true);
xlsWorksheets = xlsWorkbook.Worksheets;
//Get the first worksheet
xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]);
// add hidden validation worksheet
xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing);
xlsValidation.Name = validationSheetName;
xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
xlsWorksheet.Cells[1, 1] = "* " + TranslationLookup.GetValue("ID");
xlsWorksheet.Cells[1, 2] = "* " + TranslationLookup.GetValue("Location");
int locationCount = 2;
int xlsColCount = 2;
xlsColCount = AddTypeSpecific(xlsWorksheet, xlsColCount);
int colCountAfterStatic = xlsColCount;
foreach (var item in Indicators)
{
if (item.Value.DataTypeId == (int)IndicatorDataType.SentinelSite || item.Value.IsCalculated || item.Value.IsMetaData)
continue;
string isReq = "";
if (item.Value.IsRequired)
isReq = "* ";
xlsColCount++;
xlsWorksheet.Cells[1, xlsColCount] = isReq + TranslationLookup.GetValue(item.Key, item.Key);
}
//xlsWorksheet.Cells[1, xlsColCount + 1] = TranslationLookup.GetValue("Notes");
// row 2+ admin levels
DemoRepository repo = new DemoRepository();
int xlsRowCount = 2;
foreach (var form in forms)
{
var adminLevel = repo.GetAdminLevelById(form.AdminLevelId.Value);
xlsWorksheet.Cells[xlsRowCount, 1] = form.Id;
xlsWorksheet.Cells[xlsRowCount, 2] = GetAdminLevelName(adminLevel, form);
AddTypeSpecificLists(xlsWorksheet, xlsValidation, adminLevel.Id, xlsRowCount, oldCI, locationCount);
AddTypeSpecificListValues(xlsWorksheet, xlsValidation, adminLevel.Id, xlsRowCount, oldCI, locationCount, form);
int colCount = colCountAfterStatic;
foreach (var key in Indicators.Keys)
{
if (Indicators[key].DataTypeId == (int)IndicatorDataType.SentinelSite || Indicators[key].IsCalculated || Indicators[key].IsMetaData)
continue;
string value = "";
var iv = form.IndicatorValues.FirstOrDefault(x => x.IndicatorId == Indicators[key].Id);
if (iv != null)
value = iv.DynamicValue;
colCount++;
colCount = AddValueToCell(xlsWorksheet, xlsValidation, colCount, xlsRowCount, value, Indicators[key], oldCI, true);
}
xlsRowCount++;
}
// Auto fit
var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
var range = xlsWorksheet.get_Range("A1", last);
range.Columns.AutoFit();
// Save and display
xlsApp.DisplayAlerts = false;
xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing,
oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
oMissing, oMissing, oMissing);
xlsApp.Visible = true;
Marshal.ReleaseComObject(xlsWorksheets);
Marshal.ReleaseComObject(xlsWorksheet);
Marshal.ReleaseComObject(xlsValidation);
Marshal.ReleaseComObject(xlsWorkbooks);
Marshal.ReleaseComObject(xlsWorkbook);
Marshal.ReleaseComObject(xlsApp);
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
private void OpenExcel(string strFileName)
{
object objMissing = System.Reflection.Missing.Value;
this.appClsExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
appClsExcel.UserControl = true;
appClsExcel.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook wBookExcel = appClsExcel.Workbooks.Open(strFileName, objMissing, true, objMissing, objMissing, objMissing
, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
Microsoft.Office.Interop.Excel.Worksheet wSheetExcel = (Microsoft.Office.Interop.Excel.Worksheet)wBookExcel.ActiveSheet;
Microsoft.Office.Interop.Excel.Range rCell = wSheetExcel.UsedRange;
object[,] objList = (object[, ])rCell.Value2;
int excelcounts = objList.GetLength(0);
wbs = appClsExcel.Workbooks;
wb = wbs[1];
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["信息维护"];
int rowCount = ws.UsedRange.Rows.Count;
int colCount = ws.UsedRange.Columns.Count;
if (rowCount <= 0)
{
MessageBox.Show("文件中没有数据记录", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (colCount != 8)
{
MessageBox.Show("字段个数不对", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
DataTable MouldCycleTable = new IQC_MouldCycle().Tables["IQC_MouldCycle"];
for (int iRow = 1; iRow < rowCount; iRow++)
{
if (objList[iRow + 1, 1] != null && objList[iRow + 1, 2] != null)
{
DataRow row;
row = MouldCycleTable.NewRow();
row["materialcode"] = objList[iRow + 1, 1].ToString();
row["vendorcode"] = objList[iRow + 1, 2].ToString();
row["Mouldcode"] = objList[iRow + 1, 3] == null ? "" : objList[iRow + 1, 3].ToString();
row["Mouldtype"] = objList[iRow + 1, 4] == null ? "" : objList[iRow + 1, 4].ToString();
row["ColorMode"] = objList[iRow + 1, 5] == null ? "" : objList[iRow + 1, 5].ToString();
row["MouldQty"] = objList[iRow + 1, 6] == null ? "" : objList[iRow + 1, 6].ToString();
row["CavityQty"] = objList[iRow + 1, 7] == null ? "" : objList[iRow + 1, 7].ToString();
row["MouldLife"] = objList[iRow + 1, 8] == null ? "" : objList[iRow + 1, 8].ToString();
//row["RestQty"] = objList[iRow + 1, 1].ToString();
//row["thisTimeQty"] = objList[iRow + 1, 1].ToString();
row["updateMan"] = Login.username;
MouldCycleTable.Rows.Add(row);
}
else
{
break;
}
}
gridControl.DataSource = MouldCycleTable;
appClsExcel.Quit();
appClsExcel = null;
Process[] procs = Process.GetProcessesByName("Excel");
foreach (Process pro in procs)
{
pro.Kill();
}
GC.Collect();
}
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;
}
private void OpenFileExcel(string file)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
var viewApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
viewApp.Workbooks.Open(file,
0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
System.Threading.Thread.CurrentThread.CurrentCulture = _oldCi;
viewApp.Visible = true;
}
public void CreateImportFile(IndicatorManagerOptions indicatorManagerOptions, string filename)
{
ImporterBase importer = new ImporterBase();
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlsWorkbook;
Microsoft.Office.Interop.Excel.Workbooks xlsWorkbooks;
Microsoft.Office.Interop.Excel.Sheets xlsWorksheets;
Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet;
Microsoft.Office.Interop.Excel.Worksheet xlsValidation;
object oMissing = System.Reflection.Missing.Value;
validationRanges = new Dictionary<string, string>();
//Create new workbook
xlsWorkbooks = xlsApp.Workbooks;
xlsWorkbook = xlsWorkbooks.Add(true);
xlsWorksheets = xlsWorkbook.Worksheets;
//Get the first worksheet
xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]);
// add hidden validation worksheet
xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing);
xlsValidation.Name = validationSheetName;
xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
// row 1 column headers
xlsWorksheet.Cells[1, 1] = "Indicator ID";
xlsWorksheet.Cells[1, 2] = "Type ID";
xlsWorksheet.Cells[1, 3] = "Type Name";
xlsWorksheet.Cells[1, 4] = "Form Name"; // Drop down
xlsWorksheet.Cells[1, 5] = "Indicator Name English";
xlsWorksheet.Cells[1, 6] = "Indicator Name French";
xlsWorksheet.Cells[1, 7] = "Indicator Name Portuguese";
xlsWorksheet.Cells[1, 8] = "Indicator Name Bahasa";
xlsWorksheet.Cells[1, 9] = "Indicator Type"; // Drop down
xlsWorksheet.Cells[1, 10] = "Is Required";
xlsWorksheet.Cells[1, 11] = "Aggregation Rule"; // Drop down
xlsWorksheet.Cells[1, 12] = "Merge Rule"; // Drop down
xlsWorksheet.Cells[1, 13] = "Split Rule"; // Drop down
xlsWorksheet.Cells[1, 14] = "Sort Order";
// row 2+ indicators
int xlsRowCount = 2;
foreach (var ind in indicatorManagerOptions.SelectedIndicators)
{
xlsWorksheet.Cells[xlsRowCount, 1] = ind.ID;
xlsWorksheet.Cells[xlsRowCount, 2] = (int)indicatorManagerOptions.EntityType;
xlsWorksheet.Cells[xlsRowCount, 3] = indicatorManagerOptions.EntityType.ToString();
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(4), xlsRowCount, "", "", indicatorManagerOptions.FormTypes, System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 4] = ind.FormName;
var english = new TranslationLookupInstance(new CultureInfo("en-US"));
xlsWorksheet.Cells[xlsRowCount, 5] = english.GetValue(ind.Key, ind.Key);
var french = new TranslationLookupInstance(new CultureInfo("fr-FR"));
xlsWorksheet.Cells[xlsRowCount, 6] = french.GetValue(ind.Key, ind.Key);
var port = new TranslationLookupInstance(new CultureInfo("pt-PT"));
xlsWorksheet.Cells[xlsRowCount, 7] = port.GetValue(ind.Key, ind.Key);
var bahasa = new TranslationLookupInstance(new CultureInfo("id-ID"));
xlsWorksheet.Cells[xlsRowCount, 8] = bahasa.GetValue(ind.Key, ind.Key);
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(9), xlsRowCount, "", "", Enum.GetNames(typeof(IndicatorDataType)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 9] = ((IndicatorDataType)ind.DataTypeId).ToString();
xlsWorksheet.Cells[xlsRowCount, 10] = ind.IsRequired.ToString();
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(11), xlsRowCount, "", "", Enum.GetNames(typeof(IndicatorAggType)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 11] = ((IndicatorAggType)ind.AggregationRuleId).ToString();
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(12), xlsRowCount, "", "", Enum.GetNames(typeof(MergingRule)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 12] = ((MergingRule)ind.MergeRule).ToString();
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(13), xlsRowCount, "", "", Enum.GetNames(typeof(RedistrictingRule)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 13] = ((RedistrictingRule)ind.SplitRule).ToString();
xlsWorksheet.Cells[xlsRowCount, 14] = ind.SortOrder;
xlsRowCount++;
}
// Auto fit
var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
var range = xlsWorksheet.get_Range("A1", last);
range.Columns.AutoFit();
// Save and display
xlsApp.DisplayAlerts = false;
xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing,
oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
oMissing, oMissing, oMissing);
xlsApp.Visible = true;
Marshal.ReleaseComObject(xlsWorksheets);
Marshal.ReleaseComObject(xlsWorksheet);
Marshal.ReleaseComObject(xlsValidation);
Marshal.ReleaseComObject(xlsWorkbooks);
Marshal.ReleaseComObject(xlsWorkbook);
Marshal.ReleaseComObject(xlsApp);
}
public override bool Excel2HTML(string sourcePath, string targetPath, string targetRelativeDirectory)
{
Microsoft.Office.Interop.Excel.XlFileFormat targetType = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
try
{
if (!sourcePath.EndsWith(".xls") && !sourcePath.EndsWith(".xlsx"))
{
return(false);
}
if (File.Exists(targetPath))
{
File.Delete(targetPath);
}
excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (excelApp == null)
{
Log.LogInfo("无法创建excel对象，可能您的电脑未安装excel");
return(false);
}
Log.DebugInfo("excel srcPath: " + sourcePath);
Log.DebugInfo("excel targetPath: " + targetPath);
object target = targetPath;
object type = targetType;
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
workbook = workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workbook.SaveAs(target, type, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}
catch (Exception e)
{
Log.LogInfo("excel转换html:", e);
return(false);
}
finally
{
if (workbook != null)
{
workbook.Close(false, missing, missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
GC.Collect();
}
if (excelApp != null)
{
excelApp.Quit();
excelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
//修改html文件中的src元素的路径
changeHref(targetPath, targetRelativeDirectory);
}
return(true);
//throw new NotImplementedException();
}
/// <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;
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
System.Data.DataTable ds = new System.Data.DataTable();
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
if (chkSummery.Checked == false)
{
foreach (DataGridViewColumn col in dgvStockRegister.Columns)
{
ds.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvStockRegister.Rows)
{
DataRow dRow = ds.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
ds.Rows.Add(dRow);
}
if (cmbMainCategory.Text == "खते")
{
ds.Columns[3].ColumnName = "कोणाकडून आले व कंपनी";
ds.Columns[5].ColumnName = "दिवसातील आवक";
ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक");
ds.Columns.Remove("बियाणाचे नाव");
ds.Columns.Remove("बियाणाचा प्रकार");
ds.TableName = "खते रजिस्टर";
}
if (cmbMainCategory.Text == "किटकनाशके")
{
ds.Columns[3].ColumnName = "किटकनाशक कोणाकडून खरेदी केले त्याचे नाव व पावती क्र. व दिनांक";
ds.Columns[5].ColumnName = "खरेदी केलेला साठा";
ds.Columns.Remove("बियाणाचे नाव");
ds.Columns.Remove("बियाणाचा प्रकार");
ds.TableName = "किटकनाशके रजिस्टर";
}
if (cmbMainCategory.Text == "बियाणे")
{
ds.Columns[3].ColumnName = "कोणाकडून आले";
ds.Columns[5].ColumnName = "किती आले";
ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक");
ds.TableName = "बियाणे रजिस्टर";
}
}
else
{
foreach (DataGridViewColumn col in dgvSummerysales.Columns)
{
ds.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvSummerysales.Rows)
{
DataRow dRow = ds.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
ds.Rows.Add(dRow);
}
ds.TableName = "विक्री रजिस्टर";
}
xlWorksheet.Name = ds.TableName;
for (int j = 1; j < ds.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < ds.Rows.Count; k++)
{
for (int l = 0; l < ds.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
ds.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
catch (Exception ac)
{
MessageBox.Show(ac.Message);
}
}
private void buttonOst_Click(object sender, EventArgs e)
{
//массив0 выдано
refreshOst();
int[,] arr = new int[listViewBilety2.Items.Count, listViewBilety2.Columns.Count];
for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++)
{
arr[x, y] = int.Parse(listViewBilety2.Items[x].SubItems[y].Text.Trim());
}
}
//массив1 сдано
refreshOst1();
int[,] arr1 = new int[listViewBilety2.Items.Count, listViewBilety2.Columns.Count];
for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++)
{
arr1[x, y] = int.Parse(listViewBilety2.Items[x].SubItems[y].Text.Trim());
}
}
//массив2 названия
refreshOst2();
string[,] b = new string[listViewBilety2.Items.Count, listViewBilety2.Columns.Count];
for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++)
{
b[x, y] = listViewBilety2.Items[x].SubItems[y].Text;
}
}
//refreshBilety2();
listViewBilety2.Items.Clear();
listViewBilety2.Columns.Clear();
List<int> ARR = new List<int>();
List<int> ARR1 = new List<int>();
List<int> ARR2 = new List<int>();
List<int> ARR3 = new List<int>();
List<int> unique = new List<int>();
List<object> unique2 = new List<object>();
//диапазоны из нач/кон значений выдано
for (int x = 0; x <= ((arr.Length / 2) - 1); x++)
{
for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++)
{
ARR.Add(i);
}
}
//диапазоны из нач/кон значений сдано
for (int x = 0; x <= ((arr1.Length / 2) - 1); x++)
{
for (int i = arr1[x, 0], maxi = arr1[x, 1]; i <= maxi; i++)
{
ARR1.Add(i);
}
}
//удаление того, что есть в журнале, но нет в билетах
for (int x = 0; x < ARR.Count; x++)
{
for (int y = 0; y < ARR1.Count; y++)
{
if (ARR[x].Equals(ARR1[y]))
ARR3.Add(ARR[x]);
}
}
//объединение списков
ARR2.AddRange(ARR.ToArray());
ARR2.AddRange(ARR3.ToArray());
ARR2.Sort();
//удаление двойных
for (int m = 0; m < ARR2.Count; m++)
{
for (int j = m + 1; j < ARR2.Count; j++)
{
if (ARR2[j].Equals(ARR2[m]))
{
ARR2.RemoveAt(j--);
ARR2.RemoveAt(m);
}
}
}
//приведение к начальным
for (int x = 0; x <= ((arr.Length / 2) - 1); x++)
{
for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++)
{
if (ARR2.Contains(i))
{
unique.Add(x + 1);
unique.Add(i);
}
}
}
//собственно диапазоны остатка
for (int x = 0; x < unique.Count; x = x + 2)
{
int m;
if (x == 0) m = x + 1; else m = x - 2;
if (!unique[x].Equals(unique[m]))
{
unique2.Add(unique[x]);
unique2.Add(unique[x + 1]);
}
int f;
if (x < unique.Count - 2) f = x + 2; else f = x - 1;
if (!unique[x].Equals(unique[f]))
{
unique2.Add(unique[x + 1]);
for (int y = 0; y < b.Length / 3; y++)
{
if (unique[x + 1].Equals(int.Parse(b[y, 0])))
{
unique2.Add(b[y, 1]);
unique2.Add(b[y, 2]);
}
}
}
}
//массив для екселя
object[,] c = new object[unique2.Count, unique2.Count / 3];
for (int i = 0, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 0] = unique2[i];
}
for (int i = 1, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 1] = unique2[i];
}
for (int i = 2, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 2] = unique2[i];
}
for (int i = 3, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 3] = unique2[i];
}
for (int i = 4, k = 0; i < unique2.Count; i = i + 5, k++)
{
c[k, 4] = unique2[i];
}
object[] a = unique2.ToArray();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
string workbookPath = Application.StartupPath + "\\templates/bilety.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A4", "E" + unique2.Count);
excelCellName.Value2 = c;
}
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();
}
/// <summary>
/// Click event to handle the export to excel
/// </summary>
/// <param name="sender">Sender object</param>
/// <param name="e">event data</param>
private void Export_Click(object sender, EventArgs e)
{
try
{
var table = (DataTable)dataGridViewMembers.DataSource;
Microsoft.Office.Interop.Excel.ApplicationClass excel
= new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
int columnIndex = 0;
foreach (DataColumn col in table.Columns)
{
columnIndex++;
excel.Cells[1, columnIndex] = col.ColumnName;
}
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
columnIndex = 0;
foreach (DataColumn col in table.Columns)
{
columnIndex++;
if (columnIndex == 4 || columnIndex == 5 || columnIndex == 6)
{
if (columnIndex == 4)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(Occupation), row[col.ColumnName]);
}
if (columnIndex == 5)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(MaritalStatus), row[col.ColumnName]);
}
if (columnIndex == 6)
{
excel.Cells[rowIndex + 1, columnIndex]
= Enum.GetName(typeof(HealthStatus), row[col.ColumnName]);
}
}
else
{
excel.Cells[rowIndex + 1, columnIndex] = row[col.ColumnName].ToString();
}
}
}
excel.Visible = true;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
catch (Exception ex)
{
this.ShowErrorMessage(ex);
}
}
private void CalcData(int P_int_Row, string P_str_Column)
{
CloseProcess("EXCEL"); //关闭所有Excel进程
string P_str_workBook1 = Application.StartupPath + "\\Excel1.xls"; //记录第一个要计算的工作簿路径
string P_str_workBook2 = Application.StartupPath + "\\Excel2.xls"; //记录第二个要计算的工作簿路径
string P_str_workBook3 = Application.StartupPath + "\\Excel3.xls"; //记录第三个要计算的工作簿路径
string P_str_workBook4 = Application.StartupPath + "\\Sum.xls"; //记录存储计算结果的工作簿路径
object missing = System.Reflection.Missing.Value; //定义object缺省值
Microsoft.Office.Interop.Excel.ApplicationClass excel1 = new Microsoft.Office.Interop.Excel.ApplicationClass(); //实例化Excel对象
excel1.Visible = false; //设置Excel文件隐藏
//打开第一个计算的工作簿
Microsoft.Office.Interop.Excel.Workbook workbook1 = excel1.Workbooks.Open(P_str_workBook1, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//记录要计算的第一个工作表
Microsoft.Office.Interop.Excel._Worksheet worksheet1 = (Microsoft.Office.Interop.Excel._Worksheet)workbook1.Worksheets.get_Item(tscbox_Sheet.Text);
Microsoft.Office.Interop.Excel.ApplicationClass excel2 = new Microsoft.Office.Interop.Excel.ApplicationClass(); //实例化Excel对象
excel2.Visible = false; //设置Excel文件隐藏
//打开第二个计算的工作簿
Microsoft.Office.Interop.Excel.Workbook workbook2 = excel2.Workbooks.Open(P_str_workBook2, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//记录要计算的第二个工作表
Microsoft.Office.Interop.Excel._Worksheet worksheet2 = (Microsoft.Office.Interop.Excel._Worksheet)workbook2.Worksheets.get_Item(tscbox_Sheet.Text);
Microsoft.Office.Interop.Excel.ApplicationClass excel3 = new Microsoft.Office.Interop.Excel.ApplicationClass(); //实例化Excel对象
excel3.Visible = false; //设置Excel文件隐藏
//打开第三个计算的工作簿
Microsoft.Office.Interop.Excel.Workbook workbook3 = excel3.Workbooks.Open(P_str_workBook3, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//记录要计算的第三个工作表
Microsoft.Office.Interop.Excel._Worksheet worksheet3 = (Microsoft.Office.Interop.Excel._Worksheet)workbook3.Worksheets.get_Item(tscbox_Sheet.Text);
Microsoft.Office.Interop.Excel.ApplicationClass excel4 = new Microsoft.Office.Interop.Excel.ApplicationClass(); //实例化Excel对象
excel4.Visible = false; //设置Excel文件隐藏
//打开存储计算结果的工作簿
Microsoft.Office.Interop.Excel.Workbook workbook4 = excel4.Workbooks.Open(P_str_workBook4, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//记录要存储结算结果的工作表
Microsoft.Office.Interop.Excel._Worksheet worksheet4 = (Microsoft.Office.Interop.Excel._Worksheet)workbook4.Worksheets.get_Item(tscbox_Sheet.Text);
excel4.DisplayAlerts = false; //设置保存Excel时不显示对话框
worksheet4.Activate(); //激活工作表
Decimal P_dml_NumOne = 0; //获取第一个工作表的值
Decimal P_dml_NumTwo = 0; //获取第二个工作表的值
Decimal P_dml_NumThree = 0; //获取第三个工作表的值
//判断指定单元格内容是否为空
if (((Microsoft.Office.Interop.Excel.Range)worksheet1.Cells[P_int_Row, P_str_Column]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)worksheet1.Cells[P_int_Row, P_str_Column]).Text == null)
{
P_dml_NumOne = 0;//将第一个值设置为0
}
else
{
P_dml_NumOne = Convert.ToDecimal(((Microsoft.Office.Interop.Excel.Range)worksheet1.Cells[P_int_Row, P_str_Column]).Text);//记录第一个值
}
//判断指定单元格内容是否为空
if (((Microsoft.Office.Interop.Excel.Range)worksheet2.Cells[P_int_Row, P_str_Column]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)worksheet2.Cells[P_int_Row, P_str_Column]).Text == null)
{
P_dml_NumTwo = 0;//将第二个值设置为0
}
else
{
P_dml_NumTwo = Convert.ToDecimal(((Microsoft.Office.Interop.Excel.Range)worksheet2.Cells[P_int_Row, P_str_Column]).Text);//记录第二个值
}
//判断指定单元格内容是否为空
if (((Microsoft.Office.Interop.Excel.Range)worksheet3.Cells[P_int_Row, P_str_Column]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)worksheet3.Cells[P_int_Row, P_str_Column]).Text == null)
{
P_dml_NumThree = 0;//将第三个值设置为0
}
else
{
P_dml_NumThree = Convert.ToDecimal(((Microsoft.Office.Interop.Excel.Range)worksheet3.Cells[P_int_Row, P_str_Column]).Text); //记录第三个值
}
Decimal P_dml_Sum = P_dml_NumOne + P_dml_NumTwo + P_dml_NumThree; //计算总和
try
{
worksheet4.Cells[P_int_Row, P_str_Column] = P_dml_Sum.ToString(); //向工作簿的指定单元格中写入计算后的数据
workbook4.Save(); //保存Excel文件
}
catch
{
MessageBox.Show("写入第" + P_int_Row.ToString() + "行，第" + P_str_Column + "列时出错！");
}
finally
{
workbook4.Save();//保存Excel文件
//退出打开的4个Excel文件
excel1.Quit();
excel2.Quit();
excel3.Quit();
excel4.Quit();
}
WBrowser_Excel.Navigate(P_str_workBook4);//在窗体中显示多表计算后的Excel文件
}
public void Export(DataTable dt, string sheetName, string title)
{
//Tạo các đối tượng Excel
//Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
object oBooks = oExcel.Workbooks;
Microsoft.Office.Interop.Excel.Sheets oSheets;
Microsoft.Office.Interop.Excel.Workbook oBook;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
//Tạo mới một Excel WorkBook
//System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
//System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
//oExcel.Workbooks.Add();
//System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
oExcel.Application.SheetsInNewWorkbook = 1;
oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
oSheets = oBook.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
oSheet.Name = sheetName;
//// Tạo phần đầu nếu muốn
Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "H1");
head.MergeCells = true;
head.Value2 = title;
head.Font.Bold = true;
head.Font.Name = "Tahoma";
head.Font.Size = "18";
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo tiêu đề cột
Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
cl1.Value2 = "Tên Môn Học";
cl1.ColumnWidth = 18;
Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
cl2.Value2 = "Số Tín Chỉ";
cl2.ColumnWidth = 10;
Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
cl3.Value2 = "Họ Tên";
cl3.ColumnWidth = 20;
Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3");
cl4.Value2 = "Điểm Lần I";
cl4.ColumnWidth = 10;
Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3");
cl5.Value2 = "Điểm Lần II";
cl5.ColumnWidth = 10;
Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3");
cl6.Value2 = "Lần Thi";
cl6.ColumnWidth = 8;
Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3");
cl7.Value2 = "Ngày Thi";
cl7.ColumnWidth = 13;
Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3");
cl8.Value2 = "Ghi Chú";
cl8.ColumnWidth = 10;
Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "H3");
rowHead.Font.Bold = true;
// Kẻ viền
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Thiết lập màu nền
rowHead.Interior.ColorIndex = 15;
rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
// vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
//Chuyển dữ liệu từ DataTable vào mảng đối tượng
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
//Thiết lập vùng điền dữ liệu
int rowStart = 4;
int columnStart = 1;
int rowEnd = rowStart + dt.Rows.Count - 1;
int columnEnd = dt.Columns.Count;
// Ô bắt đầu điền dữ liệu
Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
// Ô kết thúc điền dữ liệu
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
// Lấy về vùng điền dữ liệu
Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);
//Điền dữ liệu vào vùng đã thiết lập
range.Value2 = arr;
// Kẻ viền
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Căn giữa cột STT
Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
private void buttonZP_Click(object sender, EventArgs e)
{
refreshReports();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
//копировать шаблон
//System.IO.File.Copy(Application.StartupPath + "\\template.xls", Application.StartupPath + "\\template2.xls", true);
string workbookPath = Application.StartupPath + "\\templates/template.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
// параметры опена FileName, UpdateLinks,ReadOnly, Format, Password,WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter,
// Editable, Notify, Converter, AddToMRU
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
// бланк для редактирования
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
//Имя
Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E9", "E9");
excelCellName.Value2 = comboBoxEkskursovod.Text;
//Даты отчета
Microsoft.Office.Interop.Excel.Range excelCellDate1 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("C8", "C8");
excelCellDate1.Value2 = dateTimePicker1.Text;
Microsoft.Office.Interop.Excel.Range excelCellDate2 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F8", "F8");
excelCellDate2.Value2 = dateTimePicker2.Text;
//Категория
Microsoft.Office.Interop.Excel.Range excelCellCat = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("D11", "D11");
excelCellCat.Value2 = getCategory();
//зп
Microsoft.Office.Interop.Excel.Range excelCellZP = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A14", "G46");
Microsoft.Office.Interop.Excel.Range excelCellZP1 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("J14", "P46");
Microsoft.Office.Interop.Excel.Range excelCellZP2 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("S14", "Y46");
Microsoft.Office.Interop.Excel.Range excelCellZP3 = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("AB14", "AH46");
object[,] obj = new object[listViewReports.Items.Count, listViewReports.Columns.Count];
for (int x = 0, maxX = listViewReports.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewReports.Columns.Count; y < maxY; y++)
{
obj[x, y] = listViewReports.Items[x].SubItems[y].Text;
}
if (listViewReports.Items.Count > 99)
{
object[,] obj0 = new object[33, 7];
object[,] obj1 = new object[33, 7];
object[,] obj2 = new object[33, 7];
object[,] obj3 = new object[33, 7];
Array.Copy(obj, 0, obj0, 0, 231);
Array.Copy(obj, 231, obj1, 0, 231);
Array.Copy(obj, 462, obj2, 0, 231);
Array.Copy(obj, 693, obj3, 0, ((listViewReports.Items.Count * 7) - 693));
excelCellZP.Value2 = obj0;
excelCellZP1.Value2 = obj1;
excelCellZP2.Value2 = obj2;
excelCellZP3.Value2 = obj3;
}
else if (listViewReports.Items.Count > 66)
{
object[,] obj0 = new object[33, 7];
object[,] obj1 = new object[33, 7];
object[,] obj2 = new object[33, 7];
Array.Copy(obj, 0, obj0, 0, 231);
Array.Copy(obj, 231, obj1, 0, 231);
Array.Copy(obj, 462, obj2, 0, ((listViewReports.Items.Count * 7) - 462));
excelCellZP.Value2 = obj0;
excelCellZP1.Value2 = obj1;
excelCellZP2.Value2 = obj2;
}
else if (listViewReports.Items.Count > 33)
{
object[,] obj0 = new object[33, 7];
object[,] obj1 = new object[33, 7];
Array.Copy(obj, 0, obj0, 0, 231);
Array.Copy(obj, 231, obj1, 0, ((listViewReports.Items.Count * 7) - 231));
excelCellZP.Value2 = obj0;
excelCellZP1.Value2 = obj1;
}
else
{
object[,] obj0 = new object[33, 7];
Array.Copy(obj, 0, obj0, 0, (listViewReports.Items.Count * 7));
excelCellZP.Value2 = obj0;
}
}
}
public List<DataTable> Import(string path)
{
List<DataTable> foundDataTables = new List<DataTable>();
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.Sheets sheets = workBook.Worksheets;
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) {
DataTable dt = new DataTable(sheet.Name);
if (sheet.Name.Contains("Operation")) {
int index = 0;
object rowIndex = 2;
dt.Columns.Add("Step");
dt.Columns.Add("Comment");
dt.Columns.Add("Operation");
DataRow row;
while (((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 1]).Value2 != null) {
rowIndex = 2 + index;
row = dt.NewRow();
row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 1]).Value2);
row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 2]).Value2);
row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 3]).Value2);
index++;
dt.Rows.Add(row);
}
} else if (sheet.Name.Contains("Linked")) {
int index = 0;
object rowIndex = 2;
dt.Columns.Add("FileLink");
dt.Columns.Add("RelativePath");
DataRow row;
while (((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 1]).Value2 != null) {
rowIndex = 2 + index;
row = dt.NewRow();
row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 1]).Value2);
row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, 2]).Value2);
index++;
dt.Rows.Add(row);
}
}
foundDataTables.Add(dt);
}
app.Workbooks.Close();
return foundDataTables;
}
private void buttonExcel_Click(object sender, EventArgs e)
{
if (listViewReports.Items.Count == 0)
{
MessageBox.Show("Нет элементов для экспорта в excel",
"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
excelApp.Application.Workbooks.Add(Type.Missing);
excelApp.Columns.ColumnWidth = 15;
int i = 1;
int i2 = 4;
int i3 = 1;
foreach (ColumnHeader col in listViewReports.Columns)
{
excelApp.Cells[i2-1, i3] = col.Text; i3++;
}
foreach (ListViewItem lvi in listViewReports.Items)
{
i = 1;
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
excelApp.Cells[i2, i] = lvs.Text;
i++;
}
i2++;
}
}
//将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>
/// 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;
}
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;
}
//private void subtot()
//{
// try
// {
// decimal TotalCredit = 0;
// for (int i = 0; i < dgvAccounting.Rows.Count - 1; ++i)
// {
// if (dgvAccounting.Rows[i].Cells[5].Value != null)
// {
// TotalCredit += Convert.ToDecimal(dgvAccounting.Rows[i].Cells[5].Value);
// }
// }
// dgvAccounting[5, dgvAccounting.Rows.Count - 1].Value = TotalCredit;
// // txtTotaldrAmount.Text = TotalCredit.ToString();
// decimal DueAmt = 0;
// }
// catch(Exception ex)
// {
// throw ex;
// }
//}
private void btneExcelReport_Click(object sender, EventArgs e)
{
try
{
if (dgvAccounting.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
DataTable dtAccount = new DataTable();
foreach (DataGridViewColumn col in dgvAccounting.Columns)
{
dtAccount.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvAccounting.Rows)
{
DataRow dRow = dtAccount.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
dtAccount.Rows.Add(dRow);
}
if (Utility.Langn == "English")
{
dtAccount.TableName = "Expenses Report";
}
else
{
dtAccount.TableName = "खर्च रिपोर्ट(Expenses Report)";
}
xlWorksheet.Name = dtAccount.TableName;
for (int j = 1; j < dtAccount.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = dtAccount.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < dtAccount.Rows.Count; k++)
{
for (int l = 0; l < dtAccount.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
dtAccount.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public virtual void CreateImportFile(string filename, List<AdminLevel> adminLevels, AdminLevelType adminLevelType, ImportOptions opts)
{
options = opts;
ReloadDropdownValues();
LoadRelatedLists();
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlsWorkbook;
Microsoft.Office.Interop.Excel.Workbooks xlsWorkbooks;
Microsoft.Office.Interop.Excel.Sheets xlsWorksheets;
Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet;
Microsoft.Office.Interop.Excel.Worksheet xlsValidation;
object oMissing = System.Reflection.Missing.Value;
validationRanges = new Dictionary<string, string>();
//Create new workbook
xlsWorkbooks = xlsApp.Workbooks;
xlsWorkbook = xlsWorkbooks.Add(true);
xlsWorksheets = xlsWorkbook.Worksheets;
//Get the first worksheet
xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]);
// add hidden validation worksheet
xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing);
xlsValidation.Name = validationSheetName;
xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
// row 1 column headers
DemoRepository repo = new DemoRepository();
List<string> names = adminLevelType == null? new List<string>() : repo.GetAdminLevelTypeNames(adminLevelType.Id);
if (options.SurveyNames.Count > 0) // Multiple admin units for surveys
xlsWorksheet.Cells[1, 1] = "* " + TranslationLookup.GetValue("SurveyName");
else
xlsWorksheet.Cells[1, 1] = "* " + TranslationLookup.GetValue("ID");
for (int i = 0; i < names.Count; i++)
xlsWorksheet.Cells[1, 2 + i] = "* " + names[i];
int locationCount = names.Count + 1;
int xlsColCount = names.Count + 1; // add one for id;
xlsColCount = AddTypeSpecific(xlsWorksheet, xlsColCount);
int colCountAfterStatic = xlsColCount;
foreach (var item in Indicators)
{
if (item.Value.DataTypeId == (int)IndicatorDataType.SentinelSite || item.Value.IsCalculated || item.Value.IsMetaData)
continue;
string isReq = "";
if (item.Value.IsRequired)
isReq = "* ";
// if the filtered list still has more than 7 possible multiselect values, do some weird shit.
if (options.IndicatorValuesSublist.ContainsKey(item.Value.DisplayName) && options.IndicatorValuesSublist[item.Value.DisplayName].Count > 6)
{
int optionNumber = 1;
foreach (string opt in options.IndicatorValuesSublist[item.Value.DisplayName])
{
xlsColCount++;
xlsWorksheet.Cells[1, xlsColCount] = isReq + TranslationLookup.GetValue(item.Key, item.Key) + Translations.ImportSelectionOption + optionNumber;
optionNumber++;
}
}
else
{
xlsColCount++;
xlsWorksheet.Cells[1, xlsColCount] = isReq + TranslationLookup.GetValue(item.Key, item.Key);
}
}
//xlsWorksheet.Cells[1, xlsColCount + 1] = TranslationLookup.GetValue("Notes");
// row 2+ admin levels
int xlsRowCount = 2;
if (options.SurveyNames.Count > 0) // Multiple admin units for surveys
{
foreach (var survey in options.SurveyNames.OrderBy(a => a.DisplayName))
{
xlsWorksheet.Cells[xlsRowCount, 1] = survey.DisplayName;
AddTypeSpecificLists(xlsWorksheet, xlsValidation, 0, xlsRowCount, oldCI, locationCount);
xlsRowCount = AddIndicatorColumns(oldCI, xlsWorksheet, xlsValidation, colCountAfterStatic, xlsRowCount);
}
}
else
{
foreach (AdminLevel l in adminLevels.OrderBy(a => a.SortOrder))
{
xlsWorksheet.Cells[xlsRowCount, 1] = l.Id;
List<AdminLevel> parents = repo.GetAdminLevelParentNames(l.Id);
int aCol = 2;
foreach (AdminLevel adminlevel in parents)
{
xlsWorksheet.Cells[xlsRowCount, aCol] = adminlevel.Name;
aCol++;
}
AddTypeSpecificLists(xlsWorksheet, xlsValidation, l.Id, xlsRowCount, oldCI, locationCount);
xlsRowCount = AddIndicatorColumns(oldCI, xlsWorksheet, xlsValidation, colCountAfterStatic, xlsRowCount);
}
}
// Auto fit
var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
var range = xlsWorksheet.get_Range("A1", last);
range.Columns.AutoFit();
// Save and display
xlsApp.DisplayAlerts = false;
xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing,
oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
oMissing, oMissing, oMissing);
xlsApp.Visible = true;
Marshal.ReleaseComObject(xlsWorksheets);
Marshal.ReleaseComObject(xlsWorksheet);
Marshal.ReleaseComObject(xlsValidation);
Marshal.ReleaseComObject(xlsWorkbooks);
Marshal.ReleaseComObject(xlsWorkbook);
Marshal.ReleaseComObject(xlsApp);
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
private void buttonOst_Click(object sender, EventArgs e)
{
//массив0 квитанции
refreshOst();
int[,] arr = new int [listViewKvit.Items.Count, listViewKvit.Columns.Count];
for (int x = 0, maxX = listViewKvit.Items.Count; x < maxX; x++)
{
for (int y = 0, maxY = listViewKvit.Columns.Count; y < maxY; y++)
{
arr[x, y] = int.Parse(listViewKvit.Items[x].SubItems[y].Text.Trim());
}
}
//массив1 журнал
refreshOst1();
int[] arr1 = new int[listViewKvit.Items.Count];
for (int x = 0, maxX = listViewKvit.Items.Count; x < maxX; x++)
{
arr1[x] = int.Parse(listViewKvit.Items[x].Text.Trim());
}
string[,] b = new string [10, 10];
refreshKvit();
List <int> ARR = new List <int>();
List <int> ARR1 = new List <int>();
List <int> ARR2 = new List <int>();
List <int> ARR3 = new List <int>();
List <object> unique = new List <object>();
List <object> unique2 = new List <object>();
//диапазоны из нач/кон значений квитанции
for (int x = 0; x <= ((arr.Length / 2) - 1); x++)
{
for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++)
{
ARR.Add(i);
}
}
//журнал
foreach (int g in arr1)
{
ARR1.Add(g);
}
//удаление того, что есть в журнале, но нет в билетах
for (int x = 0; x < ARR.Count; x++)
{
for (int y = 0; y < ARR1.Count; y++)
{
if (ARR[x].Equals(ARR1[y]))
{
ARR3.Add(ARR[x]);
}
}
}
//объединение списков
ARR2.AddRange(ARR.ToArray());
ARR2.AddRange(ARR3.ToArray());
ARR2.Sort();
//удаление двойных
for (int m = 0; m < ARR2.Count; m++)
{
for (int j = m + 1; j < ARR2.Count; j++)
{
if (ARR2[j].Equals(ARR2[m]))
{
ARR2.RemoveAt(j--);
ARR2.RemoveAt(m);
}
}
}
//приведение к начальным
for (int x = 0; x <= ((arr.Length / 2) - 1); x++)
{
for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++)
{
if (ARR2.Contains(i))
{
unique.Add(x + 1);
unique.Add(i);
}
}
}
//собственно диапазоны остатка
for (int x = 0; x < unique.Count; x = x + 2)
{
int m;
if (x == 0)
{
m = x + 1;
}
else
{
m = x - 2;
}
if (!unique[x].Equals(unique[m]))
{
unique2.Add(unique[x]);
unique2.Add(unique[x + 1]);
}
int f;
if (x < unique.Count - 2)
{
f = x + 2;
}
else
{
f = x - 1;
}
if (!unique[x].Equals(unique[f]))
{
unique2.Add(unique[x + 1]);
/*for (int y = 0; y < b.Length/3; y++)
* {
* if (unique[x + 1].Equals(int.Parse(b[y, 0])))
* {
* unique2.Add(b[y, 1]);
* unique2.Add(b[y, 2]);
* }
* }*/
}
}
//массив для екселя
object[,] c = new object[unique2.Count, 3];
for (int i = 0, k = 0; i < unique2.Count; i = i + 3, k++)
{
c[k, 0] = unique2[i];
}
for (int i = 1, k = 0; i < unique2.Count; i = i + 3, k++)
{
c[k, 1] = unique2[i];
}
for (int i = 2, k = 0; i < unique2.Count; i = i + 3, k++)
{
c[k, 2] = unique2[i];
}
/*for (int i = 3, k = 0; i < unique2.Count; i = i + 5, k++)
* {
* c[k, 3] = unique2[i];
* }
*
* for (int i = 4, k = 0; i < unique2.Count; i = i + 5, k++)
* {
* c[k, 4] = unique2[i];
* }*/
object[] a = unique2.ToArray();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
string workbookPath = Application.StartupPath + "\\templates/bilety.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A4", "C" + unique2.Count);
excelCellName.Value2 = c;
}
/// <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;
}
private void btnDocFile_Click(object sender, EventArgs e)
{
if (txtDuongDan.Text.Length > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range range;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(txtDuongDan.Text, 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;
if (range.Rows.Count > 0)
{
btnKiemTraDuLieu.Enabled = true;
btnXoaDuLieu.Enabled = true;
}
// string str;
int rCnt = 0;
//int stt = 0;
for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
{
try
{
if ((xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2 != null) || (xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2.ToString() == ""))
{
DataRow dr = dt.NewRow();
if (xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2 != null)
{
dr[0] = xlWorkSheet.get_Range("A" + rCnt, "A" + rCnt).Value2.ToString();
}
if (xlWorkSheet.get_Range("B" + rCnt, "B" + rCnt).Value2 != null)
{
dr[1] = xlWorkSheet.get_Range("B" + rCnt, "B" + rCnt).Value2.ToString();
}
else
{
dr[1] = "";
}
if (xlWorkSheet.get_Range("C" + rCnt, "C" + rCnt).Value2 != null)
{
dr[2] = xlWorkSheet.get_Range("C" + rCnt, "C" + rCnt).Value2.ToString();
}
else
{
dr[2] = "";
}
if (xlWorkSheet.get_Range("D" + rCnt, "D" + rCnt).Value2 != null)
{
// MessageBox.Show("" + xlWorkSheet.get_Range("D" + rCnt, "D" + rCnt).Value2);
dr[3] = DateTime.Parse(xlWorkSheet.get_Range("D" + rCnt, "D" + rCnt).Value2.ToString());
// dr[3] = DateTime.FromOADate(d);
// MessageBox.Show("" + dr[3]);
}
else
{
dr[3] = DateTime.Now;
}
if (xlWorkSheet.get_Range("E" + rCnt, "E" + rCnt).Value2 != null)
{
dr[4] = xlWorkSheet.get_Range("E" + rCnt, "E" + rCnt).Value2.ToString();
}
else
{
dr[4] = "";
}
if (xlWorkSheet.get_Range("F" + rCnt, "F" + rCnt).Value2 != null)
{
if (xlWorkSheet.get_Range("F" + rCnt, "F" + rCnt).Value2.ToString().Trim() == "Nam")
{
dr[5] = true;
}
else
{
dr[5] = false;
}
}
else
{
dr[5] = true;
}
if (xlWorkSheet.get_Range("G" + rCnt, "G" + rCnt).Value2 != null)
{
dr[6] = xlWorkSheet.get_Range("G" + rCnt, "G" + rCnt).Value2.ToString();
}
else
{
dr[6] = "";
}
if (xlWorkSheet.get_Range("H" + rCnt, "H" + rCnt).Value2 != null)
{
dr[7] = xlWorkSheet.get_Range("H" + rCnt, "H" + rCnt).Value2.ToString();
}
else
{
dr[7] = "";
}
if (xlWorkSheet.get_Range("I" + rCnt, "I" + rCnt).Value2 != null)
{
dr[8] = xlWorkSheet.get_Range("I" + rCnt, "I" + rCnt).Value2.ToString();
}
else
{
dr[8] = "";
}
if (xlWorkSheet.get_Range("J" + rCnt, "J" + rCnt).Value2 != null)
{
dr[9] = DateTime.Parse(xlWorkSheet.get_Range("J" + rCnt, "J" + rCnt).Value2.ToString());
// dr[9] = DateTime.FromOADate(d);
}
else
{
dr[9] = DateTime.Now;
}
if (xlWorkSheet.get_Range("K" + rCnt, "K" + rCnt).Value2 != null)
{
dr[10] = xlWorkSheet.get_Range("K" + rCnt, "K" + rCnt).Value2.ToString();
}
else
{
dr[10] = "";
}
dr[11] = true;
dr[12] = "0";
dt.Rows.Add(dr);
}
}
catch
{
rCnt = range.Rows.Count; // thoat vong for khi ko co du lieu
}
}
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
gridItem.DataSource = dt;
btnKiemTraDuLieu.Enabled = true;
}
}
private void buttonExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
excelApp.Application.Workbooks.Add(Type.Missing);
excelApp.Columns.ColumnWidth = 15;
excelApp.Cells[1, 1 ]= "№";
excelApp.Cells[1, 2 ]= "Дата";
excelApp.Cells[1, 3 ]= "Квитанция";
excelApp.Cells[1, 4 ]= "ФИО";
excelApp.Cells[1, 5 ]= "Город";
excelApp.Cells[1, 6 ]= "Кол-во дней";
excelApp.Cells[1, 7] = "Кол-во человек";
excelApp.Cells[1, 8] = "Житие";
excelApp.Cells[1, 9] = "Парковка";
excelApp.Cells[1, 10] = "Бронь";
excelApp.Cells[1, 11] = "Безнал";
excelApp.Cells[1, 12] = "Заказчик";
excelApp.Cells[1, 13] = "Сумма";
int i = 1;
int i2 = 4;
foreach (ListViewItem lvi in listViewRasselenie.Items)
{
i = 1;
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
excelApp.Cells[i2, i] = lvs.Text;
i++;
}
i2++;
}
}
private void buttonExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
string workbookPath = Application.StartupPath + "\\templates/zhurnal.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
int i = 1;
int i2 = 4;
foreach (ListViewItem lvi in listViewZhurnal.Items)
{
i = 1;
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
excelWorksheet.Cells[i2, i] = lvs.Text;
i++;
}
i2++;
}
}
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>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
string saveFileName = "工作难度系数";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = saveFileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
dynamic xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
ExcelHelper excel = new ExcelHelper(saveFileName);//创建excel
#region 标题信息
excel.SetCells(1, 1, "编号");
excel.SetCells(1, 2, "来源");
excel.SetCells(1, 3, "名称");
excel.SetCells(1, 4, "描述");
excel.SetCells(1, 5, "开始时间");
excel.SetCells(1, 6, "结束时间");
excel.SetCells(1, 7, "预计工作量");
excel.SetCells(1, 8, "实际工作量");
excel.SetCells(1, 9, "难度系数");
excel.SetCellsBackColor(1, 1, 1, 9, ColorIndex.灰色25); //设置颜色
excel.SetCellsAlign(1, 1, 1, 9, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter); //设置对齐
#endregion
#region 项目计划
List <string> columns;
columns = new List <string>()
{
"RowNo", "source", "name", "desc", "startdate", "enddate", "workload", "actualworkload", "degree"
};
Export(dt, excel, columns);
#endregion
#region 设置单元格格式
//先设置单元格居右
excel.SetCellsAlign(2, 1, dt.Rows.Count + 1, 9, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight);
int first = 1;
//foreach (DataRow item in dt.Rows)
//{
// rowno++;
// if (item["type"].ToString() == "-1")
// {
// last = rowno - 2;
// if (last > 2)
// excel.SetCellsBorder(first, 1, last, 8);//设置边框
// first = rowno;
// excel.SetCellsStyle(rowno, 1, rowno, 8, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, true, ColorIndex.灰色25);//设置
// excel.SetCellsStyle(rowno + 1, 1, rowno + 1, 8, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, false, ColorIndex.灰色25);//设置
// }
//}
excel.SetCellsBorder(first, 1, dt.Rows.Count, 9); //设置边框
excel.SetCellsBorder(dt.Rows.Count, 8, dt.Rows.Count + 1, 9); //设置边框
excel.InsertRows(1, 2);
excel.MergeCells(1, 1, 2, 9, project.Name + "—工作难度系数"); //项目名称
excel.SetCellsStyle(1, 1, 1, 1, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, true, ColorIndex.无色); //项目名称粗体
#endregion
excel.SaveAsFile();//文件保存
MessageBox.Show("操作成功！");
}
