public CObjectExportColumn(C1FlexGrid i_fg, Excel.Worksheet i_objWorkSheet, int i_iGridCol, int i_iSheetCol, int i_iSheetStartRow)
{
m_fg = i_fg;
m_objWorkSheet = i_objWorkSheet;
m_iGridCol = i_iGridCol;
m_iSheetCol = i_iSheetCol;
m_iSheetStartRow = i_iSheetStartRow;
}
private void btnUploadMdr_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Open Mdr File (Exel File)";
ofd.Filter = "Excel Files|*.xls;*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
var excel = new Excel.Excel(ofd.FileName, 1);
excel.Selectsheet("MDR");
var rangeData = excel.ReadUsedRange_obj();
excel.Close();
//coloumns
gridviewDocs.Columns.Clear();
// Get the title values.
for (int col = 5; col <= rangeData.GetUpperBound(1); col++)
{
if (rangeData[2, col] != null)
{
string title = rangeData[2, col].ToString();
gridviewDocs.Columns.Add(title, title);
}
}
//remove first 4 cols and 8 rows from data range
filteredarray = filterArray(rangeData, 8, 4);
// get rows
for (int i = 0; i < filteredarray.GetUpperBound(0); i++)
{
object[] row = new object[filteredarray.GetUpperBound(0)];
for (int j = 0; j < filteredarray.GetUpperBound(1); j++)
{
row[j] = filteredarray[i, j];
}
if (row[0] != null)
{
gridviewDocs.Rows.Add(row);
}
}
}
}
public void Create(Chart chart, string title, Excel.DataTable table, string xColumn, string yColumn, string style, bool user3D)
{
chart.Palette = new Color[] { Color.FromArgb(49, 255, 49), Color.FromArgb(255, 255, 0), Color.FromArgb(255, 99, 49), Color.FromArgb(0, 156, 255),
Color.FromArgb(255, 125, 49), Color.FromArgb(125, 255, 49), Color.FromArgb(0, 255, 49) };
chart.Use3D = user3D;
SeriesCollection mySC = getRandomData(table, xColumn, yColumn);
if (string.IsNullOrEmpty(style) || style == "线形")
{
chart.Type = ChartType.Combo;
mySC = getRandomData2(table, xColumn, yColumn);
}
else if (style == "柱形")
{
chart.Type = ChartType.Combo;
}
else if (style == "金字塔")
{
chart.Type = ChartType.MultipleGrouped;
chart.DefaultSeries.Type = SeriesTypeMultiple.Pyramid;
}
else if (style == "圆锥")
{
chart.Type = ChartType.MultipleGrouped;
chart.DefaultSeries.Type = SeriesTypeMultiple.Cone;
}
chart.Title = title;
if (string.IsNullOrEmpty(style) || style == "线形")
{
chart.DefaultSeries.Type = SeriesType.Line;
}
chart.DefaultElement.ShowValue = true;
chart.PieLabelMode = PieLabelMode.Outside;
chart.ShadingEffectMode = ShadingEffectMode.Three;
chart.NoDataLabel.Text = "没有数据显示";
chart.SeriesCollection.Add(mySC);
}
public void SetOrientation(string fromAddress, string toAddress, Excel.XlOrientation orientation)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Orientation = orientation;
}
public void SetAlign(string fromAddress, string toAddress, Excel.XlHAlign horizontalAlign, Excel.XlVAlign verticalAlign)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.HorizontalAlignment = horizontalAlign;
_excelRange.VerticalAlignment = verticalAlign;
}
public static void KillExcelApplication(Excel.Application excelApp)
{
#region - release excel instance -
IntPtr xlsApplicationProcessID = new IntPtr(0);
GetWindowThreadProcessId(excelApp.Hwnd, ref xlsApplicationProcessID);
System.Diagnostics.Process.GetProcessById(Convert.ToInt32(xlsApplicationProcessID.ToString())).Kill();
#endregion
}
/// <summary>
/// ������������ı߿��������Ҷ�Ϊ��ɫ�����߿�
/// </summary>
/// <param name="strStartCell">��ʼ��Ԫ���ʶ��</param>
/// <param name="strEndCell">������Ԫ���ʶ��</param>
public void SetBorderAll(string strStartCell, string strEndCell, Excel.XlBorderWeight xlEdgeWeight, Excel.XlBorderWeight xlInsideWeight)
{
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = xlEdgeWeight;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = xlEdgeWeight;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = xlEdgeWeight;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = xlEdgeWeight;
string iStart = "", iEnd = "";
Regex r = new Regex(@"(\d+)", RegexOptions.IgnoreCase);
System.Text.RegularExpressions.Match m = r.Match(strStartCell);
if (m.Success)
{
iStart = m.Groups[1].ToString();
}
m = r.Match(strEndCell);
if (m.Success)
{
iEnd = m.Groups[1].ToString();
}
if (iStart == iEnd)
return;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = xlInsideWeight;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = xlInsideWeight;
}
private int DataTable2Excel(System.Data.DataTable dt, int totalCount, int currentCount, Dictionary<string, string> dicColumnNameMapping,
object omissing, Excel.Workbook xlworkbook)
{
try
{
var tmpDataTable = dt.Copy();
#region 表头
if (dicColumnNameMapping != null)
{
ResolveDatatableColumns(tmpDataTable, dicColumnNameMapping);
}
//var dr = tmpDataTable.NewRow();
//for (int i = 0; i < tmpDataTable.Columns.Count; i++)
//{
// dr[i] = tmpDataTable.Columns[i].ColumnName;
//}
//tmpDataTable.Rows.InsertAt(dr, 0);
#endregion
#region 快速导出数据
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Excel.Worksheet xlworksheet = (Excel.Worksheet)xlworkbook.Worksheets.Add(omissing, omissing, 1, omissing);
xlworksheet.Name = tmpDataTable.TableName == null ? xlworksheet.Name : ResolveSheetName(tmpDataTable.TableName);
int colnum = tmpDataTable.Columns.Count;
Excel.Range r = xlworksheet.get_Range("A1", NumToExeclRowStr(colnum) + "1");
object[] objHeader = new object[colnum];
columnIndex = 0;//表头
for (int j = 0; j < columnNum; j++)
{
objHeader[columnIndex] = tmpDataTable.Columns[j].ColumnName;
columnIndex++;
}
r.Value2 = objHeader;
//将DataTable中的数据导入Excel中
for (int f = 0; f < rowNum; f++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
string danyinhao = "";//"'";
objHeader[columnIndex] = danyinhao + tmpDataTable.Rows[f][j].ToString();
columnIndex++;
}
r = xlworksheet.get_Range("A" + (f + 2), NumToExeclRowStr(colnum) + (f + 2));
r.Value2 = objHeader;
currentCount++;
AddProcess(currentCount, totalCount);
}
//r.EntireColumn.WrapText = true;
r.EntireColumn.AutoFit();
#endregion 快速导出数据
}
catch (Exception ex)
{
AddError(ex.Message);
}
return currentCount;
}
public CExportColumnFactory(C1FlexGrid i_fg, Excel.Worksheet i_objWorkSheet)
{
m_fg = i_fg;
m_objWorkSheet = i_objWorkSheet;
}
private Excel.Workbook TryToOpenDocument(Excel.Workbooks workbooks, string filename, bool readOnly, int attempt)
{
try
{
object updateLinks = false;
object vtReadOnly = readOnly;
object format = Excel.XlFileFormat.xlWorkbookNormal;
object vtMissing = System.Reflection.Missing.Value;
return workbooks.Open(filename, updateLinks, vtReadOnly, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing);
}
catch (COMException e)
{
bool isIncorrectPassword = IsPasswordIncorrectError(e.ErrorCode);
if ((attempt > 2) || !isIncorrectPassword)
{
if (isIncorrectPassword)
{
throw new UnauthorizedAccessException("One of the passwords specified was incorrect. Check that Caps Lock is not on", e);
}
throw;
}
return TryToOpenDocument(workbooks, filename, readOnly, attempt + 1);
}
}
private void SaveWorkbook(Excel.Workbook workbook)
{
ApplicationControllerTrace trace = new ApplicationControllerTrace();
try
{
if (!workbook.Saved)
workbook.Save();
}
catch (Exception e)
{
trace.Log(e);
}
}